Help with a OleDB connection string for excel files

Solution 1:

Unfortunately, you can't set ImportMixedTypes or TypeGuessRows from the connection string since those settings are defined in the registry. For the ACE OleDb driver, they're stored at

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

in the registry. So, you can simplify your connection string to:

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;""";

Once you set TypeGuessRows to 0 and ImportMixedTypes to Text in the registry, you should get the behavior you are expecting. You might, however, consider using a suitably large number like 1000 instead of zero if you find import performance to be less than ideal.