WIXLIB

Next, download the ‘EDP v6 Format File Only’ and unblock the zipped folder according to theinstructions above. There is no need to unzip this folder, move the zipped format folder into theC:\Program Files\EarthSoft\EDP\Formats folder. If you were required to install the 32-bit version ofthe software, this path will be C:\Program Files (x86)\EarthSoft\EDP\Formats. The remainingreference within this document will assume the 64-bit version.2.3 Registering the EDPOnce installed, the EDP must be registered. Start the EDP application by selectingStart All Programs EarthSoft EQuIS EDP Standalone.The EDP application will start and a blank screen appears. Select ‘Format’ from the upper-left menu.Browse to select the zipped EPAR4 format file. If you moved it to the suggested folder, the path youselect will be to the C:\Program Files\EarthSoft\EDP\Formats folder. Select the file, and then clickthe ‘Open’ button. The “Evaluation” screen will appear. Click the ‘Register’ button.The ‘Software Registration’ screen will appear. Go to the ‘Workstation Licenses’ tab. Click the firstlink to open the registration request page in your web browser.USEPA Region 4 EDP Reference ManualVersion 2.0 December 20157

Enter the requested information in the ‘EDP Format for EPA Region 4 – Registration’ form. YourComputer ID should be automatically populated. If you are working with several RPMs, enter theprimary RPM or the RPM who provided your Approval Code in the appropriate field of theregistration form. You will need to request the Approval Code from either your RPM or email theR4DART@epa.gov. When all information has been entered, click ‘Submit’. With the properapproval code, the key codes will be automatically emailed to the email address entered in theregistration form.Once the new key codes have been received, register the EDP by copying the registration key codesinto the “New Key Codes” box on the ‘Workstation Licenses’ tab of the Software Registrationwindow. Click the ‘Save Key(s)’ button. A screen stating that the “Registration succeeded” shouldappear. Click OK. The EDP is now registered and ready for use. Registration is unique to acomputer. You will need to go through this process again to use EDP on a different computer.USEPA Region 4 EDP Reference ManualVersion 2.0 December 20158

3.0 Using the EDPEDP is a powerful tool that can check for data completion and referential integrity, identify errorsand create compressed files containing multiple related EDDs in a single useable format for uploadand storage in a relational data base system, such as Oracle or SQL. Sections below detail starting,loading, identifying, and correcting errors and saving your data for submission to EPA Region 4.3.1 Starting EDPEDP is available in two versions: “Standalone” which is available via the download and registrationprocess outlined in Section 2.0 above and “Professional” which is only available to users who havepurchased and licensed EQuIS Professional. Most users following these guidelines will be using the“Standalone” version.3.1.1 EQuIS EDP ProfessionalStart the application by selecting Start All Programs EarthSoft EQuIS Professional from theWindows ‘Start’ menu. Select the site you wish to process data for and allow EQuIS to open. Onceopen. Select EDP from the upper left-hand corner. Once open, follow the directions for the “Standalone” version in Section 3.1.2 below.3.1.2 EQuIS EDP StandaloneStart the application by selecting Start All Programs EarthSoft EQuIS EDP Standalone fromthe Windows ‘Start’ menu.The EDP will open. If the Region 4 format file does not load automatically, you will need to select itmanually. Select ‘Format’ from the upper-left menu. Select the zipped EPAR4 format folder that youcopied into the C:\Program Files\EarthSoft\EDP\Formats folder and click the ‘Open’ button. Thelower-left displays the elapsed time while the software is loading the format and respective referencevalues.Two tabs are displayed (three tabs are displayed in EQuIS Professional) at the bottom of the screen(as shown below). Select the “Reference Values” tab to view the current valid values that areacceptable in EPA Region 4. Select the “EPAR4” tab to view the current EPA Region 4 EDD formatspecifications you will use to load and check your data.USEPA Region 4 EDP Reference ManualVersion 2.0 December 20159

The Region 4 EDD sections are displayed along the left side of the window. An empty table with thefield names associated with the highlighted section type is displayed along the top.Each of the EDD sections listed in the EDP corresponds to the EDD files described in the Region 4Format Guide. In the screen above, the ‘EPAR4 FSample v1’ section has been selected and itsassociated fields are displayed across the top.Information about each field is provided when the cursor is placed over the column header or fieldname (as indicated in the above example).3.2 Loading EDD FilesFiles are checked either by loading individually created EDD files into EDP, by loading a singleAccess database created with individual tables named according to the naming conventions, byloading an Excel spreadsheet with tabs named according to the naming conventions, by loading a zippackage of individual EDD files or by loading individual related EDDs one at a time into theircorresponding positions in EDP.USEPA Region 4 EDP Reference ManualVersion 2.0 December 201510

3.2.1 Loading a Single EDD FileTo load a single EDD file (or multiple separate, but related EDD files one at a time):First select the format table of the EDD file to be checked from the format list on the left. In theexample below, an EPAR4 FSample v1 file is going to be checked, therefore, theEPAR4 FSample v1 format has been selected. Next, load the EDD data file by clicking the EDDicon located in the top menu bar or right-click on the format table and select ‘Load Data File’.Use the Browse window to locate the EDD file and select ‘Open’. The data file will load to the EDPand be checked during loading. This make take a few minutes depending on the size of the dataset.Data will be displayed in the table and any detected errors will be shaded. Note: If the data filecontains header rows, EDP will identify fields in the header rows as errors, unless each header row ispreceded by the default pound-sign character (#) in the first column.USEPA Region 4 EDP Reference ManualVersion 2.0 December 201511

3.2.2 Loading Multiple Tables within a Single EDDTo load a single EDD file containing multiple format tables:Click the EDD button from the menu bar, use the browsing window to locate the EDD file, andselect ‘Open’. The EDP will then load the constituent parts of the EDD into the appropriate tablesand display any errors. Note: This method may take several minutes.3.2.3 Managing Error DisplayIn the screen below, rows 3, 6, and 10 have errors. Each type of error is shaded differently. Place thecursor over an error to show a description for the type of error. To update the header rows if theyappear with errors, highlight the header rows by clicking to the left of the row, and then select the“Set as Comment Row” button from the top menu.To view only the rows with errors, check the box next to ‘Errors Only’ located in the top menu bar(as indicated in the example below). To restore all the rows, uncheck the ‘Errors Only’ box. Note: itmay take a few minutes to restore all the rows.USEPA Region 4 EDP Reference ManualVersion 2.0 December 201512

Be aware that EDDs may contain thousands of records and that large EDDs that contain anexorbitant number of errors may cause EDP to appear as not responding when switching from“Errors Only” to viewing all data.To clear the data from EDP, select the ‘Clear’ drop-down button from the top menu, then select“Clear EDD”. The EDD file will be cleared from the EDP viewer. Note: Clearing the data from theEDP will not delete the EDP file; it only removes the file from the viewer.3.3 EDD Data File ChecksEDP has the ability to check for errors both within a single EDD and between related EDD files.Along with EPA Region 4 business rule verifications, the EDP checks data for the followingpotential issues: Reference Values Field Lengths Required Fields Data Types Valid Dates Duplicate Rows Parent-Child RelationshipsThese errors are outlined in detail below.USEPA Region 4 EDP Reference ManualVersion 2.0 December 201513

3.3.1 Reference Value Not FoundThe value in the field does not match the values listedin the reference file downloaded from EPA. If thevalue is correct and after careful consideration andresearch of the value to ensure it does not exist in adifferent format (many analytical methods may bewritten in similar ways but reference the same method,etc., and analytes may potentially have manysynonyms), follow the guidelines in Section 2.6 of the Region 4 Format Guide to request that thevalue be added to the EPA Region 4 valid value tables. Send that request along with the necessaryaccompanying information to R4DART@epa.gov. The DART Coordinator will review the requestand forward it, if appropriate, to the correct administrator for review and inclusion in the system.Do NOT submit your data until the request has been approved and you are notified that the valueswere added. Doing so may cause your data to be rejected for failing to pass EDP. If a newEPAR4.rvf file is not provided, check the website for and download the new one when available.Replace the old EPAR4.rvf with the updated file and recheck your EDD before submitting toR4DART.3.3.2 Value Exceeds Field LengthThe number of characters of the valueentered in the field exceeds the maximumallowed number of characters. Place yourcursor over the column header to viewthe description of the field that willinclude the field length. For further detail of each field, see Section 3 of the Region 4 Format Guide.3.3.3 Missing Required FieldThe field must be populated with a value. The field cannot be leftnull (i.e., blank). See Section 3 of the Region 4 Format Guide forinformation on required fields.Note that the field name at the top of the column is written in red.This indicates that the field is required and that the EDD will notpass EDP unless all values in this column are populated correctly.Placing your cursor over the column header will also bring up the description that includes the line:“NOTE: This is a required field.”A common problem arises with the CAS RN field during the conversion process from one type offile, such as Comma Separated to another, such as Excel. In this process, any CAS RN that mayappear as a date may be converted to a date. An example is the CAS RN for Potassium: 7440-09-7will be converted to 9/7/7440.USEPA Region 4 EDP Reference ManualVersion 2.0 December 201514

3.3.4 Invalid Data TypeThe value is not the appropriate data type. Each field has a specificdata type that must be used, such as text, date/time, or numeric. Ifthe appropriate data type for a field is Date/Time, then the valuemust be a valid date format such as the MM/DD/YYYY HH:MMformat. See the Data Type description in Section 3 Region 4Format Guide for the appropriate data type.Another common problem arises with Date/Time fields during the conversion process from one typeof file. In this process, Date/Time fields may be incorrectly converted to an integer Julian date. Besure to check your Date fields to make sure they are appropriately classified to avoid errors.3.3.5 Duplicate RowTwo or more records have the same values in theprimary key fields. The primary key fields arethe fields that make each record in the fileunique. No two records can have the same valuesin the primary keys. For example, theEPAR4 Location v1 file has the sys loc codefield as the primary keys. Two records that bothhave 006 in the sys loc code fields would beconsidered duplicate records. To make each record unique, one record would have to be changed sothat the sys loc code was something other than 006.Laboratories frequently report data from the same event in multiple packages, sometimes creatingduplications of sample records. In these cases, if all data is processed through EDP at the same time,duplicate records will appear in the EPAR4 FSample v1 EDD. These duplicate records will need tobe deleted prior to submitting the data to EPA Region 4. Data will not pass the EDP checker withduplicate rows.Refer to Section 2.4 of the Region 4 Format Guide for further discussion of data integrity andduplicate records.USEPA Region 4 EDP Reference ManualVersion 2.0 December 201515

3.3.6 Orphan RowThe record is missing a required parent record. Records thatdepend on information (i.e., child records) from anotherrecord (i.e., parent record) must reference the parent recordaccurately and the parent record must exist in thecorresponding file.For example, each row in the EPAR4 TST v1 table must include a sys sample code thatcorresponds to a sys sample code reported in the EPAR4 FSample v1 table. If a record in theEPAR4 TST v1 table has a sys sample code of GWSMP-006 then a record must also be includedin the EPAR4 FSample v1 table with a sys sample code of GWSMP-006. If a record in theEPAR4 TST v1 table has a sys sample code that is not included in the EPAR4 FSample v1 table,an “Orphan Row” error will be identified.Likewise, each row in the EPAR4 RES v1 table must have a matching “parent” row in theEPAR4 TST v1 table. There are six (6) fields that establish the relationship between the results andtest records, and they are: sys sample code, lab anl method name, analysis date, analysis time,total or dissolved, and test type. See Table 2-2 and Section 2.4 of the Region 4 Format Guide forfurther discussion of child/parent records.3.3.7 Result value is Required When detect flag YIdentifies records that have thedetect flag (EPAR4 RES v1) valueof ‘Y’ yet there is no value reported inthe result value field. This errorapplies only to records of targetanalytes (TRG) and tentativelyidentified compounds (TIC). If a record has a value of “TRG” or “TIC” in the result type code(EPAR4 RES v1) and the detect flag has a value of ‘Y’, the result value field must be populatedwith the numeric result value (i.e., it cannot be left blank).3.3.8 Quantitation limit Cannot be Null when detect flag NIdentifies records with adetect flag(EPAR4 RES v1) valueof‘N’andthequantitation limit field isnull. All records that havea value of ‘N’ in the detect flag field must have the quantitation limit field populated with theappropriate detection limit value (i.e. it cannot be left null).USEPA Region 4 EDP Reference ManualVersion 2.0 December 201516

3.3.9 Parent sample code is Required Where sample type code FDIdentifies records that have a sample type code (EPAR4 FSample v1) of “FD” but are missing theappropriate parent sample code. The above sample type codes signify duplicates, and the sampleidentifier (i.e., sys sample code) of the original sample from which the duplicate was derived mustbe populated in the parent sample

.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"