ADO ConnectionString Excel: Difference between revisions
Jump to navigation
Jump to search
mNo edit summary |
|||
| (11 intermediate revisions by the same user not shown) | |||
| Line 1: | Line 1: | ||
=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: | ||
| Line 8: | Line 18: | ||
: "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 | ;: 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) | |||
::* 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= | ;: 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 = | = External links = | ||
* [http://www.connectionstrings.com/excel/ Connection string for Excel] | * [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]] | [[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.