ADO ConnectionString Excel: Difference between revisions

From AutoIt Wiki
Jump to navigation Jump to search
Line 22: Line 22:
:: Specifies the type of Excel file to process. This can be
:: Specifies the type of Excel file to process. This can be
::* Excel 8.0: Up to Excel 2003 (*.xls) file formats  
::* Excel 8.0: Up to Excel 2003 (*.xls) file formats  
::* Excel 12.0 Xml: Excel 2007 and later (*.xlsx, *.xlsb or *.xlsm) file formats
::* Excel 12.0: Excel 2007 and later (*.xlsx, *.xlsb or *.xlsm) file formats


;: HDR=
;: HDR=

Revision as of 10:37, 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 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.

External links