Help for Continuous Based Monitoring Data Operations
Table of Contents (click on a topic below to jump to that section):
· Getting Started
· Enter Quality Control Data
· Import Raw Data From Sampler
A. NewImport.txt File Creation
B. Creation of tblDataImport Table from NewImport.txt File
C. Inspect tblDataImport Table
D. Enter Site and Sampler ID
E. Set up Transfer to Data Verification Table
E(1). Transfer SetUp: Move Headers Onto Field Names
E(2). Transfer SetUp: Mapping Fields
E(3). Transfer SetUp: Possible Blank Fields
E(4). Transfer SetUp: Check Fields
F. Transfer From tblDataImport to tblDataVerification
G. Inspect tblDataVerification Table
H. Update Site and Sampler ID
I. View Dates and Times
J. Midnight Time Correction
K. Sampling Start/End Time Correction
L. Update Selected Pollutant and Units
M. Qualify Data
M(1) Qualify Data Based On Concentration Ranges
M(2) Qualify Data Based On Entered QC Data
M(3) Qualify Data Based On External Information
N. Transfer Data to DataArchive table
· Additional Considerations for Data Importing
Getting Started
From the Main Switchboard, click on the “Continuous and Meteorological Monitoring” button under the “Routine Data Operations” category.
The following window will open. All of the continuous based and meteorological monitoring operations can be accessed from this window.
Important Note: Data should be downloaded from the analyzer every time you perform a QC check on the analyzer (every two weeks is a common interval if manual QC checks are done every two weeks). It is highly recommended that the QC check data be entered BEFORE importing the analyzer data.
Enter Quality Control Data
The following is an outline of the steps you should take to enter quality control data for the continuous monitors into the database.
- Click on the “Enter Quality Control Data” button to open the following window. The screen will open to the last record, which is blank. To see previously-entered QC data, use the navigation buttons at the bottom of the form to navigate through the different records.
· Required data – Depending on the frequency type of the QC check (such as bi-weekly, quarterly, etc.), you may not have data to enter in all of the fields.
a. The fields highlighted in yellow are required. The yellow highlighted fields with red outlines are also primary keys of the associated table in the database.
b. The fields highlighted in green are required if you plan on submitting precision and bias data to AQS.
c. The fields highlighted in blue may or may not be required, depending on the data that you enter. For example, if you are entering zero check values, you must first choose a value in the Zero Check Units field. If you are entering check 1 values, you must first choose a value in the Check 1 Type field and then choose a value in the Check 1 Units field, etc.
· Default values – The Check Type fields will be populated by the toolbox when you choose Pollutant and QC Frequency values. These default values are common check types for the pollutant and frequency that you have chosen. If these Check Types do not line up with your data, you can change them by choosing different values in the drop-down boxes.
· Navigating the form – The best way to enter data from your records is by moving from field to field using the tab key. Some of the fields will be populated by the database automatically, where applicable, and using the tab key will skip these. These include any fields with the word “Qualifier” or “Difference” in their title.
· “Qualifiers” and other calculated fields – If the data that you enter from your QC check are out of the ranges programmed into the database, an appropriate qualifier (or “flag”) will be assigned by the database. For example, if the value that you enter into the “Shelter Temperature (C)” field is not between 20 and 30, a flag of “STR” will be assigned in the “Shelter Temp Qualifier” field by the database.
· AQS Precision and Bias – The toolbox will choose whether to format the data that you enter as precision (a pipe-delimited RP text file generated from the AQS form of the toolbox) or bias (a pipe-delimited RA text file generated from the AQS form of the toolbox) for AQS submittals depending on whether you choose a “No” or “Yes” in the “External Standard?” field. If you choose “No”, the data are formatted as a precision check for the AQS submittal. If you choose “Yes”, the data are formatted as a bias check for the AQS submittal. A precision data file only includes one set of known and measured values, while a bias data file includes up to five sets of known and measured values. The data reported in these files is different for gaseous pollutants (O3, CO, NO2, and SO2) and particulates (PM10-CONT and PM25-CONT):
a. O3, CO, NO2, and SO2 –Only Check Types of “Concentration” will be included in the AQS precision or bias data file.
i. Precision-If this is a multi-point check, the first concentration check that is entered onto the form will be used for the precision value. For example, if there are five concentration checks entered, only the values entered into the Check 1 boxes will be included in the AQS file. The values entered in all of the other boxes will NOT be submitted to AQS.
ii. Bias- All of the concentration checks entered will be reported in the AQS file.
b. PM10-CONT and PM25-CONT –Only Check Types of “Total Flow”, “Main Flow”, or “Aux Flow” will be included in the AQS precision or bias data file.
i. Precision- If you enter multiple flow checks, the toolbox will choose the precision value to report in the AQS file based on the following order of Check Types: Total Flow, Main Flow, Aux Flow.
ii. Bias- All of the flow checks entered will be reported in the AQS file.
Note: If you collect data using a BAM instead of a TEOM, you will likely only have a main flow check and a secondary membrane check. Please choose the Check Type of Main Flow for the main flow check and Aux Flow for the secondary membrane check, if you have data available for the membrane check.
· Known… – Enter the known values (standard)
· Device Display... – The value that is displayed on the analyzer
· Datalogger Output… – The value that is recorded by the datalogger
Note: If you only enter a value in the Device Display field, the database will calculate the difference and relative percent difference (RPD) between the known value and the analyzer’s value. If you only enter a value in the Datalogger Output field, the database will calculate the difference and RPD between the known value and the datalogger output value. These calculated values will be shown in the Difference and Percent Difference fields. If you enter a value in both the Device Display and Datalogger Output fields, the value shown in the Difference and Percent Difference field will be the difference and RPD, respectively, between the known value and the datalogger output value. A qualifier will be assigned by the database if the value in the Difference (for Check Types of “Temperature” or “Pressure”) or Percent Difference field (for Check Types of “Concentration”, “Total Flow”, “Main Flow”, or “Aux Flow”) exceeds the acceptable difference or percent difference assigned by the database.
- To view the linear regression charts for the input and output data, which include the slope/intercept equation and r-squared value, click on the “Device Display Linear Regression Chart” and “Datalogger Output Linear Regression Chart” buttons (only Check Types of “Concentration”, “Total Flow”, “Main Flow”, or “Aux Flow” will be displayed on the charts).
- Once you are done entering data, close the form.
Note: To use this form in hard copy for field data recording, click on the “Print Blank Form” button in the upper right corner of the form.
Import Raw Data From Sampler
Note on Form Color Scheme: There are two types of buttons on the forms.
· The gray buttons are multi-use buttons. They allow you to navigate the forms, inspect data, open the help file, etc., and can be used as many times as necessary during a data import.
· The purple buttons are single-use buttons. They should be used only once during the course of a routine data import.
o The “radio buttons” next to the purple buttons will be checked off once the button has been pressed to indicate that the step has been completed.
· The buttons with letters (A., B., C., etc.) have corresponding lettered sections in the help file below.
· The colored boxes on the forms are simply labels.
· The labels with red text contain important information.
The following is an outline of the steps you should take to import data from a continuous analyzer for air pollutants or a meteorological monitoring station. Please note the following before you begin:
· The database assumes that the data are collected every hour on the hour. If this is not the case (for example, you have TEOM data that are collected every 15 minutes), please contact ITEP for information on how to import your data into the Toolbox.
· Every time that you download a file from an analyzer, that file needs to be imported into the database.
· Each individual text file must be imported separately.
· Once you begin the process of importing the text file from the analyzer, all the steps for that text file should be completed at once rather than completing a few steps at a time. (Steps A through M should be completed in one uninterrupted session.)
· After the process of importing, validating and archiving the data, you will delete the table that is imported and the text file that is created in the following steps so that the process runs correctly the next time data are imported.
· Important Note: Data should be downloaded from the analyzer every time you perform a QC check on the analyzer (every two weeks is a common interval if manual QC checks are done every two weeks). The toolbox was designed for the results of the QC checks to be entered before the pollutant data.
A. NewImport.txt File Creation:
Note: This step must be completed outside of the database (with the database closed).
- Begin by making a copy of the text file from the analyzer whose data you are importing and saving it on your hard drive.
* Make sure to keep the unaltered version of your raw data file with its original name in a safe permanent external location, like on a server, CD, etc. (NEVER manipulate the raw data file that comes from the analyzer without making a copy first).
* The copy of the file must be saved directly to your hard drive (Local Disk, usually the C:\ drive); do not save it to your Desktop, My Documents or any other folder.
* Data must be imported for each site/analyzer/pollutant separately.
- Change the name of the copy of the raw data file on your hard drive to NewImport.txt.
- Open NewImport.txt (using Notepad or any similar program) and make the following changes, if necessary:
a) Delete anything other than the field names and data (headers, metadata and blank lines). In the following example, you would delete all highlighted text. Make sure there are not any blank rows above the field names.
b) Check for any missing or incomplete dates and times. If dates or times are missing or incomplete, correct them directly in the text file.
c) Check for any columns of measurement data (columns containing the actual concentration data or meteorological data, not date columns, time columns, etc.) where the first value in the column is “0”. If there are any, replace the first value in the column with “0.00”. This will prevent Access from importing the values in that column as whole numbers without decimal places. A common example of this is precipitation data, where the column may start with a 0 since there was no precipitation for that sampling day and time, however, there was precipitation for later sampling days and times in the file. If you do not change that first value in the column from “0” to “0.00”, all of the data in that column will be imported as integers, meaning that a value of 0.49 would be imported as “0” or 1.78 would be imported as “1.”
- Save the file after completing all changes (do not close the file yet).
- Find the number of rows in the NewImport.txt file.
· Make sure that the Status Bar option in the dropdown View menu is checked. (Note: if your View menu does not have this option, select Format, uncheck Word Wrap, and then, in View, check Status Bar.)
· Click on the last row of data.
· The right side of the Status Bar (at the bottom of the window) shows the number of rows (lines) in the file (subtract one row from the number of rows reported in the status bar if the text file includes a row of field names).
· The number of rows in the NewImport.txt file corresponds to the number of records that you are importing into the database, and you can check the number of records imported after the next step.
- In your database logbook, record the number of rows containing data in the NewImport.txt file, as well as the first date/time, last date/time, instrument name, site, and any other notes.
- To proceed with the “Import Text File” function, the text file must be comma delimited, and must contain field names for every field. If this is the case, continue to Step B (Creation of tblDataImport Table from NewImport.txt File).
- If your text file is fixed-width rather than delimited, contains delimiters other than commas, or does not have field names for every field, you cannot use the “Import Text File” button unless you first convert your file into a comma delimited text file (contact ITEP for help). If you do not want to convert your file, see the Alternate Manual Import Option Help File for information on how to import the text file manually. Once you have completed the manual import, click on the “Import Raw Data File from Sampler” button in the Toolbox and continue with Step C (Inspect Data Import Table).
B. Creation of tblDataImport Table from NewImport.txt File