S-EPA
United States
Environmental Protection
Agency
Instructions for Using Microsoft Excel to Import the Third Unregulated
Contaminant Monitoring Rule (UCMR 3) Results
A basic understanding of Microsoft Excel is necessary to effectively use these instructions. Using
Microsoft Excel 2013 or a newer version is recommended due to the size of the dataset.
1. Begin by reviewing the UCMR 3 Data Summary and in particular note the table of "UCMR 3
Data Field Names and Definitions" on pages 1 through 3.
2. Access the complete UCMR 3 dataset and view reported results, by going to the UCMR
homepage and selecting "Occurrence data."
3. Determine which dataset fits your goal for filtering, analyzing, and/or viewing the analytical
results and then SAVE the zip file to your computer.
a. Select "UCMR 3 Occurrence Data by State" to view ALL the analytical results from ALL
methods from a SUBSET of water systems based on states/tribes contained in the text
file selected.
b. Select "UCMR 3 Occurrence Data by Method Classification" to view ALL analytical results
for a SPECIFIC analyte or method for ALL public water systems.
c. The COMPLETE dataset "UCMR 3 Occurrence Data" is too large to be imported into
Excel. You may still select import "UCMR3_all.txt" into Microsoft Access or an
application like Statistical Analysis System (SAS) to access ALL analytical results.
4. Navigate to the location on your computer where you saved the zip file and unzip or extract the
zip file contents.
5. Open a blank workbook in Microsoft Excel.
6. In the workbook, select "Data" among the tabs at the top of the page.
7. On the far left, top of the screen, go to the "Get External Data" section and select "From Text".
8. You will be prompted to select a text file, find the text files you unzipped or extracted in Step 4,
and click IMPORT on the text file that represents your state/tribe or the method of interest.
9. The Text Import Wizard - Step 1 of 3 will appear. The default settings will be displayed and
should have "Delimited" selected as the Original data type. Select the checkmark box next to
"My data has headers." Click NEXT.
10. The Text Import Wizard - Step 2 of 3 will appear. The default settings will be displayed and
should have "Tab" selected as the Delimiter while "Treat consecutive delimiters as one"
should be unselected. Select "Text qualifier" as {none} from the dropdown menu. Click NEXT
11. The Text Import Wizard - Step 3 of 3 will appear. The default settings will be displayed. The
"Column data format" should be specified as "General". In the "Data preview" window click on
the following columns (one-by-one): PWSID, FacilitylD, SamplePointID, AssociatedFacilitylD,
AssociatedSamplePointID, SamplelD and select "Text" under the "Column data format". Click
FINISH.
Office of Water (MS-140) EPA 815-S-16-003 July 2016
-------
12. The "Import Data" prompt will appear. Click OK. This import may take several minutes.
13. Save the Excel spreadsheet file. Note: You will need to repeat the above steps when the next
quarterly dataset is posted.
14. To efficiently search, have cell Al selected, choose "Data" among the tabs on the top of the
page and click on the "Filter".
15. Each header title for each column now will have a small dropdown arrow displayed.
a. If you want to look for a specific public water system, click the dropdown arrow for
"PWS Name", and within the search field, type the name and select from the displayed
list.
b. If you want to search for a different public water system, click the dropdown arrow and
"Clear the Filter."
c. If you want to filter the data by contaminant, select that header.
16. Multiple filters can be applied, for example, allowing you to look for an individual water
system's data for a specific contaminant of interest.
17. De-select "Filter" in the top menu bar and the entire database will again be displayed.
If you cannot find any data for a public water system:
• The water system may serve less than 10,000 individuals as a "small water system" and were
not selected as part of the representative sample for UCMR 3 monitoring. Review the
applicability criteria in the UCMR 3 Data Summary.
• The monitoring data may not have been reported yet.
Page 2 of2
------- |