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