vvEPA Instructions for Accessing Results from the Fifth Unregulated Contaminant Monitoring Rule (UCMR 5) A basic understanding of Microsoft Excel is necessary to effectively use these instructions. Microsoft Excel 2013 or a newer version is recommended due to the size of the dataset. These instructions were created using Excel in Microsoft 365. Instructions 1. Begin by reviewing the UCMR 5 Data Summary and in particular note the Data Considerations and Data Definitions on pages 11 through 13. 2. Access the UCMR 5 dataset by going to EPA's UCMR website and selecting Occurrence Data. 3. Determine which dataset you are interested in and then Save the zip file to your computer. a. Select UCMR 5 Occurrence Data to view all the analytical results to date (i.e., results for all analytes reported by all systems). Note that we expect the UCMR5_All.txt file to become too large to be imported into Excel once the majority of the UCMR 5 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 5 Occurrence Data by State to view all the analytical results to date, organized by Tribes and states. Within that zip file, one text file (UCMR5_AII_Tribes_AK_LA.txt) will have all results for Tribal systems and for the states starting alphabetically with A through L; another file (UCMR5_AII_MA_WY.txt) will have all results for the states starting alphabetically with M through W. The results are organized this way to address file size limitations and streamline data management. c. Select UCMR 5 Occurrence Data by Method Classification to view all the analytical results to date, organized by analytical method. Within that zip file, you will find individual text files with results organized by method (e.g., a Method 200.7 text file with results for lithium). 4. The following text files for the additional data elements (i.e., parameters beyond the core 30 UCMR 5 contaminants) are also contained in each of the zip files described in Step 3: a. UCMR5_ZipCodes.txt - U.S. Postal Service Zip Code(s) for all areas served by a PWS b. UCMR5_AddtlDataElem.txt - DisinfectantType, Treatmentlnformation, LithiumOccurrence, LithiumTreatment, PFASOccurrence, PFASTreatment, PotentialPFASSources, PotentialPFASSourcesDetail 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 a text file 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, make sure Tab is selected. Click Transform Data. July 2023 Office of Water (MS-140) EPA 815-B-23-008 Page 1 of 3 ------- 11. The Query Editor will appear. In the Applied Steps section on the right side of the Query Editor under Promoted Headers, click on the X next to Changed Type to remove it. This will change the data type of all columns to "Text" format, which is useful for most columns in the file. 12. In the preview of the file, change data types for the following columns: a. Scroll to the right until you see the CollectionDate column. Click on the data type options (icon: ABC) to the left of the column name and select Date from the dropdown menu. b. Scroll to the MRL column and select Decimal Number from the data type dropdown menu. c. Scroll to the AnalyticalResultValue column and select Decimal Number from the data type dropdown menu. Note that the preview will show null for any results that are below the MRL. 13. 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 Table 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 (except CollectionDate) are currently stored in a "General" format. To ensure text fields that may contain numbers (e.g., PWSID, FacilitylD, SamplePointID, ZipCode) remain in a "Text" format, highlight the column or range of columns you want to convert and 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 from cells in these columns. 14. Save the Excel file. Note: You will need to repeat the above Steps to review updated datasets posted by EPA (approximately quarterly). Filtering Occurrence Data 15. Each column header will have a small dropdown arrow displayed allowing you to filter the results. a. To look for results for a specific PWS, click the dropdown arrow for PWSID or PWSName, and within the search field, type the ID or name, and select from the displayed list. Click OK. i. To search for a different PWS, click the dropdown arrow and Clear Filter From [field name]. b. To filter the results by Contaminant, click the column dropdown arrow and select from the list. i. To further filter for results by AnalyticalResultValue, click the column dropdown arrow and hover the cursor over the Number Filters option. From there, you can select a method for filtering the data (e.g., greater than, greater than or equal to). After you select a filtering method, a Custom Autofilter pop up box will appear allowing you to enter one or more values and optional other methods for filtering the data (e.g., greater than X, less than Y). c. Multiple filters can be applied, allowing you to look for data for a specific PWS and for a specific contaminant of interest. 16. 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 dataset will again be displayed. July 2023 Office of Water (MS-140) EPA 815-B-23-008 Page 2 of 3 ------- Additional Data Elements and Zip Codes 17. To look at the additional data elements, described in Step 4, you will need to import the associated datasets into new worksheets. a. To add a worksheet for UCMR5_ZipCodes.txt (U.S. Postal Service Zip Code(s) for all areas served by a PWS, as reported by the PWS) and/or UCMR5_AddtlDataElem.txt (information on disinfectant and treatment, lithium/PFAS occurrence and treatment, and potential PFAS sources) to the workbook that contains your occurrence data worksheet, click the plus (+) symbol in the bottom left corner of the screen next to the existing worksheet. b. Follow Steps 1-14 to import another text file into the new worksheet. i. See Step 13.c to ensure that the ZipCode field remains in a "Text" format to prevent leading zeros from being removed from cells. c. In your occurrence data worksheet, follow Step 15.a to filter for a specific PWSID or PWSName and note the PWSID for this system. i. In the Zip Codes 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 reported Zip Codes served by the PWS. 1. Note: Zip Codes may not yet be available for all PWSs with analytical results. ii. 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 sampling locations and events for the selected PWS. 1. You can apply other filters to view the additional data elements for a specific facility, sample point, or sample event. 2. Note: PWSs may have entered additional data elements for sample events that do not yet have analytical results, and additional data elements may not yet be available for all sample events with analytical results. If you cannot find results for a public water system (PWS): • The PWS may serve less than 3,300 individuals and was not selected for UCMR 5 monitoring as part of the nationally representative random sample of 800 PWSs serving less than 3,300 individuals. • The monitoring for a PWS may not have occurred yet. PWSs monitor during a single 12-month timeframe in the three years of monitoring (2023-2025). • The monitoring results for a PWS may not have been reported yet. Laboratories supporting UCMR 5 are obligated to report their data to SDWARS within 90 days of sample collection. The PWS then has up to 30 days to review that data. • A resample may be in process. If multiple resampling actions have taken place and the resample window has closed (e.g., it is time for the next sample event), or an issue at the laboratory precludes successful analysis, some data may not be reported. • The PWS may be a transient non-community water system (TNCWS). UCMR 5 requirements apply to community water systems (CWSs) and non-transient non-community water systems (NTNCWSs). • A null value (or blank) in the AnalyticalResultValue field in the text file indicates the result was less than the UCMR minimum reporting level (MRL). Only analytical results at or above the UCMR MRL are reported to EPA. The AnalyticalResultSign field also indicates whether the analytical result is less than (<) the MRL or equal to (=) a numeric value at or above the MRL, which would be reported under AnalyticalResultValue. See the UCMR 5 Data Summary and the UCMR 5 website for more information and answers to common questions. July 2023 Office of Water (MS-140) EPA 815-B-23-008 Page 3 of 3 ------- |