15.10 - Define a Schema File - Access Module

Teradata Tools and Utilities Access Module Reference

prodname
Access Module
vrm_release
15.10
category
Programming Reference
featnum
B035-2425-035K

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.

  • 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 Table 18. 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 (commaseparated 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 #]

    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]
     

    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 fixedlength files and optional for characterdelimited 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 ConversionsTable 20 lists the settings and valid values for formatting currency data.
  • Note: If any entry is omitted, the default value in the Windows Control Panel is used.

     

    Table 20: 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$)
  • Format with parentheses and one character separation:
    ($ 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:

  • 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 singlecharacter 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.