ADO ConnectionString Excel: Difference between revisions
Jump to navigation
Jump to search
No edit summary |
|||
| Line 1: | Line 1: | ||
{{WIP}} | {{WIP}} | ||
You need to use different providers depending on the Excel version you want to access. | |||
=Provider= | |||
==Microsoft ACE OLEDB== | |||
Connect to Excel 2007 (and later) files with the Xlsx file extension. That is the Office Open XML format with macros disabled. | |||
Provider=Microsoft.ACE.OLEDB.12.0 | |||
==Microsoft Jet OLE DB 4.0== | |||
=Arguments= | =Arguments= | ||
The Provider supports the following arguments: | The Provider supports the following arguments: | ||
;FMT | ;FMT= | ||
: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. | :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. | ||
{| class="wikitable" | :{| class="wikitable" | ||
|- | |- | ||
! Format !! Description !! Schema.ini Syntax !! Connection String Syntax | ! Format !! Description !! Schema.ini Syntax !! Connection String Syntax | ||
| Line 20: | Line 28: | ||
|} | |} | ||
;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= | |||
: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 = | = External links = | ||
Revision as of 14:05, 8 January 2014
This page is still a work in progress.
You need to use different providers depending on the Excel version you want to access.
Provider
Microsoft ACE OLEDB
Connect to Excel 2007 (and later) files with the Xlsx file extension. That is the Office Open XML format with macros disabled.
Provider=Microsoft.ACE.OLEDB.12.0
Microsoft Jet OLE DB 4.0
Arguments
The Provider supports the following arguments:
- FMT=
- 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.
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.
The columns (fields) of the file need to be defined in a file named Schema.ini.Format=FixedLength FMT=FixedLength
- 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.