Understanding Schema.ini Files

Schema.ini files provide schema information about the records in a text file. Each Schema.ini entry specifies one of five characteristics of the table: the text file’s name; the file format; the field names, widths, and types; the character set; and special data type conversions. The following sections discuss these characteristics.

Specifying the File Name

The first entry in the Schema.ini file is always the name of the text source file enclosed in square brackets. The following example illustrates the entry for the file Sample.txt:

[SAMPLE.TXT]

Specifying the File Format

The Format option in the Schema.ini file specifies the format of the text file. The text IISAM driver can read the format automatically from most character-delimited files. You can use any single character as a delimiter in the file except the double quotation mark. The Format option in Schema.ini overrides the Format setting in the \HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.5\Engines\Text key of the registry on a file-by-file basis. The following table lists the valid values for the Format option.

Format specifier	Table format

TabDelimited	Fields in the file are delimited by tabs.
CSVDelimited	Fields in the file are delimited by commas (comma-separated values).
Delimited(*)	Fields in the file are delimited by asterisks. You can substitute any character for the asterisk except the double quotation mark.
FixedLength	Fields in the file are of a fixed-length.

For example, to specify a format of comma-delimited, you would add the following line to the Schema.ini file:

Format=CSVDelimited

Specifying the Fields

You can specify field names in a character-delimited text file in two ways: either include the field names in the first row of the table and set the ColNameHeader option in Schema.ini to True; or specify each column by number and designate the column name and data type. For fixed-length files, you must specify each column by number and designate the column name, data type, and width.

Note The ColNameHeader option in Schema.ini overrides the FirstRowHasNames setting in the registry on a file-by-file basis.

If you use the ColNameHeader option to specify field names in a character-delimited file, you can also instruct Microsoft Jet to guess the data types of the fields. Use the MaxScanRows option to indicate how many rows Microsoft Jet should scan when guessing the column types. If you set MaxScanRows to zero, Microsoft Jet scans the entire file. The MaxScanRows option in Schema.ini overrides the MaxScanRows setting in the \HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.5\Engines
\Text key of the registry on a file-by-file basis.

The following example shows how to indicate that Microsoft Jet should use the data in the first row of the table to determine field names and should examine the entire file to determine the data types used:

ColNameHeader=True
MaxScanRows=0

The next example shows how to designate fields in a table by using the column number (Coln) option, which is optional for character-delimited files and required for fixed-length files. The example shows the Schema.ini entries for two fields, a 10-character CustomerNumber text field and a 30-character CustomerName text field:

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

The syntax of the Coln entry follows:

Coln=columnname type [Width #]

The following table describes each part of the Coln entry.

Argument	Description

columnname	The text name of the column. If the column name contains embedded spaces, it must be enclosed in double quotation marks.
Type	Value types are: Microsoft Jet data types: Bit (Boolean) Byte Short (Integer) Long Currency Single Double Date/Time Text Memo ODBC Text Driver data types: Char (same as Text) Float (same as Double) Integer (same as Short) LongChar (same as Memo) Date date format
Width	The literal string value `Width`. Indicates that the following number designates the width of the column (optional for character-delimited files, required for fixed-length files).
#	The integer value that designates the width of the column (required if Width is specified).

Selecting a Character Set

You can select from two character sets: ANSI and OEM. The following example shows the Schema.ini entry for an OEM character set. The CharacterSet option in the Schema.ini file overrides the CharacterSet setting in the \HKEY_LOCAL_MACHINE
\SOFTWARE\Microsoft\Jet\3.5\Engines\Text key of the registry on a file-by-file basis. The following example shows the Schema.ini entry that sets the character set to ANSI:

CharacterSet=ANSI

Specifying Data Type Formats and Conversions

The Schema.ini file contains a number of options that you can use to specify how data is converted or displayed when read by Microsoft Jet. The following table lists each of these options.

Option	Description

DateTimeFormat	Can be set to a format string indicating dates and times. This entry should be specified if all Date/Time fields in the import or export are handled with the same format. All of the Microsoft Jet formats except AM and PM are supported. In the absence of a format string, the short date picture and time options in the Windows Control Panel are used.
DecimalSymbol	Can be set to any single character that is used to separate the integer from the fractional part of a number. If this entry is absent, the default value in the Control Panel is used.
NumberDigits	Indicates the number of decimal digits in the fractional portion of a number. If this entry is absent, the default value in the Control Panel is used.
NumberLeadingZeros	Specifies whether a decimal value less than 1 and greater than – 1 should contain leading zeros; this value can either be False (no leading zeros) or True.
CurrencySymbol	Indicates the currency symbol to be used for currency values in the text file. Examples include the dollar sign ($) and Dm. If this entry is absent, the default value in the Control Panel is used.
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 $) If this entry is absent, the default value in the Control Panel is used.

Option	Description

CurrencyDigits	Specifies the number of digits used for the fractional part of a currency amount. If this entry is absent, the default value in the Control Panel is used.
CurrencyNegFormat	Can be one of the following values: ($1) $1 $–1 $1– (1$) –1$ 1–$ 1$– –1 $ –$ 1 1 $– $ 1– $ –1 1– $ ($ 1) (1 $) The dollar sign is shown for the purposes of this example, but it is replaced with the appropriate CurrencySymbol value in the actual program. If this entry is absent, the default value in the Control Panel is used.
CurrencyThousandSymbol	Indicates the single-character symbol to be used for separating currency values in the text file by thousands. If this entry is absent, the default value in the Control Panel is used.
CurrencyDecimalSymbol	Can be set to any single character that is used to separate the whole from the fractional part of a currency amount. If this entry is absent, the default value in the Control Panel is used.