Import AQS Data Using EQuIS AQS Format

The EQuIS AQS Format is used to load AQS-ready data files into an EQuIS database. This format can load current and historical datasets. The AQS.zip format file is available for download from the Products\AQS Module folder on the Downloads Dashboard on the EarthSoft Community Center (ECC).

The sections in the AQS Format mimic the EPA AQS Transaction Formats. A complete list as well as descriptions of the AQS Transaction Types are available at https://aqs.epa.gov/aqsweb/documents/TransactionFormats.html.

EQuIS AQS Format EDD sections are available for the following AQS Transaction Types.

•AA – Site Basic

•AB – Site Street

•MB – Monitor Basic (coming soon)

•RD – Raw Data

•RB – Raw Blanks

•QA – 1-Point QC

•QA – Annual PE

•QA – Flow Rate Verification

•QA – Semi Annual Flow Rate Audit

•QA – Duplicate (coming soon)

•QA – Replicate (coming soon)

•QA – Pb Analysis Audit (coming soon)

•QA – Zero Span

If the desired AQS transaction type is not in the above list, please contact support@earthsoft.com.

To view the EQuIS database mappings for each EDD section/field, an EDD Description file for the AQS Format can be generated from EDP.

Import AQS Data

1.Open EDP from the Home ribbon in EQuIS Professional.

2.Open the AQS Format file in EDP (see Opening an EDP Format File).

3.Click the EDD button located in the Open group on the Home ribbon of EDP.

4.Select and import the desired AQS EDD to the EQuIS database. See the EDP chapter for detailed instructions on importing files, resolving errors and committing data to EQuIS. AQS EDDs need to be in pipe-delimited text file formats; the same files that are loaded to EPA’s AQS system.

Important Considerations

For data loaded using the AQS Format:

•A unique SYS_LOC_CODE will be a concatenation of the State-County-Site ID fields (e.g., 27-003-1002 in that order).

•The RAW_DATA and RAW.BLANKS.ACTION_INDICATOR fields are currently not mapped to the EQuIS database. It is defaulted to “I” in the Export.

•A unique SYS_SAMPLE_CODE will be a concatenation of the State-County-Site ID-POC.DateTime fields, where the Date and Time will be in the YYMMDDHHMM format (e.g., 27-003-1002-1.1812310800). It may be possible to change the sample naming convention to suit your needs. Contact support@earthsoft.com to learn more about the EQuIS Link feature and the associated formula builder.

•If populated, the incoming SCREENING_GROUP_CODE will be stored in DT_SAMPLE_PARAMETER.PARAM_VALUE with the PARAM_CODE = "AQS_Screening_Group".

•The Raw Data/Raw Blanks.Fraction field defaults to "Total". If "Total" is not a valid reference value in the EQuIS database, it is recommended that a remap be setup for this field to translate "Total" into an appropriate internal reference value from the RT_FRACTION table (e.g., "T").

•To populate the DT_SAMPLE.SAMPLE_TYPE_CODE, the format will look for a valid remap in RT_REMAP_DETAIL with the REMAP_CODE = “AQS”% and EXTERNAL_FIELD = “Transaction_Type”.

•The EPA AQS parameter codes, monitor method codes, and unit codes will be remapped upon data import to the appropriate EQuIS value, based on the setup of the RT_AQS_METHOD, RT_AQS_PARAMETER, and RT_AQS_UNIT tables. The raw EPA AQS parameter codes, monitor method codes, and unit codes (and qualifier codes) will also be stored in the DT_RESULT table in specific fields added by the EQuIS AQS Schema to protect against the many-to-one relationships between EPA AQS reference values and EQuIS reference values.

•For the Raw sections, the AQS Qualifier Codes will be loaded to the DT_RESULT.AQS_QUALIFIERS field. Each qualifier code field will be concatenated and separated by a comma. The NULL_DATA_CODE field will be in the first position with the QUALIFIER_CODE_* fields following in numeric order. If any qualifier fields are not populated, a comma placeholder will be added.

•Any result data will be compared against the FEDERAL_MDL, MIN_VALUE, MAX_VALUE, and DIGITS fields in the RT_AQS_METHOD table. Warning messages will be thrown if data does not comply with these rules; warnings can be ignored in EDP.

•Results should be less than the FEDERAL_MDL.

•Results should be between the MIN_VALUE and MAX_VALUE.

•Results should have the minimum number of decimal places specified by the DIGITS value.