Instructions for Using Microsoft Excel to Access Results from the Fourth Unregulated Contaminant Monitoring Rule (UCMR 4)


United States
Environmental Protection
Agency
Instructions for Using Microsoft Excel to Access Results from the Fourth
Unregulated Contaminant Monitoring Rule (UCMR4)
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. These
instructions were created using Excel 2016.
1.	Begin by reviewing the UCMR 4 Data Summary and in particular note the "Data
Considerations" on pages 10 through 14.
2.	Access the UCMR 4 dataset and view reported results by going to the UCMR homepage and
selecting "Occurrence data."
3.	Determine which dataset you are interested in and then SAVE the zip file to your computer.
a.	Select "UCMR 4 Occurrence Data" to view ALL the analytical results (i.e., results for all
analytes reported by all public water systems to date). Note that we expect the
"UCMR4_all.txt" file to become too large to be imported into Excel once the majority of
the UCMR 4 results are reported, in which case you can try other applications (e.g.,
Microsoft Access) or import a subset of the data described in 3.b and 3.c below.
b.	Select "UCMR 4 Occurrence Data by State" to view ALL the analytical results to date,
organized by tribes and states: within that zip file, one text file
("UCMR4_AII_Tribes_AK_LA.txt") will have all results for tribal systems as well as for the
states of Alaska through Louisiana; another ("UCMR4_AII_MA_WY.txt") will have all
results for the states of Massachusetts through Wyoming. The results are organized this
way to address file size limitations and make data management easier.
c.	Select "UCMR 4 Occurrence Data by Method Classification" to view ALL analytical results
to date for all systems by analytical method. Within that ZIP file you will find individual
text files with results organized by method (e.g., a Method 200.8 text file with results for
germanium and manganese).
4.	The following text files for the additional data elements (i.e., parameters beyond the core 30
UCMR 4 contaminants) are also contained in each of the zip files described in Step 3:
a.	"UCMR4_HAA_lndicators.txt"- Results for total organic carbon and bromide
b.	"UCMR4_ZipCodes.txt" - U.S. Postal Service zip code(s) for all areas served by a PWS
c.	"UCMR4_HAA_AddtlDataElem.txt" - Additional data elements for the HAAs:
DisinfectantType, DisinfectantResidual and Treatmentlnformation
d.	"UCMR4_Cyanotoxin_AddtlDataElem.txt" - Additional data elements for the
cyanotoxins: DisinfectantType, Treatmentlnformation, CyanotoxinBloomOccurrence,
CyanotoxinOccurrence, CyanotoxinPossibleBloomTreatment and
CyanotoxinPossibleBloomSource Water
Office of Water (MS-140)
EPA 815-B-18-006
October 2018

-------
5.	Navigate to the location on your computer where you saved the zip file and unzip or extract the
zip file contents.
6.	Open a blank workbook in Microsoft Excel.
7.	In the workbook, select "Data" among the tabs at the top of the page.
8.	On the far left, top of the screen, go to the "Get & Transform Data" section and select "From
Text/CSV". (Alternate option: Click "Get Data" and select "From File" from the dropdown
menu and then choose "From Text/CSV").
9.	You will be prompted to select a text file, find the text files you unzipped or extracted in Step 5,
and click IMPORT.
10.	A preview of the file will appear. The default settings will be displayed. Under Delimiter select
"Tab" and under Data Type Detection select "Do not detect data types". Click Edit.
11.	The Query Editor will appear. In the Transform section of the Home tab, click "Use First Row as
Headers". Then, in the Home tab at the far left click "Close & Load". The import may take
several minutes.
a.	Please note that Excel worksheets have a limit of 1,048,576 rows. If the text file you are
importing has more rows than this, you will see the message: "The query returned more
data than will fit on a worksheet." In this case, you may consider downloading and
importing a subset of the data; see Step 3.
b.	This import created a "link" between the Excel spreadsheet and the text file. If you want
to "unlink" the Excel workbook and the text file, go to the Design tab at the top of the
screen and in the External Table Data section click "Unlink" (icon: broken chain). A
popup window will appear saying "This will permanently remove the query definition
from the sheet. Continue?" Click "Yes".
c.	All fields are currently stored in a "General" format. To store the data in a "Text" format,
select the entire worksheet either by using the Ctrl + A keyboard shortcut or by clicking
the triangle to the left of Column A and above Row 1. Then navigate to the "Number"
section of the "Home" tab, click on the dropdown field that reads "General", and select
"Text." This prevents leading zeros from being removed (e.g., from zip codes).
Alternatively, you can select specific columns one at a time and change them to "Text".
12.	Save the Excel file. Note: You will need to repeat the above steps to review updated datasets
posted by EPA (anticipated approximately quarterly).
13.	Each header title for each column will have a small dropdown arrow displayed allowing you to
filter the results.
a.	If you want to look for a specific public water system, click the dropdown arrow for
"PWSID" or "PWS Name", and within the search field, type the ID or name and select
from the displayed list. Click "OK".
b.	If you want to search for a different public water system, click the dropdown arrow and
"Clear Filter From [field name]."
c.	If you want to filter the data by contaminant, select that header.
d.	Multiple filters can be applied, allowing you to look for a public water system's data for
a specific contaminant of interest.
14.	To remove all filters at once, select "Data" among the tabs at the top of the page and in the
"Sort & Filter" section, click "Clear" or de-select "Filter" and the entire database will again be
displayed.
Page 2 of 3

-------
15. If you want to look at the additional data elements, described in Step 4, you will need to
import the associated datasets into new worksheets.
a.	If you wish to add a worksheet for "additional data elements" to the workbook that
contains your "analytical results" worksheet, click the plus (+) symbol in the bottom left
corner of the screen.
b.	Follow steps 1-11 to import another zip file into the new worksheet.
c.	In the analytical results worksheet, follow Step 13.a to filter for the specific "PWSID" or
"PWS Name" (note the "PWSID" for this system).
d.	In the additional data elements worksheet, use the dropdown arrow for "PWSID", and
within the search field, type the PWSID you noted into the search box and click "OK".
You will now see the available additional data elements for all sampling locations and
events for the selected public water system.
e.	You can apply other filters to view the additional data elements for a specific facility,
sample point, or sampling event.
f.	Note that public water systems may have entered additional data elements for sampling
events that do not yet have analytical results reported.
If you cannot find any data for a public water system:
•	The public water system may serve less than or equal to 10,000 individuals as a "small water
system" and was not selected as part of the representative sample for UCMR 4 monitoring.
•	The monitoring for a system subject to UCMR 4 may not have occurred yet or their
monitoring results may not have been reported yet.
Page 3 of 3

-------