ADO ConnectionString Excel: Difference between revisions
Jump to navigation
Jump to search
m (→Arguments) |
m (→Arguments) |
||
| Line 23: | Line 23: | ||
::* Excel 5.0: Excel 5 to 95 (xls-files) | ::* Excel 5.0: Excel 5 to 95 (xls-files) | ||
::* Excel 8.0: Excel 97 to 2003 (xls-files) | ::* Excel 8.0: Excel 97 to 2003 (xls-files) | ||
::* Excel 12.0: Excel 2007 or later (xlsb-files, binary format) | ::* Excel 12.0: Excel 2007 or later (xlsb-files, binary format) or 97-2003 Excel workbooks (xls-files) | ||
::* Excel 12.0 Xml: Excel 2007 or later (xlsx-files, macros disabled) | ::* Excel 12.0 Xml: Excel 2007 or later (xlsx-files, macros disabled) | ||
::* Excel 12.0 Macro: Excel 2007 or later (xlsm-files, macros enabled) | ::* Excel 12.0 Macro: Excel 2007 or later (xlsm-files, macros enabled) | ||
Revision as of 11:02, 10 January 2014
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 5.0: Excel 5 to 95 (xls-files)
- Excel 8.0: Excel 97 to 2003 (xls-files)
- Excel 12.0: Excel 2007 or later (xlsb-files, binary format) or 97-2003 Excel workbooks (xls-files)
- Excel 12.0 Xml: Excel 2007 or later (xlsx-files, macros disabled)
- Excel 12.0 Macro: Excel 2007 or later (xlsm-files, macros enabled)
- 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.