Importing Row Data

The Row mapper tab of Germinate Daim is used to import what we call Row Data.

File format

Row Data can be described as a kind of data where each item you want to import is stored in a row of your data file. Below we show an example of such a file:

city       country          latitude        longitude       elevation   created_on
Dundee     United Kingdom   56.4621459197   -2.9701995850   17.318      2014-10-15
Aberdeen   United Kingdom   57.1497170000   -2.0942780000   21.068      2014-10-15

Each data item (Dundee and Aberdeen) is described by its values in each row. The (required) header row identifies the individual columns.

Data separator

In this example we chose "tab" as the data separator. Germinate Daim currently supports the following characters as data separators:

• tab
• ,
• ;
• |

Selecting the target table

The first thing after connecting to the database is to select the target database table, i.e., the table you want to import data into. This is done by selecting a table from the combo box in the top left corner.

The number in brackets shows the number of data items currently stored in this table.

Selecting an input file

Click on the folder icon button to select an input file. This is the file containing your actual data. Depending on your previous selections, the Input options dialog will open to ask you for the data separator and number format.

You can prevent this dialog from opening each time you select a file by un-ticking the checkbox labelled "Always ask after selecting a file".

Mapping your data

After the target table and the input file have been selected, you can start mapping your data to the database table.

The screenshot above shows the final mapping of our example input file and the collectingsites table. We'll now explain in detail how this view works. Each row represents a mapping of one of the columns of the input file (first combo box) to one of the columns of the database table (second combo box). As an example, we map the elevation column of the input file to the elevation column in the database. This mapping is done for all the input columns in the file. The various other buttons are explained below:

• Clicking on this opens the Date Options dialog where you can either choose a specific date, select NOW() to insert the current date/time or enter a date format that is used to parse the date in your input data (e.g. yyyy-MM-dd).
• This button will open the Number Range dialog. This dialog allows you to define specific ranges for numerical input columns. Germinate Daim will then only import those items from your data file where the value in the specified column is within these ranges.
• Clicking on this allows you to enter a static value that will be imported into the mapped database column for each of your input items (rows). This is useful if you don't have an input column that you could map to the database column.
• In certain cases you may want to reference a database item in a different table. If you don't know its id, but only its value, the Key Lookup dialog helps you define the relation between the two tables. In our example dataset, we have the column called country. The collectingsites database table does not contain the country information - only the id of the associated country which in turn in stored in the countries table. Using the [Key Lookup], we can tell Germinate Daim to search for the country name in the countries table and the country_name column. It will the automatically use the id when importing your data.
• Clicking this button removes the current row.

Starting the import process

To start the import process, click on the import data button () starts the import process. A dialog will open informing you about the import progress. Should the import fail due to an error with either the input data, the mapping or the database. An error message will open showing the error message and asking if you want to continue the import anyway. After the import finished successfully, the progress dialog will close.

Updating database entries

Germinate Daim supports updating existing database entries. Before we explain how this feature can be used, a warning: Updating existing database entries is a complicated task and if not done correctly can make a mess of your data. Please make sure that you have a backup of your existing data before you use this feature. The way this feature works is pretty straight forward. You define your column mapping just as you did before. When you're done, click on the data update button (). A dialog will open that looks something like this:

In this dialog, select the columns that you want to update in the database. The remaining columns will be used to identify the database entry you want to update. To illustrate how this works, we'll use an example:

Assume these are the entries in your database

site_name  elevation  latitude  longitude
Dundee     17.318     0         0
Edinburgh  69.642     0         0

As you can see, the latitude and longitude column don't contain valid data. You want to fix that by updating these data entries with the data from the following data file:

site_name  elevation  latitude  longitude
Dundee     17.318     56.46214  -2.970199
Edinburgh  69.642     55.95325  -3.188267

In the dialog, we selected latitude and longitude, which means that we want to update these columns. The other columns (site_name, elevation, country_id (not shown above)) will be used to identify the data entry. This will be fine in this example, because the site name and the elevation are sufficient to uniquely identify your data entry. If, for whatever reason, there would be another entry for Dundee with the same elevation, Germinate Daim wouldn't be able to identify which database entry you want to update. As a naive solution, it will select the item (from the candidates) that has been added to the database last.