Microsoft.Jet.OLEDB.4.0Standard alternative

Try this one if the one above is not working. Some reports that Excel 2003 need the exta OLEDB; section in the beginning of the string.

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

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

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

SQL syntax "SELECT [Column Name One], [Column Name Two] FROM [Sheet One$]". I.e. excel worksheet name followed by a "$" and wrapped in "[" "]" brackets.

"SELECT * FROM [Sheet1$a5:d]", start picking the data as of row 5 and up to column D.

Check out the [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel] located registry REG_DWORD "TypeGuessRows". That's the key to not letting Excel use only the first 8 rows to guess the columns data type. Set this value to 0 to scan all rows. 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.

If the Excel workbook is protected by a password, you cannot open it for data access, even by supplying the correct password with your connection string. If you try, you receive the following error message: "Could not decrypt file."

A workaround for the "could not decrypt file" problem

Connecting

This connection string (Standard alternative using Microsoft.Jet.OLEDB.4.0) can be used for connections to Excel 2000, Excel 2002, Excel 2003 and Excel 97.

Articlesread all »

Not the right connection string for you?

Start over in the connection string reference index.

You can also get help in the Q&A forums where you can ask your own question.

The kb articles contains how-tos and instructions and you can search for anything if you're not sure were to look.