Standalone Equis Data Processor (EDP) User Guide for EPA Region 2 EDP Version 6.6 and Edd Format Version 4


/I pmi Jnitri$M»

i:mr4onrr«it*l Proepcfcon

VLI

EarthSaft

Standalone EQuIS Data Processor (EDP)

User Guide

For EPA Region 2 EDP version 6.6 and EDD format version 4

Contents

1.0 Introduction	2

2.0	Standalone EDP - Version 6.6	2

2.1	Format File - Version 4	4

2.2	Opening a Format File	5

2.3	Understanding the Format	7

2.4	Reference Value File	7

2.5	Data Files	9

2.6	Finding and Resolving Errors	12

2.7	Error Log Reports	12

2.8	Error Resolution	14

2.9	Data Errors	15

2.10	Sign and Submit	16

2.11	EDP Tools	17

2.12	Export Blank EDD Template (.xls)	19

2.13	Export EDD Description	20

2.14	Generate Format Files	20

2.15	Appearance	21

3.0 Appendix A - Standard Format Files	22

4.0 Appendix B - Additional Format Files	24

-------
EarthSoft

1.0 Introduction

The EQuIS Data Processor, or EDP, is today's answer to the many data quality issues that plague
data managers. The latest in EarthSoft' s family of data checking software, EDP sets a new standard
for simplicity in data quality management while adding a host of new features and capabilities that
allow the user the utmost in data checking flexibility.

There are two desktop modes for using the EQuIS Data Processor; Standalone EDP, Professional
EDP. Standalone EDP is used primarily by data providers such as laboratories or field staff to check
data quality prior to data submittal. Professional EDP is linked directly to the EQuIS 5 database and
can be used by EQuIS power users to check data quality and then load the data into the EQuIS
database. This manual only address the use of Standalone EDP

EDP checks all of the following data quality issues:

1. Required Fields

2. Field Lengths

3. Data Types

4. Valid Dates

5. Reference Values

6. Duplicate Rows

7. Range Checking

8. Orphan Rows

2.0 Standalone EDP - Version 6.6

The Standalone EQuIS Data Processor, or Standalone EDP, is used to check data without having
access to the EQuIS 5 database. All of the functionality found in Standalone EDP is also found in
Professional EDP. The following Standalone EDP sections will outline the features found in both
Standalone EDP and Professional EDP.

All modes of EDP support formats for many fields of environmental data observation and
acquisition. From the familiar "4-File" analytical laboratory format to field activities such as
drilling and sampling to automated surface water sampling, EDP can check data files quickly and
easily.

You need to know the EQuIS format or EDD format that your data resides in, what reference values
you will use to check the data, and if there is an enumeration file associated with your format file.

There are several EQuIS formats included with the EQuIS Data Processor software. The
appropriate format you will need to use is determined by the type of data to be imported. After
determining the EDD format, you are ready to begin. For this exercise, you will be using the EPA
Region 2 EDD format version 4.

Start Standalone EDP by selecting Programs > EarthSoft > EQuIS Data Processor from the Start
menu. If you are running the EQuIS Data Processor in evaluation mode, click the OK button on the
EQuIS Data Processor Evaluation screen. Otherwise, click on the link that is on the "Software
Registration" screen as below:

-------
EarthS&ft

Software Registration	x

Review registration status of products currently installed on this computer.

C: V'rogramData ^arthSoft^QuIS. exe. config

Activation	Network Licenses | SPLA |

J<

Maintenance key not found

Computer ID: " .r"V3T

You can click one of the links that are on the "Software Registration" screen to request registration
key or maintenance extension if you already had the registration keys. You will need to fill out an
electric form and submit it to EarthSoft, and then you will receive emails to confirm your
registration and your registration keys.

A rn« hmSwm

p-MU !:w*onrr*nt»l fVo««t«on

WLI Haopky

After you've done the registration process, the EQuIS Data Processor will open to the main
window, (see image below).

-------
v>EPA

UnMSwn
f rnf*oorr*ntjl PnttKtton
AmncY

EarthS^ft

EQulS Data Processor

m ft a

Format EDD DB Error Summary
Format Log
Open Error Log

J _ Comment Rows Filter Column(s) 3"" Add New Row

Q) Errors Only sf1 Pin Column(s) 3;:i Copy Row(s)

Column Chooser - ~ Set as Comment Row

10 i® ^ iqi 1 u Bi-kEDD

y EDD Descriptor

Hi Generate Forms

Please Open a Format File (*Jtsd, *.xse)

Figure 1: EDP

2.1 Format File - Version 4

The Format File is the essence of data checking in EDP. The Format File contains the definitions
for each individual section belonging to the EDD format. For example, the Chemistry section
contains the laboratory "4-File" sections: Sample_v4, SampleParameter_v4, TestResultQC_v4,
Batch_v4.

There are three or four files that make a format file; the format definition file (xsd) file, custom
handler (vb) file, the enumeration (enum) file and the reference values (rvf) file. Together these
files determine how your data will be checked.

The XSD file contains the mapping and definition for the sections of the format file, such as; the
mapping of the fields in the format to the data table and fields in the EQulS database, required
fields, primary keys, field length, field description and field type (numeric, text, etc.)

The vb file contains the custom handling and business rules that apply to the format such as the
analysis date cannot proceed sample date or reportable_result cannot be 'Yes" where the
lab qualifier is E, G, P, or R. Another example of a business rule is if a certain sample type is
encountered then another field must be populated. For example, if you have a sample type of FD'.
short for field duplicate, then the parent sample field must be populated to avoid an orphan quality
assurance/quality control sample.

The enum file is an optional file that allows EDP to enforce a set of lookup values similar to a
reference table (rt.) lookup. This allows EDP to control the values for specific fields that are not
linked to a reference table in the EQulS database. The rvf is created as an export from EQulS
Professional, while the enumeration file is created and maintained manually using any text editor.
Unlike the. rvf the enumeration file is used in EQulS Professional EDD as well as standalone EDP.

-------
a rm

wEF¥\

UnMSwn

f rnf4o«rr*ntjl Pro*®ct»on
iAihucy

EarthSaft

It should also be noted that the enum file may also be used to narrow a list of reference values to a
smaller list. For example, typically a unit field such as elevation units would be linked to the rt unit
table which may contain 100 or more units. However, it may make sense to limit the elevation units
to "meters" and "feet" since those are the only valid units for this type of data.

The rvf is created as an export from EQuIS Professional reference tables. This file is only needed
when running standalone EDP and it allows EDP to check reference values remotely against values
established in EQuIS Professional.

Format Files may be encrypted or unencrypted, and typically include both the format definition file
(.xsd or .xse) and a custom handler file (.vb or .vbe). The unencrypted format files may be modified
to meet clients specific needs, however these modifications are not supported under the EarthSoft
Maintenance agreement. If modifications are made to these formats, it is highly recommended that
clients rename the formats to include their company name. The formats are written in XML and
may be viewed using a text editor. Many free software applications are available online for viewing
and editing XML files. Formats that are unencrypted do not require a license key.

A list of the Standard Format Files that are included with all installations of EDP can be found in
Appendix A. A list of the Alternate Format Files that may downloaded with any installation of EDP
can be found in Appendix B.

The first time EDP is launched, a format file must be selected before a data file can be loaded.

To open a Format File:

1. Click on the Format button (located in the Open group on the Home tab). NOTE: The
default location for system formats is -YProgram Files \EarthSoft\EQuIS\System. The

default location for custom formats is -YProgram Files \EarthSoft\EQuIS\Formats.
2. Select the EPAR2.xse format file and click "Open".

Once the format file has been selected and successfully opened, the format's sections will be listed
in the left hand portion of the EDP window. If prompted to open the reference file, select
"EPAR2.rvf".

2.2 Opening a Format File

The image below is the EQuIS format version 4, and the EDD format legend in 1-4 points:

-------
vvERA

Unrlfld

i iw.fonrrwt*! PnH«t«on
AflincV

d -7 *

EQulS Data Processor

EarthSaft

11 e

Format EDD

pa sjj/i
1'—ol [EO|

Error Summary
Log

V = Comment Rows
Errors Only

— Filter Column(s)
^ Pin Co)umn(s)
Column Chooser -

a*3 Add New Row

lb CopyRow(s)

Set as Comment Row

IS li®

Clear Refresh

?\ 13

Find Sort

(jjj Blank EDD ~ Ijf^J Design
M EDD Description
^f1: | Generate Format File

Open

Error Log

View

Data

Tools

B -Q Initial EDD

Q DataProvider_v4

~	HBI

: ~ Location_v4
Q Files_v4
@ (i^l Field

: Q DrillActivity_v4

Lithology_v4
: Q Well_v4
r] WellConstruction_W
|_j GeologySamples_v4
Q WaterTable_y4
rl DownholePoint_v4
£] SoilGas_v4
Q WaterLevel_v4
f- Q BctractionInjectionWells_v4

~	Field Res ults_>4
El £|| Vapor Intrusion

I"] VI_Bldg_Address_v4
f Q VI_Building_Inspection_v4
Q VI_Building_Parameters_v4
Q VI_Locations_v4
[j VI_0utdoor_Locations_v4
; 2 VI_Samples_v4
| ~ VI_TestResultsQC_v4
Q VI_Batches_v4
~ Chemistry

PI Sample_v4
Q] SampleParameter_v4
h-'Q TestResultQC_v4
Q Batch_v4
B Basic

[]] BasicLocations_v4

) BasicWater_Level_v4
l] BasicChemistry_v4
P] BasicGeology_v4

\

program_code

site_task_code

contact_name

ill

~ Reference Values Q EPA Region 2 ~ EQuIS\LIPARI LANDFILL
A Status Window

I

I

Format: C:\Users \nlai\Desktop\format_EPAR2_master\EPAR2.xse

Figure 2: EDP Standalone Legend

(1)	-The sections of the format

(2)-	The Format Tab

(3)	-The directory path to the current format

(4)-	The version of the format

Users can identify what format file and the version of the format file has been loaded by selecting
the Format Tab. The path to the format file and the version are displayed in the lower left comer. It
is recommended that all EDP users check this path and version prior to each use of EDP.

Now that a Reference Value File and Format File have been successfully opened, EDP is ready to
load and check a data file.

-------
EarthSrft

2.3 Understanding the Format

Standard Earth Soft formats were designed with tools that are available to assist users in
understanding the format's requirements. These tools include; tool tips, color coded column
headers, and drop down boxes that enforce reference values.

In each format file provided by Earth So ft. there are tool tips in the column headers. To access these
tool tips, simply hold your mouse over the column headers name. These tool tips may be used to
assist the user in determining what values should be used to populate each field as well as
determining which reference value table is used to populate the field.

Irrors Only 7- Pin Column(s)
ilH Column Chooser

View

e-"— y

Clear Refresh

Data

Find Sort

|^| Lliai IN LUL>

jsc) EDD Description
rjvj Generate Format File
Tools

Rows: 1740 of 1740 [Comment Rows]

svs sample code sample name

sample_matrix_code

sample_type_code

sample_

source

parent_sam|:"

-------
A rryi jam tain

r"H]Q tnvionmiBW ft-otfcton

>fLI OAgPity

Reference Values Tab

EarthSaft

In order to assist clients in loading the appropriate set of reference values for each format, users do
not have to manually open the reference value file. Rather, each time a user opens a format file
EDP will look in the same folder as the format file for a file with the extension- RVF (the reference
value file). If a file is present, it will be opened and used. If an *.RVF file is not located or if there
are more than one *.rvf in the same directory as the Format File, then the user will be prompted to
browse to the appropriate *.RVF file.

For example, if a user loads the 4-file format (C:\Program
Files\EarthSoft\EQuIS\System\EFWEDD.XSD), EDP will check the C:\Program
Files\EarthSoft\EQuIS\System\ folder for an *.RVF file. If the file is found, it will be loaded. If not,
then the user will be prompted to open another *.RVF file of their choice.

If multiple *.RVF files exist in the \\EarthSoft\EQuIS\System\ folder, the user will be prompted to
select which *.RVF file they would like to use. This is useful when laboratories and other data
providers are supporting multiple clients with different reference values files.

The image below is the EPAR2.rvf is opened in the EDP.

•/, _ •/ 7 __ Commen

0 Errors On
fiat EDD Error Summary
Log

Open Error Log

rt_analyte
rt_analyte_detail

rt_basin
rt_building_type
rt_b u i I d i n g_us e_type
rt_company
rt_company_type
rt_co o rd_el ev_d at um
rt_co o rd_e! ev_m eth od
rt_co o rd_g eo m etric_type
rt_co o rd_h o rz_d at u m
rt_co o rd_h o rz_m eth o d
rt_county
rt_d o wn h o I e_p o i nt_p a ra m_type
rt_four>dation_type
rt_fraction
rt_location_type
rt_material Jk

rt_matrix ^

rt_matrix_dass
rt_prep_method
rt_preservative
rt_qua!ifier
rt_result_type
rt_s a m p I e_m eth o d
rt_sample_param_tvpe
rt_saraple_type
rt_s o i l_g as_p a ra m_type
rt_state

rt_s ubf aci I ity_p a ramjype
rt_subfacility_type
rt_tes t_b at ch_ty pe
rt_test_type
rt_unit

rt_wel l_s eg ment_ty pe
st_config a

analytic method

1010MOD

1311/6010B/7470A

1311/8081A

1311/8151A

1311/826 OB

1311/8270C

1311/8321

1312/6010B/7470A

1312/80 81A

1312/8151A

1312/8260B

1312/8270C

13C/12C IN ORG

1664

1697M

180/160 RATIO

2216

2540B

3550C

3620C

4500 CLB

4500-CLE

4500-SF

5035A-1

5035A-2

5035^

5220 D

lu-

6010B

^ Reference Values [_] EPA Region 2 EQuISV T\l : Vf>
ft Status Window

Figure 4: Reference Values window showing a list of reference values tables.

(1) -Reference Tables

(2) -Reference Values Tab

(3)- Current format

-------
/I pmi Jnitri$M»

i:mr4onrr«it*l Proepcfcon

VLI

EarthSaft

Therefore, when using EDP, it is important to verify that the correct Reference Value File is being
used.

2.5 Data Files

The data files, often referred to as Electronic Data Deliverables (EDDs), are the files that contain
the data to be checked. The EDDs must be in an electronic, tabular format that adheres to the layout
and rules specified by the Format File.

The data file may be a tab-delimited (.txt) or comma -separated (.csv) file, Excel files (.xls), as well
as zipped EDD files (.zip) may also be loaded. Some formats maybe set up to accept an XML
formatted dataset.

2.5.1 Loading Individual Data Files

Individual Data files can be loaded into the EQuIS Data Processor by highlighting the section to load
the data file into and then using one of the following methods:

•Click Open > Data File from the Application menu (the button)

•Right-click a section and select "Load Data File".

When multiple sections exist within a given Format File, the data file selected is loaded into the
currently selected section. By default, when EDP is opened, the first section of the format is
highlighted. If the users select File > Open immediately after opening a format file, the data will be
loaded into that section of the format regardless of the file's content. For example, in the EDD Format
File, the section Initial EDD is the first section listed. A data file opened through the File menu will
be loaded into this section regardless of whether it is appropriate for this format or not if another
section was not selected.

To open a data file in txt format:

1. In the Initial EDD section, select "DataProvider_v4"

2. Select the Application menu > Open > EDD Data File. An alternate method of opening
a data file is to right-click on the desired section and select "Load Data File" from the popup menu.

3. Browse to the data file that you saved, for example "DataProvider_v4.txt"

4. Click "OK".

5. Load the remaining data files in the folder to the appropriate section using the previous
instructions.

The data check occurs in the background while the data file is being loaded. After all of the data
files have been loaded, you will review any errors that EDP has detected.

To ensure that all of the business rules are properly processed by EDP it is recommended that the
Data Files are loaded in the order that the sections appear in the format. This order represents the
Parent/Child Relationship between the data in the data files. For example, Sample_v4 should be
loaded before TestResultQC_v4 and Batch_v4. If the files are not opened in order, it is

-------
/I pmi Jnitri$M»

i:mr4onrr«it*l Proepcfcon

VLI

recommended that the user select the refresh button

EarthS Clear EDD from the data group.

2.5.4 Loading Data Files in different file extension
A. Excel file

When using Excel files with EDP, the workbook tabs must be named with the EDD section's name.
For example, the workbook tab that contains Sampling information must be named "Sample_v4",
for Test Results QC, the workbook tab must be named "TestResultQC_v4" ... etc.

See image below shows the tabs of the data workbook:

-------
vvERA

Unrlfld

i iw.fonrrwt*! PnH«t«on
AflincV

Home

Page Layout Formulas

TS ^

~ ii, -
Paste

Clipboard Q

EarthSaft

Tahoma '10

= = = w

General

B J U » K A

=: =: := m T

$ - % *

H4 w <5* - A '

«-= ¦*=' -

<-.0 .00
.00 -».o

Font m

Alignment Q

Number Q

5[§] Conditional Formatting
Format as Table T
Cell Styles *

Styles

H36

A B C D E F , G H I

#svs sanlab aril ri anatvsis total or column n test tvDt test bah test batch id f:

FDGW-030 SW6010

2016/03/0: T NA

INITIAL

ANALYSIS

FDGW-030316;03,

PZ-12-26BI SW6010

2016/0 3/0 !T NA

INITIAL

ANALYSIS

PZ-12-26BD;03/02

PZ-12-26BI SW6010

2016/03/01T NA

DILUTIONl ANALYSIS

PZ-12-26BD;03/02

PZ-12-26B: SW6010

2016/03/0: T NA

INITIAL

ANALYSIS

PZ-12-26BS;03/02

RBGW 03CSW6010

2016/03/0: T NA

INITIAL

ANALYSIS

RBGW_030316;03

RBGW 03CSW7470

2016/03/0!T NA

INITIAL

ANALYSIS

RBGW_030316;03

RBGW 03

SW8260

2016/03/0! N NA

INITIAL

ANALYSIS

RBGW_030316;03

Sample_v4 TestResultQC_v4 Batch_v4

: hi

Ready

h m

Figure 5: Excel Workbook with Sections named properly for opening all sections directly into EDP.

The Excel file (.xls) may use any nomenclature. EarthSoflt recommends including the format name
at the end of the file name. However, to successfully load all of the sections of the EDD at once the
worksheets must be named according to the EDD section name.

B, Loading Zipped Data Files

Zipped data files can also be used with EDP. Any of the standard types of files (e.g., .txt, .csv, .xls)
can be zipped and opened at the same time with EDP. When using Zipped data files with EDP, the
data files (in the *.zip file) must be named with the EDD format's section name. For example, the
zipped file shown below contains data files which use the EDD format name as file name.

Additional information may be included in the data file's name, such as the sample delivery group
number, as long as that information is separated from the section name by a period (.). For example,
Sample_v4.txt' is an acceptable name for loading data.

-------
a rm

wEF¥\

UnMSwn

f rnf4o«rr*ntjl Pro*®ct»on
iAihucy

EarthSaft

20170208.NYD0000001.EPAR2.ZIP

Batch_v4.txt

Type: Text Document

Date mc
Size: 13.

FieldResults_v4.txt
Type: Text Document

Date mc
Size: 49.

Sample_v4.txt

Type: Text Document

Date m<
Size: 12.

Te stRe su ltQC_v4.txt

Type: Text Document

Date mt
Size: 82<

WaterLevel_v4,1xt

Type: Text Document

Date mc
Size: 4.5

Figure 6: Zipped file's with the EPA Region 2 EDD format's section names as individual flat file names.

Regardless of whether the user is opening Excel, or Zipped files, notice that the file name (*.xls,
*.zip) may use any nomenclature, but the sheet name (for Excel), and the individual file name (for
zipped files) must use the EDD format's section names.

2.6 Finding and Resolving Errors

Once a data file has been loaded into EDP, any cell in the data grid that does not satisfy the
checking requirements identified in the Format File are highlighted in colors that indicate the type
of error. Users may obtain additional details about what each error is by holding their curser over
the highlighted cell in the data grid. For additional information on changing these color codes see
the Options section of this manual.

In addition to denoting the cell and/or row in which the error(s) occur in the grid, the line number
along the left side of the data grid may also signify any line in which an error has occurred by
appearing in bold red text.

Also, you can identify which sections of the formats contain at least one error because they will be
highlighted in red. Notice that all data files that were checked contain errors as the format names
are highlighted in red.

You can view a summary report of all of the errors in the data files by reviewing the Error Log or
Error Summary reports.

The Error Log Report is a report that can be created to show what line in each section of the format
contains exactly what error. The Error Log Report maintains EDP's error color coding to make
error resolution easier.

2.7 Error Log Reports

The Error Summary Report may also be created to show a summary of all of the errors in the data
files. Each row of the Error Summary Report contains the number of rows where the specified error

-------
EarthSgrft

occurred in each section of the format. The Summary Report does not maintain the color coding.

The Error Log and Error Summary Reports are saved as an HTML files, and open automatically in
your default browser. Note also that the EDD Format File name and Version information are also
reported in both Error Logs. In addition, information related to the EDD Data Files, User Name,
and Reference Values File that were used during the error checking process are also listed in both
of these logs to facilitate error resolution.

Users may find it useful that Error Logs and Error Summary Reports may also be opened in
Microsoft Excel to allow for additional sorting. To do this, browse to the saved Error log in
Windows Explorer, right click on the file name and select 'Open with' then select Microsoft Excel.

To view the Error Log, follow these steps:

1. Select the Error Log option from the Error Log group (see image below)

Hy ° ;

Home Professional

fisH fp.

¦ ¦«/

UH Wj

1 0

Format EDD

Error

Log

Summary
Tr?>r Log

'¦/ = Comment Rows - Fil
10 Errors Only w Pir

Colu IT

View

Q O Initial EDD

: Q DataProvider_v4

Location_v4

Line

site code

2. Enter a file name (or use the default file name) and click Save.

EDP Error Log

User Name:nlai

Format Name:DataProvider_v4, Site_v4
Format Version:4.00.3
EDD Filefs):

Reference Values File: Database
Run Date: 2/8/2018 1:04:47 PM

18 total errors:

Table

Line

Column

Value

Message

Type

DataProvider_

_v4

data_provider_code

[NULL]

Missing required field

ERROR

DataProvider.

_v4

data_provider

[NULL]

Missing required field

ERROR

DataProvider,

_v4

data_contact_name

[NULL]

Missing required field

ERROR

DataProvider_

_v4

da ta_co n ta ct_a d d ress 1

[NULL]

Missing required field

ERROR

....

Figure 7: EDP Error Log Report

-------
A PHIl JnrHDSWm

l ¦ -1- ' r l.ii I r"i -I :n

A rriA

To view the Error Summary, follow these steps:

EarthS^ft

1. Select Summary from the Error Log group

d "J

Home

Professional

Format EDD Error
Log

Open Error Log

Gra-Sl^

Summary

7 _ Comment Rows
E0 Errors Only

lEHcH Initial EDD

DataProvider_v4

\-nwm

Location_v4
1 Files v4

Line

2. Enter a file name (or use the default file name) and click Save.

2.8 Error Resolution

As you now know, errors that are found in EDP are highlighted with a specified color to signify
what the error is. These data errors can be corrected within EDP if the Auditing feature was enabled
during installation of EDP. These modifications may be made to any cell within the data file after it
is loaded by simply highlighting the cell and typing the modification.

Before beginning it is important to understand that edits made to data files within EDP are not
automatically saved. If users are modifying data files using the edit feature available in EDP then
users should save their data files frequently during their EDP Session. Modifications made to data
in EDP may be saved by selecting Save -> Data file from the Application menu.

2.8.1 Column Headers Errors

You may have noticed that many of the are attributed to the two header rows that are contained in
each of the data files. These two rows contain column header information that is useful to ensure
that the correct data populates each column and to check that the appropriate data file was loaded
into the correct section of the format.

You can instruct EDP to ignore any header rows by selecting the header rows in the data grid, right-
click while the rows are highlighted and select "Set as Comment Row". You will need to complete
this step for each section of the format. To do this:

1. Select the header row error in the section. Such as Sample_v4

2. Highlight the header rows, right-click and select "Set as Comment Row".

-------
EarthSrft

Set as Comment Row k

Copy Row(s)

a*™

Add New Row

Column Chooser

Figure 8: Set as Comment Row

The text in the first two rows becomes italicized and includes a pound sign (#) directly before the
first letter of the column header. The colors of the cells in these rows also change indicating that
they are no longer problematic. Repeat this step for the remaining loaded files.

The step of setting a row as a column header may be skipped if the data file's comment rows
include this pound sign (#) prior to being loaded into EDP.

Line

svs sample code

sample_name

sample_matrix_co

sample_type

#sys_ sampb_code

sampfe_name

sampb_mathx_code

sampte_type_.

#Text[40]

Text[30]

Text[10]

Text[20]

Figure 9: Example of Pound Sign (#) Comment Indicator

After you identify "Comment Rows" in the remainder of the data files, you can choose to not view
the Comment Rows. On the View group, there is an option for Comment Rows. Note the check
next to the word "Comment Rows". If you click on the "Comment Row" option, the check is
removed and the Comment Rows will not be displayed. You can always choose to view the
Comment Rows by making the same selection again.

After identifying the "Comment Rows", all errors should be removed from the sections.

2.9 Data Errors

Next, we need to check the data errors. The EDD format will highlighted cells to show errors, you
can also click "Errors Only" to see only the error rows. In this document, we can look some of the
error messages, such as the items below:

1. "Orphan Row" error in the parent sample code of the Sample_v4 section indicates that the
parent_sample_code has not been defined. This error indicates that the parent_sample_code is not
related to a svs sample code of an ®N* or Normal Environmental Sample from the
sys_sample_code field. By showing all records in the EDD (Select Errors Only from the view
group). To check this error, you can check the entries in the sys_sample_code field comparing
with the Parent_sample_code field, maybe the spelling for one of these fields are not matched.

2. "Valid Value Not Found". We will see this error when the values in the specific field are not in
the reference values tables. You may need to check the reference values. TIP: If you do not know
which reference value table to use, hold your mouse over the column header in the format and the
tool tip will tell you which reference values table to refer to for additional troubleshooting.

To check your reference values, click on the Reference Values tab. In this document, we have
problem with the chemical name of the Cas number 7440-32-5 for Sodium. Click the rt analyte

-------
EarthSjrft

table and sort on the chemical_name and scroll down to locate the entry for Sodium. Note that the
CAS number for Sodium = 7440-23-5.

3. To correct the error, click back to the EPA Region2 EDD format tab and select the section.
Locate the problems with the CAS numbers and replace the incorrect values with the correct values
from the preceding step. Use the Refresh Table option from the Data group to ensure that the
changes are accepted. If the values are not found in the reference table, email to the data
administrator to add the new value.

NOTE: If all errors have been corrected and the 'Errors Only" feature has been selected, no data

will appear. You can review all the data by unselecting the "Errors Only" feature.

Save changes to the original data file using the Save -> EDD option on the Application menu.

2.10 Sign and Submit

After using the tools outlined above to resolve all of the issues in a set of Data Files, the data is
ready to be submitted for loading into the EQuIS 6 database. The Sign and Submit tool was
designed to facilitate submittal of data to EQuIS Enterprise EDP. Sign and Submit option packages
the data files with the correct naming convention which allows easy submittal of data packages.
Use of the Sign and Submit feature requires a user name and password that can be created by the
data providers.

To use the Sign and Submit feature, after data files have been loaded and all of the errors have been
resolved,

1. Select Sign and Submit from the Application Menu. This will open the Sign and Submit window.

Sign and Submit
User Name:
Password:
EPA ID:

Registry ID:

Save Password

Save

Figure 10: Sign and Submit Window

2. Enter your User Name and Password as well as the EPAID that applies to the data package being
submitted. The Registry ID will be automatically populated based on the selected EPA ID.

NOTE: The EPA ID and Registry ID are extremely important for Enterprise Data Submittals;
however, the data entered into these sections of the Sign and Submit screen may be modified by the
user as needed.

-------
/* FR* JniMStiKi
fcWj HHXl l:mr
-------
EarthSrft

V Filter Column(s) q*3 Add New Row (ff^i

' Pin Colurnn(s) ^
jH Column Chooser ~

Clear

}

Data

Rows: 1740 of 1740 [Comment Rows

code V

sample_name V

sample_matrix_code V

(AH)

(Custom)

/EARTHS

D="EFWE

(NonBlanks)

//TEMPU

Figure 12: Filtering columns.

For example, if you are interested in reviewing the results for sample B-31_20000325, click on the
Filter icon and using the pull-down menu enabled for the sys_sample_code field, select the value B-
31_20000325. Now, you can review all of the results gathered for the sample in question.

2.11.3 Pin

Use the Pin button to assist with viewing large data sets. By clicking on the Pin icon, a "pin" is
enabled adjacent to each field. The Pin icon allows certain fields to remain stationary while viewing
the associated related fields for each record (this is analogous to freeze panes in Excel). For
example, if you want to look at the test data for the matrix spike duplicate sample (B-
45_20000325_MSD), click on the Pin icon. You will notice that a small "pin" appears adjacent to
each field name. To more easily review the test data for B-45_20000325_MSD, click on the pin in
the sys_sample_code field. As you scroll to the right, the sys sample code field remains stationary
so you can review the data associated with each sample.

irnent Rows
•rs Only

V Filter Column(s)

PinColumn(s)
11 Column Chooser

a*™ Add New Row
Copy Row(s)

Set as Comment Row

Bf ii

Clear Refresh

Find

View

i Dite

1 1

r fJws: 1740 of 1740 [Comment A^s] f

svs sample code -0

sample_name -ta sample_matriK_code sample_type_code

?XML VERSION

J="1.0"

. m wmmmw

:!--$HEADER: /EARTHS

•VC.CY-UCMA Tr

\_"ccu>c

Figure 13: Pinning Columns.

2.11.4 Find and Replace

Use the Find button to find or replace multiple fields with new data. To use this, select any field
within the column you would like to search. Then select the Find button, and the Find and Replace
window opens. This will allow you to enter the text to be found (and the text to replace the original
text with if desired).

-------
/I pmi Jnitri$M»

i:mr4onrr«it*l Proepcfcon

VLI

¦¦ Find and Replace

~ X

1 Find/Replace: Remap

Find what:

v|| Fmd All 1

Find

Replace with:

v | Replace All j |

Replace

| | Match entire cell contents
| | Show Matches

EarthSaft

Search By: Column

Figure 14: Find and Replace Window

TIP: Recent selections are available for reuse under the drop-down arrow(s).

The Find and Replace tool can be focused on a selection of the available rows in a displayed data
file by selecting these rows prior to the opening the Find and Replace option and choosing
"Selection" from the drop-down menu in the "Within:" option. Additional choices are "Table" for
the entire data file screen displayed, and "All" for all EDD tables open.

• Search by: provides options to search by rows or columns.

Other options include Match Case and Match entire cell contents, which should be used when an
exact match is desired. These options also eliminate the inclusion of partial matches, which could
create new errors. For example, when changing a Method from E300.0 to E310.1, selecting Match
entire cell contents will prevent the modification of a Batch ID or Lab QC Sample ID that may
also include the text "E300.1". With this setting, only the method cells containing E300.1 as the
sole content will be selected.

• Show matches will open a detail box that will list the matches found

• Replace will replace the items one at a time.

• Replace All will replace all items found.

2.12 Export Blank EDD Template (.xls)

Many users have their data providers create EDDs using Excel Workbook templates. Built into
EDP is an easy tool for creating these templates. To do this:

1. From the Tools group select Blank EDD

2. Excel will open and an EDD template will be created. The template includes the column header
color coding and tool tips described in a previous section.

Users who create their own custom formats find this tool extremely useful. However, it should be
noted that EarthSoft does not support custom formats, therefore custom formats will not be covered
in this document.

-------
EarthSaft

2.13 Export EDD Description

The EDD Description option allows users to export detailed description of a format. This detail
includes datatypes, key fields, required fields, field description and the database mapping.

To review the descriptions in Excel, select EDD Description from the Tools group. Excel will open
and the descriptions will be displayed.

3 *5 * c* - -

EPA Region 2.xlsx - Excel

Lai, Ngateng B

- ~ X

lie ^^9 Insert

Page Layout Formulas

Data Review View Q Tell me what you want to do

a y

P^i Tahoma
¦LJ (Tg , 1

-|™ - A' A" ~ = =

p Wrap Text General * |p|

, h—ri| Insert • X •
£* Delete - IT] -

zT P

Paste ^ B / U - 1 E

- O-A- ^13

~III S Merge & Center • $ • % » *b§ -f!8 Conditional Format

as Cell

Sort & Find &

Formatting - Table ¦

• Styles- ® format • •

Filter- Select-

Clipboard ft 1 Font r, 1

Alignment ft I Number ft I Styles

Cells

Editing a

ai - h

f* | Field Name

B | C i D

I E I F I G | H

—

Field Name

Comment

data provider code

Text(20) PK Y

rt_company.company_code

Data Provider company code

data_provider

Text(70) Y

rt_company.company_name

Data Provider company name

data_contact_name

Text(50) Y

rt_company.contact_name

Data Provider contact name

data_contact_addressl

Text(40) Y

rt_company.address_l

Data Provider address 1

data_contact_address2

T ext(40)

rt_company.address_2

Data Provider address 2

data_contact_city

Text(30) Y

rt_company.city

Data Provider city

data_contact_state

Text(2) Y

rt_state.state_code rt_com pa ny. state

Data Provider state

data_contact_zipcode

Text(30) Y

rt_company.postal_code

Data Provider zipcode

data_contact_country
data_contact_phone

Text(50)

Text(20) Y

rt_company.country
rt_company.phone_number

Data Provider zipcode
Data Provider phone

datacontactemail

Text(100) Y

rt_company.email_address

Data Provider email address

Method Mappings

CompanyType_SUBCONTRACTOR rt_company.company_type

~ Format Information Custom Checks

DataProvider_v4 Site_v4 | Locations ... (+) ! |< | |

I Ready B 01 ED 1 + 100% |

Figure 15: EDD Description in Excel.

2.14 Generate Format Files

The Generate Format Files option allows user to select an existing Access database that has been
set up as a format file. This option will generate an *.xsd. As described previously in this document
the *.xsd contains the field specifications and definitions. The *.xsd will also use the primary key
fields defined in the Access database to create the primary key fields for the format. It does not
contain the database mapping. This option will not create the reference values file (*.rvf) and
enumeration file (*.xsd) or the conditional checks file (*.vb). This option can be used to create a
skeleton of a format. This is a good starting point for generating user specific formats.

To use this option a user would create an Access database with at least one table which contains the
fields, field lengths, types, descriptions as well as a primary key. If more than one table is included
in the database this option will create multiple sections for the format. Once the database has been
created the user would open EDP and select Generate Format File from the Tools group. First the
user will be prompted to Select and Access Database, once selected click Open. The next screen
will prompted the user for a new format file name. Once the new format file name has been entered
the user selects the Save button and the format will be created. To view the newly created format,
select the Format option from the Open group.

Please note that the format will not import data into the EQuIS database until the database mapping
has been added to the newly created format.

-------
EarthSgrft

2.15 Appearance

The default appearance of Column Headers. Errors, and Informational Messages may be modified
by users to enhance the user experience. This functionality is available through the Application
menu -> Options button. To modify any of these items" appearance:

1. Select the Application menu then the Options button

2. Expand the node (+) to the left of Appearances

3. Select an attribute you would like to modify. Such as the 'Reference Value Cell Errors" under
Errors-> Cell Errors in the Appearance tree located in the left of the Options screen.

4. Click in the cell that appears in the right hand portion of the Options window next to
'BackColor'.

5. Click on the Down Arrow to display the colors selection options for cell background colors.

6. Select a color

7. Select OK

S €3 Appearances
Q Column Headers
Key Column
Required Column
Lookup Column
Q Errors

B Row Errors

Duplicate Row
Orphan Row'
Line Number
Table Name
E) Cell Errors

Required Field
Data Type
Field Length

Reference Value

Out of Range

Pattern

Enumeration

Invalid
(±1 Custom Errors
Information

Table Name

!~ 128, 255,128

AlphaLevel

BackColoi2

BackColorAlpha
BackColorDisabled
BackColorDisabled2
BackGradientAlignment
BackGradientStyle
BackHatchStyle
BorderAlpha
BorderColor
BorderColor3DBase
I Cursor
0 FontData
ForeColor
ForeColorDisabied
BackColor

The primary color used to fill the background.

Custom | web | System |

rmrrrrmm
rmurmmmm

Figure 16: Example of Modifying Appearance in the Options Window.

This process may be repeated for any Appearance setting that are applied to cells in EDP.

-------
EarthSaft

3.0 Appendix A - Standard Format Files

There are several formats that are installed by default with the any type of EDP. By default, these
standard formats are located in the /Program Files/EarthSoft/EQuIS/System directory and are free
of charge.

Format Name

Format Files

Section Names

Action Levels

ActionLevels.xsd

ActionLevel

ActionLevelParameter

EPAR2

EPAR2.xse

Initial EDD

DataProvider_v4

Site_v4

Location_v4

File v4

Field

DrillActitvity v4

Lithology v4

Well_v4

W ellconstruction_v4

GeologySamples_v4

WaterTable_v4

DownholePoint v4

SoilGas_v4

WaterLevel_v4

Extractionlnj ectionW ells_v4

FieldResults_v4

Vapor Intrusion

VI_bldg_Address_v4

VI_Building_Inspection_v4

VI_Building_Parameters-v4

VI_Locations_v4

VI_Outdoor_Loations_v4

VI_Samples_v4

VITestRe sultsQ C_v4

VI_Batches_v4

Chemistry

Sample_v4

SampleParameter_v4

TestResultQC_v4

Batch_v4

Basic

BasicLocations_v4

BasicWater_Level_v4

BasicChemistry_v4

BasicGeology_v4

-------
EarthSaft

Format Name

Format Files

Section Names

EQEDD

EQEDD.xsd

Subfacilityvl

EQEDD vb

Taskvl

Locationvl

LocationParameterv 1

DrillActivity vl

Lithology vl

Well_vl"

WellConstruction_vl

Geotechnical Samplev 1

WaterLevel_vl

WaterTable_vl

DownholePoint vl

Extrationlnj ectionWellsv 1

Purgevl

BasicResultsvl

FieldSamplevl

SampleParameterv 1

LabSamplevl

TestResultsvl

TestResultsQCv 1

TestBatchvl

HistoricalLocationv 1

HistoricalWaterLevelv 1

HistoricalChemistryv 1

HistoricalGeologyv 1

ESBasic

ESBasic.xsd

ESBasic

ESBasic.vb

EZEDD

EZEDD xsd

EZEDD

EZEDD. vb

EZLithology

EZLithology.xsd

EZLithology

Geology EDD

Geology EDD xsd

Site

Geology EDD. vb

Location

Well

SiteLoc

LocationParameter

AlternatePosition

DownholePoint

DrillActivity

Drill Parameter

GeologySample

GeoSampleParameter

Atterberg

StaticProps

Lithology

WaterLevel

WaterTable

WellConstruction

WellDatum

-------
EarthSaft

Format Name

Format Files

Section Names

Reference Values

refvals.xsd

All Reference Tables in EQuIS 6 Database

Water Level

waterlevel.xsd

WaterLevel

4.0 Appendix B - Additional Format Files

Additional formats may be installed with any type of EDP. These formats are listed below and by
default are installed in the C:/Program Files/EarthSoft/EQuIS/Formats directory. These formats
may be licensed by contacting the organization that designed the formats, or by contacting
EarthSoft.

Format Name

Format Files

Section Names

Audomated Data

ADRxse

eQAPP

Review (ADR)

ADRvbe

Delaware Natural

DNREC xse

Facililtyvl

Resources and

DNREC. vbe

Taskvl

Environmental Control

DNREC -enum .xsd

Subfacilityvl

(DNREC)

Locationvl

LocationParameterv 1

DrillActivity vl

Lithology vl

Well_vl"

WellConstruction_vl

GeoSamplevl

WaterLevel_vl

WaterTable_vl

DownholePoint vl

Extrationlnj ectionWellsv 1

Purgevl

FieldSamplevl

SampleParameterv 1

LabSamplevl

TestResultvl

TestResultQCvl

TestBatchvl

HistoricalLocationv 1

HistoricalWaterLevelv 1

HistoricalChemistryv 1

HistoricalGeologyv 1

-------
EarthSaft

Format Name

Format Files

Section Names

EPAR4

EPAR4.xse

EPAR4 Site vl

EPAR4.vbe

EPAR4 Location vl

EPAR4-enum.xsd

EP AR4W ellv 1

EPAR4 SiteLoc vl

EPAR4 LocationParemeter vl

EPAR4 AlternatePosition vl

EPAR4 DownholePoint vl

EPAR4 DrillActivity vl

EPAR4_GeologicSample_vl

EPAR4_GeoSampleParameter_v 1

EPAR4 Atterberg vl

EPAR4_StaticProps_v 1

EPAR4 Lithology vl

EPAR4 WaterLevel vl

EPAR4 WaterTable vl

EPAR4 WellConstruction vl

EPAR4 WellDatum vl

EPAR4 FieldResuits vl

EPAR4 FSample vl

EPAR4 TST vl

EPAR4_RES_v 1

EPAR5

EPAR5 xse

EPAR5SITE v3

EPAR5 vbe

EPAR5LOC v3

EPAR5-enum.xsd

EPAR5DRA v3

EPAR5LTH v3

EPAR5WEL v3

EPAR5WSG v3

EPAR5GSMP v3

EPAR5TBL v3

EPAR5DHP v3

EPAR5EIW_ v3

EPAR5SoilGas v3

EPAR5SMP v3

EPAR5TRS v3

EPAR5TRSQC v3

EPAR5BAT v3

EPAR5HistLOC v3

EPAR5HistWTR v3

EPAR5HistCHEM v3

EPAR5HistGEO v3

-------
EarthSaft

Format Name

Format Files

Section Names

Geotracker

GeotrackerEDF .xse

EDFSAMP

GeotrackerEDF.vbe

EDFTEST

EDFRES

EDFQC

EDFCL

GEO XY

GEO Z

GEO WELL

WELL CONSTRUCTION

EDFFlat

EDFCLFlat

gINT

gINT.xse

PROJECT

gINT_vbe

POINT

CPT

LITHOLOGY

REMARKS

SAMPLE

TESTS

WELL

gINTLab

gINT_Lab.xse

PROJECT

gINT_Lab.vb

POINT

CPT

LITHOLOGY

REMARKS

SAMPLE

ATTERBERG

ATTB READINGS

COMPACTION

COMP READINGS

CONSOL READINGS

CONSOLIDATION

DIRECT SHEAR

DSHR READINGS

FALL HEAD K

FHK READINGS

FINE SG

READINGS

HYD READINGS

HYDROMETER

LAB SPECIMEN

SIEVE

SV READINGS

UNC READINGS

UNCONF COMPR

WC DENSITY

WELL

-------
EarthSaft

Format Name

Format Files

Section Names

Multiple Facility EDD

Facility.vb

dtfacility

Facility .xsd

dtcoordinate

dtlocation

rtgroup

rtgroupmember

RockWorks

RockWorks .xse

RockWorks. vbe

RUSS

RUSS.xse

RUSS. vbe

West Virginia

WVDEP vbe

WV DATAPROVIDER V2

Department of

WVDEQ.xse

VW SITE V2

Enviromental Protection

WV SUBSITE VI

(WVDEP)

WV LOCATION V2

WV WELL VI

WV DRILLACTIVITY VI

WV LITHOLOGY VI

WV WELLC ONSTRUCTION VI

WV GEOLOGIC SAMPLE VI

WV WATERTABLE VI

WV DOWNHOLEPOINT VI

WV FIELDRESULTS V2

WV WATERLEVEL V2

WV SAMPLECOLLPROC V2

WV SAMPLEINFO V2

WV TESTRESULTS V2

-------