Defining Fields in Delimited Variable Format Files

This procedure describes how to define fields in the Read from Variable Format File stage for delimited files.

  1. In the Read from Variable Format File stage, click the Fields tab.
  2. Click Regenerate.

    A list of all the fields for each record type is displayed. For each field the following information is displayed:

    Parent
    The tag from the input file indicating the record type in which the field appears. If the tag begins with a number, the tag is prefixed with "NumericTag_". For example, a tag named 100 would become NumericTag_100. The prefix is necessary because dataflow field names cannot begin with a number.
    Field
    The name that will be used in the dataflow for the field. By default, fields are given names in the format <Tag Name>_<Column n>. For example, the first field of record type Owner would be Owner_Column1, the second would be Owner_Column2, and so on.
    Type
    The field's data type.
    Note: The first 50 records are used to generate the fields list. The input file must contain at least two root tags in order to generate a fields list.
  3. If you want to modify the parent/child relationships between the tags:
    1. Click Modify Tag Hierarchy.
    2. Click and drag the tags to define the tag hierarchy you want.
    3. Click OK.
  4. If you want to modify the a field's name or data type, select the field and click Modify.
  5. In the Name field, choose the field you want to add or type the name of the field.

    Typically you will want to replace the default names with meaningful names to represent the data in the field. For example, consider this input data:

    001   Joe,Smith,M,100 Main St,555-234-1290

    This record has a parent tag of 001 and would have these fields created by default:

    • NumericTag_001_Column1: Joe
    • NumericTag_001_Column2: Smith
    • NumericTag_001_Column3: M
    • NumericTag_001_Column4: 100 Main St
    • NumericTag_001_Column5: 555-234-1290

    You would probably want to rename the fields so that the names describe the data. For example:

    • FirstName: Joe
    • LastName: Smith
    • Gender: M
    • AddressLine1: 100 Main St
    • PhoneNumber: 555-234-1290
    Note: You cannot rename list fields. List fields, which contain all the fields for a given record type, always use the tag name from the input file as the field name.
  6. To change a field's data type, select the data type you want in the Type field.

    The following data types are available:

    bigdecimal
    A numeric data type that supports 38 decimal points of precision. Use this data type for data that will be used in mathematical calculations requiring a high degree of precision, especially those involving financial data. The bigdecimal data type supports more precise calculations than the double data type.
    boolean
    A logical type with two values: true and false.
    bytearray
    An array (list) of bytes.
    Note: Bytearray is not supported as an input for a REST service.
    date
    A data type that contains a month, day, and year. For example, 2012-01-30 or January 30, 2012. You can specify a default date format in Spectrum Management Console.
    datetime
    A data type that contains a month, day, year, and hours, minutes, and seconds. For example, 2012/01/30 6:15:00 PM.
    double
    A numeric data type that contains both negative and positive double precision numbers between 2-1074 and (2-2-52)×21023. In E notation, the range of values is -1.79769313486232E+308 to 1.79769313486232E+308.
    float
    A numeric data type that contains both negative and positive single precision numbers between 2-149 and (2-223)×2127. In E notation, the range of values -3.402823E+38 to 3.402823E+38.
    integer
    A numeric data type that contains both negative and positive whole numbers between -231 (-2,147,483,648) and 231-1 (2,147,483,647).
    list
    Strictly speaking, a list is not a data type. However, when a field contains hierarchical data, it is treated as a "list" field. In Spectrum Technology Platform a list is a collection of data consisting of multiple values. For example, a field Names may contain a list of name values. This may be represented in an XML structure as:
    <Names>
    <Name>John Smith</Name>
    <Name>Ann Fowler</Name>
</Names>
    It is important to note that the Spectrum Technology Platform list data type different from the XML schema list data type in that the XML list data type is a simple data type consisting of multiple values, whereas the Spectrum Technology Platform list data type is similar to an XML complex data type.
    long
    A numeric data type that contains both negative and positive whole numbers between -263 (-9,223,372,036,854,775,808) and 263-1 (9,223,372,036,854,775,807).
    string
    A sequence of characters.
    time
    A data type that contains the time of day. For example, 21:15:59 or 9:15:59 PM.
  7. If you selected a date, time, or numeric data type, you can use the default date and time or number format or you can specify a different format for this specific field. The default format is either the system default format that has been set in the type conversion options in Spectrum Management Console, or it is the dataflow's default format specified in the type conversion options in Spectrum Enterprise Designer. The format that is in effect is displayed. To use the default format, leave Default selected. To specify a different format, choose Custom and follow these steps:
    Note: It is important that you choose a date and time format that accurately reflects the data you are reading from the file. For example, if the file contains date data in the format Month/Day/Year but you choose Day/Month/Year, any date calculations you perform in the dataflow, such as sorting by date, will not reflect the correct date. In addition, records may fail type conversion, in which case the failure behavior specified in the type conversion options in Spectrum Management Console or Spectrum Enterprise Designer will take effect.
    1. In the Locale field, select the country whose formatting convention you want to use. Your selection will determine the default values in the Format field. For date data, your selection will also determine the language used when a month is spelled out. For example, if you specify English the first month of the year would be "January" but if you specify French it would be "Janvier."
    2. In the Format field, select the format for the data. The format depends on the data type of the field. A list of the most commonly used formats for the selected locale is provided.

      An example of the selected format is displayed to the right of the Format field.

      You can also specify your own date, time, and number formats if the ones available for selection do not meet your needs. To specify your own date or time format, type the format into the field using the notation described in Date and time patterns. To specify your own number format, type the format into the file using the notation described in Number Patterns.

  8. Click OK.