Home >

AX2425

About imports

Axiom Software provides robust data import functionality. This topic explains some of the key concepts and requirements for importing data.

Import sources

You can import data into Axiom Software from the following sources:

• Directly from another database
• Delimited files
• Excel files (XLS or XLSX)

You can also import from one table in Axiom Software to another table in Axiom Software.

The import process

When data is queried from the source file or database, it is first placed in a temporary table known as the temptable. You can then perform actions on the temptable before the data is saved to the destination table, such as mapping or data transformations. Use the reserved term {temptable} whenever you want to refer to this temporary table in SQL statements.

When an import is executed, the following processes occur:

1. If any variables are defined on the Variables tab, the user is prompted to select values for these variables. The selected variable values are then substituted for the variable names within the import settings.

   When running the import via Scheduler, you must specify values for the import variables within the Scheduler task, or use job variables that will populate the import variable values when the job is executed.

2. The import creates the temptable by querying data from the source database or by gathering data from the specified file. The settings on the Mapping tab are used to determine the structure of the temptable. You can insert additional columns into the temptable (meaning columns that were not in the source file or table) by adding them as work column mappings.

3. Any transforms defined on the Transforms tab are processed, in the order listed. Transforms can edit the temptable directly, and they can reference information held in other tables in the Axiom Software database. Transforms can also set values for transform variables, which can then be used in subsequent transform steps and in certain import settings.
4. The temptable data is validated and then saved to the destination table, based on the destination column settings on the Mapping tab. If a column in the temptable is not mapped, then that data is not saved.

Import save behavior

The save-to-database process for imports is performed as follows:

• If the destination table has any validated columns (columns that have an assigned lookup column), then the temptable data is validated against these lookup columns before saving. If a data row contains an invalid value, that data row is invalid and cannot be saved. The behavior of the import depends on whether Ignore lookup and key errors is enabled.

  • By default, if any invalid data rows are found, then the import will abort and no data will be saved.
  • If instead the optional setting Ignore lookup and key errors is enabled, then the save-to-database process will ignore the invalid data rows and only valid data rows will be saved.

• By default, temptable data is aggregated before the save is performed. This means that duplicate rows (rows with the same key column values) will be treated as follows:

  • Columns holding numeric data will be summed, unless the destination table is a reference table, in which case the maximum value will be used.
  • For all other column types, the maximum value will be used.

  If instead the optional setting Aggregate rows on final save option is disabled, then no aggregation is performed on the temptable data. If any duplicate rows are found, then the import will abort and no data will be saved.

• Blanks are not allowed in key columns. If a data row in the temptable contains a blank key value, that data row is invalid and cannot be saved. The behavior of the import depends on whether Ignore lookup and key errors is enabled.