Concepts – PWQMN Data Loading

Concepts – PWQMN Data Loading - Overview

 

The PWQMN data loader was added to SiteFX in 2006 to make importing of PWQMN datasets as efficiently and consistently as possible. A snippet of a PWQMN data file is shown below:

 

image

PWQMN Snippet

Assumptions

The following assumptions are made:

·         Each record must have a date. The date must be 10 characters in length and formatted as YYYY-MM-DD as in the example snippet above.  In Excel, these cells will be formatted with the ‘General’ formatting type.

·         All times must have between 1 and 4 characters. Blanks are not allowed. (Note: 1 character is allowed because a time of ‘0’ can appear in the PWMQN file)

·         If the value in the TIME column is a 0 then 12:00 AM is assumed. (Note: Using 12:00 AM essentially retains the fact that the actually sampling time is unknown.)

·         A REMARK of ‘000’ means ‘no remark’ and it is treated the same as if It were blank. All other remarks are imported as value qualifiers.

Processing Notes

Duplicate Records

A very important check has been put in place in order to prevent the same data from being loaded more than once. This check occurs at the record level, which means that if part of the file contains new data and part of the file contains duplicate data (data that has already been loaded) only the new data will be imported. 

As a result, SiteFX will prevent the same file from being loaded twice AND handle the situation where several PWQMN data files contain some overlap of data.

The check for duplicate data uses the following fields: STATION ID, PARAMETER DESCRIPTION, DATE, TIME, RESULT and UNITS. (Note: REMARK is purposely omitted from the duplicate check.)

 

Unit Conversions

Units are converted according to the unit conversions library that can be edited and maintained by the user. These conversions can be managed before OR after the data has been loaded. (Note: the original data values and units are always stored in the database. Subsequent conversions always refer back to the original data values)

 

Parameter Descriptions

Parameter names are mapped to the spelling and/or abbreviation of the user’s choice according to the parameter library that can be edited and maintained by the user. The parameter name library can be managed before OR after the data has been loaded. (Note: the original parameter name is always stored in the database.)

 

Accessing the PWQMN Data Loader

 

SiteFX understands a variety of data formats and the PWQMN Excel format has been added to this library. The data loader is accessed from the Temporal Data menu as shown in the screenshot below:

 

image

Accessing the temporal data loading tool from the SiteFX toolbar

When the ‘Load Data File’ option has been selected from the menu, the following form will appear:

image

Data Loader tool in SiteFX

Clicking on the ‘Load File’ button will prompt the user to select one file. (Note: only one file can be loaded at a time). Upon selection of a file, the Data Loader form will update and appear similar to the screenshot below and the user will be notified when the importing has been completed.

image

Data Loader tool – PWQMN format recognized

Other Considerations

Duplicate Records

In the event that data in the import file is already in the database, the user will be notified of the number of duplicate records and asked how they would like to proceed. An example notification is shown below.

 

image

Notification that duplicate records have been found

Parameter Names

The user’s database contains a parameter name library which is a list of all parameter names and alias names being used by the database. If during import SiteFX encounters a parameter name that has not been seen before by the database, SiteFX will automatically add the parameter name to the library if the user has set that option under Tools > Options. If the option has not been set, they will be able to either add the parameter names later, or map the names to existing parameter names (e.g., the spelling has changed).

Loaded File Manager

To view a list of files loaded by SiteFX go to Temporal Data > Manage Loaded Data Files. This tool is useful to see what files have been loaded and, if necessary, View or Delete the data. Note that the ‘View’ option shows just the full dataset and does not come close to the functionality provided by the Analyze Data tool, which is used for graphing and reporting. A screenshot of the Manage Loaded Data Files tool is shown below:

 

image

Manage Loaded Data Files tool