Time Based Data – Importing Files

Time Based Data – Importing Files - Overview

Tool Location: Data > Import Data > From Files…

This section describes the procedure for loading digital data into the database.

The data loading process streamlines the often complex process of converting data files into a format that is suitable for the database, and consistent with the data and business rules applicable to water quality and other temporal data.

Highlights of the data loading process are as follows:

·         Import file can be a text file, spreadsheet or other Access database table or query

·         Columns from import file are easily mapped to fields in database tables

·         Frequently used field mappings can be saved for reuse

·         Data can be loaded into ANY table in the database

Time Based Data – Importing Files - General Usage

The data loading form is shown below.

image

The form is divided into three steps. In Step 1, the user points to a file. SiteFX does a quick scan of the file and shows the number of records in that file. It also displays a list of all the column headings found (list is displayed along left hand side of form under ‘Source File Fields’ heading).

If an Excel spreadsheet is selected, the user gets to pick which spreadsheet within that workbook they wish to import. If an Access database is selected, the user gets to pick which table or query they wish to import.

In Step 2, the user tells SiteFX ‘what goes where’ in the database. The destination table is first selected from the drop down list (Temporal Data is most commonly used). Field names from the source file are then dragged and dropped onto the destination field names. Mappings that are going to be reused can be saved and pulled up next time data is loaded.

In Step 3 the Append button is pressed and the appending process takes place. If there are unusual elements in the source file, the user will be notified.

Time Based Data – Importing Files - Features

Load File Button

Opens a dialog window that allows the user to select a file for import. If the selected file is a database that contains more than 1 table or query, an additional dialog will appear that allows the user to select the desired data source.

Mapping Template

Lists templates for mappings between import file and database if they have been previously saved.

Save, Delete, Resave Buttons

Once fields are mapped, these buttons can save, delete or resave the mapping template.

Destination Table

Lists tables in the database for appending data. Once selected, the underlying list of fields is updated based on the current selection. Besides tables, some easy to use templates are also listed such as ‘Temporal Data’ which actually comprises two database tables.

Check Boxes for Destination Fields

A check box is placed beside each field in the ‘destination field’ list. These check boxes are used when you have data in your import file that may already exist in your database. Check marks are placed next to the field names you wish to base the duplicate check on.

Source File Field

List of Source File fields in the table to be imported.  Fields only appear after the file has been selected.

Append Button

After field mapping is completed, pressing this button starts the append process. During this process additional prompts may be given depending on the data file being imported.

Time Based Data – Importing Files - Notes/Tips

The following assumptions are integral to the successful operation of the data validation routines:

·         The import data must be a flat file – all the data must be in one file

·         The data must be orthogonal (or normalized)

·         The field names must be in the first row of the file

·         The actual names used for column headings is not important in the source file. One of the steps involved in loading a file includes mapping where the data should go.

·         There can be no spaces between the top of the file (or spreadsheet) and the header  row, or the header row and the data

·         If you suspect that some or all of the data you are loading into the database is already in your database, you can specify that SiteFX check for and exclude and duplicate records by placing check marks beside the destination field names.

·         Instead of mapping a field, you can simply enter the text you wish to be appended to that field by double clicking and entering text.  For example, if your import file is missing a sample date and all data is from the same date, you could simply click next to SAM_SAMPLE_DATE and enter a date, such as 01/01/2009.

·         Instead of mapping a single field, you can also combine fields from the import file. To do this, double click next to the field you wish to map and type the column names you wish to combine by using square brackets around the columns names. For example, if you want to combine a field called ‘Site’ and a field called ‘Location’, you could double click in the Source File Field column and type: [Site] & [Location].

Time Based Data – Importing Files - Loading A File

1.        From main menu, select Data > Import Data > From Files…

2.        Select the Load File button. A window will pop up and prompt you to select a file for importing.  Navigate to the folder where the file resides. Select the file name and click on “Open” button.  The window on the left side of the form will become populated with field names from the selected file.

Note: If the file you select to import is a database containing more than 1 table or query, an additional dialog will appear prompting you select the desired data source. If an Excel file, you will be prompted to select the worksheet.

3.        Select an item from the Destination Table drop down list to choose the destination for the data file you are about to append. The field names underneath will change depending on what table is selected. Note: Some destination tables have a minimum or required set of fields that MUST be used. In these cases the colour-coding scheme will be used with red to indicate a required field.

4.        Click and drag fields from the Source File Fields list to the matching Destination Field, one at a time.  This is what is meant by “mapping the fields”.  You can also double click and specify text or combine fields from the source field (see Notes/Tips above for more details)

Optional: Place a check mark beside any fields that should be used to check for duplicate data. If you suspect that data in your import file already exists in the database, you should use this option.

5.        If this combination of field mappings will be frequently used (for example, to load in data from a lab monthly), press the Save button and follow the instructions in the window.  The mapping template is now saved.

6.        Press the Append button. Any unusual circumstances will be reported to the user who can then decide how to proceed based on the information given.

Time Based Data – Importing Files – REMP Dose Data

In the REMP database, a mapping template has already been designed to assist with the importing of dose data. 

Dose data starts in an Excel spreadsheet. A snippet is shown below.

image

In order to import this data into SiteFX, the file must be slightly modified first. This involves:

1.       Removing the header rows

2.       Adding a Site column to the left hand side, and

3.       Filling in the empty cells along the left hand side

An example of the above spreadsheet prepared for SiteFX import is shown below.

image

The rest of the import is performed from SiteFX. You will need to close the Excel spreadsheet in order to import it.

SiteFX has no knowledge of how many columns your spreadsheet has so you must import one column at a time. An import template has already been prepared to assist with this.

A screenshot of this import template is shown below.

image

As noted above, each column must be imported separately. The centre section of the form displays how data from the import file will be handled.  Each of the 5 ‘mappings’ will be described below. The ‘Sediment (ingestion)’ column will be used as an example.

SAM_SAMPLE_NAME à Expr: 'Sediment (ingestion)' & [Location] & [HumanType] & [Site]

SAM_SAMPLE_NAME represents the sample name.

This mapping starts with the words Expr which stands for ‘expression’. This indicates that this mapping is not a straight mapping of one field from the source file. In this case, it combines a string of text (‘Sediment (ingestion)’) with other columns from the import file. By combining all of this together, we get a string that represents a unique sample name that in turn becomes linked with one interval in the database.

For each column imported, the column name (in this case ‘Sediment (ingestion)’) would have to be manually changed. Double click to edit this text in SiteFX.

For future importing: remember to double click and change the text to match the column that you are currently importing.

 

SAM_SAMPLE_DATE à Expr: 01/01/2009

SAM_SAMPLE_DATE represents the sample date.

Again this is an expression. The source file does not contain a sample date, so we simply double click and enter the date we wish to use.

For future importing: remember to change the date to match the year of the data you are importing.

 

RD_NAME_OUOM à Radionuclide

RD_NAME_OUOM represents the reading or parameter name. OUOM means original unit of measure.  There may already be a conversion set up in SiteFX that converts the spelling of the parameter name from one spelling to another.

In this mapping we are simply going to use the text that appears in the Radionuclide column in the import file.

For future importing: unless the column name in the Excel file changes, you will not need to change this column.

 

RD_VALUE_OUOM à Sediment (ingestion)

RD_VALUE_OUOM represents the numeric value. OUOM means original unit of measure. There may already be a conversion set up in SiteFX that converts the units from one set of units to another.

For future importing: for each column to be imported, drag the desired column from the left hand ‘Source File Fields’ list and drop it on this row. Remember you must manually change the text that appears in the SAM_SAMPLE_NAME mapping to match the column you are importing.

 

RD_UNIT_OUOM à Unit

RD_UNIT_OUOM represents the unit that goes along with the value.

The source file already contains a Unit column so we can simply use the text that is provided in that column.

For future importing: unless the column name in the Excel file changes, you will not need to change this column.

After mapping each column, press the Append File button at the bottom of the import form.

After all columns have been imported, you should make sure that everything loaded properly by using the Validate Data tool (located under Data > Validate Data). After pressing the Check for Errors button there should be no errors shown, as in the screenshot below.

image

If any errors are present, refer to the Validating Data section of the manual, or contact EarthFX for assistance.

Time Based Data – Importing Files – REMP Chemistry

In the REMP database, a mapping template has already been designed to assist with the importing of chemistry data. 

Chemistry data starts in a csv (comma separated values) file. A snippet is shown below.

image

Note that although a csv file can be opened in Excel, the file itself is actually a text file that can be opened in Notepad or another text editor.

SiteFX can read this file with no modification required to the file.

1.        To start the import process, go to Data > Import Data > From Files…

2.        Press the ‘Load File’ button and select the file to be imported.

3.        From the Mapping Template drop down list, choose ‘Lab Data’. A screenshot of an example of this mapping having been selected is shown below. No changes are required.

image

4.        Press the ‘Append File’ button. During the import, SiteFX will check to see if any of the data already exists in the database and provide warnings if necessary.

5.        During the import, SiteFX will attempt to match data from the import file to intervals in the database and the Sample Name Mapping form will appear. A screenshot example of this is shown below.

image

6.        Carefully scroll through the list and ensure there is a check mark in each row, which indicates a match is found. If any rows are not matched, press Continue anyway and use the Data Validation tool (found under Data > Validate Data) to make the match. The Data Validation tool is shown below.

image

If any errors are present, refer to the Validating Data section of the manual, or contact EarthFX for assistance.

Note that after a match is made once, it is remembered as long as the data exists in the database. Also note that if you use the Validate Data tool to match sample names to intervals, you will need to use the conversion tool (Tools > Perform Conversion) to convert the raw chemistry values and units into the units used for reporting. The tool will warn you of this also.