ADO ConnectionString Excel
This page is still a work in progress.
Providers
Microsoft ACE OLEDB
Provider=Microsoft.ACE.OLEDB.12.0
With Office 2010, there are new drivers, the 2010 Office System Driver, which will be provided in both 32-bit and 64-bit versions. You can use these drivers to let your application connect to Access, Excel and text files in a 64 bit environment utilizing the new 64-bit drivers.
You don't need to buy or install the Office suite, the components are available as a separate download.
Microsoft Jet OLEDB
Provider=Microsoft.Jet.OLEDB.4.0
If you want to work with Excel 2007 or newer file formats then you need to use ACE OLEDB provider instead of Jet. Jet does not recognize new formats.
Arguments
The Provider supports the following arguments:
- Data Source=
- Path & Filename of the Excel workbook you want to process
- Extended Properties="x"
- "x" stands for one or multiple of the following extended properties separated by a semicolon:
- Excel x
- Specifies the type of Excel file to process. This can be
- Excel 8.0: Up to Excel 2003 (*.xls) file formats
- Excel 12.0: Excel 2007 and later (*.xlsx, *.xlsb or *.xlsm) file formats
- HDR=
- Specifying HDR=YES means that the test file contains a header row. A header row simply means that the first row in the text file is a list of fields, with all subsequent rows containing the actual data.
- IMEX=
- Value 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.