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 ?

Impossible to tell without any example how the Excel file (which doesn't work) looks... anyways, you can try and add ;Empty Text Mode=NullAsEmpty to the connection string — Tyron78
This code is incomplete. Where is the bit where you open the OleDbConnection? Also, the ACE.OLEDB driver ignores ImportMixedTypes and TypeGuessRows. Those worked on the JET driver but are now controlled through the registry. — Ciarán

bdxpo bdxpo · Accepted Answer · 2021-02-08T10:37:43

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.

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

1 Answers