ADO ConnectionString TextFile

From AutoIt Wiki
Revision as of 12:45, 5 January 2014 by Rt01 (talk | contribs)
Jump to navigation Jump to search

This page is still a work in progress.

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

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.

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.

Important: Schema.ini must be in the same folder as your text file.

Each Schema.ini entry specifies one of five characteristics of the table:

  • The text file name
  • The file format
  • The field names, widths, and types
  • The character set
  • Special data type conversions

Text file name

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

Fields

You can specify fields by column number, column name, data type and width.

Parameter Description
Coln The literal string value "Col" plus the number of the column starting with 1.
ColumnName The text name of the column. If the column name contains embedded spaces, you must enclose it in double quotation marks.
type Data types are as follows (Microsoft Jet data types):
  • Bit
  • Byte
  • Short
  • Long
  • Currency
  • Single
  • Double
  • DateTime
  • Text
  • Memo
Width The literal string value "Width". Indicates that the following number designates the width of the column
# The integer value that designates the width of the column (required if Width is specified).

Example:

Col1=CustomerNumber Text Width 10
Col2=CustomerName Text Width 30

Character Set

You can select from two character sets: ANSI and OEM. The CharacterSet setting in Schema.ini overrides the setting in the Windows Registry, file by file.

Conversions and Data Type Formats

The Schema.ini file contains several options that you can use to specify how data is converted or displayed. The following table lists each of these options.

Option Description
DateTimeFormat Can be set to a format string that indicates dates and times. You should specify this entry if all date/time fields in the import/export are handled with the same format. All Microsoft Jet formats except A.M. and P.M. are supported. If there is no format string, the Windows Control Panel short date picture and time options are used.
DecimalSymbol Can be set to any single character that is used to separate the integer from the fractional part of a number.
NumberDigits Indicates the number of decimal digits in the fractional portion of a number.
NumberLeadingZeros Specifies whether a decimal value less than 1 and more than –1 should contain leading zeros; this value can be either False (no leading zeros) or True.
CurrencySymbol Indicates the currency symbol that can be used for currency values in the text file. Examples include the dollar sign ($) and Dm.
CurrencyPosFormat Can be set to any of the following values:
  • Currency symbol prefix with no separation ($1)
  • Currency symbol suffix with no separation (1$)
  • Currency symbol prefix with one character separation ($ 1)
  • Currency symbol suffix with one character separation (1 $)
CurrencyDigits Specifies the number of digits used for the fractional part of a currency amount.
CurrencyNegFormat Can be one of the following values:
  • ($1)
  • –$1
  • $–1
  • $1–
  • (1$)
  • –1$
  • 1–$
  • 1$–
  • –1 $
  • –$ 1
  • 1 $–
  • $ 1–
  • $ –1
  • 1– $
  • ($ 1)
  • (1 $)


This example shows the dollar sign, but you should replace it with the appropriate CurrencySymbol value in the actual program.

CurrencyThousandSymbol Indicates the single-character symbol that can be used for separating currency values in the text file by thousands.
CurrencyDecimalSymbol Can be set to any single character that is used to separate the whole from the fractional part of a currency amount.