Latest revision as of 09:44, 10 January 2014

Text files accessed via ADO will be read only. ADO does not have a driver that supports writing to or creating text files.

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

Arguments

Both providers supports the following arguments:

Data Source=: Path to the directory where the text file to be processed is stored

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

Text: Specifies that a text file is to be processed

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 text 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.

External links

@@ Line 1: / Line 1: @@
-{{WIP}}
+Text files accessed via ADO will be read only. ADO does not have a driver that supports writing to or creating text files.
-Text files files accessed via ADO will be read only. ADO does not have a driver that supports writing to or creating text files.
+=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
 =Arguments=
-The Provider supports the following arguments:
+Both providers supports the following arguments:
+; Data Source=
+: Path to the directory where the text file to be processed is stored
+; Extended Properties="x"
+: "x" stands for one or multiple of the following extended properties separated by a semicolon:
+;: Text
+:: Specifies that a text file is to be processed
-;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
@@ Line 17: / Line 34: @@
 | 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 Schema.ini. || Format=FixedLength || FMT=FixedLength
+| 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 text 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.
-[[Category:ADO]]
-= Schema.ini=
-Schema.ini files provide schema information about the records in a text file. This, by the way, is optional only when using a delimited file; if you have a fixed-length text file, you must use a Schema.ini file.
-Each Schema.ini entry specifies one of five characteristics of the table:
+= External links =
-* The text file name
+* [http://www.connectionstrings.com/textfile/ Connection string for text files]
-* The file format
+* [http://msdn.microsoft.com/en-us/library/ms974559.aspx Much ADO about Text Files]
-* The field names, widths, and types
-* The character set
-* Special data type conversions
-== Text file name ==
+[[Category:ADO]]
-The first entry in Schema.ini is always the name of the text source file enclosed in square brackets. A Separator.ini can hold information for many files:
- [File_1.txt]
- Format=CSVDelimited
- [File_2.txt]
- Format=TabDelimited
-== File format ==
-The Format option in Schema.ini specifies the format of the text file. You can use any single character as a delimiter in the file except the double quotation mark ("). The Format setting in Schema.ini overrides the setting in the Windows Registry, file by file. The list of valid values can be found [[ADO ConnectionString TextFile#Arguments|here]].
-In this file, you define the separator to be used (optional) and define the columns (fields) using syntax similar to this:
- Coln=Fieldname Datatype <additional information>
-Example:
- Col1=FirstName Text Width 7
- Col2=LastName Text Width 10
- Col3=ID Text Integer 3
-After you create the Schema.ini file, make sure you indicate in your script that the file does not use a header row. To do that, just set the HDR parameter to No.
-'''Important:''' Schema.ini must be in the same folder as your text file.
-== Delimiter ==
-With a delimited file, this often involves nothing more than telling ADO what the delimiter is:
- [MyLog.txt]
- Format=TabDelimited
-== Data Types ==
-You can use the following data types:
-* Text: Text Width n
-* Short
-* Long
-* Currency
-* Single
-* Double
-* DateTime
-* Memo

ADO ConnectionString TextFile: Difference between revisions