Laboratory Sample Result Electronic Submission

1. Laboratory Sample Result Electronic Submission Lab to State 2.0 and Electronic Submission

2. 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!

3. Laboratory Electronic SubmissionWeb Seminar Training • Changes in Reporting Methods • The CSV Template • Sample.csv • Result.csv • MonitoringSchedule.csv • Web site tools • Questions

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

5. 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).

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 tabs within this file • 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 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. “<.”

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 (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.)

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

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

24. File Saving Procedure and Requirements

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

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

27. 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 labadmin@vdh.virginia.gov • Submit only new samples each time…do not re-submit samples.

28. Examples

29. Any Questions?