Providers to use when connecting to Excel
» Microsoft Jet OLE DB 4.0
» .NET Framework Data Provider for OLE DB (OleDbConnection)
» Microsoft Excel ODBC Driver
» .NET Framework Data Provider for ODBC (OdbcConnection)
» .NET xlReader for Microsoft Excel (ExcelConnection)
» .NET Framework Data Provider for OLE DB (OleDbConnection)
» Microsoft Excel ODBC Driver
» .NET Framework Data Provider for ODBC (OdbcConnection)
» .NET xlReader for Microsoft Excel (ExcelConnection)
Microsoft Jet OLE DB 4.0
Type: OLE DB Provider
Usage: Provider=Microsoft.Jet.OLEDB.4.0
Usage: Provider=Microsoft.Jet.OLEDB.4.0
Manufacturer: Microsoft
Standard
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";
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.
OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";
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 * FROM [sheet1$]". 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.
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."
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 * FROM [sheet1$]". 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.
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."
.NET Framework Data Provider for OLE DB
Type: .NET Framework Wrapper Class Library
Usage: System.Data.OleDb.OleDbConnection
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.
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";
Microsoft Excel ODBC Driver
Type: ODBC Driver
Usage: Driver={Microsoft Excel Driver (*.xls)}
Usage: Driver={Microsoft Excel Driver (*.xls)}
Manufacturer: Microsoft
Standard
Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=C:\MyExcel.xls;DefaultDir=c:\mypath;
SQL syntax "SELECT * FROM [sheet1$]". I.e. excel worksheet name followed by a "$" and wrapped in "[" "]" brackets.
.NET Framework Data Provider for ODBC
Type: .NET Framework Wrapper Class Library
Usage: System.Data.Odbc.OdbcConnection
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.
Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=C:\MyExcel.xls;DefaultDir=c:\mypath;
.NET xlReader for Microsoft Excel
Type: .NET Framework Class Library
Usage: VM.xPort.ExcelClient.ExcelConnection
Usage: VM.xPort.ExcelClient.ExcelConnection
Manufacturer: xPortTools
Excel file with header row
Data Source =c:\myExcelFile.xls;HDR=yes;Format=xls;
Excel file without header row
Data Source =c:\myExcelFile.xls;HDR=no;Format=xls;