ADO ConnectionString Excel: Difference between revisions

From AutoIt Wiki
Jump to navigation Jump to search
No edit summary
mNo edit summary
 
(13 intermediate revisions by the same user not shown)
Line 1: Line 1:
{{WIP}}
=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=
=Arguments=
The Provider supports the following arguments:
The Provider supports the following arguments:
; Data Source=
; Data Source=
: Path to the directory where the text file to be processed is stored
: Path & Filename of the Excel workbook you want to process


; Extended Properties="x"
; Extended Properties="x"
: "x" stands for one or multiple of the following extended properties separated by a semicolon:
: "x" stands for one or multiple of the following extended properties separated by a semicolon:


;: Excel 12.0 Xml
;: Excel x
:: Specifies the type of Excel file to process
:: Specifies the type of Excel file to process. This can be
 
::* Excel 5.0: Excel 5 to 95 (xls-files)
;: FMT=
::* Excel 8.0: Excel 97 to 2003 (xls-files)
:: This argument tells ADO which delimiter is used for the file. Although the comma is probably the most widely-used delimiter for text files, it's not the only one. Another popular delimiter is the TAB.
::* Excel 12.0: Excel 2007 or later (xlsb-files, binary format) or 97-2003 Excel workbooks (xls-files)
:{| class="wikitable"
::* Excel 12.0 Xml: Excel 2007 or later (xlsx-files, macros disabled)
|-
::* Excel 12.0 Macro: Excel 2007 or later (xlsm-files, macros enabled)
! Format !! Description !! Schema.ini Syntax !! Connection String Syntax
|-
| Tab Delimited || Fields in the file are separated by tabs || Format=TabDelimited || FMT=TabDelimited
|-
| CSV Delimited || Fields in the file are separated by commas (note that there should not be a space between the comma and the start of the next field name or value) || Format=CSVDelimited|| FMT=CSVDelimited
|-
| Custom Delimited || Fields in the file are separated by some character other than a tab or a comma (with one exception: you can't use the double-quote as a delimiter). For example, the asterisk is used as the delimiter || Format=Delimited(*) || FMT=Delimited(*)
|-
| Fixed-Length || Fields in a file take up a specific number of characters. If a value is too long, "extra" characters are chopped off the end. If a value is too short, blank spaces are appended to it to make it fill out the requisite number of characters. <br> The columns (fields) of the file need to be defined in a file named [[ADO_Schema.ini|Schema.ini]]. || Format=FixedLength || FMT=FixedLength
|}


;: HDR=
;: 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.
:: 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=
;: 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.
:: 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.
 
;: MaxScanRows=
:: Excel does not provide the detailed schema definition of the tables it finds. It needs to scan the rows before deciding the data types of the fields. MaxScanRows specifies the number of cells to be scanned before deciding the data type of the column. By default the value of this is 8. You can specify any value from 1 - 16 for 1 to 16 rows. You can also make the value to 0 so that it searches all existing rows before deciding the data type. You can change the default behaviour of this property by changing the value of [HKLM\Software\Microsoft\Jet\4.0\Engines\Excel\TypeGuessRows] which is 8 by default.
 
;: ReadOnly=
:: You can open a Workbook in readonly mode by specifying ReadOnly=True. By Default Readonly attribute is False, so you can modify data within your Workbook.
 
= External links =
* [http://www.connectionstrings.com/excel/ Connection string for Excel]
* [http://www.codeproject.com/Articles/37055/Working-with-MS-Excel-xls-xlsx-Using-MDAC-and-Oled Working with MS-Excel using MDAC and Oledb]
[[Category:ADO]]

Latest revision as of 16:17, 17 January 2014

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.
MaxScanRows=
Excel does not provide the detailed schema definition of the tables it finds. It needs to scan the rows before deciding the data types of the fields. MaxScanRows specifies the number of cells to be scanned before deciding the data type of the column. By default the value of this is 8. You can specify any value from 1 - 16 for 1 to 16 rows. You can also make the value to 0 so that it searches all existing rows before deciding the data type. You can change the default behaviour of this property by changing the value of [HKLM\Software\Microsoft\Jet\4.0\Engines\Excel\TypeGuessRows] which is 8 by default.
ReadOnly=
You can open a Workbook in readonly mode by specifying ReadOnly=True. By Default Readonly attribute is False, so you can modify data within your Workbook.

External links

Retrieved from "https://www.autoitscript.com/w/index.php?title=ADO_ConnectionString_Excel&oldid=12275"