Defining Import Formats for file-based Mappings

When the source is a file-based data load, you define the settings and the import format mapping definition.

You can add a mapping row to the import format definition based on the following options:

Table 14. Import format definition options

Import Definition OptionDescription
SkipThe skip option is used to indicate rows in the input file that should be skipped. For example, rows with no data, negative numbers, or for specific accounts. The specification for a skip row is defined in the same way as that for a data row, and the system looks for the exact textual match for text entered in the expression field in the indicated location in the input file.

The system automatically skips rows in the input file that have “spaces” and “non-numeric” characters in the amount location, so a skip specification is only needed when non-amount data is present in a row of the input file in the same location as the amount. For example, the input file may contain a label named “date.” An entry should be added for a skip row to indicate the starting column of the text “date,” the length of the text, and the exact text to match.

The Skip row option is only available when the file type is fixed, and not delimited.
AttributeThe TDATASEG table includes 13 attribute columns that you can load from the selected input file. You can provide the location of the attribute in the input file by specifying the starting location and length, or plug the value during processing by entering the value for the attribute in the expression field. If the value for the attribute is entered in the expression field, then a starting location and length are not needed.

The attribute fields are generally used to help compose a drill-through URL or for history or documentation needs. You may want to populate the attribute field to support searching and filtering in the Data Load Workbench.

DescriptionThe TDATASEG table includes two description columns, and you can load these columns in the same way as the attribute columns. You can specify the location in the input row that contains a description, or specify an explicit value by entering it in the expression field in the mapping table.
CurrencyFDMEE supports the ability to load data that is of a currency different from the default currency of the selected location. This option enables you to specify the location in the input line that specifies the currency for the related amount field. For the file import format, specify a currency on each row of data, or make sure that a currency is specified in the location that uses the selected import format.

Note:

You may encounter issues with loading data if the currency is not specified correctly.

DimensionFDMEE supports multiple entries for a dimension in the import format when the dimension specification is spread between multiple locations on the same line. This feature enables you to concatenate fields for file-based data. To use this option, select the dimension, start and end positions, and the expression.

  To define an import format for data files with a fixed length:

  1. On the Setup tab, under Integration Setup, select Import Format.

  2. In Import Format, from the Import Format summary grid, select the file.

  3. In the Import Format Detail grid, select the type or row to add from the Add drop-down.

    Available options:

    • Skip Row

    • Currency Row

    • Attribute Row

    • Description Row

    • Dimension Row

  4. In the Start field, specify where on the file the column starts.

  5. In the Length field, enter the length of column.

  6. In the Expression field, enter the expression that overwrites the contents of the column.

    When entering a constant, enter a starting position and length. Use a start position of “1 ” and a length of “1.”

    See Adding Import Expressions.

  7. Click Save.

  To define an import format for delimited data files:

  1. On the Setup tab, under Integration Setup, select Import Format.

  2. In Import Format, from the Import Format summary grid, select a file.

  3. In the Data Load Mappings section, click Add, and select the type or row to add.

    Available options are:

    • Currency Row

    • Attribute Row

    • Description Row

    • Dimension Row

  4. In Field Number, enter the field to import.

    The Expression field ignores the Field Number value when you provide a column expression for a multiple period data load.

  5. In the Expression field, enter the expression that overwrites the contents of the field.

    See Adding Import Expressions.

  6. Click Save.