Data Module > Import & export

Data import into relational databases is complex as a result of the numerous constraints inherent to maintain data consistency.

To manage these constraints, most data imports use data mappings that transform data into formats that can be accepted by the database.

Data_External_Import_AGS_Dialog

Once fully developed, user's typically only need to select files and the appropriate data mapping, before clicking the Import button.

A few formats allow direct mappings (i.e. XML, Excel) where the file being imported is expected to exactly match the database structure.

Data_External_Import_XML_Dialog

Forms

Definition

Data_External_Import_Dialog

File - defines the file(s) that will be imported.

Mapping - specifies the import mode:

Direct mode is only an option for of Microsoft Excel and XML, and is provided as a means to transfer data between databases with the exact same structure (i.e. between the office and field):

Mapped mode uses the selected data mapping to transform data from one structure to another during the import process (i.e. different table and/or columns names, perform data checks, unit conversion):

Options - provides additional options.

•Full Log - shows a more detailed log of the import process to better understand what is happening.

•Test Mode - runs the entire import process, but does not commit any data to the database. This is useful when developing data mappings as it's possible to make a change to the mapping and retest without having to delete data.

Force Save - controls how the import transaction is handled.

•Unchecked (default) - if for some reason the import fails the process will immediately stop and no data will be imported, even for tables already processed without issues.

•Checked - if for some reason the import fails the process will continue to import data into all of the defined tables and commit the changes to the database.

Import - starts the import process and shows the process form.

Close - closes the form and refreshes the data.

Progress

Data_External_Import_Progress

Auto-close - automatically closes the process form once the import has finished.

Cancel / Close - stops the import process if running, or closes the window if complete.

Note

See reading logs to better understand the meaning of this information.

Process logs are automatically saved into a folder named:

•LOGS in the same folder as the import file

•C:\ProgramData\Geroc\LOG\Import

Both of these can be disabled in the user settings.xml file.

Details

The import process uses the SQL Bulk Copy method, as this process is significantly faster than row-by-row processing. This method attempts to insert the entire contents of a source table (i.e. external file) into the destination table (i.e. database). If an error occurs, no records will be inserted, and any attempt to insert data into child tables will fail also.

All imports are run inside a transaction, meaning that when a failure occurs all changes are discarded by default. This behaviour can be overridden so any data that can be imported will be, by selecting the Force Save option.

Importing data into a database requires some knowledge of the database setup, as there are many constraints on what and when data can be added.

The following requirements exist:

•Tables must be filled in the correct order (this is handled by the software).

•All names in the source file must be exactly the same as those in the database, or after data mapping.

•All key, index and required columns in a database table must be present in the source file, or after data mapping.

•All data must be compatible with the data types of the associated column, or after data mapping.

•All values used in lookup columns should match those in the lookup table, or after data mapping. If not, the user will be shown the Lookup Conflict Resolver.

•Data can be imported into any public table.

•Any project codes in the source file are overridden with the active project code, except if using the Bulk Import system.

•Record update is not supported.

How To: Import a file

Click the file type that matches the file to import:

Data_Interface_Ribbon_Page_External

Select files to import, and click Open:

Data_External_Import_OpenFileDialog

Select a data mapping (if required) and any other options, then click the Import button:

Data_External_Import_Import

If the Conflict Resolver is shown, fix any issues:

Data_External_Resolver_Resolved

Review the log for success or failure, and any other warnings or errors:

Data_External_Import_Progress

How To: Use the conflict resolver

When a conflict is detected on a lookup column, the user is shown the Conflict Resolver:

Data_External_Resolver

Select replacement codes from the destination (i.e. database):

Data_External_Resolver_Resolved

Note

If the source value does not relate to any destination values, Division Administrator and System Administrator are given the option to add codes to the lookup:

Data_External_Resolver_AddCode

Once all conflicts are resolved the import will continue.