Define a Schema File - Access Module

The format of a text file is determined by using a schema information file. This file, named schema.ini, is always kept in the same directory as the text data source, and is required for accessing fixed‑length data. Use a schema.ini file when a text table contains DateTime, Currency, Decimal data, or whenever more control is needed to handle table data.

Create a schema.ini file with entries to specify each of the following five characteristics for the table that needs to be created.

Text File Name – The first entry in the schema.ini file must be the name of the text source file enclosed in square brackets.

File Format – The file format option specifies how the text file fields are delimited or the length of the fields in a file that uses a fixed length format, as shown in . If used, the file format settings in the schema.ini file override file‑by‑file settings in the Windows Registry.

Table 18: Schema File Formats
Format specifier	Delimiter Description	Format statement example
Tab Delimited	Fields in the file are delimited by tabs	Format=TabDelimited
CSV Delimited	Fields in the file are delimited by commas (comma‑separated values)	Format=CSVDelimited
Custom Delimited	Fields in the file are delimited by the character specified in the Format statement. All characters are allowed (even the blank character) except the double quote (“)	Format=Delimited(custom charater)
Fixed Length	Fields in the file are a fixed length	Format=FixedLength

Field Names, Widths, and Types – Specify the field names in a character‑delimited text file in one the following ways:

Set the ColNameHeader to True and include the field names in the first row of the table.

For example, the following schema.ini file sets the ColNameHeader to true, keeping the column definitions and names.

[Text_In_Out.txt]

ColNameHeader=True

Format=Delimited(#)

MaxScanRows=25

CharacterSet=ANSI

The ColNameHeader setting overrides the FirstRowHasNames setting.

Set ColNameHeader to False and specify each column by number and designate the column name, data type, and width for fixed‑length types.

For example, the following schema.ini file sets the ColNameHeader to false and redefines the columns.

[Text_In_Out.txt]

Format=Delimited(#)

ColNameHeader=False

MaxScanRows=25

CharacterSet=ANSI

Col1=SOR Char Width 255

Col2=ID_Integer

Col3=CREATE_DT Date

Col4=FREETEXT LongChar

Col5=EOR Char Width 255

The ColNameHeader setting overrides the FirstRowHasNames setting.

Use the MaxScanRows option to indicate how many rows to scan when determining the column types. If MaxScanRows is set to zero, the entire file is scanned.

The MaxScanRows setting in the schema.ini file overrides the setting file by file.

Use the column number (Coln) option. This option is required for fixed‑length files; it is optional for character‑delimited files.

Coln=ColumnName type [width #]

describes each part of the Coln statement. The following example shows the schema.ini entries for two fields. The fields are specified as the fifth and sixth in the row format, with PartName defined as a text field with a width of ten, and PartNumber also defined as a text field, with a width of 30.

Col[5]=PartNumber text width[10]

Col[6]=PartName text width[30]

Table 19: Coln Statement Parameters
Parameter	Description
ColumnName	Text name of the column. If the column name contains embedded spaces, enclose them in double quotation marks.
type	MicroSoft Jet data types: bit byte short long currency single double datetime text memo	ODBC data types char (same as text) float (same as double) integer (same as short) longchar (same as memo) date date format
width	Literal string value that specifies the width of the column (required for fixed‑length files and optional for character‑delimited files.
#	Integer value that designates the width of the column. Required if width is specified.

Character Sets – Two character sets are available: ANSI and OEM. This setting overrides the setting in the Windows Registry. The following code sample sets the character set to ANSI:

CharacterSet=ANSI

Note: If any entry is omitted, the default value in the Windows Control Panel is used.

For more information about the settings in a schema.ini file, go to http://msdn.microsoft.com.