Data Types > Chemistry (Water, Soil, Gas etc..) > Laboratory Chemistry

ESdat Sample and Chemistry Electronic Data Deliverables v2e:

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:

Sample File,

Chemistry Results File (which contains the results).

Files not required, but recommended:

Batch Receipt (SDG) File,

Lab Report File,

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 EDD 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		Text(40)
	Blank1**	Empty
	Depth	From COC, (non-electronic COC’s only)		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 LabReportID_DateReceived.	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		N	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

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

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) More detailed lab method categories eg: PAH Amines
	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)
	QA_Status	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 LabReportID_DateReceived.	Y	Text(20)
	Destination_Laboratory	Lab Name	Y
	Laboratory_Contact	Lab Project Manager	Y	Date
	Date_Sent	Date sent from the field	Y	Text(40)
	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	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 and subsequent

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.

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.

Laboratory Data Transfer Format