AX2429

Import Wizard: 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 temp table, 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.

When an import is executed, data is first imported from the source file or table into the temp table. Each entry in the Temp Table Column field becomes a column in the temp table. After performing any mappings or calculations on the temp table (as defined in the transforms), data is imported from the temp table 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.

Setting the destination table

The Destination table field at the top of the tab specifies the table where imported data is to be saved. To specify this table, you can:

Type the name of an existing table into the field, or use the Choose table button to select an existing table.
Enter a variable name in curly brackets. When using this approach, the destination table will be determined dynamically based on the variable value. For more information, see Using variables in imports.

If needed, you can use the create destination table link to create a new table to hold the imported data. This process opens the Create Table wizard and populates the Columns screen with the columns defined in the Mapping tab, using the temp table names and data types. Before using this option, you should first use Auto-generate temp table and destination columns to generate the list of columns based on the source data. Once the new table is created, the destination table and destination columns are automatically set. Only administrators and users with the Administer Tables security permission can see this option and create new tables.

Populating the import column mappings

The top section of the Mapping tab must contain a row for each column of data in the source. Each column in the source data becomes a column in the import temp table, which is then mapped to a column in the destination table.

The easiest way to generate the list of columns is to use the Auto-generate temp table and destination columns button in the top right of the tab. This process reads the source data and creates a row for each column. If a destination table is already specified, the auto-generate process attempts to map the source columns to the appropriate destination columns, based on column name. After auto-generating, you should review all entries to be sure they are correct.

NOTES:

If the import source is set to Prompt for file during execution, you will be prompted to select a file to use for the auto-generated mappings.
If the import source is set to import from multiple files, the mappings are generated based on the first file that is found in the specified folder.
If the import uses a remote data connection, that connection is used to access the specified source file or database.
If the import source is Ellucian, the import columns are automatically generated based on the selected data type on the Source tab. In this case, the only purpose of the auto-generate button is to map these columns to the destination columns.
If the import source or the destination table uses variables, the Variables dialog opens so that you can specify variable values to use when generating the column 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. This option is not available for certain source types that have a fixed set of source columns.
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 . This option is not available for certain source types that have a fixed set of source columns. Keep in mind that all columns in the import source must have a corresponding row in the mapping tab. If you do not need to save the corresponding data to the destination table (either because it is unneeded or because you need to manipulate data using a work column), simply leave the column unmapped.
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.

IMPORTANT: For all import types except Ellucian, the columns must be listed in the order of the columns in the source. For example, the first column of the source data must be row 1 in the mapping grid, the second column must be row 2, and so on. 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.

Each row in the top section of the Mapping tab has the following properties:

Item	Description
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. NOTE: For Ellucian imports, this field displays the source column name without a number, because the columns can be listed in any order.
Temp Table Column	The name of the column to create in the temp table 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 temp table column names. For more information, see Using variables in imports. NOTE: Temp table column names must follow the same rules as normal table columns. See Column naming requirements. Keep in mind that if you auto-generate the column mappings, the temp table 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. The data type must match the data type of the destination column. The data type is automatically selected when you use the auto-generate mappings feature, or when you manually select a destination column. You only need to manually select a data type if the column is only for the temp table and does not have a destination column. EXCEPTION: If you are importing into a table with an identity column, and the identity column is mapped, the type should be set to Integer. For more information, see Importing data into tables with identity columns. 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, select the row and then 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). NOTES: To create a string column with unlimited size, leave the string field blank. You should only do this if you understand the ramifications. If a string column has a destination column, the column in the temp table 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 temp table column. If disabled (the default behavior), then blank values are brought into the temp table 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 temp table column as null values. This setting can impact transform statements that look for "blank" or undefined values in the temp table. 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 temp table does not necessarily mean that the imported data will contain null values. When the temp table 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. NOTE: If the destination table uses a variable and you are manually creating column mappings, the selections in the destination column drop-down lists are based on the first choice listed for the variable. 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). For more information, see Using variables in imports.

Using work columns

You can use work columns in the temp table to perform calculations and data mapping before saving data to the destination table. For example, there may be a column in the source data that you need to manipulate before it can be saved to the destination table. In this case you would do the following:

In the top mapping section, set the destination column for the source column to <not mapped>.
In the work columns section, add a row for the work column, and set the destination column to the appropriate column in the destination table.
On the Transforms tab, define a transform that manipulates the data from the original source column and populates the work column with the resulting desired data.

Any column defined in the Work column mappings section will be created in the temp table. 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.

Each row in the work columns section of the Mapping tab has the following properties:

Item	Description
Temp Table Column	The name of the work column in the temp table. Import variables can be used to define temp table column names. For more information, see Using variables in imports. NOTE: Temp table column names must follow the same rules as normal table columns. See Column naming requirements.
Type	The data type of the work column. See the Type entry in the previous table for more information.
Null	Determines the starting values for work columns in the temp table (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 temp table. 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 temp table does not necessarily mean that the imported data will contain null values. When the temp table 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 temp table 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.

Column naming requirements

When naming temp table columns, the following requirements apply:

Column names can use standard ASCII alphanumeric characters (a-z, 0-9) and the underscore character. No spaces and no other special characters are allowed.
The first character in a column name cannot be a number or an underscore. For example, you can have a column named Plan_09 but you cannot have a column named 09_Plan.
Columns cannot be named Col#, where # is a number, such as Col1, Col2, etc. Other column name constructions with text and numbers are allowed—for example, BUD1 is valid.
Database reserved words should be avoided whenever possible.
Column names are limited to 50 characters by default.

Miscellaneous behavior notes

When importing numeric values, the number of digits in the import source cannot exceed the number of digits allowed by the data type assigned to the temp table column.
If column data is null in the temp table and the destination column is a String column, the null data is converted to an empty string when the data is saved to the destination table (rather than using the column's default value).