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, which determines which fields (columns) are extracted from the source system and how data is stored in the staging table.

For detailed information about working with import formats, see Working with Import Formats. For information about working with all data types data loads in import formats, see All Data Types Data Load Process Description.

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

• Skip

• Currency

• Attribute

• Description

• Dimension Row

Table 4-1 Import format definition options

Import Definition Option	Description
Skip	The 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." Add an entry 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 available for both fixed and delimited file types.
Attribute	The TDATASEG table includes 40 attribute columns to which you can import values 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. Each attribute column can accept up to 300 characters. Note: If you integrate an Financial Consolidation and Closeor source with an explicit period mapping type, the system stores Tax Reporting the mapping year (SRCYEAR) and mapping period (SRCPERIOD) in the ATTR2 column and year in ATTR3 columns. For this reason when importing data from Financial Consolidation and Close, attribute columns ATTR2 and ATTR3 should not be used for any other dimension mappings. Similarly when you map a Movement source attribute to any target dimension, the system automatically creates another map for mapping the Movement to the ATTR1 column.
Description	The 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.
Currency	Data Management 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.
Dimension	Data Management 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 numeric data files with a fixed length:

Note:

For information about defining import formats for fixed length all data type data files, see Setting the Import Format Data Types.

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 of row to add from the Add drop-down.

   Available options:

   • Skip Row

   • Currency Row

   • Attribute Row

   • Description Row

   • Dimension Row

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

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

6. In Expression, 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 numeric data files:

Note:

For information about defining import formats for delimited all data type data files, see Setting the Import Format Data Types.

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.