Delimited text files
This following .adoinfo file uses the Microsoft OLE DB Provider for ODBC to read from delimited text files:
<ADODSC>
<ConnectionString>
Provider=MSDASQL;
Driver={Microsoft Access Text Driver (*.txt; *.csv)}
DBQ=
D:\temp\csv;
Extensions=asc,csv,tab,txt;
Persist Security Info=False;
</ConnectionString>
<Table>
QueryResult.csv
</Table>
</ADODSC>
DBQ specifies the folder location for the .csv file. The .csv file name is specified as inner text for the Table element.
Depending on the version of the Microsoft Access Database Engine Redistributable you are using, the Driver name might be:
{Microsoft Access Text Driver (*.txt; *.csv)}
or
{Microsoft Text Driver (*.txt; *.csv)}
Delimiter formats
Although the comma character is the most widely-used delimiter the ODBC Text Driver supports the following delimiter formats, which can be set in the Schema.ini file.
The Schema.ini file must be in the same location as the .csv file.
Tab delimited
File fields are separated by tabs.
Schema.ini syntax
Format = TabDelimited
CSV delimited
File fields 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. For example:
LastName,FirstName
Schema.ini syntax
Format = CSVDelimited
Custom delimited
File fields are separated by some character other than a tab or a comma. You cannot use the double-quote character as a delimiter. The following example uses the asterisk character as the delimiter:
LastName*FirstName Jones*Steve
Schema.ini syntax
Format = Delimited(x) where x represents the character used as the delimiter. For example, when using the asterisk (*) as the delimiter, the Format statement would be:
Format = Delimited(*)
Fixed-length
File fields take up a specific number of characters. If a value is too long, the extra characters are removed. If a value is too short, blank spaces are appended in order to fill the requisite number of characters.
Schema.ini syntax
Format = FixedLength
Specifying column types
When reading from delimited text files, the Microsoft text file driver scans the rows in the file to determine the column data types. To specify how many rows to scan, set the MaxScanRows parameter, either in the Windows Registry or in the extended properties of the text driver connection string.
If the text driver cannot determine the type of each column correctly, you can specify the type for each column in a schema.ini file. You must include all the columns.
Example schema.ini file
The contents of the schema.ini file could resemble the following:
[File_1.txt]
ColNameHeader=True
Format=CSVDelimited
MaxScanRows=0
Col1="StartDate" DateTime
Col2="Status" Text
Col3="Progress" Long
Col4="Finished" Bit
Col5="Location" Double
Col6="Comments" Memo
[File_2.txt]
Format=TabDelimited
[File_3.txt]
Format=Delimited(;)
See also
See also