Importing other Tables

Almost any information/data may be imported into Aquabase as long as these comply with the Aquabase field and integrity rules.

PLEASE READ the bottom of this Help page for important hints and requirements!

These import tables may be in CSV or DBase format and must contain the field names (as in DBase) or in the header first line (for CSV). These field names do not have to be the same as in Aquabase and will be matched to the Aquabase fields in the process.

1.) In order to import a table the user must provide the import table first, which after selection with “Browse” will show the Import Fields in the top right listbox.
2.) Select the Aquabase table to receive the imported data from the top left listbox
3.) Select a field in the top middle listbox and a corresponding field in the top right listbox and click “Add” (or double-click the field in the top right listbox. By repeating this process all fields to be imported will be mapped against the Aquabase fields as shown in the Field Mappings listbox (from which incorrect mappings can be deleted with the “Remove” button).
4.) The bottom checkboxes allow import of certain rows only and you can also specify to import only rows which have a value in a certain field, e.g. you may have a table with 100 rows of water levels and only some of the rows also have a discharge rate, so you would select the discharge field to be not empty/NULL when importing the data into the discharge table.
5.) Clicking “OK” will start the import process, which may take a while, depending on the size of the import file. Each record will be checked for integrity and a log file written into your Workspace with errors.

SO WHAT IS IMPORTANT WHEN IMPORTING?

It is always advisable to add as many data columns as possible to keep the integrity of your database and allow searches for data in columns that actually have data. For example:

If you want to import sites you have to make sure that you have at least one unique field for the Aquabase SITE_ID_NR field in the “basicinf” table. That means you cannot have duplicate site identifiers as this field links all other data in the database to the sites. It is recommended to use all 11 available characters in the SITE_ID_NR, which just aIlows more options; e.g. ‘2125AB’ as first part (a map reference or something) and then ‘00001’ to ‘99999’. But it is also possible to use ‘00000000001’ and increase, as long as the numbers are unique. It is also useful to have a NR_ON_MAP (the number you would identify in the field) and a SITE_NAME, which is more of a description of the location, and the SITE_TYPE. The coordinates have to be imported into the X_COORD, Y_COORD columns in the format that was chosen when creating the new workspace or the format the rest of the coordinates of existing sites. If you import Longitude/Latitude then you can also add those to the LONGITUDE/LATITUDE fields, which will then automatically create the points in WGS84 for the site for direct use in GIS systems.

If you want to import water level data you need to have data for at least the following Aquabase columns (your import table can have other names for these columns):

SITE_ID_NR: These are the sites for which water levels will be imported (you can import for as many sites as you want at the same time)
PIEZOM_NR: The piezometer number (0..9), which indicates in which piezometer the water level was measured (0 is the default)
DATE_MEAS: The date on which the water level was measured
TIME_MEAS: The time at which the water level was measured
WATER_LEV: The actual water level in SI units [m]

But it would also be advantageous to have the water level status (static or affected by nearby abstraction) and maybe the method (e.g. logger or by hand) etc., obviously these have to be the relevant codes as in the Lookup tables.

If you want to import chemistry data then you would need a few more columns:

SITE_ID_NR: These are the sites for which chmistry will be imported
SAMPLE_NR: This could be the same as the NR_ON_MAP, but does not have to be, e.g. the number written on the sample bottle
DATE_SAMPL: This is really important as results change over time

Maybe you can also add the date analysed and possibly the laboratory with a laboratory sample number (alternative numbers) so that one could go back to the laboratory with queries.

But most importantly you also need the:
CHM_REF_NR: a unique number identifying each sample in your database and linking the chemistry information to your sample information. Before importing you have to make sure that these numbers do not interfere with existing numbers as this would lead to duplicates resulting in non-import. In order to find the highest existing number you could use the Tools|Run SQL Query tool with the following query: “SELECT MAX( CHM_REF_NR ) FROM chem_000” and then use the next number (or any number above that) as your first record’s CHM_REF_NR and increase all rows below with one (1).

Then you can have all the columns for your parameters, but you must make sure you import the values according to the unit as stored in Aquabase, e.g. mg/l, mS/m etc. (values reported as micrograms/l will have to be converted to mg/l before import).

While you can import one table into several Aquabase tables with the same import process this is not advisable as erroneous data would lead to wrong or incomplete imports, which are then difficult to delete again. So water levels you would import separately and then the sample information separately and then the actual chemistry parameters.

EVEN MORE IMPORTANT:

1.) BACKUP YOUR DATABASE BEFORE A LARGE IMPORT.
2.) USE THE ADDITIONAL NGDB_FLAG AND FILL IT WITH AN INTEGER NUMBER E.G. 99 OR WHATEVER, SO IT IS EASY TO IDENTIFY THE RECORDS IF YOU WANT TO CHANGE OR DELETE THEM AFTER THE IMPORT

The flag is not really used otherwise in Aquabase (with a few

Almost any information/data may be imported into Aquabase as long as these comply with the Aquabase field and integrity rules.

PLEASE READ the bottom of this Help page for important hints and requirements!

These import tables may be in CSV or DBase format and must contain the field names (as in DBase) or in the header first line (for CSV). These field names do not have to be the same as in Aquabase and will be matched to the Aquabase fields in the process.

1.) In order to import a table the user must provide the import table first, which after selection with “Browse” will show the Import Fields in the top right listbox.
2.) Select the Aquabase table to receive the imported data from the top left listbox
3.) Select a field in the top middle listbox and a corresponding field in the top right listbox and click “Add” (or double-click the field in the top right listbox. By repeating this process all fields to be imported will be mapped against the Aquabase fields as shown in the Field Mappings listbox (from which incorrect mappings can be deleted with the “Remove” button).
4.) The bottom checkboxes allow import of certain rows only and you can also specify to import only rows which have a value in a certain field, e.g. you may have a table with 100 rows of water levels and only some of the rows also have a discharge rate, so you would select the discharge field to be not empty/NULL when importing the data into the discharge table.
5.) Clicking “OK” will start the import process, which may take a while, depending on the size of the import file. Each record will be checked for integrity and a log file written into your Workspace with errors.

SO WHAT IS IMPORTANT WHEN IMPORTING?

It is always advisable to add as many data columns as possible to keep the integrity of your database and allow searches for data in columns that actually have data. For example:

If you want to import sites you have to make sure that you have at least one unique field for the Aquabase SITE_ID_NR field in the “basicinf” table. That means you cannot have duplicate site identifiers as this field links all other data in the database to the sites. It is recommended to use all 11 available characters in the SITE_ID_NR, which just aIlows more options; e.g. ‘2125AB’ as first part (a map reference or something) and then ‘00001’ to ‘99999’. But it is also possible to use ‘00000000001’ and increase, as long as the numbers are unique. It is also useful to have a NR_ON_MAP (the number you would identify in the field) and a SITE_NAME, which is more of a description of the location, and the SITE_TYPE. The coordinates have to be imported into the X_COORD, Y_COORD columns in the format that was chosen when creating the new workspace or the format the rest of the coordinates of existing sites. If you import Longitude/Latitude then you can also add those to the LONGITUDE/LATITUDE fields, which will then automatically create the points in WGS84 for the site for direct use in GIS systems.

If you want to import water level data you need to have data for at least the following Aquabase columns (your import table can have other names for these columns):

SITE_ID_NR: These are the sites for which water levels will be imported (you can import for as many sites as you want at the same time)
PIEZOM_NR: The piezometer number (0..9), which indicates in which piezometer the water level was measured (0 is the default)
DATE_MEAS: The date on which the water level was measured
TIME_MEAS: The time at which the water level was measured
WATER_LEV: The actual water level in SI units [m]

But it would also be advantageous to have the water level status (static or affected by nearby abstraction) and maybe the method (e.g. logger or by hand) etc., obviously these have to be the relevant codes as in the Lookup tables.

If you want to import chemistry data then you would need a few more columns:

SITE_ID_NR: These are the sites for which chmistry will be imported
SAMPLE_NR: This could be the same as the NR_ON_MAP, but does not have to be, e.g. the number written on the sample bottle
DATE_SAMPL: This is really important as results change over time

Maybe you can also add the date analysed and possibly the laboratory with a laboratory sample number (alternative numbers) so that one could go back to the laboratory with queries.

But most importantly you also need the:
CHM_REF_NR: a unique number identifying each sample in your database and linking the chemistry information to your sample information. Before importing you have to make sure that these numbers do not interfere with existing numbers as this would lead to duplicates resulting in non-import. In order to find the highest existing number you could use the Tools|Run SQL Query tool with the following query: “SELECT MAX( CHM_REF_NR ) FROM chem_000” and then use the next number (or any number above that) as your first record’s CHM_REF_NR and increase all rows below with one (1).

Then you can have all the columns for your parameters, but you must make sure you import the values according to the unit as stored in Aquabase, e.g. mg/l, mS/m etc. (values reported as micrograms/l will have to be converted to mg/l before import).

While you can import one table into several Aquabase tables with the same import process this is not advisable as erroneous data would lead to wrong or incomplete imports, which are then difficult to delete again. So water levels you would import separately and then the sample information separately and then the actual chemistry parameters.

EVEN MORE IMPORTANT:

1.) BACKUP YOUR DATABASE BEFORE A LARGE IMPORT.
2.) USE THE ADDITIONAL NGDB_FLAG AND FILL IT WITH AN INTEGER NUMBER E.G. 99 OR WHATEVER, SO IT IS EASY TO IDENTIFY THE RECORDS IF YOU WANT TO CHANGE OR DELETE THEM AFTER THE IMPORT

The flag is not really used otherwise in Aquabase (with a few internal exceptions in Basic Information) and is therefore safe to use.

internal exceptions in Basic Information) and is therefore safe to use.