16.10 - Define a Schema File - Access Module

Teradata Tools and Utilities Access Module Reference

prodname
Access Module
vrm_release
16.10
created_date
July 2017
category
Programming Reference
featnum
B035-2425-077K

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 the following table. If used, the file format settings in the schema.ini file override file-by-file settings in the Windows Registry.
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 character)
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  #]

      The following table 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]
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
  • Currency Data Formats and Conversions – The following table lists the settings and valid values for formatting currency data.

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

Data Formats and Descriptions 
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$-
  • Formats with one character separation: -1 $, -$ 1, 1 $-, $ 1-, $ -1, or 1- $
  • Formats with parentheses: ($1) or (1$)
  • Formats with parentheses and one character separation: ($ 1) or (1 $)
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:
  • 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 $)
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.
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.