Download two Excel templates at the bottom of the page below -- Sample Results and Operational Data. Example files, including sample data, are also included for your reference. Note: The latest version of the CMDP templates should be downloaded directly from the CMDP application (cmdpprep.epa.gov or cmdp.epa.gov).
Things to know when using Excell templates for CMDP submission:
- You may want to make a copy of the template you'll use and name the file to reflect the data you'll be entering into it. You likely will want to use the same name when you generate an XML file from the template, so have it handy. The name you give to the XML file will be recorded in CMDP when you upload the XML file.
- Some data validations are included in the templates to help ensure that the data are valid and will be accepted by CMDP.
- Be sure to enter valid data in appropriate formats in each cell so that a record is not rejected. If any cell contains invalid data or formats, the record will be rejected. Please refer to the CMDP Web Services Sampling Data Dictionary for valid values for the fields used by CMDP.
- It is critical that users take into consideration the stored reference data in CMDP and that data are case-sensitive. For example, entering “oh0000001” as a Water System ID is not a valid value; the correct value is “OH0000001.”
- If a record contains a value not stored in CMDP as reference data for these fields, then the value will not be considered valid, and CMDP will reject the record. To help avoid these kinds of errors, please log into CMDP and view the PWS Profiles or Laboratory Profiles to check for the reference data stored in CMDP for critical fields such as: Water System ID, Water System Facility ID, Sampling Point ID, and Laboratory ID. Alternatively your state primacy agency may already provide a reference for submitters, for example, Drinking Water Watch, or another state website, where these values are stored.
- Because the CMDP templates are in MS Excel, copy and paste features are available for use. If multiple samples share the same information (same collection date, sample time, etc.), you can copy the information contained in a row and paste it in the next row.
- When entering repeat samples, make sure that you populate the routine (Original) Sample ID and optionally the Repeat Location field. It is important that the value (ID) entered in the Original Sample ID field exists in CMDP before the associated repeat samples are reported, otherwise the repeat samples will be rejected. To ensure this works correctly when CMDP processes the content of the Template, enter the routine sample into a row in the template and then enter any associated repeat samples in the rows below.
- Save your progress regularly when using Excel. Also, save your template prior to clicking the “Generate XML” button on each tab.
- While it is possible to use the CMDP_ Sample_Result_Template.xlsm to enter multiple samples (Microbial, Chem/Radionuclides, and Crypto) for different water systems if needed, the CMDP_Operational_Data_Template.xlsm for CFE, IFE, and Disinfectant Residuals will only allow reports for one particular water system facility at a time.
- The Excel Templates cannot be uploaded as Excel files to the CMDP application; only the XML files created using the “Generate XML” button can be uploaded.
- Once an XML file is uploaded successfully, a draft Sample Job number will be created, and the contents will appear to the user in CMDP as web forms for each sample result, so that you can edit the data and submit just as if you had entered the results as web forms.
- The following features are available in the CMDP user interface for uploaded jobs (as long as the user has the appropriate permissions): Add/Remove Attachments, View Job History (any actions will be recorded when Job is in Draft with Reviewer Status and forward), View Validations, and Add/Remove Samples for a Job.
- Some of the columns contain pick-lists where you can search for a specific value (e.g., Analytes). In that case, you can double-click the cell and enter the value to look up; the field will be populated with the result of your search when you press Enter.
The key is as follows: