c# - Can't read file after 8th row EXCEL OLE DB

Question

I can't read this excel file of mine after the 8th row. I am using a OLEDB connection to access it from a c# script task inside a SSIS package :

            strCoExcel = "Provider = Microsoft.ACE.OLEDB.12.0;Mode=Read;Data Source =" + Path.Combine((string)Dts.Variables["PathINPUT"].Value, Dts.Variables["FileNameForEach"].Value.ToString()) + ";Extended Properties="Excel 12.0 Xml;HDR=NO;ImportMixedTypes=Text;TypeGuessRows=0;IMEX=1;"";

 //Gathering data from the renamed sheet
                    OleDbDataAdapter adapter = new OleDbDataAdapter("Select * from [DataTQT$]", coExcel); 
                    DataTable data = new DataTable();
                    adapter.Fill(data);

What is wrong: Some excel files are opened and everything is fine but others do not produce any rows or only 8.

I tried the following: -HDR no/yes -IMEX =1 doesnt change anything -nor do ImportMixedTypes=Text;TypeGuessRows=0 -setting all the cell from the excel file to standard or text field

any help ?

question from:https://stackoverflow.com/questions/66064991/cant-read-file-after-8th-row-excel-ole-db

Answer 1

OK so the final answer for me was not in this piece of code as suspected by others.

This data goes into a SQL database and the columns were not big enough for the data i wanted to insert. I modified the table and now everything works fine.