Customizing the Schema.ini File

To read, import, or export text data, create a Schema.ini file in addition to including the Text ISAM information in the .ini file. The Schema.ini file contains the specifics of a text data source including: how the text file is formatted, how it is read at import time, and the default export format for files.

The following examples show the layout for a fixed-width file, Filename.txt.

[Filename.txt]
ColNameHeader=False
Format=FixedLength
MaxScanRows=25
CharacterSet=OEM
Col1=columnname Char Width 24
Col2=columnname2 Date Width 9
Col3=columnname7 Float Width 10
Col4=columnname8 Integer Width 10
Col5=columnname9 LongChar Width 10

Similarly, the format for a delimited file is specified as follows:

[Delimit.txt]
ColNameHeader=True
Format=Delimited(!)
MaxScanRows=0
CharacterSet=OEM
Col1=username char width 50
Col2=dateofbirth Date width 9

If you are exporting data into a delimited text file, specify the format for that file as well.

[Export: My Special Export]
ColNameHeader=True
Format=TabDelimited
MaxScanRows=25
CharacterSet=OEM
DateTimeFormat=mm.dd.yy.hh.mm.ss
CurrencySymbol=Dm
CurrencyPosFormat=0
CurrencyDigits=2
CurrencyNegFormat=0
CurrencyThousandSymbol=,
CurrencyDecimalSymbol=.
DecimalSymbol=,
NumberDigits=2
NumberLeadingZeros=0

The My Special Export example (above) refers to a specific export option; you can specify any variation of export options at connect time. This last example also corresponds to a data source name (DSN) that can be optionally passed at connect time. All three format sections can be included in the same .ini file.

The Microsoft Jet database engine uses the Schema.ini entries as follows.

Entry Description
ColNameHeader Can be set to either True (indicating that the first record of data specifies the column names) or False.
Format Can be set to one of the following values: TabDelimited, CSVDelimited, Delimited (<single character>), or FixedLength. The delimiter specified for the Delimited file format can be any single character except a double quotation mark (").
MaxScanRows Indicates the number of rows to be scanned when guessing the column data types. If this is set to 0, the entire file is searched.
CharacterSet Can be set to OEM or ANSI, indicating whether the source file is written using an OEM or ANSI code page.
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/export are handled with the same format. All Microsoft Jet database engine formats are supported except AM and PM. In the absence of a format string, the Windows Control Panel Short Date Picture and Time options are used.
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 Windows 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 Windows Control Panel is used.
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 Windows Control Panel is used.
CurrencyNegFormat Can be one of the values listed below. The dollar sign is shown below for purposes of this example, but it should be replaced with the appropriate CurrencySymbol value in the actual program. If this entry is absent, the default value in the Windows Control Panel is used.

($1)

–$1

$–1

$1–

(1$)

–1$

1–$

1$–

–1 $

–$ 1

1 $–

$ 1–

$ –1

1– $

($ 1)

(1 $)
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 Windows 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 Windows Control Panel is 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 Windows 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 Windows 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.
Col1, Col2, ... Lists the columns in the text file to be read. The format of this entry should be:
  • Coln = columnName type [ Width # ]

  • columnName: Column names with embedded spaces should be enclosed in quotation marks.

  • type: Can be Bit, Byte, Short, Long, Currency, Single, Double, DateTime, Text, or Memo.

In addition, the following ODBC Text Driver types are supported:

  • Char (same as Text)

  • Float (same as Double)

  • Integer (same as Short)

  • LongChar (same as Memo)

  • Date date format

Note When you change Windows Registry settings, you must exit and then restart the database engine for the new settings to take effect.