Define a Schema File
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.
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
|
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.
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.
The MaxScanRows setting in the schema.ini file overrides the setting file by file.
Coln=ColumnName type [width #]
Table 19 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]
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: |
ODBC data types |
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. |
CharacterSet=ANSI
Note: If any entry is omitted, the default value in the Windows Control Panel is used.
Data Format |
Description |
CurrencyDigits |
Specifies the number of digits in the fractional part of a currency amount. |
CurrencyDecimalSymbol |
Set to any single character that separates the whole from the fractional part of a currency amount. |
CurrencyNegFormat |
Set to one of the following values: ‑1 $, ‑$ 1, 1 $‑, $ 1‑, $ ‑1, or 1‑ $ ($ 1) or (1 $) Note: These examples use the dollar sign as a symbol value. Use the CurrencySymbol format to set the symbol value. |
CurrencyPosFormat |
Set to one of the following values: ($ 1) |
CurrencySymbol |
Specifies the currency symbol to use for currency values in the text file. |
CurrencyThousandSymbol |
Indicates the single‑character symbol to use for separating currency values in the text file by thousands. |
DateTimeFormat |
Specifies a format string used for all dates and times. If set, all the date and time fields in the load or export job are handled with the same format. If not specified, the Windows Control Panel short date picture and time options are used. Note: All Microsoft Jet formats are supported except A.M. and P.M. |
DecimalSymbol |
Set to any single character used to separate the integer from the fractional part of a number. |
NumberDigits |
Provides the number of decimal digits in the fractional portion of a number. |
NumberLeadingZeros |
Set to specify if a decimal value less than one and greater than negative one should contain leading zeros. Valid values are False (no leading zeros) or True (insert leading zeros). |
For more information about the settings in a schema.ini file, go to http://msdn.microsoft.com.