Laboratory Sample Result Electronic Submission

1
Laboratory Sample Result Electronic Submission

• Using Lab to State 2.0 and
• Electronic Submission

2
Laboratory Electronic SubmissionWeb Seminar
Training

• Changes in Reporting Methods
• The CSV Template
• Sample.csv
• Result.csv
• MonitoringSchedule.csv
• AnalyteCodeGroups.csv
• Lab to State 2.0 file submission
• Web site tools
• Questions

3
Changes in Reporting Methods

• The Office of Drinking Water (ODW) will implement
  a new electronic reporting system by 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.
• Results will be submitted at a website, through
  the ODW web portal.

4
Changes in Reporting Methods

• We have selected an Environmental Protection
  Agency (EPA) developed application SDWIS/Lab to
  State 2.0.
• This application is compliant with EPA electronic
  reporting standards in the Cross-Media Electronic
  Reporting Regulation (CROMERR).

5
Changes in Reporting Methods
Weve worked on the format required for the .CSV
templates!
So that you wont need to!
6
CSV Templates Fields and Submission
Requirements

• CSV Templates Introduction and Requirements
• Sample.csv
• New Fields and Data Requirements
• Result.csv
• New Fields and Data Requirements
• MonitoringSchedule.csv and AnalyteCodeGroups.csv
• File saving procedure and requirements
• Examples

7

• CSV Templates
• Introduction and Requirements

8
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

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

10

• Sample.csv
• Fields and Requirements

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

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

13
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
14
Sample.csv Fields and Requirements

• Collect Date and Receive Date must have
  mm/dd/yyyy format. Leading zeros must be
  included.
• Collect Time must have hhmmss format. Example
  935 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. lt.

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

16

• Result.csv
• Fields and Requirements

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

18
Result.csv Fields and Requirements

• The Analyte Code can be found in the Analyte Code
  Groups file as SDWIS Code. Example Total
  Haloacetic Acids (HAA5s) 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.)

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

20
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

21

• Monitoring Schedule
• and Analyte Code Groups

22
Monitoring Schedule and Analyte Code Groups

• The files MonitoringSchedule.csv and
  AnalyteCodeGroups.csv can be found on the ODW
  website.
• Note The MonitoringSchedule.csv file on the
  website is only an EXAMPLE file.

23

• File Saving Procedure and Requirements

24
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(yym
  mdd)_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.

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

26
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.
• When submitting to Lab to State 2.0, you will
  submit both the sample.csv and result.csv at the
  same time.
• Submit only new samples to Lab to State 2.0 each
  timedo not re-submit samples.

27

• Examples

28
Lab to State 2.0

• Demonstration of the use of Lab to State 2.0

29
Web Site Changes

• Regularly check back for FAQ updates and
  information!

30
Any Questions?