Wizard Tutorial

Import wizard - tutorial

This tutorial demostrates the import of a small file into the database. The following data should be imported (the example file is included in the software):   At the end of this tutorial you will have imported several datasets and practiced most of the possibilities provided by the import wizard. The import is done in 2 steps to demonstrate the attachment functionallity of the wizard.

 Step1 - Import of the collection events

Choose Data -> Import -> Wizard Sampling plot … from the menu. A window as shown below will open. that will lead you through the import of the data. The window is separated in 3 areas. On the left side, you see a list of possible data related import steps according to the type of data you choosed for the import. On the right side you see the list of currently selected import steps. In the middle part the details of the selected import steps are shown.

Choosing the File

As a first step, choose the File from where the data should be imported. The currently supported format is tab-separated text. Than choose the Encoding of the file, e.g. Unicode. The Start line and End line will automatically be set according to your data. You may change these to restrict the data lines that should be imported. The not imported parts in the file are indicated as shown below with a gray background. If the First line contains the column definition this line will not be imported as well. If your data contains e.g. date information where notations differ between countries (e.g. 31.4.2013 - 4.31.2013), choose the Language / Country to ensure a correct interpretation of your data. Finally you can select a prepared Schema (see chapter Schema below) for the import.

Choosing the data ranges

In the selection list on the left side of the window (see below) all possible import steps for the data are listed according to the type of data you want to import.

Certain tables can be imported in parallel. To add parallels click on the button (see below). To remove parallels, use the button. Only selected ranges will appear in the list of the steps on the right (see below).

To import informations of logging columns like who created and changed the data, click on button in the header line. This will include a additional substeps for every step containing the logging columns (see below). If you do not import these data, they will be automatically filled by default values like the current time and user.

Attaching data

You can either import your data as new data or Attach them to data in the database. Select the import step Attachment from the list. All tables that are selected and contain columns at which you can attach data are listed (see below). Either choose the first option Import as new data or one of the columns the attachment columns offered like SeriesCode in the table Series in the example below.

If you select a column for attachment, this column will be marked with a blue backgroud (see below and chapter Table data).

Merging data

You can either import your data as new data or Merge them wih data in the database. Select the import step Merge from the list. For every table you can choose between Insert, Merge, Update and Attach (see below).

The Insert option will import the data from the file independent of existing data in the database.

The Merge option will compare the data from the file with those in the database according to the Key columns (see below). If no matching data are found in the database, the data from the file will be imported, otherwise the data will be updated..

The Update option will compare the data from the file with those in the database according to the Key columns. Only matching data found in the database will be updated.

The Attach option will compare the data from the file with those in the database according to the Key columns. The found data will not be changed, but used as a reference data in depending tables. 

Table data

To set the source for the columns in the file, select the step of a table listed underneath the Merge step. All columns available for importing data will be listed in the central part of the window. In the example shown below, the first column is used to attach the new data to data in the database.

A reminder in the header line will show you what actions are still needed to import the data into the table:

• Please select at least one column   = No column has been selected so far.
• Please select at least one decisive column   = If data will be imported depends on the content of decisive colums, so at least one must be selected.
• Please select the position in the file   = The position in the file must be given if the data for a column should be taken from the file.
• Please select at least one column for comparision   = For all merge types other than insert columns for comparision with data in the database are needed.
• From file or For all   = For every you have to decide whether the data are taken from the file or a value is entered for all
• Please select a value from the list   = You have to select a value from the provided list
• Please enter a value   = You have to enter a value used for all datasets

The handling of the columns in described in the chapter columns.

Testing

To test if all requirements for the import are met use the Testing step. You can use a certain line in the file for you test and than click on the Test data in line: button. If there are still unmet requirements, these will be listed in a window as shown below.

If finally all requirements are met, the testing function will try to write the data into the database and display you any errors that occurred as shown below. All datasets marked with a red backgroud, produced some error.  

To see the list of all errors, double click in the error list window in the header line (see below).

If finally no errors are left, your data are ready for import. The colors in the table nodes in the tree indicate the handling of the datasets: INSERT, MERGE, UPDATE, No difference. Attach, No data. The colors of the table colums indicate whether a colums is decisive , a key column or an attachment column.  

Import

With the last step you can finally start to import the data into the database. If you want to repeat the import with the same settings and data of the same structure, you can save a schema of the current settings (see below).

Tables

CollectionSpecimen (CollectionSpecimen)
Parent: CollectionEvent
Merge handling: Insert

IdentificationUnit_1 (IdentificationUnit)
Parent: CollectionSpecimen
Merge handling: Merge

IdentificationUnitAnalysis_1_1 (IdentificationUnitAnalysis)
Parent: IdentificationUnit_1
Merge handling: Update

 Lines that could not be imported will be marked with a red background while imported lines are marked green (see below).

If you want to save lines that produce errors during the import in a separate file, use the Save failed lines option. The protocol of the import will contain all settings acording to the used schema and an overview containing the number of inserted, updated, unchanged and failed lines (see below).