schema.ini (Microsoft OLE DB Provider for Microsoft Access database engine)

A schema.ini file is needed in all but the most rudimentary cases when a CSV file or other text file is read with the OLE DB provider for Microsoft Access database engine. The file specifies some characteristics of the file(s) to be read.

Options

colNameHeader

Setting colNameHeader to true specifies that a the text file has a header with the names of the data columns. Setting it to false indicates that there is no header at all.

The default is taken from the registry value FirstRowHasNames under the registry key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Text.

colNameHeader=[true|false]

format

The format option specifies how field values are separated one from another. They can be separated by a specific character or by using a fixed width for each column

The following three lines shows how to specify field values that are separated by a character (tabulator, comma, bar). delimited=(X) allows to specifiy an arbitrary character as delimiter (except ").

format=tabDelimited
format=CSVDelimited
format=delimited(|)

If the columns have a fixed width, format needs to be set to fixedLength.

format=fixedLength

textDelimiter

textDelimiter=X specifies the character with which a text can be enclosed that contains characters that are also used as field-delimiter.

For example, the value He said: yes in a delimited file whose field-delimiter is the colon (:) might be enclosed with a single apostrophe ('), like so:

foo:some text
baz:more text
bar:'he said:yes'

In order to parse such a file, the following two options need to be set:

format=delimited(:)
textDelimiter='

The default of textDelimiter is the double apostrophe (").

MaxScanRows

…

decimalSymbol

…

numberLeadingZeros

numberLeadingZeros specifies if numbers in the range -1 through 1 have a zero to the left side of the decimal point (or more accurately: decimal symbol).

numberLeadingZeros=[true|false]

dateTimeFormat

dateTimeFormat specifies the expcected format for date and time values.

Note espacially that minutes are specified with nn (not mm because mm indicates the month).

dateTimeFormat=yyyy-mm-dd hh:nn:ss

When a dateTimeFormat is missing, the format is taken from the control panel item region.

Column names

For CSV files without a header, the column names are named F1, F2 …

Spcifying data types for columns

The data types for a CSV file's column can be explicitly stated like so:

[data.csv]
col1=id   long 
col2=num  long
col3=flt  double
col4=txt  text
col5=dt   dateTime

It seems that column names need to be specified even if the colNameHeader option was set to true, and that the names of the column specification overwrite the names of the header.

Character Set

A CSV file's character set can be either specified to be ANSI or OEM:

[data.csv]
characterSet = ANSI  ; or OEM

Comments

Lines in a schema.ini can be commented with a hash sign (#) or a semicolon (;).

The hash sign or semicolon needs to be the first non-whitespace character, otherwise, it will cause an error.

Thus, the following is correct:

; Customer data
[customers.csv]
# customers is delivered with a header
colNameHeader=true

Either of the following two lines is not correct and causes an error:

[customers.csv]     ; Customer data
colNameHeader=true  # customers is delivered with a header