Transcript Laboratory Sample Result Electronic Submission

Laboratory Sample Result
Electronic Submission
Lab to State 2.0 and
Electronic Submission
Conference Call Etiquette
 Please MUTE your phone, this helps to reduce
background noise and static. You can do this by pressing
 * 6 , then press again to un-MUTE.
 If you can not MUTE your phone, please refrain from
having conversations with coworkers on your end.
 If you must make a call on your phone, it may be best to
hang up the conference call on your line, and dial in again
afterwards. Please don’t put us on Hold!
Laboratory Electronic Submission
Web Seminar Training
 Changes in Reporting Methods
 The CSV Template



Sample.csv
Result.csv
MonitoringSchedule.csv
 Web site tools
 Questions
Changes in Reporting Methods
 The Office of Drinking Water (ODW) will implement a
new electronic reporting system at the end of January,
2009.
 The new system, in addition to bacteriological results, will
allow laboratories to submit inorganic and organic
chemical, physical and radiological results.
Changes in Reporting Methods
 We have selected an Environmental Protection
Agency (EPA) developed application:
SDWIS/Lab to State 2.0. CSV templates sent to
ODW are uploaded through this system.
 This application is compliant with EPA electronic
reporting standards in the Cross-Media Electronic
Reporting Regulation (CROMERR).
CSV Templates:
Fields and Submission Requirements


CSV Templates Introduction and Requirements
Sample.csv


Result.csv




New Fields and Data Requirements
New Fields and Data Requirements
MonitoringSchedule.csv and tabs within this file
File saving procedure and requirements
Examples
CSV Templates:
Introduction and Requirements
File format: What does a .CSV file
look like?
The .CSV (Comma Separated Values) file appears below
as it might be viewed when opened in excel and notepad:
CSV Templates:
Submission Requirements
 Both the sample.csv and result.csv files must be completed and submitted for
data to be accepted into the ODW database.
 sample.csv contains the information identifying a sample, result.csv contains
the analytical result of the sample.
Sample.csv
Fields and Requirements
Sample.csv: Fields and Requirements
 Sample.csv contains the identifying information of
the sample.
 The 1st column in the file must always read
“sample.” No capitalization. This allows Lab to
State 2.0 to recognize the type of data.
 The 2nd column, or “Certifying Agency”, will be
“STATE” for state-certified labs.
 Lab ID, Sample ID, and PWSID have not
changed from the last template. These MUST be
duplicated on result.csv.
Sample.csv: Fields and Requirements




Sample Category identifies the sample as type coliform (TC), Lead and Copper (PB) or
General (GE).
Classification Code further identifies the sample based on the sample category.
Sample Type indicates the replacement type: Routine (RT), Repeat (RP), Special (SP),
etc.
Compliance Indicator: “Y” or “N” indicates whether the sample is for compliance or
not.
Examples
Sample
Category
Classification
Code
Sample Type
Compliance
Indicator
Coliform P/A Routine
TC
TC
RT
Y
Coliform P/A Special
TC
TC
SP
N
Coliform P/A Repeat
TC
TC
RP
Y
MPNs
GE
MPN
SP
N
Lead and Copper
PB
LC
RT
Y
Total Haloacetic Acids (HAA5)
GE
HAA
RT
Y
Total TriHalomethanes (TTHM)
GE
THM
RT
Y
Benzene
GE
VOC
RT
Y
Sample.csv: Fields and Requirements
 Facility ID is a new field that identifies a water system facility within a water
system with a specific ID, identified in the water system Monitoring Schedule.
 Sampling Point uniquely identifies a sampling point within the water system
facility. Refer to the Monitoring Schedule.
 Sample Location describes the address or location, i.e., “Kitchen Sink.”
Example: Monitoring Schedule
PWSID
Water
System
Name
Facility
ID
PWSFacility
Description
Sample
Category
Classification
Code
Sample
Type
Compliance
Sampling
Point
VA1000000
ABINGDON
GENERAL
DS001
Distribution System
TC
TC
RT
Y
TCR01
VA2000000
LEXINGTON
GENERAL
TP003
Chlorination System
GE
TOC
RT
Y
RW001
RAW WATER
SAMPLE
VA2000000
LEXINGTON
GENERAL
EP006
Entry Point
GE
RAD
RT
Y
EP001
LAB SAMPLE
TAP
VA2000000
LEXINGTON
GENERAL
TP003
Treatment Plant
GE
VOC
RT
Y
UP001
COMBINED
FILTER EFFL
VA2000000
LEXINGTON
GENERAL
DS005
Distribution System
5
GE
DBP
RT
Y
DBP01
COUNTY ADM.
BUILDING
VA2000000
LEXINGTON
GENERAL
DS001
Distribution System
1
PB
LC
RT
Y
LCR01
MENS REST
ROOM LOBBY
VA3000000
SOUTHEAST
GENERAL
DS001
Distribution System
GE
MPN
RT
N
EP001
TAP INSIDE
BUILDING
Sample
Location
Sample.csv: Fields and Requirements
 Collect Date and Receive Date must have
mm/dd/yyyy format. Leading zero’s must be
included.
 Collect Time must have hhmmss format.
Example: 9:35 AM will be 093500.
 Sampler Name: Enter name of sample collector.
 CL2 Residual: Leave blank if not analyzed, enter
0.0 if not detected, or enter actual numeric value
of Total or Free CL2 residual. No special
characters permitted, i.e. “<.”
Sample.csv: Optional Fields
 The template fields in the darker green color are
optional.
 Reject: If sample is rejected, results should not
be provided in result.csv. Select reject reason
from code list (on template and instructions.)
 PB Type Code: If analyzing Lead and Copper
samples, enter “FSD.”
 Repeat Samples: Complete information for
Original Sample Certifying Agency, Original
Sample Lab ID and Original Sample ID.
Result.csv
Fields and Requirements
Result.csv: Fields and Requirements
 Result.csv contains the analyte result for the
sample.
 The 1st column in the file must always read
“result.” No capitalization. This allows Lab to
State 2.0 to recognize the type of data.
 Certifying Agency, Lab ID, Sample ID, and
PWSID should be the same as on sample.csv,
unless the sample was rejected.
Result.csv: Fields and Requirements
 The Analyte Code can be found in the Analyte
Code Groups file as “SDWIS Code.” Example:
Total Haloacetic Acids (HAA5’s) = 2456.
 Standard Method – Standard analysis method
code for analyte.
 State Notification Date – enter date data is sent to
state in mm/dd/yyyy format.
 Data Quality – Indicates whether the analytical
result meets established data criteria (Accepted =
A, Rejected = R.)
Result.csv: Fields and Requirements
 Microbe Presence Indicator (Bacti ONLY) –
Enter “P” for Positive microbial result, “A” for
negative result.
 Count (MPN ONLY) – Enter a value for a
positive microbial result, or “0” for a negative
result.
 Volume (MPN ONLY) – Volume Assayed for
MPN, i.e. “100ML.”
 Count Type (MPN ONLY) – Enter “MPN.”
Result.csv: Fields and Requirements
 Less Than Indicator (GE or PB ONLY) – Enter “Y” if






analytical result is less than Lab Reporting Level (MRL) or
Federal Minimum Detection Limit (MDL). Enter “N” if result
exceeds level.
Less Than Code (GE or PB ONLY) – Code for reporting level
used, “MRL” or “MDL.”
Detection Level – Enter detection level used by lab if “MRL”
was entered for reporting code.
Detection Unit Measure – Unit of measure associated with
detection level used by lab if “MRL” was entered.
Concentration – Enter a concentration value if the analytical
result was reported as exceeding MRL or MDL.
Concentration Unit Measure – Enter unit measure associated
with the concentration value.
Note: “GE or PB ONLY” does NOT include MPN
Monitoring Schedule
and Analyte Code Groups
Monitoring Schedule
and Analyte Code Groups
The ‘MonitoringSchedule’ files can be found on the ODW website. Enter your PWSID list
into the Monitoring Schedule to retrieve FacilityID, Sampling Point information.
Helpful tools such as “Std. Methods Analytes assoc.” and “AnalyteCodeGroups” can be
found in the tabs of the Monitoring Schedule. These are useful in obtaining the correct
Standard Method code and Sample Category – Classification Code pairing.
Other Resources found on the Website
The ‘State Classified Water Quality List’ is a complete listing of required water quality
parameters organized by panels. Classification codes and Analyte codes are also
conveniently listed here.
The ‘Common Non-Regulated Parameters’ list follows the State Classified Water Quality
List and offers common non-regulated analytes that may be detected and reported to ODW.
The ‘Template: CSV Format’ provides links to download the sample and result templates,
and complete instructions and descriptions for each column.
The ‘CSV Examples’ is a good place to start when filling out CSV templates. Four links
are provided: 1 Bacti sample template, 1 Bacti result template; 1 Chemical sample
template; 1 Chemical result template. Bacti and Chemicals can be submitted together, but
be careful of filling out all the right columns!
The ‘Template: XML Format’ offers instructions on how to submit with XML.
File Saving Procedure and
Requirements
CSV Template:
File Saving Procedure and Requirements
 If using the Template_sample.xls and
Template_result.xls to enter sample data, in order to
submit it is necessary to:
 Delete the first row containing header names
 Save each file separately with the following format:
Lab Certification ID_BeginDate(yymmdd)_EndDate(yymmdd)_sample
Example: 00900_051107_051111_sample

Save with file type
 CSV (COMMA DELIMITED) *.csv
 It is recommended that you also save a copy
with file type .xls in order to make edits.
CSV Template:
File Saving Procedure and Requirements
You may get a few warnings from excel when saving your
.csv template.
 “The selected file type does not support
workbooks that contain multiple sheets.” Click
“OK”
 “The file.csv may contain features that are not
compatible with CSV. Do you want to keep the
workbook in this format?” Click “Yes” to keep
this format, which leaves out any incompatible
features.
CSV Template:
File Saving Procedure and Requirements
 Once you have saved your file as .csv, do NOT open
the .csv file to modify. You will lose the formatting
required to submit without errors.
 If you would like to modify the file, open the
original .xls file, modify, and save again as a .csv
file. Or, you can modify the .csv file directly in
Notepad.
 You will submit both the sample.csv and result.csv
at the same time to [email protected]
 Submit only new samples each time…do not resubmit samples.
Examples
Any Questions?