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.