Ready to check if your inventory data matches the EPA template? We have launched an online data validation tool that allows you to upload your data, map fields and values, and see potential errors and warnings. This tool is free of charge. Use it to ensure your data is LCRR compliant via a few simple steps!
Why this matters: Water utilities must submit their service line material inventories to their state primacy agency ahead of the US EPA deadline of October 16, 2024.
Who this is for: Water Utilities preparing their service line material inventory for Lead and Copper Rule Revision compliance.
Getting Started
Once you’ve navigated to the tool, you can
- answer a few preliminary questions,
- upload your inventory data in a spreadsheet file,
- follow the simple steps to address any issues,
- then download the completed data file.
To use the tool, you will need to have your inventory data as a comma separated value (*.csv) or Excel (*.xlsx) file.
Field Mapping – Columns
Once the file is uploaded, you will go through the list of fields in your file (think column headings). These are the incoming fields. You will then need to match or map them to the destination fields in the EPA template. You can choose to map as many or as little as your want based on your dataset.
Please note that the EPA has many optional fields and four required fields: a location or street address, material of the system-side, material for the customer-side, and a classification of the entire line. If these fields are not identified in the incoming data file, the tool will alert you at this stage.
The tool will then intelligently map the incoming fields to destination fields by their names and even remember your previous mappings. You should check the suggested mappings and then confirm which of your fields (e.g. “Address”) go to which destination fields (e.g. “Street Address”) in the template. Some fields may not match to anything.
Any fields you don’t map will be dropped in the downloaded version of the file. We are working on potentially relaxing this constraint in future versions.
Field Mapping – Records or Values
After you have completed the column mapping, you can map the individual records, cells, or values for each field to the answers described in the EPA template. For instance, you may have coded things a little differently than the template expects and, at this stage, you simply tell the tool how you would like the data to look.
Given that you can also see all the unique values within a field during the mapping process, this would be a great way to address any spelling issues or typos that arise from human error. For example, “COP” can be mapped to “Copper” or “Pb” to “Lead” or similar. Then it will adjust the data based on your guidance. Similar to the intelligent mapping of the fields, the tool remembers how you mapped the incoming values to that of the template and will have the mappings stored and ready for any subsequent run of the tool, for example another file with similar fields.
When you’re done with the mapping, you click [Continue] to proceed.
Error Checking Stage
When you view the Detailed Inventory, the tool will check for warnings or errors/invalid answers. These are shown with yellow or red cells respectively. You can use the filtering above the table to hone in on particular errors and settle them.
For instance, a row in the file may be missing required information. You can enter missing information by hand directly in the tool or go back to your file, make adjustments in your source file, and then start again with the validation tool. Your choice.
Beyond the simple matchings and missing values, our validation tool also includes built in logic of the EPA template and checks against it. For example, according to the EPA’s guidance, if any part of the full service line is lead (customer-side or service-side), the entire service line must be classified as lead, otherwise the tool will flag it as an error with the recommended change. Another example is if you state that your service line is verified, it will prompt you for a verification date.
After you finish working through the errors or warnings, congratulations you’re done!
You can download the adjusted file, include it to your existing records, or prepare to submit it to the state agency in time for the October 2024 deadline. If you would like to check another file, the process is as simple as clicking [Restart] and repeating the process with the added benefit of the tool remembering your previous configurations.
Thank You to Our Funders
Many thanks to Google.org and The Rockefeller Foundation that have provided support so that we can make this tool available to water systems free of charge. More information on Google.org support for BlueConduit’s work. More information on The Rockefeller Foundation support for BlueConduit’s work.
Next Steps for LCRR Compliance
The EPA has provided guidance on preparing a materials inventory and a template that can be used. BlueConduit is proud to have been included in the EPA’s materials inventory guidance see more on predictive modeling in the EPA guidance). Wondering if your state will accept predictive modeling as a service line investigation method? Check your state’s guidance on our map.
