AX2425

About imports

Imports can be used to import external data into Axiom Software tables, so that the data can be included in reports or used in planning models and calculations. 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:

  • From an external database
  • From a file (delimited or Excel)
  • From special designated sources, such as Intacct

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

Import Wizard

Imports are defined in the Import Wizard dialog. Imports consist of the following:

  • Source tab: Specifies the source of the data to be imported.
  • Variables tab: Defines variables to be used in the import, in order to dynamically change certain import settings.
  • Mapping tab: Specifies the destination table for the import, and maps the import data to columns in the destination table.
  • Transforms tab: Defines transformation statements to be performed on the import data before it is saved to the destination table. Transforms can use SQL or built-in Axiom Software functions.

The Import Wizard also contains the Execute tab, which can be used to execute the import in preview mode for testing, or to execute the actual data import.

The import process

When data is queried from the source file or database, it is first placed in a temporary table known as the temp table. The import can perform actions on the temp table before the data is saved to the destination table in Axiom Software, 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 import 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 using a Scheduler job, you must specify values for the import variables within the Scheduler import task, or use job variables that will populate the import variable values when the job is executed.

  2. The import creates the temp table 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 temp table. You can insert additional columns into the temp table (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 temp table 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 temp table 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 temp table is not mapped, then that data is not saved.

If the import utility is a multiple-file import, then steps 2-4 are performed for each file to be imported. For more information, see How multiple-file imports work.

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 temp table 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.

  • By default, temp table 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.
    • For all other column types, the duplicate rows must have the same values. For example, if a column contains strings or dates, the duplicate rows must have the same string or the same date.

    If instead the optional setting Aggregate rows on final save option is disabled, then no aggregation is performed on the temp table data. In this case, any duplicate rows are invalid and cannot be saved.

    NOTE: Aggregation only applies when importing data to a data table. If the destination table is a reference table, aggregation is not allowed. If duplicate keys are present in the import data, those rows are invalid and cannot be saved to the reference table.

  • Blanks are not allowed in key columns. If a data row in the temp table contains a blank key value, that data row is invalid and cannot be saved.

If any invalid rows are present in the import data, the import behaves as follows:

  • By default, if any invalid data rows are present, then the import is aborted and no data is saved to the destination table.
  • If instead the optional setting Ignore lookup and key errors is enabled, then the save-to-database process ignores the invalid data rows and only valid data rows are saved.