* A vo
i SEI
EPA Region 4
Format File Guide
Version 1.2
Updated December 2015
EPA Region 4 Superfund
Updated by:
earthstrft
-------
DISCLAIMER OF ENDORSEMENT
Reference herein to any specific commercial products, process, or service by trade name, trademark,
manufacturer, or otherwise, does not necessarily constitute or imply its endorsement,
recommendation, or favoring by the United States Government. The views and opinions of authors
expressed herein do not necessarily state or reflect those of the United States Government, and shall
not be used for advertising or product endorsement purposes.
STATUS OF DOCUMENT
As of November 2015, this document and all contents contained herein are considered DRAFT
and are subject to revision and subsequent republication. Ecological EDD specifications do not
appear in this guidance as they are currently under development, and will appear in future
addenda.
-------
CONTACTS
For questions and comments, contact:
Your RPM or,
DART Coordinator
Superfund Division, 11th Floor East
United States Environmental Protection Agency, Region 4
Sam Nunn Atlanta Federal Center
61 Forsyth Street, SW
Atlanta, GA 30303-8960
(707) 752-5254
R4DART@epa.gov
-------
Acronyms
CAS RN - Chemical Abstracts Service Registry Number
DART - Data Archival and ReTrieval
EDD - Electronic Data Deliverable
EDP - EQuIS Data Processor
EPA - Environmental Protection Agency
EQuIS - Environmental Quality Information System
O&M - Operation and Maintenance
SESD - Science and Ecosystem Support Division
SRS - Substance Registry System
CLP - Contract Laboratory Program
PRP - Potentially Responsible Party
QC - Quality Control
Definitions
Darter - Darter is a set of software utilities written by EPA that assist in moving data from other
platforms such as FORMS, Niton, YSI and Scribe to the Region 4 EDD format.
Data Provider - "Data Provider" and "Sampling Company" are defined to be interchangeable with
regard to EDD submittals. The data provider may be defined as the person or agency that organized,
formatted and submitted the electronic data from a sampling event. This may or may not be the
sampling company, particularly when working with historic data. Unless otherwise noted by your
RPM, the prime contractor or grantee is always entered as the Sampling Company for the Samples
and the Data Provider for the Geology EDDs.
Electronic Data Deliverable (EDD) - An Electronic Data Deliverable, or EDD for short is a flat file
format, such as text, Excel, or other tabular file that follows a consistent design meant to organize
information in a useful format. EDD files use a row of headers (typically one to two rows) that
describe what information should be completed in each column the header precedes, and in what
format that data should be entered.
Header Row 1
Header Row 2
Data Row 1
Data Row 2
Scribe - Scribe is a software tool developed by EPA to assist in the process of managing
environmental data. Scribe captures soil, water, air, and biota sampling, observational, and
monitoring field data. Scribe can import electronic data deliverables (EDD) from analytical
laboratories, location data from a global positioning system (GPS), and from exported EQuIS™
EDDs.
Column 1 Column 2 Column 3 Column 4
sysloccode
x coord
ycoord
coordtypecode
Text (20)
Numeric
Numeric
Text (20)
MW14
-81.26551
38.80360
LAT LONG
MW15
-81.60310
38.12871
LAT LONG
-------
Sample Company - "Data Provider" and "Sampling Company" are defined to be interchangeable
with regard to EDD submittals. The data provider may be defined as the person or agency that
organized, formatted and submitted the electronic data from a sampling event. This may or may not
be the sampling company, particularly when working with historic data. Unless otherwise noted by
your RPM, the prime contractor or grantee is always entered as the Sampling Company for the
Samples and the Data Provider for the Geology EDDs.
.rvf- The ".rvf' file (reference value file) is associated with the EQuIS Data Processor (EDP) from
EarthSoft. This file contains the valid values reference tables used by EDP to populate the drop down
menus used when a specific type of value is required in an EDD, such as the units "mg/kg"
(milligrams per kilogram) or a media code such as "GW" (groundwater). These fields limit the type
of data permitted in certain columns of the EDD, and all the most recent valid values are in the ".rvf'
file. Therefore, it is extremely important to insure you are using the most current file. You should
check the EarthSoft web site to see if your version is current before working on your data.
.zip archive - The ZIP file format is a data compression and archival format that contains one or
more files that have been compressed, to reduce their file size, or stored as-is. Many software utilities
are available to create, modify, or open (unzip, decompress) ZIP files.
ZIP files typically use the file extensions ".zip" or ".ZIP" and the MIME media type application/zip.
However, due to security features at EPA, compressed files with the extension .zip should be
renamed to the extension ".edd."
-------
TABLE OF CONTENTS
1.0 INTRODUCTION 1
1.1 What You Will Find in this Guide 1
1.2 What You Will NOT Find in this Guide 1
1.3 Projects 1
1.4 Data Provider 2
1.5 Historical Data 2
1.6 Current Data 2
1.7 Scribe 3
2.0 GENERAL EDD REPORTING REQUIREMENTS 4
2.1 Defining the Data Submittal 4
2.2 File Formats 5
2.3 EDD Files 6
2.4 Data Integrity Rules 10
2.5 File Naming Convention 11
2.6 Valid Values 12
2.7 Understanding Analytical Methods 14
2.8 Reporting Results 16
2.9 Reporting Duplicate Lab Results 16
2.10 Reporting Tentatively Identified Compounds (TICs) 16
2.11 Using the EQuIS Data Processor to Check EDD Formatting 17
3.0 EDD FILE FORMATS 18
3.1 Site EDD File 18
3.2 Location EDD File 19
3.3 Alternate Position EDD File 23
3.4 Site Location EDD File 24
3.5 Location Parameter EDD File 25
3.6 Field Results EDD File 26
3.7 Chemistry Field Sample EDD File 27
3.8 Chemistry Sample Parameter EDD File 31
3.9 Chemistry Test EDD File 32
3.10 Chemistry Result EDD File 36
3.11 Well EDD File 41
3.12 Well Construction EDD File 44
3.13 Well Datum EDD File 45
3.14 Water Level EDD File 48
3.15 Geologic Sample EDD File 49
3.16 Geologic Sample Parameter EDD File 50
3.17 Water Table EDD File 51
3.18 Lithology EDD File 52
3.19 Drill Activity EDD File 54
3.20 Drill Parameter EDD File 56
3.21 Downhole Point Parameter EDD File 56
-------
3.22 Static Properties EDD File
3.32 Files EDD Section
3.24 Working with Data
-------
LIST OF TABLES AND FIGURES
Table 1.1 Data Reporting Requirements - Project ID Format 3
Table 2-1 Data Submittal Summary Form 4
Table 2-2 When to Submit Your Data Submittal Summary Form 5
Table 2-3 Instructions for Producing Tab-Delimited Text Files 5
Figure 2-1 Conceptual Model of the EDD Submittal Process 7
Table 2-4 General Information on EDD Files 8
Table 2-5 Valid Values and Associated EDD files 12
Table 2-6 EPA Method Codes 14
Table 2-7 Example of Reporting Non-detects 16
Table 2-8 Example of Reporting Laboratory Duplicates 16
Table 2-9 Example of TIC Reporting 17
Figure 3-1 Measure Datum Calculation 47
-------
1.0 INTRODUCTION
The Region 4 Format Guide describes the formats of the various EDD files necessary for properly
preparing, formatting and electronically submitting operation and maintenance (O&M)
environmental data to EPA Region 4 using the EQuIS® Data Processor (EDP) from EarthSoft.
Instructions for obtaining EDP can be found in Section 2.11 of this document. Instructions for using
the EDP are available in the EPA Region 4 EDP Reference Manual. You may obtain instructions
from the manufacturer as well at http://www.earthsoft.com.
While it should be understood that it would be a rare occasion that any one site would require the use
of every possible Electronic Data Deliverable (EDD), and that simultaneously no amount of EDD
files could completely cover the requirements of every possible site, every effort has been made to
create EDD files that incorporate as much useful discreet information as is possible based on the
commonalities of the various sites under the EPA Region 4 purview. Thus, a reasonable effort should
be made by data managers to prepare data to correctly import into the EQuIS database structure when
required to do so. It is also understood that in some rare cases, historical data may lack key
components necessary for complete importation and subsequently may only be partially input, or in
some extremely rare cases not be capable of input at all. The decision to import or not import this
data lies exclusively with EPA Region 4.
1.1 What You Will Find in thisGuide
This document should serve as a reference guide for the individuals who understands the structure of
their own data and the process used to format and import electronic data into management systems
such as EQuIS. In this guide you will find a detailed description of all EDD formats currently used
by EPA Region 4 as well as information for handling formatting issues with historic data.
1.2 What You Will NOT Find in thisGuide
This guide does not contain detailed instructions on how to manage information nor does it contain
directions for importing electronic data into the EPA EQuIS database. This information is contained
in the EPA Region 4 EDP Reference Manual found on EPA Region 4 Superfund's website at
http://www2.epa.gov/superfund/region-4-superfund-electronic-data-submission
You will also not find lessons or instructions on general data management. It is assumed that persons
dealing with data of this nature have some experience and understanding of basic data management
guidelines and software.
1.3 Projects
Before data can be submitted to EPA Region 4, the dataset must be defined as a "proj ect" in the EPA
Region 4 data system. Once this has occurred, the system will generate an E-mail to the data provider
(see below) requesting the data for most fund lead projects. Other type of projects should follow
their statement of work or directive from their RPM. The EDD(s) containing the data are then
USEPA Region 4 Format Guide
Version 1.20 December 2015
1
-------
attached to this E-mail and returned to EPA Region 4 R4DART. The project defines the numbers of
samples, the media sampled, and the analyses performed (including lab results, field results,
lithology, etc.). Each project will be assigned a unique ID (project ID) to identify the dataset in the
system. Data from a given project can be sent to EPA Region 4 in multiple submittals, if desired.
When multiple laboratories and/or chain-of-custodies are needed for the unique project id, then
multiple proj ect numbers will be assigned under the proj ect ID. Proj ects that are not logged through
R4LIMS or if you have not received your project ID and project numbers, contact R4DART for
further instructions.
1.4 Data Provider
The EPA Region 4 data system defines the "data provider" as the entity preparing and sending the
EDDs to the R4DART. It is the responsibility of the data provider to ensure completeness of the data
submittal, and to correct any errors in the dataset.
1.5 Historical Data
The determination as to the status of data as either "historical" or "current" is at the sole
discretion of the EPA Region 4 DART Coordinator. The data provider is encouraged to become
familiar with and submit Electronic Data Deliverables (EDDs) in accordance with the standard
EPA Region 4 EDP Reference Manual located on the EPA Region 4 web site:
http://www2.epa.gov/superfund/region-4-superfund-electronic-data-submission
EPA Region 4 will require all data providers to submit EDDs in accordance with this manual and
reference guide.
The intent of developing special requirements for historical information is to decrease the burden and
complications associated with reporting in-depth details about data that may have been collected a
number of years ago while retaining the value of the information being reported. EPA recognizes that
some information about data collected in the past may not be readily available and, by reducing the
requirements for electronic historical data, is endeavoring to strike a balance between minimizing the
amount of effort involved in inputting information and maximizing the ability to document remedy
progress.
1.6 Current Data
Data is considered to be "current" if it was collected on or after the deadline specified for the specific
data type and provider outlined in Table 1.1. The data provider should anticipate all future data being
collected for a site on or after the deadline to be submitted according to the site-specific legal order,
on a regular basis (at least annually, semi-annually) or in accordance with this manual.
In addition to the routine data checking performed on all data as it loads into the EPA Region 4
DART system, current data will undergo an additional electronic EPA Region 4 review prior to
USEPA Region 4 Format Guide
Version 1.20 December 2015
2
-------
being loaded. This may include but not limited to the plotting of submitted locations to verify the
accuracy of the spatial information being submitted.
1.7 Scribe
Samples collected during the course of an Emergency Response that are analyzed using the EPA
Region 4 SESD lab or the CLP will be submitted as Current Data: Fund Lead (See Table 1.1). If
samples are analyzed using a lab hired for the response, that data can be reported directly from Scribe
using the EPA Region 4 macro software utility. Prior to sending the data to R4DART, the created
EPAR4_FieldResults_vl.txt file should first be loaded into the EDP checker to verify if any
additional information needs to be added.
Table 1.1 Data Reporting Requirements - Project ID Format
Historical
Data
Source
Project ID Format*
PRP
HPYYYY-####
Fund Lead (SESD or CLP data)
HFYYYY-####
Fund Lead (Not SESD or CLP data)
HCYYYY-####
Other
HOYYYY-####
Current
Data
PRP
PYYYY-####
Fund Lead (SESD or CLP data)
YY-####
Fund Lead (Not SESD or CLP data)
RYYYY-####
Scribe (Not SESD or CLP data) - Emergency Response
SYYYY-####
Other
OYYYY-####
*Project ID is provided by EPA Region 4 Sample Coordinator or R4DART. If you have not been
provided a Project ID contact R4DART@epa.gov.
USEPA Region 4 Format Guide
Version 1.20 December 2015
3
-------
2.0 GENERAL EDD REPORTING REQUIREMENTS
2.1 Defining the Data Submittal
It is important to define what is to be submitted ahead of the data submittal. While it is often not
possible to know precisely how many rows of data may be submitted, it should be possible to
provide a summary of the amounts and types of data that are to be submitted.
The summary is a tabular list of the types of samples being submitted, the analyses performed (see
Section 2.7), and the EDD file types (see Section 3). Table 2-1 presents an example summary and
the template, 'Data Submittal Summary Template (.XLS)', may be downloaded from:
http://www.epa.gov/superfund/region-4-superfund-electronic-data-submission
Fund lead projects going through the R4LIMS system will receive an email with the summary.
When you do not receive an email with a Project Log Summary Report, then prepare the summary
according to the data requirements outlined in your QAPP.
See Table 2-2 for an explanation of when this should be submitted, depending on your submittal
type. The email containing Project Log Summary Report serves as the Data Submittal Summary.
The data provider completes the file, indicating the data that will be submitted when ready, and E-
mails the file to: R4DART@epa.gov.
The format of the file is shown below (the actual file may have too many columns to display on the
printed page):
Table 2-1 Data Submittal Summary Form
Media
SVOA
VOA
PCBA
Etc.
Surface Soil
54
54
27
Subsurface Soil
Sediment
12
12
12
Groundwater
10
10
Surface Water
12
12
12
Etc.
EDD Types
AlternatePosition
N
SiteLoc
N
FieldResults
Y
Well
Y
Etc.
The table is in two sections. The first section (Media) lists the number of samples anticipated to be
submitted to EPA Region 4 by media and analytical method. The second section simply lists the
available EDD Types, with the data provider indicating by Y/N whether or not an EDD of that type
will be submitted for that Project ID.
USEPA Region 4 Format Guide
Version 1.20 December 2015
4
-------
Note: Location EDD is assumed to be present if required. Also, if the media portion of the table is
completed, FSample, TST, and RES are assumed to be present (see Section 3).
Table 2-2 When to Submit Your Data Submittal Summary Form
Historical
Data
Source
When to Submit
PRP
Submit with EDD(s)
Fund Lead (SESD or CLP data)
Submit with EDD(s)
Fund Lead (Not SESD or CLP data)
Submit with EDD(s)
Other
Submit with EDD(s)
Current
Data
PRP
Submit prior to field event
Fund Lead (SESD or CLP data)
Submit prior to field event
Fund Lead (Not SESD or CLP data)
Submit prior to field event
Scribe (Not SESD or CLP data) - Emergency Response
Submit prior to field event
Other
Submit prior to field event
2.2 File Formats
All data to be reported to EPA Region 4 must be submitted as signed ".edd" files generated by
importing EDD files to EDP (Sect. 2.11) and exporting for submission. EDD files can be produced
using any software with the capability to create text files but these files must be checked with the
EDP (Sect. 2.11) prior to submittal to EPA Region 4. These files are especially easy to create using
spreadsheet or database software packages. However, if these are unavailable, the files can be created
using a word processor or text editor. Table 2-3 provides instructions for creating tab-delimited text
files from some widely-used software packages.
Table 2-3 Instructions for Producing Tab-Delimited Text Files
Package
Type
Instructions
EQuIS
Database
Data already existing in an EQuIS Database of any version must still be
exported to the various EDD files and checked with the EDP. Export
individual tables into a spreadsheet format so that they may be reformatted
into the correct data structure described in Section 3. Then follow the
instructions from "Excel" below for exporting to the text file format.
Access
Database
Create tables using file structures in Section 3.
After data are entered, close table.
Click on table name (under table tab) and then select "File," "Save As,"
from the top menu. Save to an external file or database. Change "Save as
Type" to a text file. Change the file extension from "txt" to "tab." Press
OK. This will start the export wizard.
In the export wizard, select "Delimited," then press the "Next" button.
Select "Tab" as the delimiter type and (") as the text qualifier. Press the
"Next" button. Select a destination and name for the file. Press the
"Finish" button.
Excel
Spreadsheet
Select "File," "Save As," from the top menu. Change "Save as Type" to a
"Text (Tab Delimited)" file. Press the "Save" button. Note: When using
Excel, care must be taken to prevent the software from stripping leading
and trailing zeroes from your dataset.
USEPA Region 4 Format Guide
Version 1.20 December 2015
5
-------
Package
Type
Instructions
Quattro® v8
Spreadsheet
Select "File," "Save As," from the top menu. Change the "File Type" to
"ASCII Text (Tab Delimited)." Press the "Save Button."
Be sure to check that Quattro does not delete leading and trailing zeros.
Lotus 1-2-3
Spreadsheet
Select "File," "Save As," from the top menu. Change "Save as Type" to a
"Comma Separated Value (CSV)" file. Provide file name. Press the "Save"
button.
Be sure to check that Lotus does not delete leading and trailing zeros.
2.3 EDD Files
The tables in this guidance identify the various types of data being requested. Each EDD file should
be saved as an individual text file and should be named in accordance with the naming convention
rules. Table 2-4 provides general information on the files that make up this EDD. Detailed
instructions for creating all the EDD files are provided in Section 3. Instructions for submitting your
EDDs to EPA Region 4 are presented in Section 4.0 and in the EDP Reference Manual. Figure 2-1
below shows the EDD creation process conceptually.
It is not possible to prepare a dataset for acceptance into a database without understanding the basics
of how databases operate. Row uniqueness and referential integrity are requirements of the EQuIS
database. Their requirements impact how the EDD is prepared.
Row uniqueness is assured when no two rows in a file contain the same values for all the fields listed
under the heading "What makes a row of data unique?" in Table 2-4, also see Section 2.4.
USEPA Region 4 Format Guide
Version 1.20 December 2015
6
-------
Figure 2-1 Conceptual Model of the EDD Submittal Process
The relationship between rows within the files of the EDD must be assured by enforcing the
"referential integrity" rules discussed in Table 2-4 under the column labeled "Dependence of other
files on these data", also see Section 2.4.
USEPA Region 4 Format Guide
Version 1.20 December 2015
7
-------
Table 2-4 General Information on EDD Files
File Type
File Name
Contents
What makes a row of
data unique? (i.e.
Primary Key fields)
Dependence of
other files on
these data
Site
(Created by
EPA)
(Section 3.1)
EPAR4_Site_vl
One-time definition
of site including EPA
Region 4 data
providers' contact
information.
sitecode
(This will be provided
by EPA Region 4)
The location file
cannot be loaded
without properly
referenced sites
(sitecode).
Reserved.
Location
(Section 3.2)
EP AR4_Location_v 1
One entry for each
location on a site.
Contains elevation,
coordinate, and
general locational
data. Data should
only be reported once
for a location.
sysloccode
All Except
Chemistry Field
Sample,
Chemistry Test,
and Chemistry
Result
Alternate
Position
(Section 3.3)
EPAR4 AlternatePosition
_vl
Alternate
position/location
information for
sample locations, if
required.
None. Related by
sysloccode
coordtypecode
identifier
None
Site Location
(Section 3.4)
EP AR4_SiteLoc_v 1
Information relating
sample locations to
the site, as on, off, or
background.
None. Related by
sysloccode
sitecode
None
Location
Parameter
(Section 3.5)
EPAR4_LocationParamete
r_vl
None. Related by
sysloccode
None
Field Results
(Section 3.6)
EP AR4F ieldRe sultsv 1
Results of field tests
for unique locations.
Not all locations will
have Field Results,
but Field Results
must have a location.
sysloccode
samplename
field_parameter
resultdate
None
Chemistry
Field Sample
(Section 3.7)
EPAR4_FSample_v 1
One row for each
sample submitted to
the lab.
syssamplecode
Chemistry Test
and Chemistry
Result
Chemistry
Sample
Parameter
(Section 3.8)
EPAR4 SampleParameter
_vl
One row for each
parameter associated
with a sample sent to
a lab.
syssamplecode
paramcode
measurementdate
None
Chemistry
Test
(Section 3.9)
EPAR4_TST_v 1
One row for each test
run on each sample
submitted, including
duplicates.
Duplicates must have
a unique
syssamplecode.
syssamplecode
labanlmethodname
analysisdate
analysistime
totalordissolved
testtype
Chemistry Result
USEPA Region 4 Format Guide
Version 1.20 December 2015
8
-------
Table 2-4 General Information on EDD Files
File Type
File Name
Contents
What makes a row of
data unique? (i.e.
Primary Key fields)
Dependence of
other files on
these data
Chemistry
Result
(Section 3.10)
EP AR4_RES_v 1
One row for each
analyte reported for a
given sample and
test. Additional rows
can be added to
report total and
dissolved results and
to report results for
re-extracts.
Duplicates must have
a unique
syssamplecode.
syssamplecode
labanlmethodname
analysisdate
analysistime
totalordissolved
testtype
casrn
None
Well
(Section 3.11)
EP AR4W ellv 1
Data that relates to
any well used for
sampling under the
project.
sysloccode
Well Construction
(sysloccode)
Well
Construction
(Section 3.12)
EPAR4 WellConstruction
_vl
Well construction
information including
segment type,
material type, depths,
and diameters.
None. Related by
sysloccode
segmenttype
materialtypecode
startdepth
None
Well Datum
(Section 3.13)
EP AR4W ellDatumv 1
Well elevation
information for
measurements of
water level. Includes
datum used.
sysloccode
startdate
Water Level
Water Level
(Section 3.14)
EPAR4_WaterLevel_vl
Groundwater level
data for monitoring
wells.
sysloccode
measurementdate
Well Datum
Geologic
Sample
(Section 3.15)
EPAR4 Geologic Sample
vl
Geology data for a
borehole.
geosamplecode
Geologic Sample
Parameter
Static Properties
Geologic
Sample
Parameter
(Section 3.16)
EP AR4_Geo SampleParam
etervl
Field parameters
specifically related to
drilling a borehole,
i.e. conductivity,
turbidity.
None. Related by
geosamplecode
paramcode
paramunit
None
Water Table
(Section 3.17)
EP AR4_WaterT ablev 1
Aquifer information.
None. Related by
sysloccode
type
sequence
None
Lithology
(Section 3.18)
EP AR4_Lithology_v 1
Lithology
information from a
borehole.
None. Related by
sysloccode
startdepth
None
USEPA Region 4 Format Guide
Version 1.20 December 2015
9
-------
Table 2-4 General Information on EDD Files
File Type
File Name
Contents
What makes a row of
data unique? (i.e.
Primary Key fields)
Dependence of
other files on
these data
Drill Activity
(Section 3.19)
EPAR4_Drill Activity _v 1
Drilling event
information including
unique location and
depths.
None. Related by
sysloccode
drillevent
DrillParameter
Drill
Parameter
(Section 3.20)
EPAR4 DrillParameter v
1
Field parameters
specifically related to
drilling, i.e.
conductivity,
turbidity. Relates to
Drill Activity.
None. Related by
sysloccode
drillevent
startdepth
paramcode
None
Downhole
Point
Parameter
(Section 3.21)
EPAR4 DownholePoint v
1
Drilling tip and
sleeve stress
information.
None. Related by
sysloccode
depth
param
None
Static
Properties
(Section 3.22)
EP AR4_StaticProps_v 1
Static physical
properties such as
geologic materials,
specific gravity and
porosity related to
drilling.
None. Related by
geosamplecode.
None
Files
(Section 3.32)
Filesvl
Loads supplementary
information into
EQuIS
None
None
2.4 Data I ntecfity Rules
Data providers are responsible for performing three types of integrity checks on their data prior to
submittal.
Validity. All codes used in a data set must be valid. Valid values for all coded fields are either
provided in the description columns of the tables in Section 3 or in the reference tables of the EDP.
Data providers must use the most up-to-date reference tables (.rvf files) available from EarthSoft. For
example, sample matrix information is input in the samplematrixcode field of the Field Sample
Results table and must be reported using one of the values provided in the reference file associated
with EDP. It is essential to obtain the latest version of the formats and reference files before
beginning work.
Row Uniqueness. Row uniqueness must be verified using the guidance provided in Table 2-2. In
database terminology this is called a primary key. For example, the sys loc code is the primary key
in the Location EDD table and therefore no two rows in the file can have the same sys loc code
value. The EDP will highlight duplicate rows of data to assist you in locating these errors.
USEPA Region 4 Format Guide
Version 1.20 December 2015
10
-------
Row Integrity . The relationship between rows within the tables of the EDD must be assured by
enforcing the "referential integrity" rules discussed in Table 2-2 under the column labeled
"Dependence of other files on these data." For example, the values in the sys loc code field in the
Field Sample Results table must match with a value previously reported in the sys loc code field of
the Location table. The EDP will not test for integrity between EDDs and existing data in the
database until it is loaded. The data provider will be responsible for taking care not to submit
duplicate data. If the data fails integrity tests, it will NOT be uploaded into the DART system at EPA
Region 4 and you will be notified via automated E-mail that the data was not successfully loaded.
You will need to correct any errors and resubmit the data along with the error report E-mail.
Instructions are included with the E-mail.
2.5 File Naming Convention
The data submittal is actually a .zip archive file containing one or more of the individual EDD files
(see Section 3).
The submitted file IS REQUIRED to be named according to the following convention after
processing with the EDP:
Lab Project NumberLab Project NameData ProviderYY MM DD_Type.EPAID.EPAR4.edd
Note:
Lab Project Number is provided by EPA Region 4.
Lab Project Name is provided by EPA Region 4.
Data Provider is provided by EPA Region 4
EPA ID is provided by EPA Region 4
The .edd file is a standard .zip file. The extension must be changed to .edd or the file will be rejected
by the USEPA E-mail server. EDP has an option to save the EDD with the .edd extension.
As an example:
P2015-0001_ChemicalCompanyABC_PL-DataProvider_15 01 08_Ch.l 10001224773,EPAR4.edd
• Task Code is P2015-001,
• Site Name is Chemical Company ABC,
• Company Code is PL-DataProvider,
• Date Submitting is January 8th, 2015 as 15 01 08,
• Type of data submission is Ch for Chemistry,
• Facility Code is 110001224773, and the
• Format used is EPAR4
This filename is in the E-mail you received when the proj ect was created at EPA Region 4 - you can
copy and paste to rename your .edd file.
NOTE: In order for EPA Region 4 to properly process the EDDs, the file naming convention must be
USEPA Region 4 Format Guide
Version 1.20 December 2015
11
-------
followed or the EDD will be sent back to the data provider for correction.
2.6 Valid Values
Valid values, also known as reference values or code lists, govern the contents of some fields in the
EDD files and create some of the drop-down menus in EDP. In other words, some fields may only
be populated with data that matches a value listed in the EPA Region 4 list of valid values in EDP
exactly. A list of all the data fields that must contain valid values is presented in Table 2-5. This list
is also cross-referenced to the EDD file(s) in which the field appears.
If data providers need to enter a value not already in the EPA Region 4 Valid Value List, they can
request the proposed addition to the valid value list by sending an E-mail to R4DART@epa.gov.
The data provider should explicitly state the valid value that she/he would like added, into what
reference table the value needs to be added, provide a description of the value, and explain why
the addition is necessary. In the case of requesting a new laboratory code, the data provider
should include the full name of the laboratory and its address and contact information. When
requesting an addition of an analyte, the data provider must include the appropriate CAS RN or
EPA ID along with a description of the analyte. The EPA SRS
(http://iaspub.epa.gov/sor internet/registry/substreg/home/overview/home.do) should be
thoroughly examined for an existing CAS RN before submitting a request for the addition of new
analytes. Select the "Search & Retrieve" tab and enter the CAS Number, Chemical name or other
identifier. Be certain to check for synonyms. This will minimize possible confusion with
conflicting numbers and small variations in chemical naming conventions. EQuIS relies on the
CAS RN as the primary key, not the chemical name.
Table 2-5 Valid Values and Associated EDD files
Valid Value Name
EDD Field Name
EDD Table
Analyte
cas rn, chemical name, dnapl cas rn.
Result, Water Level, VI Test
lnaplcasrn
Results QC
Analytical Method
(see Section 2.7 below)
labanlmethodname
Test, Result, VI Test Results
QC, VI Batches
Basin
locmajorbasin
Location
Building Type
buildingtype
VI Building Inspection
Building Use Type
buildingusetype
VI Building Inspection. VI
Task Parameters
Company
data_provider, drillingsubcontractor,
engineersubcontractor, inspectcontractor,
estabcompanycode, excavcompanycode,
samplingcompanycode, lab name code
subcontractor name code, construct contractor.
Location. All tables
containing data_provider.
Field Sample, Test, VI Test
Results QC, Alternate
Position
subcontractornamecode
Company Type
Not Currently Used
None
Coordinate Elevation Datum
elevdatumcode
Location, Alternate Position
Coordinate Elevation Method
elev collect method code.
Location, Alternate Position,
datumcollectmethodcode
Well Datum
Coordinate Geometric Type
geometrictypecode
Alternate Position
Coordinate Horizontal Datum
horzdatumcode
Alternate Position, VI
USEPA Region 4 Format Guide
Version 1.20 December 2015
12
-------
Table 2-5 Valid Values and Associated EDD files
Valid Value Name
EDD Field Name
EDD Table
Locations
Coordinate Horizontal Method
horzcollectmethodcode, verificationcode
Alternate Position, VI
Locations
Coordinate Type
coordtypecode
Location, Alternate Position
Coordinate Type Verification
verificationcode
Location, Alternate Position
County
county, loccountycode, state, locstatecode
VI Building Address, VI
Locations
Downhole Point Parameter Type
param
Downhole Point
Field Parameters
field_parameter
Field Results
Foundation Type
foundationtype
VI Building Inspection
Fraction
totalordissolved
Test, Result
Geologic Unit
geo unit code 1, geo unit code 2,
geo unit code 3, geo unit code 4,
geo_unit_code_5, geologic unit code
Lithology, Static Properties
Lab Parameters (View)
chemicalname
Result
Location Parameter Type
paramcode
Location Parameter
Location Type
loctype
Location, VI Locations
Material
material type, material name
Lithology, Static Properties
Matrix
matrix code, sample matrix code,
labmatrixcode
Geologic Sample, Field
Sample, Result, Field
Results, Test, VI Test
Results QC
Preparation Method
lab_prep_method_name, prep method
Test, VI Test Results QC
Preservative
preservative
Test, VI Test Results QC
Qualifier
interpretedqualifiers
VI Test Results QC
Result Type
resulttypecode
Result, VI Test Results QC
Sample Method
sample method, sampling technique,
samplingmethod
Geologic Sample, Field
Sample, VI Samples
Sample Parameter Type
paramcode
Geologic Sample Parameter,
Drill Parameter, Sample
Parameter
Sample Type
sampletypecode
Field Sample, Result, VI
Samples
State
loc state code, state
Location, Site, VI Building
Address, VI Locations
Stream
streamcode
Location
Sample Parameter Type
paramcode
Sample Parameters
Subfacility Parameter Type
paramcode
VI Building Parameters
Subfacility Type
sitetype
Site
Task Parameter Type
paramcode
VI Task Parameters
Task Type
activitycode
Occurs in multiple.
Test Batch Type
testbatchtype
Result
Test Type
testtype
Test, Result, VI Test Results
USEPA Region 4 Format Guide
Version 1.20 December 2015
13
-------
Table 2-5 Valid Values and Associated EDD files
Valid Value Name
EDD Field Name
EDD Table
QC, VI Batches
Unit
variousunit fields throughout all files
All Files
Well Segment Type
segmenttype, materialtypecode
Well Construction
2.7 Understanding Analytical Methods
Analytical Methods pose a special challenge in dealing with data from multiple sources and time
frames within a single database environment and must be handled with care to provide the most
accurate information possible. The EPA Region 4 valid value table, included with the reference files,
contains analytical methods based on the EPA laboratory nomenclature.
EPA Method CoderLab Analytical Code i.e., VOArEPA 8260B
In the example above," VOA" refers to volatile organic compounds while "EPA 8260B" refers to the
laboratory analytical method of discovery of volatile organic compounds by gas
chromatography/mass spectrometry (GC/MS). When formatting your own data to comply with the
standards necessary for input into the EPA Region 4 EQuIS System, it is important to ensure the
methods used in identifying your data fit properly into one of these standard methods. Table 2-6
below provides a list of EPA Method Codes.
For "Historical" data, if the analytical method is not known, the data may be reported using only the
appropriate "Method Code" from Table 2-6. For all "Current" data, the Method Code and Lab
Analytical Code are required.
Table 2-6 EPA Method Codes
EPA Method Code
Description
ALG
Algal Assay
ASBES
Asbestos
CLP
Contract Laboratory Program
CNA
Classical/Nutrient Analyses
DCNA
Dissolved Classical Nutrients
DIO
Dioxin
DMTL
Dissolved Metals
HERB
Herbicides
MICRO
Microbiology
NITRO
Nitroaromatics
OCP
Organochlorine Pesticides
OMTL
Organometallics
OPP
Organophosphorous Pesticides
PCBA
PCB Araclors
PCBC
PCB Congeners
PEST
Pesticides
PHYSP
Physical Properties
USEPA Region 4 Format Guide
Version 1.20 December 2015
14
-------
Table 2-6 EPA Method Codes
PSC
Particle Size Characterization
RAD
Alpha and Beta Emitters
SM
Classical/Nutrient Analyses
SMTL
Speciated Metals
SPLPD
SPLP Dioxin
SPLPM
SPLP Metals
SPLPP
SPLP Pesticides
SPLPS
SPLP Semi Volatiles
SPLPV
SPLP Volatiles
SVOA
Semi Volatile Organics
TCLPA
TCLP Aroclors
TCLPD
TCLP Dioxin
TCLPH
TCLP Herbicides
TCLPM
TCLP Metals
TCLPP
TCLP Pesticides
TCLPS
TCLP Semi Volatiles
TCLPV
TCLP Volatiles
TMTL
Total Metals
VOA
Volatile Organics
Historic Method Codes
HIST-CNA
Classical/Nutrient Analyses
HIST-DCNA
Dissolved Classical/Nutrients
HIST-DIO
Dioxin
HIST-DMTL
Dissolved Metals
HIST-HERB
Herbicides
HIST-NITRO
Historical Nitroaromatics
HIST-OCP
Organochlorine Pesticides
HIST-OMTL
Organometallics
HIST-OPP
Organophosphorous Pesticides
HIST-PCBA
PCB Aroclors
HIST-PCBC
PCB Congeners
HIST-PESTPCB
Pesticides/PCBs
HIST-PHYSP
Physical Properties
HIST-PSC
Particle Size Characterization
HIST-RAD
Historical Alpha and Beta Emitters
HIST-SMTL
Speciated Metals
HIST-SPLPM
SPLP Metals
HIST-SPLPP
SPLP Pesticides
HIST-SVOA
Semi-Volatiles
HIST-TCLPM
TCLP Metals
HIST-TCLPP
TCLP Pesticides
HIST-TCLPS
TCLP SemiVolatiles
HIST-TCLPV
TCLP Volatiles
HIST-TMTL
Total Metals
HIST-VOA
Volatile Organics
USEPA Region 4 Format Guide
Version 1.20 December 2015
15
-------
2.8 Reporting Results
Laboratory results (both Detects and Non-detects) must be reported as shown in the example below.
Each non-detect row must show an "N" in the detect flag field and must have values entered in the
quantitationlimit and detectionlimitunit fields (i.e., these fields cannot be left null. If no
quantitation limit is available for historic data, but a result value is available, a qualifier of "X"
should be added to the interpreted qualifiers column and a value of -999999 should be added to the
quantitation limit column. Table 2-7 presents an example of how to report a detect (1st row) and
non-detect (2nd row) data. Results with a text value, such as "NA," "ND," or other non-numeric
values should not be reported, as noted in the last row of Table 2-7.
Table 2-7 Exam
)le of Reporting Non-detects
result value
detect_flag
quantitation_limit
detection limit unit
lab_qualifiers
interp reted_qualifiers
0.15
Y
0.05
ug/ml
0.005
N
0.005
ug/ml
U
U
8.0
N
-999999
ug/ml
U
ux
5.2
Y
-999999
ug/ml
J
JX
N©
N
ug/ml
¥
2.9 Reporting Duplicate Lab Results
"Laboratory Duplicates" can occur for a variety of reasons including dilutions, equipment tolerances,
sample conditions, and holding time allowances. Results should be validated and only the final result
may be submitted to EPA Region 4. When duplicate results are reported from the lab and
determined by your RPM to report BOTH results, the Test Type specified in the EDD needs to
identify accurately the type of result. An example of how to report laboratory duplicates is presented
in Table 2-8 below.
Table 2-8 Example of Reporting Laboratory Duplicates
sys_sample_
code
lab_anl_method_
name
Analysis_
date
analysis_
time
testtype
casrn
chemical
_ name
result_ value*
C082402-10
TMTL:EPA 200.7
01/05/2008
12:41
initial
7440-36-0
Antimon
y
82
C082402-11
TMTL:EPA 200.7
01/05/2008
13:05
dilution
7440-36-0
Antimon
y
8500
* additional fields are required but have not been shown here for simplicity. All required fields
for a normal result are required here.
2.10 Reporting Tentatively Identified Compounds(TICs)
Tentatively Identified Compounds (TICs) present an additional challenge in reporting to a single
database as there is no standardized method of reporting from one source to another. TICs should be
reported when the following conditions are satisfied:
1) The compound is accompanied in the lab report by a valid CAS RN (or EPA ID) and
USEPA Region 4 Format Guide
Version 1.20 December 2015
-------
chemical name. Validity should be checked with EPA SRS.
2) The quantitationlimit is present.
3) A valid labanalyticalmethod was used and reported.
When reporting TICs that meet these conditions, the resulttypecode in the Result EDD must be set
to "TIC". Table 2-9 demonstrates the proper method for reporting this result data along with standard
data.
Table 2-9 Example of TIC Reporting
lab analytical method
cas in
chemical name
result value
result type code
SVOA:CLP SOMOl.O BS
26914-18-1
Methylanthracene
0.05
TIC
VOA:CLP SOMOl.O BS
51-28-5
2,4-Dinitrophenol
0.07
TRG
All ancillary information associated with reporting TRG chemical results should also accompany
TICs. These include sys sample code, labanlmethodname, analysis date, analysis time, cas rn,
chemicalname, result type code, quantitation limit, result unit, detectionlimitunit, and
testbatchid.
2.11 Using the EQulS Data Processor to Check EDD Formatting
All EDD files must be processed with the EQulS Data Processor (EDP) prior to submittal to EPA
Region 4. The EDP is a no-cost application that performs a series of formatting checks on EDD files
and then identifies any records that have errors, along with a description of those errors. This allows
the data provider to correct the errors before sending the files to EPA Region 4. EDD files that pass
through the EDP error-free should also result in error-free import at EPA Region 4.
The EDP, EPA Region 4 Format File, and EPA Region 4 Reference Values Files (.rvf) are
available as no-cost downloads from the EarthSoft website located at
http://earthsoft.com/produrisfedp/edp-format-for-epar4/
Instructions on how to install and use the EDP are also provided on the website and in the Region 4
EDP Reference Manual. You will need to register the EPA Region 4 Format with EarthSoft in order
to use EDP. Follow the instructions provided with EDP for registering properly. Contact R4DART
for the required authorization code to enter during registration. You will not be able to use EDP with
the correct EPA Region 4 Format without proper registration.
Once you have installed and licensed EDP, you should use this document as a guideline for
formatting your data to conform to the necessary EDD formats. Load the EPA Region 4 Format and
reference tables into EDP per the instructions in the Region 4 EDP Reference Manual. Import your
individual EDDs to the correct section (i.e. Geology, Chemistry. Or Basic Field Results) and test
your data for readiness to import.
USEPA Region 4 Format Guide
Version 1.20 December 2015
17
-------
3.0 EDD FILE FORMATS
This section contains detailed information regarding the files that make up the EPA Region 4 EDD Format. As stated in section 2.1, each
EDD file must be saved as individual text files and can be created using any software with the capability to create text files (including
EDP). If a column is limited to a specific number of characters, the limit will be given in parenthesis within the "Data Type" column (e.g.,
"Text (3)" signifies the value cannot exceed 3 characters in length). Columns marked "Required" must be reported for each row in the file.
If these fields are not reported, errors will be identified in the EDD and the EDD will need to be resubmitted. Columns marked "If
available" should also be reported if possible.
The Location file needs to be submitted as part of the first EDD submittal. This file need only be submitted once, unless information in the
file changes or additional information, such as a new sampling location, needs to be added.
For all other EDD files, EPA Region 4 will send a list of the EDDs expected for the data submittal.
Note: EPA Region 4 will provide the valid values for data_provider, task code and activity code for each data submittal.
3.1 Site EDD File
The Site EDD file contains general information about the site, including EPA Region 4 data providers' contact information. The Site
EDD file is typically submitted only once for new sites and must be part of the first EDD submittal. The site file only needs to be
resubmitted if the site information needs to be updated, such as the site name or data provider information for the site. When
resubmitting the site file, please notify EPA Region 4 that the site information has changed and needs to be updated.
Site EDD files should be named according to the following convention: EPAR4_Site_vl.csv or .txt for tab-delimited files.
Required
current/h istorical
Field Name
Data Type
Key
Parent
Description
sitecode
Text (20)
PK
current/historical
Use "Registry ID" (EnviroFacts) or "GNIS ID" (USGS NHD).
sitetype
Text (20)
Site type.
USEPA Region 4 Format Guide
Version 1.20 December 2015
18
-------
Field Name
Data Type
Key
Required
current/h istorical
Parent
Description
sitename
Text(60)
PK
Use "Facility Name (EnviroFacts) or "GNIS Name" (USGS
NHD).
sitetaskcode
Text(20)
Unused.
sitedescl
Text(255)
Alternate Name 1.
site_desc2
Text(255)
Alternate Name 2.
contactname
Text(50)
Unused.
addressl
Text(40)
Location Address (EnviroFacts).
address2
Text(40)
Supplemental Address (EnviroFacts).
city
Text(40)
City Name (EnviroFacts).
state
Text(10)
State (EnviroFacts).
zipcode
Text(30)
Zip/Postal Code (EnviroFacts).
phonenumber
Text(30)
Unused.
alt_phone_number
Text(30)
Unused.
faxnumber
Text(30)
Unused.
emailaddress
Text(lOO)
Unused.
3.2 Location EDD File
The Location file is typically submitted only once and must be part of the first EDD submittal. The location file only needs to be
resubmitted if a new sampling location is used, such as a new monitoring well, or to update previously submitted information. When
resubmitting the location file, only include data for the new locations and/or for the locations whose information is being updated. The
Location EDD file contains general information about sampling station locations and station ID numbers. This table does not need to be
resubmitted if information has previously been submitted to EPA Region 4 as an EDD.
Location EDD files should be named according to the following convention: EPAR4_Location_vl.csv or .txt for tab-delimited files.
USEPA Region 4 Format Guide
Version 1.20 December 2015
19
-------
Field Name
Data Type
Key
Required
current/h istorical
Parent
Description
data_provider
Text (20)
current/historical
Location data provider. Refer to Valid Values.
sysloccode
Text (20)
PK
current/historical
Unique Station or Well ID such as MW01, A24, SW12, or
SB2S, for all samples. Required for mapping. Must be a valid
code for the facility and will relate to the reported values in the
sys loc code fields in other EDDs related to this facility.
xcoord
Numeric
current/historical
X coordinate/Easting (WGS 1984 in decimal degrees ONLY).
ycoord
Numeric
current/historical
Y coordinate/Northing (WGS 1984 in decimal degrees ONLY).
coordtypecode
Text (20)
current/historical
Code representing coordinate system in which x coord and
y coord are expressed (WGS 1984). Refer to Valid Values.
verificationcode
Text (20)
current/historical
Verification Code for coordinates. This value represents the
accuracy with which the data locations were collected or
discovered. Refer to Valid Values.
loctype
Text (20)
current/historical
Media Code for the general location. In many cases, multiple
sample types (i.e., surface soil, subsurface soil) were collected.
Identify the initial media type here as multiple values are not
available and only one location should be specified under
sys loc code. Refer to Valid Values.
surfelev
Numeric
Elevation of the ground surface, or, if location is for surface
water samples, water surface elevation (Decimal feet).
elevunit
Text (15)
Required if
surf elev is
populated.
Surface elevation unit (Decimal feet). This is required if
surf elev is populated in this EDD. Refer to Valid Values.
elevdatumcode
Text (20)
Required if
surf elev is
populated.
Code that represents the reference datum used to determine the
vertical measure or elevation. This is required if surf elev is
populated in current or historic location EDDs. Refer to Valid
Values.
elevcollectmethodcode
Text (20)
Required if
surf elev is
populated.
Code that represents the method used to collect the vertical
measure or elevation of a reference point. This is required if
surf elev is populated in the location EDDs. Refer to Valid
Values.
parcelcode
Text (255)
Assessor's Parcel Number, if available.
USEPA Region 4 Format Guide
Version 1.20 December 2015
20
-------
Field Name
Data Type
Key
Required
current/h istorical
Parent
Description
locdesc
Text (255)
Location description
totaldepth
Numeric
Total depth associated with location.
depthtobedrock
Numeric
Depth to bedrock.
units
Text (15)
Required if
total depth or
depthtobedrock
is populated.
Depth units. Required if total depth or depth to bedrock is
populated. Refer to Valid Values.
bearing
Text (20)
Angle of variance from a given reference point (i.e. North).
plunge
Text (20)
Angle of variance (inclination) from horizontal.
locname
Text (40)
Location name. May be used for clarification or better
relationship to text, i.e. MW04 was originally MW-04.
loc_type_2
Text (20)
Location type 2. Used to clarify sampling locations used for
multiple media types, such as a bore hole that becomes a
permanent monitoring well. Refer to Valid Values.
loc_purpose
Text (20)
Location purpose.
sitecode
Text (20)
Site EDD,
sitecode
Code used to specify site for location. Must correlate with
site code in Site EDD.
startdate
Date
Date started at location. MM/DD/YYYY format.
enddate
Date
Date ended at location. MM/DD/YYYY format.
logdate
Date
Date log entry made. MM/DD/YYYY format.
surveydate
Date
Date survey was done. MM/DD/YYYY format.
surveyorname
Text (255)
Name of surveyor. May use name or data_provider.
driller
Text (50)
Name of driller. May use name or data_provider.
drillingsubcontractor
Text (20)
Code of drilling subcontractor. Refer to Valid Values.
drillingmethod
Text (40)
Simple description of drilling method.
geologist
Text (50)
Name of geologist. May use name or data_provider.
USEPA Region 4 Format Guide
Version 1.20 December 2015
21
-------
Field Name
Data Type
Key
Required
current/h istorical
Parent
Description
engineer
Text (50)
Name of engineer. May use name or data_provider.
engineersubcontractor
Text (20)
Engineering subcontractor. Refer to Valid Values.
inspector
Text (50)
Name of inspector.
inspectcontractor
Text (20)
Code of drilling subcontractor. Refer to Valid Values.
drawingchecker
Text (50)
Name of person checking the drawing.
drawingcheckdate
Date
Date drawing was checked. MM/DD/YYYY Format.
drawingeditor
Text (50)
Name of person editing the drawing.
drawingeditdate
Date
Date edit to drawing was made. MM/DD/YYYY format.
within_facility_yn
Text (1)
Yes/No value indicating if location is within facility. Y/N option
only.
loccountycode
Text (20)
Location county code.
locdistrictcode
Text (20)
Location district code.
locstatecode
Text (10)
Location state code. Refer to Valid Values.
locmajorbasin
Text (20)
Location major basin Refer to Valid Values.
locminorbasin
Text (20)
Location minor basin.
estabcompanycode
Text (20)
Location establishing company code. Refer to Valid Values.
excavcompanycode
Text (20)
Location excavation company code. Refer to Valid Values.
remark
Text (255)
Remark 1 - open text.
remark_2
Text (255)
Remark 2 - open text.
approved
Text (1)
Approval status. Y/N option only.
streamcode
Text (30)
Stream code.
streammile
Numeric
Stream mile.
custom_field_2
Text (255)
Custom field 2.
custom_field_3
Text (255)
Custom field 3.
USEPA Region 4 Format Guide 22
Version 1.20 December 2015
-------
Field Name
Data Type
Key
Required
current/h istorical
Parent
Description
custom_field_4
Text (255)
Custom field 4.
custom_field_5
Text (255)
Custom field 5.
3.3 Alternate Position EDD File
When multiple coordinates may exist for an identified location, an Alternate Position EDD may be submitted, ranking the locational
information according to preference.
Alternate Position EDD files should be named according to the following convention: EPAR4_AlternatePosition_vl.csv or .txt for tab-
delimited files.
Field Name
Data Type
Key
Required
current/h istorical
Parent
Description
sysloccode
Text (20)
PK
current/historical
Location EDD,
sysloccode
Unique Station or Well ID. Required for mapping. Must be a
valid code for the facility and must match one of the reported
values in the sys loc code field of the Location EDD file
submitted in the current or previous EDD.
coordtypecode
Text (20)
PK
current/historical
Unique identifier describing coordinate system in which this
location is referenced (WGS 1984). Refer to Valid Values.
identifier
Text (20)
PK
current/historical
Text identifier that facilitates unique representation of the
coordinate system.
observation date
Date
Date when position observation was made.
alt x coord
Text (20)
Alternate x coordinate. WGS 1984 in decimal degrees ONLY.
alt_y_coord
Text (20)
Alternate y coordinate. WGS 1984 in decimal degrees ONLY.
elev
Text (20)
Alternate elevation.
elev unit
Text (15)
Unit of measurement for the elevation. Refer to Valid Values.
horzcollectmethodcode
Text (20)
Code that represents the method used to determine the
coordinates for a point on the earth. Refer to Valid Values.
horzaccuracyvalue
Text (20)
Measure of the accuracy of the x, y coordinates.
USEPA Region 4 Format Guide
Version 1.20 December 2015
23
-------
Field Name
Data Type
Key
Required
current/h istorical
Parent
Description
horzaccuracyunit
Text (15)
Unit of measure used to quantify the measure of horizontal
accuracy. Refer to Valid Values.
horzdatumcode
Text (20)
Code that represents the reference datum used in determining x,
y coordinates. Refer to Valid Values.
elevcollectmethodcode
Text (20)
Code that represents the method used to collect the vertical
measure or elevation of a reference point.
Refer to Valid Values.
elevaccuracyvalue
Text (20)
Measure of accuracy of the elevation.
elevaccuracyunit
Text (15)
Unit of measure used to quantify the measure of vertical or
elevation accuracy. Refer to Valid Values.
elevdatumcode
Text (20)
Code that represents the reference datum used to determine the
vertical measure or elevation. Refer to Valid Values.
sourcescale
Text (20)
Represents the proportional distance on the ground for one unit
of measure on a map or photo.
subcontractornamecode
Text (20)
Code used to represent the subcontractor or party responsible
for providing coordinate information.
verificationcode
Text (20)
Code that represents the process used to verity the coordinate
information. Refer to Valid Values.
reference_point
Text (50)
Text that identifies the place for which geographic coordinates
were established.
geometrictypecode
Text (20)
Code that defines the geometric entity represented. As
sys loc code typically defines a location (borehole, well, etc.)
this will likely be "point". Refer to Valid Values.
remark
Text (255)
Remarks.
rank
Numeric
Integer that represents preference where more than one
coordinate system exists for a given sys loc code.
3.4 Site Location EDD File
The Site Location EDD identifies sampling locations in relation to the site as on site, off site or a background sample. It also ties a group of
locations as various sys loc code fields directly to a site code.
USEPA Region 4 Format Guide
Version 1.20 December 2015
24
-------
Site Location EDD files should be named according to the following convention: EPAR4_SiteLoc_vl. csv or .txt for tab-delimited files.
Field Name
Data Type
Key
Required
current/h istorical
Parent
Description
sysloccode
Text (20)
PK
current/historical
Location EDD,
sysloccode
Unique Well ID. Required for mapping. Must be a valid code for the facility
and must match one of the reported values in the sys loc code field of the
location EDD file submitted in the current or previous EDD.
site code
Text (20)
PK
current/historical
Use Registry ID (EnviroFacts) or GNIS ID (USGS NHD).
siteloctypecode
Text (10)
Code indicating whether the location is onsite, offsite or background.
gradient
Text (20)
Gradient at site.
3.5 Location Parameter EDD File
Additional location parameters are related by the Primary Key sys loc code. Parameter codes must be added to the rt_location_param_type
table first.
Location Parameter EDD files should be named according to the following convention: EPAR4_LocationParameter_vl.csv or .txt for
tab-delimited files.
Field Name
Data Type
Key
Required
current/h istorical
Parent
Description
sysloccode
Text (20)
PK
current/historical
Location EDD,
sysloccode
Unique Station ID. Required for mapping. Must be a valid code
for the facility and must match one of the reported values in the
sys loc code field of the Location EDD file submitted in the
current or previous EDD.
paramcode
Text (20)
Code used to identify parameter being measured.
paramvalue
Text (240)
Value of parameter.
paramunit
Text (15)
Unit of parameter measurement.
measurementmethod
Text (20)
Method used to measure parameter.
measurementdate
Date
Date of measure parameter.
remark
Text (255)
Remarks about parameter.
USEPA Region 4 Format Guide
Version 1.20 December 2015
25
-------
3.6 Field Results EDD File
Field results may be entered for current and historical sampling events where field information such as temperature, pH, turbidity, salinity,
and dissolved oxygen was captured. The field_parameter column should be used to identify what was captured with the results of that test in
the result value column and the appropriate units in the result unit column. Field parameters not identified in the valid values table for
Field Results should be submitted to EPA with an explanation of the parameter, its units, and result type (numeric, text, etc.) for possible
inclusion into the valid values.
Field Results EDD files should be named according to the following convention: EPAR4_FieldResults_vl.csv or .txt for tab-delimited
files.
Field Name
Data Type
Key
Required
current/h istorical
Parent
Description
data_provider
Text (20)
current/historical
Field Results data provider. Refer to Valid Values.
taskcode
Text (20)
current/historical
Task Code. Task code, sample name, and result date combined
cannot exceed 40 characters.
activitycode
Text (20)
current/historical
Activity type. Refer to Valid Values.
sysloccode
Text (20)
PK
current/historical
Unique Station ID. Required for mapping. Must be a valid code for
the facility and must match one of the reported values in the
sys loc code field of the Location EDD file submitted in the current
or previous EDD.
samplename
Text (17)
PK
current/historical
Sample ID. Task code, sample name, and result date combined
cannot exceed 40 characters.
field_parameter
Text (15)
PK
current/historical
Field parameter. Refer to Valid Values.
startdepth
Numeric
Sample start depth.
enddepth
Numeric
Sample end depth.
depthunit
Text (2)
Sample depth unit. Refer to Valid Values.
resultdate
DateTime
PK
current/historical
Result date/time (mm/dd/yr hh:mm:ss). Task code, sample name,
and result date combined cannot exceed 40 characters.
USEPA Region 4 Format Guide
Version 1.20 December 2015
26
-------
Field Name
Data Type
Key
Required
current/h istorical
Parent
Description
resultvalue
Numeric
Result value. Values 'R4C' and 'R4E' are acceptable qc level values
but are not present in the drop down menu. If detect flag = N then
quantitation limit is required.
resultunit
Text (15)
current/historical
Result unit. Refer to Valid Values.
quantitationlimit
Text (20)
Quantitation limit.
sample_matrix_code
Text (20)
current/historical
Sample matrix code. Refer to Valid Values.
qualifier
Text (20)
Qualifier.
samplingreason
Text (30)
sampling reason.
samplemethod
Text (40)
Sampling method.
reportableresult
Text (3)
current/historical
Reportable result. The default value is "Yes". Y/N option only.
valuetype
Text (10)
current/historical
How value was derived. Actual/Calculated/Estimated option only.
detectflag
Text (1)
current/historical
May be either Y for detected analytes or N for non-detects. If
detect_flag='N' then quantitation limit is required otherwise
result value is required.
remark
Text (225)
Unused in Region 4.
3.7 Chemistry Field Sample EDD File
The Chemistry Field Sample EDD is the crucial file by which all chemistry result data is based. Syssamplecode uniquely identifies each
sample that was returned from a laboratory with a result. This value is unique throughout the database, even for resamples, duplicates, etc.
This does not represent the value submitted on the chain of custody when sending samples to the lab for analyses (which should be
populated in the samplename column).
Chemistry Field Sample EDD files should be named according to the following convention: EPAR4_FSample_vl.csv or .txt for tab-
delimited files.
USEPA Region 4 Format Guide
Version 1.20 December 2015
27
-------
Data
Type
Required
current/h istorical
Field Name
Key
Parent
Description
syssamplecode
Text (40)
PK
current/historical
Lab Sample ID. For data generated by R4 and CLP, prefix
Lab Sample ID with R4. Each sample must have a unique
value, including spikes and duplicates. Laboratory QC
samples must also have unique identifiers. Uniqueness
throughout the database is the only restriction enforced by
EQuIS.
samplename
Text (30)
Sample ID from COC. Is not required to be unique (i.e.,
duplicates are OK). This is commonly called a Field Sample
ID.
samplematrixcode
Text (10)
current/historical
Media Code. Refer to Valid Values. If
sample_matrix_code=SF, SB, or SD then start depth,
end depth and depth unit are required.
sampletypecode
Text (10)
current/historical
Code to distinguish between field samples (N) and field
QA/QC and Laboratory samples. Refer to Valid Values.
samplesource
Text (10)
current/historical
This field identifies where the sample came from. In this
import, this should always be Field. Default value is "Field."
Field/Lab option only.
parentsamplecode
Text (40)
Sample EDD,
syssamplecode
The value of "sys sample code" that uniquely identifies the
sample that was the source of this sample. For example, the
value of this field for a duplicate sample would identify the
normal sample of which this sample is a duplicate. Required
in the laboratory EDD for all laboratory "clone" samples
(e.g., spikes and duplicates). Field duplicates may be
submitted blind to the laboratory, so this field is not required
in the laboratory EDD for field "clones". Must be blank for
samples which have no parent (e.g., normal field samples,
LCS samples, method blanks, etc.). Parent sample code is
required where sample type code=BD, FD, FR, FS, LR,
MS, SD, orMSD.
USEPA Region 4 Format Guide
Version 1.20 December 2015
28
-------
Field Name
Data
Type
Key
Required
current/h istorical
Parent
Description
projectnumber
Text (20)
The unique Project Number that is assigned to individual
labs or events under a given Project ID. If the project uses
multiple labs or is preparing multiple COCs or undergoing
multiple events during the course of the project this will be
unique for each lab event/EDD submittal.
sampledate
Date
Date/time sample was collected in the field. Date
information must be identical with the date from the
AR/COC form. Leave blank for lab samples. Year may be
entered in abbreviated YY format.
Sample date cannot be null when when
sample_type_code='TB', 'N', 'MB', 'FD', 'FB', 'EB', 'AB'.
sampletime
Time
Time sample was collected in the field. Time information
must be identical with the date from the AR/COC form.
Leave blank for lab samples.
sysloccode
Text (20)
Unique Station or Well ID. Required for mapping. Must be a
valid code for the facility and must match one of the reported
values in the sys loc code field of the location EDD file
submitted in the current or previous EDD. Sys loc code is
required where sample_type_code='N'. Child and parent
sample must have the same sys loc code.
startdepth
Numeric
Beginning depth (top) of soil sample. This is an optional
field for the laboratory EDD unless otherwise specified by
the Chem. project manager. If the start depth or end depth
is populated then depth unit is required
enddepth
Numeric
Ending depth (bottom) of soil sample. This is an optional
field for the laboratory EDD unless otherwise specified by
the Chem. project manager. If start depth is not null,
end depth must be greater than start depth.
USEPA Region 4 Format Guide
Version 1.20 December 2015
29
-------
Field Name
Data
Type
Key
Required
current/h istorical
Parent
Description
depthunit
Text (15)
Only if startdepth or
enddepth is populated;
or if
sample_matrix_code=SF,
SB, or SD
Unit of measurement for the sample begin and end depths.
IRPIMS-style unit of measurement codes (see table X03) are
recognized by Chem; other codes may be allowed by the
Chem. project manager. This is an optional field for the
laboratory EDD unless otherwise specified by the Chem.
project manager. Refer to Valid Values.
chainofcustody
Text (15)
Chain of custody identifier. A single sample may be assigned
to only one chain of custody. This is an optional field for
laboratory EDD unless otherwise specified by the Chem.
project manager.
senttolabdate
Date
Date sample was sent to lab (in MM/DD/YY format for
EDD). Not included in the laboratory EDD.
samplereceiptdate
Date
Date that sample was received at laboratory (in MM/DD/YY
format for EDD).
sampler
Text (30)
Name or initials of sampler. Not included in the laboratory
EDD.
samplingcompanycode
Text (20)
current/historical
Name or initials of sampling company (no controlled
vocabulary). Not included in the laboratory EDD. Refer to
Valid Values.
samplingreason
Text (30)
Optional reason for sampling. No controlled vocabulary is
enforced. Not included in the laboratory EDD.
samplingtechnique
Text (40)
Sampling technique (no controlled vocabulary). Not
included in the laboratory EDD. Refer to Valid Values.
projectid
Text (20)
current/historical
The Project ID is the EPA tracking number for the entire
project, including multiple field events.
activitycode
Text (20)
current/historical
The program activity associated with the sampling event
(e.g. CME, SI, ODMDS, etc.). Refer to Valid Values.
collectionquarter
Text (5)
Quarter of the year sample was collected (e.g., "1Q96"). Not
included in the laboratory EDD.
composite_yn
Text (1)
Sample collection location. Boolean field used to indicate
whether a sample is a composite sample. Not included in the
laboratory EDD. Y/N option only.
USEPA Region 4 Format Guide
Version 1.20 December 2015
30
-------
Field Name
Data
Type
Key
Required
current/h istorical
Parent
Description
compositedesc
Text (255)
Description of composite sample (if composite_yn is 'Y').
Not included in the laboratory EDD.
sampleclass
Text (10)
Sample class code. Not included in the laboratory EDD.
custom field 1
Text (255)
Custom sample field.
custom field 2
Text (255)
Custom sample field.
custom field 3
Text (255)
Custom sample field.
comment
Text (255)
Sample comments as necessary (optional).
samplereceipttime
Time
Time of lab receipt sample in 24-hr (military) HH:MM
format.
sampledeliverygroup
Text (10)
Sample delivery group as defined by the Chem project
manager. This is an optional field for the laboratory EDD
unless otherwise specified by the Chem. project manager.
catsamplecode
Text (50)
Do not edit, this code is to match Sample with Field Sample.
3.8 Chemistry Sample Parameter EDD File
The Chemistry Sample Parameter EDD contains associated information about the sample that may not have an analytical method or a true
numerical type result. Use the Sample Parameter EDD to record parameter data associated with a sample sent to a lab. This is commonly
used for measurements associated with biological data to record information about the species being analyzed.
Chemistry Sample Parameter EDD files should be named according to the following convention: EPAR4_SampleParameter_vl.csv or
.txt for tab-delimited files.
USEPA Region 4 Format Guide
Version 1.20 December 2015
31
-------
Field Name
Data
Type
Key
Required
current/h istorical
Parent
Description
syssamplecode
Text (40)
PK
current/historical
Lab Sample ID. For data generated by R4 and CLP, prefix
Lab Sample ID with R4. Each sample must have a unique
value, including spikes and duplicates. Laboratory QC
samples must also have unique identifiers. Uniqueness
throughout the database is the only restriction enforced by
EQuIS.
paramcode
Text (20)
PK
current/historical
Unique code for sample parameter type. Refer to Valid
Values: must exist in the rt_sample_param_type.param_code
table.
measurementdate
DateTime
PK
current/historical
Date of sample parameter measurement in MM/DD/YYYY
HH:MM:SS format.
paramunit
Text (15)
Unit of measurement for subsample amount. Refer to Valid
Values.
paramvalue
Text (255)
Sample parameter value.
measurementmethod
Text (20)
Measurement method.
remark
Text
(2000)
Parameter measurement specific comment.
3.9 Chemistry Test EDD File
The Test EDD ties the Field EDD to the results by correlating the analytical method used with the sys sample code, along with the analysis
date and time. It is important to follow the guidelines for the EPA Region 4 sys sample code. The R4 prefix is reserved for SESD/CLP
LIMS (Element) data. Regardless of the sample's source, the sys sample code must match the Chemistry Field Sample section's
sys sample code to tie the tests back to the sample. Lab analytical methods should be matched to the valid values. Historical data should be
matched as closely as possible or an R4-HIST or HIST method should be chosen that matches. When these assumptions are made, they
should be listed in the technical documentation of the conversion. When no suitable analytical method can be found, the method should be
submitted to EPA Region 4 along with a description for review and possible inclusion in the valid values tables.
When preparing data from non-SESD/CLP labs, as well as historical data, certain required data such as the analysis date and time may be
USEPA Region 4 Format Guide 22
Version 1.20 December 2015
-------
missing. A convention has been created to handle these inconsistencies and enter data successfully into the EPA EQuIS system. When the
analysis date is not present in the lab report, it should be reported in the EDD using the same date as the sample date but with an analysis
time of 23:59. This will allow the user to easily identify data with missing analysis dates, and to recognize that information was missing.
Data results with an analysis date but no time should be given an analysis time of 23:59. It's important not to use arbitrary times that are
similar to other actual data times as these give the appearance that the data set was complete and not simply an estimate.
At any time there is estimated information populated you must include a comment describing what was added within the comment field.
Chemistry Test EDD files should be named according to the following convention: EPAR4_TST_vl.csv or .txt for tab-delimited files.
Field Name
Data Type
Key
Required
current/h istorical
Parent
Description
syssamplecode
Text (40)
PK
current/historical
Field Sample
EDD,
syssamplecode
Lab Sample ID. For data generated by SESD LIMS (Element), prefix
Lab Sample ID with R4. Each sample must have a unique value,
including spikes and duplicates. Laboratory QC samples must also have
unique identifiers. Uniqueness throughout the database is the only
restriction enforced by EQuIS.
labanlmethodname
Text (20)
PK
current/historical
Laboratory analytic method name or description. A controlled
vocabulary (i.e., list of valid method names) is not required for the
laboratory EDD unless otherwise specified by the EQuIS Chemistry
project manager. The method name should be sufficient to reflect
operation of the laboratory. For example both "SW8080-pest" and
"SW8080-PCB" may be necessary to distinguish between laboratory
methods, while "SW8080" may not provide sufficient detail. Refer to
Valid Values.
analysisdate
Date
PK
current/historical
Date of sample analysis in MM/DD/YY format. May refer to either
beginning or end of the analysis as required by EQuIS Chemistry
project manager. This field is not always required, but most users will
want it.
analysistime
Time
PK
current/historical
Time of sample analysis in 24-hr (military) HH:MM format. Time
zone and daylight savings must be same as analysis date.
USEPA Region 4 Format Guide
Version 1.20 December 2015
33
-------
Field Name
Data Type
Key
Required
current/h istorical
Parent
Description
totalordissolved
Text (10)
PK
current/historical
If required, then it must be either "T" for total [metal] concentration,
"D" for dissolved or filtered [metal] concentration, or "N" for organic
(or other) constituents for which neither "total" nor "dissolved" is
applicable. This field might be required, depending on the test primary
key used by the EQuIS Chemistry user. Default value is "N". Refer to
Valid Values.
columnnumber
Text (2)
If required, then it must be either" 1C" for first column analyses, "2C"
for second column analyses, or "NA" for analyses for which neither
"1C" nor "2C" is applicable. Second column data may not be required,
depending on the needs identified by the EQuIS Chemistry project
manager, in which case all results may be reported as "NA". However,
if any "2C" tests are reported, then there must be corresponding " 1C"
tests present also. Also, laboratories typically can report which of the
two columns is to be considered "primary". This distinction is handled
by the "reportable result" field in the result table. This field might be
required, depending on the test primary key used by the EQuIS
Chemistry user. 1C/2C/NA option Only. Default is "NA".
testtype
Text (20)
PK
current/historical
Type of test in the laboratory. This field is used to distinguish between
initial runs, re-extractions, reanalysis and dilutions. Refer to Valid
Values.
labmatrixcode
Text (10)
Code which describes the matrix as analyzed by the lab. May differ
from sample matrix code. Default is "NA". Refer to Valid Values.
analysislocation
Text (2)
current/historical
Note where sample was analyzed. FL for mobile Field Laboratory
analysis, LB for fixed Based Laboratory analysis or FI for Field
Instrument. FL/LB/FI option only.
basis
Text (10)
Must be either Wet for wet weight basis reporting. Dry for dry weight
basis reporting, or null for tests for which this distinction is not
applicable. Wet/Dry option only.
containerid
Text (30)
Sample container identifier. This is an optional field for the laboratory
EDD unless otherwise specified by the EQuIS Chemistry project
manager.
USEPA Region 4 Format Guide
Version 1.20 December 2015
34
-------
Field Name
Data Type
Key
Required
current/h istorical
Parent
Description
dilutionfactor
Numeric
Dilution factor at which the analyte was measured effectively. Enter
"1" if not diluted.
lab_prep_method_name
Text (20)
Laboratory sample preparation method code. A controlled vocabulary.
Refer to Valid Values.
prepdate
Date
Date sample preparation began in the MM/DD/YYYY format.
preptime
Time
Time sample preparation began in 24-hr (military) format. Time zone
and daylight savings must be same as analysisdate.
leachatemethod
Text (15)
Laboratory leachate generation method name or description. A
controlled vocabulary (i.e., list of valid method names) is not required
for the laboratory EDD unless otherwise specified by the EQuIS
Chemistry project manager. The method name should be sufficient to
reflect operation of the laboratory.
leachatedate
Date
Date of leachate preparation in the MM/DD/YYYY format.
leachatetime
Time
Time of leachate preparation in 24-hr (military) format. Time zone and
daylight savings must be same as analysis date.
labnamecode
Text (20)
current/historical
Unique identifier of the laboratory as defined by the EPA. Refer to
Valid Values.
qclevel
Text (10)
current/historical
Quality Control Level. Please enter the Corresponding Label Code
from Appendix B of the Guidance for Labeling Externally Validated
Laboratory Analytical Data for Superfund Use.
labsampleid
Text (20)
Laboratory LIMS sample identifier. If necessary, a field sample may
have more than one LIMS lab sample id (maximum one per each test
event). Lab sample id cannot be null when analysis_location=LB.
percentmoisture
Text (5)
Percent moisture of the sample portion used in this test; this value may
vary from test to test for any sample. Report 70.1% as 70.1 not as
70.1%.
subsampleamount
Text (14)
Amount of sample used for test.
subsampleamountunit
Text (15)
Unit of measurement for subsample amount. Refer to Valid Values.
analystname
Text (30)
Name or initials of laboratory analyst. This is an optional field for the
laboratory EDD unless otherwise specified by the EQuIS Chemistry
project manager.
USEPA Region 4 Format Guide
Version 1.20 December 2015
35
-------
Field Name
Data Type
Key
Required
current/h istorical
Parent
Description
instrumentid
Text (50)
Instrument identifier. This is an optional field for the laboratory EDD
unless otherwise specified by the EQuIS Chemistry project manager.
comment
Text (255)
Comments about the test as necessary. This is an optional field for the
laboratory EDD unless otherwise specified by the EQuIS Chemistry
project manager.
preservative
Text (20)
Sample preservative used. Refer to Valid Values.
finalvolume
Text (15)
The final volume of the sample after sample preparation. Include all
dilution factors.
finalvolumeunit
Text (15)
The unit of measure that corresponds to the final volume. Refer to
Valid Values.
3.10 Chemistry Result EDD File
The Chemistry Sample Result EDD file contains result data for laboratory analyses only. For surface water samples, record the sample
depths, start depth (field 9), and end depth (field 10), as depth below the water surface elevation. The water surface elevation at the time
of the sampling should be recorded in the Water Level file (see Section 3.11).
Chemistry Results EDD files should be named according to the following convention: EPAR4_RES_vl.csv or .txt for tab-delimited files.
Data
Type
Required
current/h istorical
Field Name
Key
Parent
Description
syssamplecode
Text (40)
PK
current/historical
Test EDD,
syssamplecode
Lab Sample ID. For data generated by SESD LIMS (Element),
prefix Lab Sample ID with R4. Each sample must have a unique
value, including spikes and duplicates. Laboratory QC samples
must also have unique identifiers. Uniqueness throughout the
database is the only restriction enforced by EQuIS.
USEPA Region 4 Format Guide
Version 1.20 December 2015
36
-------
Field Name
Data
Type
Key
Required
current/h istorical
Parent
Description
labanlmethodname
Text (20)
PK
current/historical
Test EDD,
labanlmethodname
Laboratory analytic method name or description. A controlled
vocabulary (i.e., list of valid method names) is not required for
the laboratory EDD unless otherwise specified by the EQuIS
Chemistry project manager. The method name should be
sufficient to reflect operation of the laboratory. For example both
"SW8080-pest" and "SW8080-PCB" may be necessary to
distinguish between laboratory methods, while "SW8080" may
not provide sufficient detail. Refer to Valid Values.
analysisdate
Date
PK
current/historical
Test EDD,
analysisdate
Date of sample analysis in MM/DD/YY format. May refer to
either beginning or end of the analysis as required by EQuIS
Chemistry project manager. This field is not always required, but
most users will want it.
analysistime
Time
PK
current/historical
Test EDD,
analysis time
Time of sample analysis in 24-hr (military) HH:MM format.
Time zone and daylight savings must be same as analysis date.
totalordissolved
Text (10)
PK
current/historical
Test EDD,
totalordissolved
If required, then it must be either "T" for total [metal]
concentration, "D" for dissolved or filtered [metal]
concentration, or "N" for organic (or other) constituents for
which neither "total" nor "dissolved" is applicable. This field
might be required, depending on the test primary key used by the
EQuIS Chemistry user. Default Value is "N". Refer to Valid
Values.
USEPA Region 4 Format Guide
Version 1.20 December 2015
37
-------
Field Name
Data
Type
Key
Required
current/h istorical
Parent
Description
columnnumber
Text (2)
If required, then it must be either "1C" for first column analyses,
"2C" for second column analyses, or "NA" for analyses for
which neither "1C" nor "2C" is applicable. Second column data
may not be required, depending on the needs identified by the
EQuIS Chemistry project manager, in which case all results may
be reported as "NA". However, if any "2C" tests are reported,
then there must be corresponding "1C" tests present also. Also,
laboratories typically can report which of the two columns is to
be considered "primary". This distinction is handled by the
"reportable result" field in the result table. This field might be
required, depending on the test primary key used by the EQuIS
Chemistry user.
Default value is "NA". 1C/2C/NA option only.
testtype
Text (20)
PK
current/historical
Test EDD,
testtype
Type of test in the laboratory. This field is used to distinguish
between initial runs, re-extractions, reanalysis and dilutions.
Default value is "NA". Refer to Valid Values.
cas rn
Text (15)
PK
current/historical
CAS Number or R4 designation. Refer to Valid Values.
chemical name
Text (60)
current/historical
Chemical or Analyte Name
result value
Numeric
Result Value
resulterrordelta
Text (20)
Error range applicable to the result value; typically used only for
radiochemistry results.
resulttypecode
Text (3)
current/historical
Must be either TRG for a target or regular result, TIC for
tentatively identified compounds, SUR for surrogates, IS for
internal standards, or SC for spiked compounds. Refer to Valid
Values.
reportableresult
Text (3)
current/historical
Must be Yes for results considered to be reportable, or No for
other results. Y/N option only.
detectflag
Text (1)
current/historical
May be either Y for detected analytes or N for non-detects. Y/N
option only. If detect_flag='N' and result_type_code='TRG', 'TIC'
or 'SC' then quantitation limit is required.
labqualifiers
Text (20)
Qualifier flags assigned by the lab. This is a controlled
vocabulary column. Qualifiers are separated by commas. Refer to
Valid Values.
USEPA Region 4 Format Guide
Version 1.20 December 2015
38
-------
Field Name
Data
Type
Key
Required
current/h istorical
Parent
Description
validatorqualifiers
Text (40)
Qualifier flags assigned by the validator. This is a controlled
vocabulary column. Refer to Valid Values.
interpretedqualifiers
Text (20)
Final qualifier flags. This is a controlled vocabulary column.
Refer to Valid Values.
organic_yn
Text (1)
Must be either Y for organic constituents or N for inorganic
constituents. Default value is "Y". Y/N option only.
method detection limit
Text (20)
Method detection limit.
reportingdetectionlimit
Numeric
Concentration level above which results can be quantified with
95% confidence limit. Must reflect conditions such as dilution
factors and moisture content. Report as the sample specific
detection limit.
quantitationlimit
Text (20)
Concentration level above which results can be quantified with
95% confidence limit. Must reflect conditions such as dilution
factors and moisture content. Report as the sample specific
quantitation limit. If detect_flag='N' and
result_type_code='TRG', 'TIC' or 'SC' then quantitation limit is
required.
resultunit
Text (15)
current/historical
Units of measurement for the result unit. Controlled vocabulary.
This field is required if a reporting detection limit is reported.
Refer to Valid Values.
detectionlimitunit
Text (15)
Required if
reportingdetectionlimit
is populated.
Units of measurement for the detection limit(s). Controlled
vocabulary. This field is required if a reporting detection limit is
reported. Refer to Valid Values.
tic retention time
Text (8)
TIC Retention Time.
result comment
Text (254)
Result specific comments.
qcoriginalconc
Text (14)
The concentration of the analyte in the original (unspiked)
sample. Might be required for spikes and spike duplicates
(depending on user needs). Not necessary for surrogate
compounds or LCS samples where the original concentration is
assumed to be zero.
qcspikeadded
Text (14)
The concentration of the analyte added to the original sample.
Might be required for spikes, surrogate compounds, LCS and any
spiked sample (depending on user needs).
USEPA Region 4 Format Guide
Version 1.20 December 2015
39
-------
Field Name
Data
Type
Key
Required
current/h istorical
Parent
Description
qcspikemeasured
Text (14)
The measured concentration of the analyte. Use zero for spiked
compounds that were not detected in the sample. Might be
required for spikes, spike duplicates, surrogate compounds, LCS
and any spiked sample (depending on user needs).
qcspikerecovery
Text (14)
The percent recovery calculated as specified by the laboratory
QC program. Always required for spikes, spike duplicates,
surrogate compounds, LCS and any spiked sample. Report as
percentage multiplied by 100 (e.g., report 120% as 120).
qcduporiginalconc
Text (14)
The concentration of the analyte in the original (unspiked)
sample. May be required for spike or LCS duplicates only
(depending on user needs). Not necessary for surrogate
compounds or LCS samples (where the original concentration is
assumed to be zero).
qcdupspikeadded
Text (14)
The concentration of the analyte added to the duplicate sample.
Might be required for spike or LCS duplicates, surrogate
compounds, and any spiked and duplicated sample (depending on
user needs).
qcdupspikemeasured
Text (14)
The measured concentration of the analyte in the duplicate. Use
zero for spiked compounds that were not detected in the sample.
Might be required for spike and LCS duplicates, surrogate
compounds, and any other spiked and duplicated sample.
qcdupspikerecovery
Text (14)
The duplicate percent recovery calculated as specified by the
laboratory QC program. Always required for spike or LCS
duplicates, surrogate compounds, and any other spiked and
duplicated sample. Report as percentage multiplied by 100 (e.g.,
50% as 50).
qcrpd
Text (8)
The relative percent difference calculated as specified by the
laboratory QC program. Required for duplicate samples as
appropriate. Report as percentage multiplied by 100 (e.g., report
30% as 30).
USEPA Region 4 Format Guide
Version 1.20 December 2015
40
-------
Field Name
Data
Type
Key
Required
current/h istorical
Parent
Description
qcspikelcl
Text (8)
Lower control limit for spike recovery. Required for spikes,
spike duplicates, surrogate compounds, LCS and any spiked
sample. Report as percentage multiplied by 100 (e.g., report 60%
as 60).
qcspikeucl
Text (8)
Upper control limit for spike recovery. Required for spikes,
spike duplicates, surrogate compounds, LCS and any spiked
sample. Report as percentage multiplied by 100 (e.g., report
120% as 120).
qcrpdcl
Text (8)
Relative percent difference control limit. Required for any
duplicated sample. Report as percentage multiplied by 100 (e.g.,
report 25% as 25).
qcspikestatus
Text (10)
Used to indicate whether the spike recovery was within control
limits. Use the * character to indicate failure, otherwise leave
blank. Required for spikes, spike duplicates, surrogate
compounds, LCS and any spiked sample.
qcdupspikestatus
Text (10)
Used to indicate whether the duplicate spike recovery was within
control limits. Use the * character to indicate failure, otherwise
leave blank. Required for any spiked and duplicated sample.
qcrpdstatus
Text (10)
Used to indicate whether the relative percent difference was
within control limits. Use the * character to indicate failure,
otherwise leave blank. Required for any duplicated sample.
testbatchtype
Text (10)
Required if test batch id
is populated.
Lab Batch type. Refer to Valid Values. If test batch id is
populated, then test batch type is required.
testbatchid
Text (20)
Required if
test batch type is
populated.
Unique identifier for all lab batches. If test batch type is
populated, then test batch id is required.
3.11 Well EDD File
USEPA Region 4 Format Guide
Version 1.20 December 2015
41
-------
The Well EDD file should be submitted for all sites where extraction and/or injection wells are a part of the remedial work at the site. The
purpose of the Well EDD file is to provide EPA Region 4 with detailed well information that may be correlated with water levels, well
construction and geologic information. It also provides pump type, capacity and yield information.
Well EDD files should be named according to the following convention: EAPR4_Well_vl.csv or .txt for tab-delimited files.
Field Name
Data Type
Key
Required
current/h istorical
Parent
Description
data_provider
Text (20)
current/historical
EDD provider. Refer to Valid Values.
task code
Text (20)
current/historical
Unique identifier for sampling event.
activitycode
Text (20)
current/historical
The program activity associated with the sampling event (e.g. CME,
SI, ODMDS, etc.). Refer to Valid Values.
sysloccode
Text (20)
PK
current/historical
Location EDD,
sysloccode
Unique Well ID. Required for mapping. Must be a valid code for the
facility and must match one of the reported values in the
sys loc code field of the Location EDD file submitted in the current
or previous EDD.
topcasingelev
Numeric
current/historical
Elevation of the top of well casing.
well id
Text (30)
Non-unique well identifier, name, or alias.
well owner
Text (50)
Owner of well.
well_purpose
Text (20)
Brief description of well use (i.e. Monitoring, Injection, Extraction,
Water Supply, etc.)
well status
Text (20)
Current status of well.
depthofwell
Numeric
Total depth of the well as measured from a specified measuring point
at construction.
depthunit
Text (15)
Unit of measure for well depth. Refer to Valid Values.
depthmeasuremethod
Text (20)
Method used to make the well depth measurement.
stickupheight
Text (8)
Total height which well extends about ground surface.
stickupunit
Text (15)
Unit of measure for stickup height. Refer to Valid Values.
sumplength
Text (20)
Length of sump at well installation.
sumpunit
Text (15)
Unit of measure for sump length. Refer to Valid Values.
installation date
Date
Date well was installed.
construct start date
Date
Date well construction began, if different from installation date.
USEPA Region 4 Format Guide
Version 1.20 December 2015
42
-------
Field Name
Data Type
Key
Required
current/h istorical
Parent
Description
constructcompletedate
Date
Date well construction was completed, if different from installation
date.
constructcontractor
Text (20)
Name of company that installed well.
pumptype
Text (20)
Type of pump installed, if applicable.
pumpcapacity
Text (6)
Pump capacity.
pumpunit
Text (15)
Unit of measure for pump capacity (i.e. gal/minute). Refer to Valid
Values.
pump_yield
Text (6)
Pump yield
pump_yield_method
Text (20)
Method of testing pump yield.
weephole
Text (1)
Does well have weep hole? Y/N option only.
headconfiguration
Text (50)
Description of well head configuration.
access_port_yn
Text (1)
Does well have access port? Y/N option only.
casing J ointtype
Text (50)
Description of well casing joint type.
perforatorused
Text (50)
Description of well perforator, if applicable.
intakedepth
Numeric
Depth of well intake.
disinfected_yn
Text (1)
Has well been disinfected? Y/N option only.
liistorical reference elev
Numeric
Elevation of reference measuring point.
geologicunitcode
Text (20)
Geologic unit which well samples from.
geologistname
Text (50)
Geologist name.
driller
Text (50)
Driller.
custom field 1
Text (255)
Custom field 1.
custom field 2
Text (255)
Custom field 2.
custom field 3
Text (255)
Custom field 3.
custom field 4
Text (255)
Custom field 4.
custom field 5
Text (255)
Custom field 5.
remark
Text (2000)
Remark.
USEPA Region 4 Format Guide
Version 1.20 December 2015
43
-------
3.12 Well Construction EDD File
The Well Construction EDD allows for the input of information regarding the materials, sizes, and program associated with well
installation. Wells should be tied to a location by a valid sys loc code populated in the Location EDD (which may have been previously
submitted). Record the activity code, task code, and other information related to the specific installation event.
Well Construction EDD files should be named according to the following convention: EPAR4_WellConstruction_vl.csv or .txt for tab-
delimited files.
Please note: The Well Construction section (EPAR4_WellConstruction_vl) needs to be submitted during the initial Well set-up and
should minimally include the segment type of 'Screen' for any Wells that will have groundwater samples submitted.
Field Name
Data Type
Key
Required
Parent
Description
sysloccode
Text (20)
PK
current/historical
Location EDD,
sysloccode
Unique Station or Well ID. Required for mapping. Must be a valid code for
the facility and must match one of the reported values in the sys loc code
field of the Location EDD file submitted in the current or previous EDD.
segmenttype
Text (20)
PK
current/historical
Type of segment described in this record. Refer to Valid Values.
materialtypecode
Text (20)
PK
current/historical
Material used for construction of this well. Refer to Valid Values.
startdepth
Numeric
PK
current/historical
Start depth of well segment (starts at zero).
depthunit
Text (15)
current/historical
Unit of measure for well segment start depth and end depth. Refer to Valid
Values.
inner diameter
Numeric
current/historical
Inner diameter of well segment.
diameter unit
Text (15)
current/historical
Unit of measure for inner and outer diameters. Refer to Valid Values.
perflength
Numeric
current/historical
Total perforated length for Screen segment types.
enddepth
Numeric
End depth of well segment.
outer diameter
Numeric
Outer diameter of well segment.
thickness
Numeric
Thickness of well segment.
thickness unit
Text (15)
Unit of measure for thickness. Refer to Valid Values.
USEPA Region 4 Format Guide
Version 1.20 December 2015
44
-------
Field Name
Data Type
Key
Required
Parent
Description
slottype
Text (20)
For screen segments, indicates type of screen slot.
slot size
Numeric
For screen segments, indicates size of screen slot.
slotsizeunit
Text (15)
Unit of measure for slot size where segment type is Screen. Refer to Valid
Values.
screentype
Text (15)
Type of screen.
materialquantity
Text (20)
Quantity of fill material where applicable (annulus or grouted annulus
segment types).
materialdensity
Text (20)
Density of fill material where applicable (annulus or grouted annulus
segment types).
remark
Text (2000)
Remarks.
3.13 Well Datum EDD File
When capturing elevation information for wells and groundwater levels, it is important to specify reference point from which water level
readings were taken, especially if these values change.
Well Datum EDD files should be named according to the following convention: EPAR4_WellDatum_vl.csv or .txt for tab-delimited
files.
Please note: The Well Datum section (EPAR4_WellDatum_vl) is required when submitting water levels for the first time as well as
any time a well is modified.
Field Name
Data Type
Key
Required
current/h istorical
Parent
Description
data_provider
Text (20)
current/historical
EDD provider. Refer to Valid Values.
task code
Text (20)
current/historical
Unique identifier for sampling event.
activitycode
Text (20)
current/historical
The program activity associated with the sampling event
(e.g. CME, SI, ODMDS, etc.). Refer to Valid Values.
USEPA Region 4 Format Guide
Version 1.20 December 2015
45
-------
sysloccode
Text (20)
PK
current/historical
Location EDD,
sysloccode
Unique Station or Well ID. Required for mapping. Must
be a valid code for the facility and must match one of the
reported values in the sys loc code field of the Location
EDD file submitted in the current or previous EDD.
start date
DateTime
PK
current/historical
Date/time started (mm/dd/yr hh:mm:ss).
steporlinear
Text (6)
current/historical
Indicates whether the change in well datum was step or
linear. Default is "step".
See Figure 3-1 below for information on how this
measurement is used during a water level elevation
calculation.
datumvalue
Numeric
current/historical
Elevation of measuring reference point from which water
level readings were taken.
datum unit
Text (15)
current/historical
Unit of measure for well datum. Refer to Valid Values.
datum desc
Text (70)
current/historical
Description of well datum.
datumcollectmethodcode
Text (20)
Code representing method used to measure well datum.
Refer to Valid Values.
USEPA Region 4 Format Guide
Version 1.20 December 2015
46
-------
Figure 3-1 Measure Datum Calculation
http://help.earthsoft.com/default.asp7W849
l Sl.l'A Region 4 Format Guide
Version 1.20 December 2015
47
-------
3.14 Water Level EDD File
The Water Level EDD file includes information on water level measurements collected at the site over the years. Depth to water, reference
elevations, and groundwater elevation information should be recorded here. Surface water level data may be recorded in this file as well. In
these cases, you should indicate in the remarks column which data represents surface water. All other required fields remain required
regardless of groundwater or surface water data. Light non-aqueous phase liquids (LNAPL) and dense non-aqueous phase liquids (DNAPL)
occurrences and their depths should be recorded here as well.
Water Level EDD files should be named according to the following convention: EPAR4_WaterLevel_vl.csv or .txt for tab-delimited
files.
Please note: The Well Datum EDD (EPAR4_WellDatum_vl) needs to be submitted with the Water Level EDD
(EP AR4W aterLevelv 1).
Field Name
Data Type
Key
Required
current/h istorical
Parent
Description
data_provider
Text (20)
current/historical
Location data provider. Refer to Valid Values.
taskcode
Text (20)
current/historical
Task code.
activitycode
Text (20)
current/historical
Activity type code. Refer to Valid Values.
sysloccode
Text (20)
PK
current/historical
Location EDD,
sysloccode
Unique Station or Well ID. Required for mapping. Must be a valid code
for the facility and must match one of the reported values in the
sys loc code field of the Location EDD file submitted in the current or
previous EDD.
measurement date
DateTime
PK
current/historical
Date/time of measurement (mm/dd/yr hh:mm:ss).
waterleveldepth
Numeric
current/historical
Depth of water level.
depthunit
Text (15)
current/historical
Unit of measure for water level depth corrected depth, and measured
depth of well. Refer to Valid Values.
water level elev
Numeric
Elevation of water level.
measureddepthofwell
Numeric
Depth of well as measured at the time of this water level measurement.
USEPA Region 4 Format Guide
Version 1.20 December 2015
48
-------
Field Name
Data Type
Key
Required
current/h istorical
Parent
Description
correcteddepth
Numeric
Depth of water level, corrected, for example, for free product.
corrected elevation
Numeric
Elevation of water level, corrected, for example, for free product.
technician
Text (50)
Name of technician taking measurements.
dry_indicator_yn
Text (1)
Is well dry? Y/N option only.
measurement method
Text (20)
Method used to make the well depth measurement.
diporelevation
Text (10)
current/historical
Enforced vocabulary (dip, elevation). Default is "dip".
batch number
Text (10)
Batch, or grouping number, for water level measurement.
remark
Text (2000)
Remarks.
lnapldepth
Numeric
LNAPL depth.
lnaplcasrn
Text (15)
LNAPL CAS number. Refer to Valid Values.
dnapldepth
Numeric
DNAPL depth.
dnaplcasrn
Text (15)
DNAPL CAS number. Refer to Valid Values.
equipmentcode
Text (60)
Equipment code.
3.15 Geolocpc Sample EDD File
The variations in geology nomenclature make the submittal of geologic data, particularly historic geologic data, a special challenge. The
Geology Sample EDD is the first of several EDDs used to capture as much geologic data in a useable form as possible. The Geologic
Sample Parameter, and Static Properties EDDs rely on information populated in the Geologic Sample EDD. Other files, related to geology
are correlated to the Geologic Sample EDD via the sys loc code for the boring. This EDD links locations with geologic samples, tasks, and
activity codes.
Geologic Sample EDD files should be named according to the following convention: EPAR4_GeologicSample_vl.csv or .txt for tab-
delimited files.
Field Name
Data
Type
Key
Required
current/h istorical
Parent
Description
data_provider
Text (20)
current/historical
Geologic Sample data provider. Refer to Valid Values.
task code
Text (20)
current/historical
Unique identifier for sampling event.
USEPA Region 4 Format Guide
Version 1.20 December 2015
-------
Field Name
Data
Type
Key
Required
current/h istorical
Parent
Description
activitycode
Text (20)
current/historical
The program activity associated with the sampling event (e.g. CME, SI,
ODMDS, etc.) Refer to Valid Values.
geosamplecode
Text (40)
PK
current/historical
Unique Sample ID.
samplename
Text (50)
Optional, non-unique Sample ID.
sysloccode
Text (20)
current/historical
Unique Station or Well ID. Required for mapping. Must be a valid code for
the facility and must match one of the reported values in the sys loc code
field of the Location EDD file submitted in the current or previous EDD.
sampletop
Numeric
Top (depth) of geologic sample.
samplebottom
Numeric
Bottom (depth) of geologic sample.
samplingdate
DateTime
Date (and time, if appropriate) of sampling.
matrix code
Text (20)
Geologic matrix or sample type (i.e. rock or soil, etc). Refer to Valid Values.
samplemethod
Text (40)
Method used for sample collection. Refer to Valid Values.
sampledesc
Text (255)
Free text description of geologic sample.
custom field 1
Text (255)
Custom field 1.
custom field 2
Text (255)
Custom field 2.
custom field 3
Text (255)
Custom field 3.
custom field 4
Text (255)
Custom field 4.
custom field 5
Text (255)
Custom field 5.
3.16 Gedogc Sample Parameter EDD File
Measurements of conductivity, moisture, pH, and specific gravity for a geologic sample should be recorded here if available. One primary
key is the geosamplecode, which relates information recorded in the Geologic Sample Parameter EDD to the Geologic Sample EDD.
Geologic Sample Parameter EDD files should be named according to the following convention: EPAR4_GeoSampleParameter_vl.csv or
.txt for tab-delimited files.
USEPA Region 4 Format Guide
Version 1.20 December 2015
50
-------
Field Name
Data Type
Key
Required
Parent
Description
geosamplecode
Text (40)
PK
current/historical
Unique Sample ID.
paramcode
Text (20)
PK
current/historical
Parameter observed during acquisition of this sample (i.e. Nl, N2, N3, N4,
PID, FID, OVM, Length Advanced, Length Recovered, Tip Stress, etc.)
paramvalue
Text (255)
PK
current/historical
Value observed or measured for this parameter.
paramunit
Text (15)
PK
current/historical
Unit of measurement for parameter value, if applicable.
measurement method
Text (20)
Method of measurement for this parameter, if applicable.
remark
Text (2000)
Remark.
3.17 Water Table EDD File
Water table depth information, as well as general or specific aquifer information can be populated in the Water Table EDD. As always, it is
important to refer to the valid values in the EDP reference tables to be certain information is accurate.
Water Table EDD files should be named according to the following convention: EPAR4_WaterTable_vl.csv or .txt for tab-delimited
files.
Field Name
Data Type
Key
Required
Parent
Description
data_provider
Text (20)
current/historical
Water Table data provider. Refer to Valid Values.
task code
Text (20)
current/historical
Unique identifier for sampling event.
activitycode
Text (20)
current/historical
The program activity associated with the sampling event (e.g. CME, SI,
ODMDS, etc.). Refer to Valid Values.
sysloccode
Text (20)
PK
current/historical
Location EDD,
sysloccode
Unique Station or Well ID. Required for mapping. Must be a valid code
for the facility and must match one of the reported values in the
sys loc code field of the Location EDD file submitted in the current or
previous EDD.
type
Text (20)
PK
current/historical
Designation of aquifer. Can be general (such as UNCONFINED or
CONFINED), or a specific aquifer name such as OGALALLA.
sequence
Text (20)
PK
current/historical
Sequence in which this measurement was taken. Intended to represent 1
of 2 mutually exclusive values, i.e. Unstabilized/Stabilized, or
Initial/Final, or 1/2, etc.
depth
Numeric
current/historical
Depth of water table (starts at zero).
USEPA Region 4 Format Guide
Version 1.20 December 2015
51
-------
Field Name
Data Type
Key
Required
Parent
Description
flowing_yn
Text (1)
Is water flowing from drill hole? Y/N option only.
measurement method
Text (50)
Method used to obtain water table measurement.
capped_pressure
Numeric
Water pressure when capped.
capped_pressure_unit
Text (15)
Unit of measure for water pressure when capped. Refer to Valid Values.
reference_point
Text (50)
Point of reference for water table measurement.
reference elevation
Numeric
Elevation of reference point for water table measurement.
temperature
Numeric
Water temperature.
temperatureunit
Text (15)
Unit of measure for water temperature. Refer to Valid Values.
3.18 Lithology EDD File
Due to the complexity of the possible lithology in any boring, the Lithology EDD is designed to allow for multiple material types (lithologic
layer types) to be assigned by depth to a single location (sys loc code) under a single activity code. An example appears below the EDD
table.
Lithology EDD files should be named according to the following convention: EPAR4_Lithology_vl.csv or .txt for tab-delimited files.
Field Name
Data Type
Key
Required
Parent
Description
data_provider
Text (20)
current/historical
Lithology data provider. Refer to Valid Values.
task code
Text (20)
current/historical
Unique identifier for sampling event.
activitycode
Text (20)
current/historical
The program activity associated with the sampling event (e.g. CME, SI,
ODMDS, etc.). Refer to Valid Values.
sysloccode
Text (20)
current/historical
Location EDD,
sysloccode
Unique Station or Well ID. Required for mapping. Must be a valid code for
the facility and must match one of the reported values in the sys loc code
field of the Location EDD file submitted in the current or previous EDD.
startdepth
Numeric
PK
current/historical
Start depth of lithology layer (starts at zero).
materialtype
Text (40)
PK
Code used to specify material type. Refer to Valid Values.
bearing
Numeric
Bearing, may be used for non-vertical boreholes.
plunge
Numeric
Plunge, may be used for non-vertical boreholes.
geounitcodel
Text (20)
Code used to specify geologic unit. Refer to Valid Values.
USEPA Region 4 Format Guide
Version 1.20 December 2015
52
-------
Field Name
Data Type
Key
Required
Parent
Description
geo_unit_code_2
Text (20)
Code used to specify geologic unit. Refer to Valid Values.
geo_unit_code_3
Text (20)
Code used to specify geologic unit. Refer to Valid Values.
geo_unit_code_4
Text (20)
Code used to specify geologic unit. Refer to Valid Values.
geo_unit_code_5
Text (20)
Code used to specify geologic unit. Refer to Valid Values.
remark 1
Text (2000)
Lithologic layer remark 1.
remark 2
Text (2000)
Lithologic layer remark 2.
moisture
Text (20)
Qualitative description of soil moisture.
permeable
Text (20)
Indicator of permeability
consolidated_yn
Text (1)
Is layer consolidated? Y/N option only.
cementation
Text (20)
Qualitative description of cementation.
color
Text (30)
Layer color.
observation
Text (255)
General layer observation.
consistency
Text (20)
Soil consistency.
sorting
Text (20)
Descriptor of soil particle size sorting.
grainsize
Text (20)
Measure of particle size.
odor
Text (20)
Soil odor.
angularity
Text (20)
Angularity of soil particles.
custom field 1
Text (255)
Custom field 1.
custom field 2
Text (255)
Custom field 2.
custom field 3
Text (255)
Custom field 3.
custom field 4
Text (255)
Custom field 4.
custom field 5
Text (255)
Custom field 5.
Example of Required Fields in Lithology EDD
data_provider
taskcode
activitycode
sysloccode
startdepth
materialtype
PL-CONTXl
15-1101
RI
B09
0
TOP SOIL
PL-CONTXl
15-1101
RI
B09
0.5
SILTY CLAY
PL-CONTXl
15-1101
RI
B09
3
CLAY
USEPA Region 4 Format Guide
Version 1.20 December 2015
53
-------
3.19 Drill Activity EDD File
Drilling activity related to any drilling event can be stored in the Drill Activity EDD and coupled with the Drill Parameter EDD to save a
solid record of the event, including rig and auger type, viscosity, and pressure as well as other information chronicling the drilling
procedure. In most cases, this EDD will be used to document the start and end depths of a drilling event.
Drill Activity EDD files should be named according to the following convention: EPAR4_DrillActivity_vl.csv or .txt for tab-delimited
files.
Field Name
Data
Type
Key
Required
current/h istorical
Parent
Description
data_provider
Text (20)
current/historical
Drill Activity data provider. Refer to Valid Values.
task code
Text (20)
current/historical
Unique identifier for sampling event.
activitycode
Text (20)
current/historical
The program activity associated with the sampling event (e.g.
CME, SI, ODMDS, etc.). Refer to Valid Values.
sysloccode
Text (20)
PK
current/historical
Location EDD,
sysloccode
Unique Station or Well ID. Required for mapping. Must be a
valid code for the facility and must match one of the reported
values in the sys loc code field of the Location EDD file
submitted in the current or previous EDD.
drill event
Text (20)
PK
current/historical
Used to uniquely identity a drilling event.
startdepth
Numeric
current/historical
Start depth of drill activity.
enddepth
Numeric
current/historical
End depth of drill activity.
start date
DateTime
Date/time drilling activity started (mm/dd/yr hh:mm:ss).
enddate
DateTime
Date/time drilling activity was completed,
(mm/dd/yr hh:mm:ss).
diameter
Text (20)
Diameter of drilled hole.
diameterunit
Text (15)
Unit of measurement for diameter of drilled hole. Refer to Valid
Values.
drill method
Text (50)
Method of drilling/advancement.
casingsize
Text (50)
Size of casing installed. Note that this is a general text field and
non-numeric entries such as "Schedule 40" are permitted.
USEPA Region 4 Format Guide
Version 1.20 December 2015
54
-------
Field Name
Data
Type
Key
Required
current/h istorical
Parent
Description
rigdesc
Text (50)
Description of drilling rig.
rig_make
Text (50)
Drilling rig make.
rigmodel
Text (50)
Drilling rig model.
rod desc
Text (50)
Description of drilling rod.
bit desc
Text (50)
Description of drilling bit.
hammer desc
Text (50)
Description of hammer.
augerdesc
Text (50)
Description of auger.
samplerdesc
Text (50)
Description of sampler.
fluid
Text (50)
Drilling fluid used.
viscosity
Text (50)
Viscosity of drilling fluid.
drilling_pressure
Text (50)
Drilling pressure.
hammer wt
Text (50)
Weight of hammer.
hammer fall
Text (50)
Fall length of hammer.
lift mechanism
Text (50)
Mechanism used to lift hammer.
new_yn
Text (1)
Is this a new borehole? Y/N option only.
repair_yn
Text (1)
Is this drilling activity to repair an existing borehole? Y/N option
only.
deepen_yn
Text (1)
Is this drilling activity to deepen an existing borehole? Y/N
option only.
abandon_yn
Text (1)
Is this drilling activity to abandon an existing borehole? Y/N
option only.
replace_yn
Text (1)
Does this drilling activity replace another borehole? Y/N option
only.
public_yn
Text (1)
Is this borehole owned or used by a public agency? Y/N option
only.
purpose
Text (70)
Purpose of drilling activity.
remark
Text (255)
Drilling activity remark.
custom field 1
Text (255)
Custom field 1.
custom field 2
Text (255)
Custom field 2.
custom field 3
Text (255)
Custom field 3.
custom field 4
Text (255)
Custom field 4.
USEPA Region 4 Format Guide
Version 1.20 December 2015
-------
Field Name
Data
Type
Key
Required
current/h istorical
Parent
Description
custom field 5
Text (255)
Custom field 5.
3.20 Drill Parameter EDD File
Drill Parameter may be used to capture specific results for a drilling event listed in the Drill Activity EDD.
Drill Parameter EDD files should be named according to the following convention: EPAR4_DrillParameter_vl.csv or .txt for tab-
delimited files.
Field Name
Data Type
Key
Required
current/h istorical
Parent
Description
sysloccode
Text (20)
PK
current/historical
Location
EDD,
sys loc code
Unique Station or Well ID. Required for mapping. Must be a valid code for the
facility and must match one of the reported values in the sys loc code field of
the Location EDD file submitted in the current or previous EDD.
drill event
Text (20)
PK
current/historical
Used to uniquely identity a drilling event.
startdepth
Numeric
PK
current/historical
Start depth of drill activity.
paramcode
Text (20)
PK
current/historical
Parameter observed or measured over this interval. Refer to Valid Values.
enddepth
Numeric
End depth of drill activity.
runlength
Text (20)
current/historical
Length of drilling run.
paramvalue
Text (255)
Value observed or measured over this interval for this parameter.
paramunit
Text (15)
Unit of measurement for parameter value. Refer to Valid Values.
remark
Text (255)
Drilling activity remark.
3.21 Downhole Point Parameter EDD File
The Downhole Point Parameter EDD should be used for recording drilling-specific results, such as tip and sleeve stress, pore pressure, and
ratio. All fields are required in this EDD and must relate back to a valid location (sys loc code).
Downhole Point Parameter EDD files should be named according to the following convention: EPAR4_DownholePoint_vl.csv or .txt for
USEPA Region 4 Format Guide
Version 1.20 December 2015
-------
tab-delimited files.
Field Name
Data
Type
Key
Required
Parent
Description
data_provider
Text (20)
current/historical
Downhole Point data provider. Refer to Valid Values.
task code
Text (20)
current/historical
Unique identifier for sampling event.
activitycode
Text (20)
current/historical
The program activity associated with the sampling event (e.g. CME, SI, ODMDS,
etc.). Refer to Valid Values.
sysloccode
Text (20)
PK
current/historical
Location EDD,
sysloccode
Unique Station or Well ID. Required for mapping. Must be a valid code for the
facility and must match one of the reported values in the sys loc code field of the
Location EDD file submitted in the current or previous EDD.
depth
Numeric
PK
current/historical
Depth at which parameter was observed or measured (starts at zero).
param
Text (20)
PK
current/historical
Parameter observed or measured at this point. Refer to Valid Values.
paramvalue
Text (255)
Value observed or measured at this point for this parameter.
paramunit
Text (15)
Unit of measurement for parameter value. Refer to Valid Values.
3.22 Static Properties EDD File
Additional static information recorded as a part of a drilling activity and related to a geologic sample such as porosity, void ratios, moisture
contents, etc., that have not been recorded elsewhere may be recorded here. This EDD also contains 5 custom fields that may be used for
recording additional information.
Static Properties EDD files should be named according to the following convention: EPAR4_StaticProps_vl.csv or .txt for tab-delimited
files.
Field Name
Data
Type
Key
Required
current/h istorical
Parent
Description
geosamplecode
Text (40)
PK
current/historical
Geologic Sample EDD,
geosamplecode
Unique Sample ID.
sampledesc
Text (255)
Sample description.
material name
Text (40)
Material name. Refer to Valid Values.
USEPA Region 4 Format Guide
Version 1.20 December 2015
57
-------
Field Name
Data
Type
Key
Required
current/h istorical
Parent
Description
geologicunitcode
Text (20)
Geologic unit code. Refer to Valid Values.
e
Numeric
Void ratio.
e max
Numeric
Maximum void ratio.
e min
Numeric
Minimum void ratio.
n
Numeric
Porosity.
specificgravity
Numeric
Specific gravity.
w
Numeric
Moisture content.
optw
Numeric
Optimum moisture content.
S
Numeric
Saturation.
K
Numeric
Hydraulic conductivity.
Kunit
Text (15)
Required if K is
populated.
Unit of measurement for hydraulic conductivity. Refer to Valid
Values. If K is populated then K unit is required.
unit wt
Numeric
Unit weight.
sat unit wt
Numeric
Saturated unit weight.
dryunitwt
Numeric
Dry unit weight.
dryunitwtmax
Numeric
Maximum dry unit weight.
dryunitwtmin
Numeric
Minimum dry unit weight.
density unit
Text (15)
Required if
satunitwt or
dry unit wt is
populated.
Unit of measure for density (unit weight). Refer to Valid Values.
If sat unit wt or dry unit wt is populated then density unit is
required.
reldensity
Numeric
Relative density.
relcompaction
Numeric
Relative compaction.
consistency
Text (20)
Consistency.
organiccarbon
Numeric
Organic carbon content.
organiccarbonunit
Text (15)
Required if
organic carbon is
populated.
Unit of measure for organic carbon. Refer to Valid Values. If
organic carbon is populated then organic carbon unit is
required.
custom field 1
Text (255)
Custom field 1.
custom field 2
Text (255)
Custom field 2.
custom field 3
Text (255)
Custom field 3.
USEPA Region 4 Format Guide
Version 1.20 December 2015
-------
Field Name
Data
Type
Key
Required
current/h istorical
Parent
Description
custom field 4
Text (255)
Custom field 4.
custom field 5
Text (255)
Custom field 5.
3.32 FilesEDD Section
The Filesvl table of the EPA Region 4 Format allows maps, figures, and published layer types of files to be loaded into the EPA Region 4
database. When completing a data submission using EDP Sign and Submit, the files entered into the Files vl table's file name field will be
packaged within the EDD.
Site base maps can be submitted as CAD files in a DXF interchange format, drawing (.dwg) file, or Shape files (.shp) generated from
ArcGIS. The maps should include all well locations, waste management units, property boundary, landfills, buildings, and roads. Do
not include any groundwater contours, contaminant contours, or other temporal type information. If the CAD file is available in real
world locational coordinates, provide the coordinates along with a brief text description of the type of projection and datum used.
(Note: Lat Long as WGS 84 in decimal degrees is required by EPA Region 4). Also include text descriptions of the units and scale of
the base map. The site base map file should be named according to the following convention:
TaskCode.SiteName.CompanyCode.SubmissionDate.FacilityCode.DXF (or dwg, or shp)
An example for the file name P2015-0001_ChemicalCompanyABC_PL-DataProvider_150108.110001224773.DXF
• Task Code is P2015-0001, (PRP use PYYYY-nnnn as provided by R4DART; Fund-lead use YY-nnnn as provided by EPA
work order)
• Site Name is Chemical Company ABC
• Company Code is PL-DataProvider
• Submission Date is January 8th, 2015 as 150108
• Facility Code is 110001224773, and the
USEPA Region 4 Format Guide
Version 1.20 December 2015
59
-------
The file's EDD file section should be named according to the following convention: EPAR4_Files_vl.csv or .txt for tab-delimited files.
Field Name
Data Type
Key
Required
current/h istorical
Parent
Description
filename
Text(255)
current/liistorical
Location EDD,
sys loc code
Name of the file.
filetype
Text(20)
current/liistorical
Type of the file. Site base maps can be submitted in CAD files
in a DXF interchange format drawing (.dwg) file, or Shape files
(.slip) generated from ArcGIS.
filedate
DateTime
Date of the file.
title
Text(255)
Title of the file.
author
Text(255)
Author of the file.
confidential_yn
Text(l)
Whether or not the file is confidential.
remark
Text(255)
Remark for the file.
placetype
Text(50)
Type of place this file is associated with.
placecode
Text(50)
Code/identifier of the place this file is associated with.
placesubcode
Text(50)
Subcode/identifier of the place this file is associated with.
content
Content of the file. Site base maps should include all well
locations, waste management units, property boundary,
landfills, buildings, and roads. Do not include any groundwater
contours, contaminant contours, or other temporal type
information. If the CAD file is available in real world locational
coordinates, provide the coordinates along with a brief text
description of the type of projection and datum used. (Note: Lat
Long as WGS 84 in decimal degrees is required by EPA Region
4). Also include text descriptions of the units and scale of the
base map.
3.24 Working with Data
It must be assumed that persons working with EDDs have a working understanding of databases and some fundamental knowledge of data
USEPA Region 4 Format Guide
Version 1.20 December 2015
60
-------
base structure. Database managers will need to work in conjunction with environmental management in order to properly process and
format this data. It is crucial to the EQuIS DART project that the data provided be of the highest standard and that every effort to properly
format the data be made prior to submittal to EPA Region 4.
USEPA Region 4 Format Guide
Version 1.20 December 2015
61
------- |