OleDB connection string help for excel files

Question

The problem I am facing is that the data adapter only scans the first row in each column to determine the data type. In my case, the first column “SKU” is the numbers for the first 500 lines, then I have SKUs that are mixed numbers and letters. So, what ends, the rows in the SKU column remain empty, but I still get different information for each row in the column.

I believe this is the connection string that controls this, and it should work with my current settings, but it is not.

Connection string:

conn.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Nick\Desktop\Pricing2.xlsx" + @";Extended Properties=""Excel 12.0 Xml;HDR=Yes;IMEX=1;ImportMixedTypes=Text;TypeGuessRows=0"""; 

 ImportMixedTypes=Text;TypeGuessRows=0 

There should be important keywords, look at 0 lines and just use text as value types for everything.

The "Bandaid" I put this on is to make the first row in the spreadsheet a mixture of letters and numbers and, in particular, leave that row in my query.

Answer 1

Unfortunately, you cannot install ImportMixedTypes or TypeGuessRows from the connection string, because these parameters are defined in the registry. For the ACE OleDb driver, they are stored in

 HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Excel 

in the registry. This way you can simplify the connection string:

 conn.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Nick\Desktop\Pricing2.xlsx;Extended Properties=""Excel 12.0 Xml;HDR=Yes;IMEX=1;"""; 

After installing in the registry from TypeGuessRows to 0 and ImportMixedTypes to Text you should get the behavior you expect. However, you can use a suitable amount, for example 1000 instead of zero, if you find that the import performance will be less than ideal.