Import / Export

At times you may need to export or import data to or from another system. This is done via Excel spreadsheets and shapefiles.

Import

In order to import data users must populate a spreadsheet for each context with the import data and upload this. The relationship of records in the import data to the parent records under which they are to be imported is determined by specific columns which must be present in the import data. Domain values (VT values) can be automatically populated during the import. Spatial data can be associated with each record by uploading a zipped shapefile. The user can associate the records in the shape file to the records in the spreadsheet by specifying which column in the shapefile relates to the import records in the spreadsheet. In order to simplify this process the system can automatically generate a template spreadsheet with all columns for a context type and which will include the columns required to relate the import records to existing data. The import runs in the background so a user can start the process and then leave the import screen to do other work and return when the process completes.

Process

• Navigate to an existing record of the context type in question or to the context type in search view.

• Click the import button above the grid - this opens up the import tool.

• Optionally download a template spreadsheet for this context.

• Populate the spreadsheet with the import data. You will need to do any data mapping to transform it to match the required format. Note that the importer ignores all columns which do not match columns in the application.

• Populate the "Import FK" column: Values in this column identify the parent records that the import data will be located under. You can use any values you wish as long as they uniquely identify the parent records. This same value is set in the "Import PK" field of the parent record in existing data and the "Import FK" field of the import data. Note that records can be imported to be under various parents in the same import. The place from which the import is invoked determines the context type but records imported for that context type are not restricted to the parent from this location.

• Populate the "Import PK" column: Values in this column identify the import records themselves. They must be unique. When the importer encounters a record where the "Import PK" value already exists in the database it will update that record. If no records with that value exist it will insert the record.
  Example: consider a situation where there are Tracts and these Tracts contain Stands, Stands among other things contains a column "Stand Number":

  • • Tract: "First Tract"

      1. Stand: 5

      2. Stand: 6

    • Tract: "Second Tract"

      1. Stand: 7

      2. Stand: 8

To import records into these two tracts, in the existing data, assign each tract an "Import PK" if they have none already: "First Tract" gets "T0001" and "Second Tract" gets "T0002". In the spreadsheet, assign values as follows:

• Upload the spreadsheet. 

• Optionally upload a zipped shape file. Use the dropdown to select which field in the shape file corresponds to the "Import PK" column. The values in this field must match the values in the "Import PK" column in the spreadsheet to allow the importer to relate the data records to the shape records.

• Click "Import". 

• If no shapefile is specified:

• The application will begin processing the records and show the progress and status of each record. While the records are being processed you can close the import tool - it will continue to run in the background while you do other things. To check on the import status, click the same "Import" button as before and the import tool will reappear.

• The importer will check all columns referring to domain tables (VTs) for values which are not already represented in the domain table. If the domain table is configured to be user-updatable, the importer will give the option to automatically add these values. If not, records with domain values which are not already configured will fail to import.

• If a shapefile is specified: The importer will process all the records from the spreadsheet. Then, as a separate step it will process all the spatial data from the shapefile and link it to the already imported tabular records. 

• When the import finishes processing all the records in the spreadsheet, a status will appear showing which records were imported successfully and which failed. In addition, the number of records where the shape data failed to import is shown. A link for details on failed records is provided by clicking "View Errors". This is a spreadsheet which contains the failed records along with a message as to why each one failed. To streamline the process, this spreadsheet can be directly modified to fix problems and re-imported. The importer will ignore the column containing the error details. In addition, for each import, a record is added to the activity stream summarizing the operation.

Restrictions

• Import operates at a context level. Because of this, only one import can run at a time. Any import can be canceled. Records already completed will not be rolled-back. In addition, an active, completed or canceled import must be archived before another import can be started. Archiving an import also frees up database space occupied by data required while running the import.

• The context type being imported must have IMPORTERPK and IMPORTERFK fields.

• Import can only be invoked via the search view or records which are not locked via the LOCK_FIELDS bizrules.

• Only Excel spreadsheets and zipped shape files can be imported.

• The user doing the import must be a member of TFM_ADMIN_ROLE.

• The user doing the import must be a member of only one subscriber.

• The user doing the import must have credentials to write to the context and create records as descendants of the specified parent records.

• Top level contexts (contexts with no parents) cannot be imported.

• The following restrictions determine if specific records can be imported: (if these fail, the record fails to import but the process as a whole continues):

  • All Import FK values (parent keys) must indicate valid records and be unique.

  • All Import PK values must be unique.

  • All domain values must be either already present in the database or configured to be subscriber specific and auto-imported.

  • All fields must contain valid values, or blank if they are not required.

  • All required fields must be populated.

  • All bizrules and bizactions must pass validation.

File Format

A template for the import spreadsheet can be downloaded as part of the import process. This will contain all columns required to complete the import. This spreadsheet will contain a tab with the displayname of the context prefixed by the context ID, e.g. "410 - Reforestation". The first row in the spreadsheet contains the column display names prefixed by the column group names, e.g. "Activity - Activity Status". Subsequent rows contain data. The values in each field should be formatted similar to how the data is presented in the data grid in the application. Columns for required fields must be present, but for fields which are not required they can be omitted, in which case default values are used for new records as per fieldinfo definition.

Export

Exporting data is done by selecting the records of interest in the grid or search view and invoking the export functionality. The application asks the user if they wish to export tabular data or spatial data or both. The data is exported either as a CSV file and/or a zipped shape file.

The data is exported as seen on the grid. The first row is the column display names, subsequent rows are data.