Extended Properties for Excel Connection String

While Reading from Excel, we have two properties which help us to read correct data from excel sheet. Those are

HDR
"HDR=Yes;" indicates that the first row contains columnnames, not data. "HDR=No;" indicates the opposite.

IMEX
"IMEX=1;" tells the driver to always read "intermixed" (numbers, dates, strings etc) data columns as text.
Note that this option might affect excel sheet write access negative.

Below is the Connection string to read from Excel sheet, which shows the usage of the above

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties="Excel 12.0;HDR=Yes;IMEX=1";

I.e. excel worksheet name followed by a "$" and wrapped in "[" "]" brackets.

While reading from excel sheet, by default it uses only the first 8 rows to guess the column datatype.
We can change this behavior by setting the value 0 to the registry key

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel] located registry REG_DWORD "TypeGuessRows".

This might hurt performance.
Please also note that adding the IMEX=1 option might cause the IMEX feature to set in after just 8 rows. Use IMEX=0 instead to be sure to force the registry TypeGuessRows=0 (scan all rows) to work.

Gopikrishna