Importing Chemistry Data from CSV/DBase Tables

While chemistry data can be imported with the “Import other Table” function of Aquabase there is an easier way to achieve this, which makes the manual formatting of the import table almost redundant.

The table to be imported is often saved in spreadsheets with detection limits and other characters in normally numeric columns. This is invalid data for numeric database columns and needs to be handled by the import process. Often the data is in different units from the standard units used in the Aquabase chemistry parameter fields (e.g. µg/l instead of mg/l), but this can be handled by the import.

NOTE: It is important to import all required fields in one go, as several imports with fewer parameters would create new samples with same dates and time, which may lead to key violations.

The four major requirements for the import table are:

1.) The data needs to be in a CSV or a DBase file format.
2.) In case of a CSV file the first line needs to list the header fields (columns), which may not have duplicate names (an error will be shown when opening the import table). DBase tables cannot have duplicate names anyway.
3.) There needs to be a Site Identifier column so that the samples and chemistry results are linked to the correct sites (the OK button will not be enabled without a SITE_ID_NR column in the mappings).
4.) There needs to be a Date Sampled and a Time Sampled field, otherwise the chemistry data cannot be displayed on a time-dependent chart.
5.) The CHM_REF_NR will be generated automatically during the import process.

In order to import a file with chemistry data select it from your disk and then map the fields recognised in the import table to the relevant parameters in the Aquabase chemistry fields. This can be done by either typing the field names into the relevant grid row/column (an error will be shown if the typed-in field does not exist) or selecting it by clicking at the right hand side of the column to open a dropdown with all the available parameters.

The factor column allows the on-the-fly conversion to the units saved in Aquabase (e.g. the conversion from µg/l to mg/l would be 0.001). Other conversions (which are also shown in the dropdown when clicking the right hand side of the column) include conversion from:
a.) NO3 to N
b.) N to NO2
c.) NH4 to N
d.) P to PO4