Connection strings for Excel
Microsoft Jet OLE DB 4.0
Usage Provider=Microsoft.Jet.OLEDB.4.0
Manufacturer Microsoft
Standard
Standard 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.
Important note!
The quota " in the string needs to be escaped using your language specific escape syntax.
c#, c++ \"
VB6, VBScript ""
xml (web.config etc) "
or maybe use a single quota '.
"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.
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."
ACE OLEDB 12.0
Usage Provider=Microsoft.ACE.OLEDB.12.0
Manufacturer Microsoft
Excel 97-2003 Xls files with ACE OLEDB 12.0
You can use this connection string to use the Office 2007 OLEDB driver (ACE 12.0) to connect to older 97-2003 Excel workbooks.
"HDR=Yes;" indicates that the first row contains columnnames, not data. "HDR=No;" indicates the opposite.
.NET Framework Data Provider for OLE DB
Usage System.Data.OleDb.OleDbConnection
Manufacturer Microsoft
Bridging to Jet OLE DB 4.0
This is just one connection string sample for the wrapping OleDbConnection class that calls the underlying OLEDB provider. See respective OLE DB provider for more connection strings to use with this class.
Microsoft Excel ODBC Driver
Usage Driver={Microsoft Excel Driver (*.xls)}
Manufacturer Microsoft
Standard
SQL syntax "SELECT [Column Name One], [Column Name Two] FROM [Sheet One$]". I.e. excel worksheet name followed by a "$" and wrapped in "[" "]" brackets.
Specify ReadOnly
[Microsoft][ODBC Excel Driver] Operation must use an updateable query. Use this connection string to avoid the error.
ReadOnly = 0 specifies the connection to be updateable.
Microsoft Excel 2007 ODBC Driver
Usage Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)
Manufacturer Microsoft
.NET Framework Data Provider for ODBC
Usage System.Data.Odbc.OdbcConnection
Manufacturer Microsoft
Bridging to Microsoft Excel ODBC Driver
This is just one connection string sample for the wrapping OdbcConnection class that calls the underlying ODBC Driver. See respective ODBC driver for more connection strings to use with this class.
.NET xlReader for Microsoft Excel
Usage VM.xPort.ExcelClient.ExcelConnection
Manufacturer xPortTools