Mapping tab

The Mapping tab of the Import Wizard maps the import data to the destination table. In this tab, you define:

The columns to be created in the temptable, including any "work columns" to be used for interim calculations only.
The destination table for the imported data.
The destination columns for the imported data.

Data is first imported from the source file or table into the temptable. Each entry in the Temp Table Column field becomes a column in the temptable. After performing any mappings or calculations on the temptable (as defined in the transforms), data is imported from the temptable to the destination table. The entries in the Destination Column field determine whether a column of data is imported to the destination table, and to which column in the destination table.

This tab has two sections. The top section is for mapping imported data columns from the source file or table, and the bottom section is for work columns.

Variables can be used in the Destination table field, the Temp Table Column field, and the Destination Column field. See Using variables in imports.

Imported column mappings

The top section of the Mapping tab must contain a row for each column of data to be imported.

The columns must be listed in the order of the columns in the source. For example, the first column of the source file must be row 1 in the mapping grid, the second column must be row 2, and so on.

You can click Auto-generate temp table and destination columns to auto-generate an entry for each column in the source table or file. The import source settings must be complete and accurate to do this, and you must have specified the destination table. Axiom Software will attempt to auto-populate the data types and destination columns for each column of data in the source table or file. After auto-generating, review all entries to be sure they are correct.

NOTE: If the import source is set to Prompt for file during execution, then when you click Auto-generate, you will be prompted to select a file to use for the auto-generated mappings.

You can also define columns manually, or edit the settings after auto-generating:

To add a row, click Add imported column mapping . The new row is added below the row that is currently selected.
To edit a row, type changes into the grid.
To delete a row, select the row that you want to delete and then click Remove mapping .
To change the order of rows, select the row that you want to move and then click the arrow icons to move it up or down.

NOTE: If you perform any action that changes the existing order of rows, this may cause data to be imported incorrectly. After making manual adjustments to the grid, check to make sure that each mapping row in the grid matches up with the appropriate source column.

Mapping settings

Item	Description
Destination table	The destination table for the imported data. You can also use an import variable if you want the destination table to be determined by a variable. NOTE: Tables with identity key columns cannot be destination tables, because it is not supported to import data into an identity column or list it as a destination column.
Source Column	The number of the corresponding source column in the import source. The first row in the grid corresponds with the first column in the source, and so on. These numbers cannot be edited. If you auto-generate the mappings, the name of the source column will display next to the number for reference. Names are only displayed when the import source is a database or a file with a header row.
Temp Table Column	The name of the column to create in the temptable to hold the imported data. The column name does not have to match the name of the column in the source. The data is imported in column order, not by name. Import variables can be used to define temptable column names. NOTE: Temptable column names must follow the same rules as normal table columns. See Table and column naming requirements. Keep in mind that if you auto-generate the column mappings, the temptable column names are based on the headers in the source. These headers may contain spaces or other invalid naming conventions that should be manually corrected.
Type	The data type of the column. For more information on the available column data types, see Column properties. If the data type is String, you must also specify the maximum length of the string field. This entry should match the string length of the destination column so that data is saved appropriately. To specify the string length, click the browse button (...) to the right of the field. In the Edit String Length dialog, type the string length (from 1 to 4000). The string length displays in parenthesis after the data type. For example: String (200). The type must match the type of the destination column. The type is automatically selected when you use the auto-generate feature or when you manually select a destination column. You only need to manually select a type if the column is only for the temptable and does not have a destination column. NOTES: To create a string column with unlimited size, leave the string field blank. You should only do this if you understand the ramifications. See Column properties. When importing numeric values, the number of digits in the import source cannot exceed the number of digits allowed by the data type. If a string column has a destination column, the column in the temptable automatically matches the Unicode status of the destination column (True/False). However, if the string column does not have a destination column, Unicode is assumed as True.
Nulls	Determines how blank values in the import source are brought into the temptable column. If disabled (the default behavior), then blank values are brought into the temptable column as the default value for the specified destination column. If the column is unmapped, then the system default value as defined for the column type is used. If enabled, then blank values are brought into the temptable column as null values. This setting can impact transform statements that look for "blank" or undefined values in the temptable. You should set this as appropriate depending on whether you want to check for null values or the default value for the affected column. The presence of null values in the temptable does not necessarily mean that the imported data will contain null values. When the temptable values are imported into the destination table, the default values for the destination columns are always applied to any null values at that point. However, if the default value for a destination column is null, then the null values will be retained.
Destination Column	The name of the column in the destination table where you want this data to be saved. You can type the name of the column directly, or use the drop-down list to select a column name. The data type of each column is displayed in the drop-down list for reference. The data type of the destination column must match the data type specified in the mapping grid. If this column is not intended to be saved to the destination table, select <not mapped>. "Not mapped" is the default if you do not explicitly select a destination column. Import variables or transform variables can be used to define destination column names. If you are using a variable for a destination column and you want to set the column to not mapped, the value of the variable must be blank for import variables and empty string ('') for transform variables).

Work column mappings

You can use work columns in the temptable to perform calculations and mapping on the data before saving it to the destination table. Any column defined in the Work column mappings section will be created in the temptable. If a destination column is specified, the data will be saved to the destination table.

To add a row, click Add work column mapping .
To edit a row, type changes into the grid.
To delete a row, select the row that you want to delete and then click Remove mapping .
To change the order of rows, select the row that you want to move and then click the arrow icons to move it up or down.

Mapping settings

Item	Description
Temp Table Column	The name of the work column. Import variables can be used to define temptable column names. Temptable column names must follow the same rules as normal table columns. See Table and column naming requirements.
Type	The data type of the work column. For more information on the available column data types, see Column properties. See the Type entry in the previous table for more information.
Null	Determines the starting values for work columns in the temptable (before transforms are applied). If disabled (the default behavior), then the work column starts with the default value for the specified destination column. If the column is unmapped, then the system default value as defined for the column type is used. If enabled, then the work column starts with null values. This setting can impact transform statements that look for "blank" or undefined values in the temptable. You should set this as appropriate depending on whether you want to check for null values or the default value for the affected column. The presence of null values in the temptable does not necessarily mean that the imported data will contain null values. When the temptable values are imported into the destination table, the default values for the destination columns are always applied to any null values at that point. However, if the default value for a destination column is null, then the null values will be retained.
Destination Column	The name of the column in the destination table where you want this data to be saved. If this column is used only for calculations in the temptable and is not intended to be saved to the destination table, select <not mapped>. See the Destination Column entry in the previous table for more information.

Remarks

If you select to auto-generate the column mappings and either the import source or the destination table uses variables, then the Variables dialog opens so that you can specify variable values to use to generate the mappings.
If the destination table uses a variable and you are manually creating column mappings, then the selections in the destination column drop-down lists are based on the first choice listed for the variable.

Get more information and training resources: www.kaufmanhall.com

Was this topic helpful?

mapping_tab.htm