ESDAT Sample and Chemistry Electronic Lab Data Format v2 extended:
Download File Checker
The purpose of the ESDAT Electronic Lab Data Format (ELDF) is to enable transfer of data from a laboratory to ESDAT users in a format which allows
checking and direct import into ESDAT.
This version 2 extended format is compatible with all current implementations of ESdat, and is additionally ready for the pending upgrade to v 4 of the
ELDF (there is no v3). Upgrade to v 4 will only require the removal of the fields named Blank# in the Sample file.
The files contain batch receipt, lab report, sample and chemistry information, including comprehensive quality assurance data. The ELDF consists of two
required files and two optional files. All four files will be required for v4 compatibility.
Required Files:
Files not required, but recommended; required if client is using electronic Chain of Custody (COC) functionality in ESdat:
The ELDF file names are of the format: the project name as provided by the users; a full stop; a laboratory file identifier; a full stop; the text
"Chemistry2e" or "Sample2e" etc.. (For the Chemistry or Sample ELDF respectively) and the suffix ".csv".
An example of a file set would be
Project1.LabFile01.Sample2e.csv
Project1.LabFile01.Chemistry2e.csv
Project1.LabFile01.SDG2e.csv
Project1.LabFile01.LabReport2e.csv
Within the export files Line 1 lists the data fields, line 2 and onwards contains the data for those fields:
Sample File:
One record for each field and laboratory sample
|
Primary Key
|
Field
|
Description
|
Req'd
|
Data Type
|
|
*
|
SampleCode
|
Unique Identifier for each sample *
|
Y
|
Text(40)
|
|
|
Sampled_Date_Time
|
From COC
|
|
Date with optional time
|
|
|
Field_ID
|
From COC (Normal samples only)
|
|
Text(40)
|
|
|
Blank1**
|
Empty
|
|
|
|
|
Depth
|
From COC
|
|
Number
|
|
|
Blank2
|
Empty
|
|
|
|
|
Matrix_Type
|
|
Y
|
List of Soil, Water, Gas, SoilGas
|
|
|
Sample_Type
|
Description of sample as from the field, spike, surrogate.
|
Y
|
List of :
Normal,
MS, (Matrix Spike)
MS_D, (Matrix Spike Duplicate)
MB, (Method Blank)
SB, (Storage Blank)
LCS, (Lab Control Sample)
SRM, (Standard)
CRM, (certified reference material)
LAB_D (Duplicate)
LAB_T (Triplicate)
NCP (Non-Client Parent - for duplicates or surrogates from another client in the same sample analysis batch)
|
|
|
Parent_Sample
|
For duplicates, give the SampleCode of the original
|
|
Text(40)
|
|
|
Blank3
|
Empty
|
N
|
|
|
|
SDG
|
Sample Delivery Group, From COC, or if not available use "Lab Name Date Received".
|
Y
|
Text(20)
|
|
|
Lab_Name
|
|
Y
|
Text(20)
|
|
|
Lab_SampleID
|
Laboratory Sample ID
|
Y
|
Text(20)
|
|
|
Lab_Comments
|
Comments regarding the sample
|
N
|
Text(255)
|
|
|
Lab_Report_Number
|
|
Y
|
Text(20)
|
*The SampleCode should be constructed using the following method. Field Samples to be coded SDG_FieldID. Laboratory Samples (all non client samples) to
be coded Lab Report Number_LabSampleID
**The SampleCode should be constructed using the following method. Field Samples
to be coded SDG_FieldID. Laboratory Samples (all non client samples) to be coded
Lab Report Number_LabSampleID
**Fields Blank1, Blank2 etc.. are to maintain consistency with the current v2.7
export. They do not need to be populated as this data is now provided in the Lab
Report and SDG files. They will eventually be removed from the export when all
ESdat users can support v4 of the ELDF. When they are removed the alteratered
files will be v4 compatible.
*** The records in the file should be sorted by Sample_Type
DESC. This means the "Normal" samples will appear first. A
secondary sort should be by Field_ID ASC. This makes it easy for users to
identify their QA Samples.
Chemistry File:
One record for each result
|
Primary Key
|
Field
|
Description
|
Required
|
Data Type
|
|
*
|
SampleCode
|
Unique Identifier for each sample (as per Sample File)
|
Y
|
Text(40)
|
|
*
|
ChemCode
|
Unique Identifier for each analyte (eg. CAS number)
|
Y
|
Text(20)
|
|
|
OriginalChemName
|
Common name for the analyte
|
Y
|
Text(50)
|
|
|
Prefix
|
|
if below detection limit
|
"<" or ">"
|
|
|
Result
|
|
Y
|
Number
|
|
|
Result_Unit
|
|
Y
|
Text(10)
|
|
*
|
Total_or_Filtered
|
Either Total or Filtered, if blank will default to T.
|
Y
|
List of
T (Total)
F (Filtered)
|
|
*
|
Result_Type
|
|
Y
|
Restricted to:
REG (regular result)
leached_REG (regular leached results)
SUR (Surrogate)
SC (Spike Compound)
|
|
|
Method_Type
|
|
Y
|
Text(50):
PAH
Pesticides
Inorganic
Metals...
Etc as indicated on the CoC.
|
|
*
|
Method_Name
|
|
Y
|
Text(70)
Method or Test Code.
|
|
|
Extraction_Date
|
Date extracted
|
if possible
|
Date
|
|
|
Analysed_Date
|
Date analysed
|
if possible
|
Date
|
|
|
EQL
|
Quantitation Limit
|
Y
|
Number
|
|
|
EQL_Units
|
Quantitation Limit Units
|
Y
|
Text(15)
|
|
|
Comments
|
|
N
|
Text(255)
|
|
|
Lab_Qualifier
|
Optional *
|
N
|
Text
|
|
|
UCL
|
Upper Confidence Limit used for surrogate recoveries
|
N
|
Number
|
|
|
LCL
|
Lower Confidence Limit used for surrogate recoveries
|
N
|
Number
|
*The QA_Status may be populated with a data qualifier (U,J,J+,J-,R,UJ) as described in various USEPA documents, for example:
http://www.epa.gov/superfund/programs/clp/download/inorgfg10-08-04.pdf
, / Data Qualifier Definitions
SDG File:
Collects information recorded on the COC and lab receipt information
|
Primary Key
|
Field
|
Description
|
Req'd
|
Data Type
|
|
*
|
SDG
|
Sample Delivery Group, From COC, or if not available use "Lab Name Date Received".
|
Y
|
Text(20)
|
|
|
Destination_Laboratory
|
Lab Name
|
Y
|
|
|
|
Laboratory_Contact
|
Lab Project Manager
|
Y
|
Text(40)
|
|
|
Date_Sent
|
Date sent from the field
|
Y
|
Date
|
|
|
Relinquished_By
|
Field Contact
|
Y
|
|
|
|
Primary_Contact
|
Client Project Manager
|
Y
|
|
|
|
Turnaround_Request
|
|
Y
|
|
|
|
Special_Instructions
|
|
Y
|
If nothing use NA
|
|
|
Cooled
|
Y/N
|
Y
|
|
|
|
Lab_Quote_No
|
|
Y
|
|
|
|
Number_Delivery_Boxes
|
Ie number of eskys
/ coolboxes
|
Y
|
|
|
|
Lab_Recipient
|
Person receiving the boxes
|
Y
|
|
|
|
Lab_Login_Date
|
|
Y
|
|
Laboratory Report File:
Collects report level information
|
Primary Key
|
Field
|
Description
|
Req'd
|
Data Type
|
|
*
|
Lab_Report_Number
|
Unique Identifier for each lab report
|
Y
|
Text(20)
|
|
|
Date_Reported
|
|
Y
|
|
|
|
Project_Name
|
Name\Number as given by the client
|
Y
|
|
|
|
Lab_Name
|
|
Y
|
|
|
|
Lab_Signatory
|
|
Y
|
|
|
|
Report_Recipient1
|
Main recipient
|
Y
|
|
|
|
Report_Recipient2
|
Second
|
|
|
|
|
Report_Recipient3
|
Third
|
|
|
File Format
The fields and data in the file may optionally be surrounded by quotation marks.
Duplicates
Lab duplicates and triplicates should have a different sample code to the original sample, (typically the same code ending in _D, _T or similar, ie
each sample code is unique). The Sample_Type field should indicate whether it is a duplicate or triplicate. The original sample code for duplicates or
triplicates should go in the field "Parent_Sample".
Leachate Tests
Leaching test results are not given a separate SampleCode, (ie are not treated as a separate sample). Results are indicated in the chemistry file, and
are identified in the Result_Type field.
Date Formats
Dates should be in the format dd mmm yy (eg 3 Jan 07). This avoids confusion between users with different regional settings. If time is included it should be hh:mm AM/PM.
Tentatively Identified Compounds
Tentatively Identified Compounds should be identified as such in the Comments field of the chemistry file.
Asbestos
As the result value requires a number, and asbestos is often reported as text the following convention is adopted. For a non detect the result has a
value of 0. For a detect the result has a value of 1 and the text is inserted into the Comments Field.
