Search Knowledgebase

Follow

Excel Spreadsheet Tools for mapping data to ICIS XML format

EPA Regional and state data entry professionals can batch NPDES program data into ICIS-NPDES using the following XML spreadsheet generators and the Exchange Network Service Center website. EPA provided webinar training on this batch data entry method on June 10, 2021. Refer to the presentation and recording provided:

Please note that these spreadsheets may be helpful if you are an EPA Regional data entry professional or a state without a state NPDES data system (i.e., a direct user of ICIS-NPDES). Use of these spreadsheets can save considerable time as you can upload data to ICIS-NPDES without using the ICIS-NPDES data entry web screens. For states with their own NPDES data system, EPA recommends that they first try to automatically flow data from the state system to ICIS-NPDES before they consider using this manual method.

Refer to the following download:

  1. Facility, Permit, Contact, and Permitted Feature Data (BasicPermitSubmission, GeneralPermitSubmission, PermittedFeatureSubmission) - Use this XML generator to insert, update, or delete permit, facility, contact, and permitted feature data.
  2. Unpermitted Facilities (UnpermittedFacilitySubmission) - Use this XML generator to insert, update, or delete facility information for unpermitted facilities.
  3. Permit Reissuance (PermitReissuanceSubmission) - Use this XML generator to re-issue one or more NPDES permits.
  4. Permit Termination (PermitTerminationSubmission) - Use this XML generator to terminate one or more NPDES permits.
  5. NPDES ID Deletion (BasicPermitSubmission, GeneralPermitSubmission) - Use this XML generator to delete one or more NPDES ID records from ICIS-NPDES. Please note this is different from terminating a permit in ICIS-NPDES. This will completely remove the NPDES ID from ICIS-NPDES (i.e., no record left in ICIS-NPDES). This spreadsheet includes the ICIS business rules for this deletion process.
  6. NPDES Data Group (BasicPermitSubmission, GeneralPermitSubmission) - Use this XML generator to insert, update, or delete “NPDES Data Group” data in ICIS-NPDES. These data identify the notices and reports that an NPDES-regulated entity is required to submit.
  7. NPDES Compliance Tracking and Status (BasicPermitSubmission, GeneralPermitSubmission, 
    LimitSetSubmission) - Use this XML generator to insert, update, or delete data on permit compliance tracking, DMR non-receipt tracking, and limit set status tracking.
  8. POTW & Pretreatment (BasicPermitSubmission, GeneralPermitSubmission, POTWPermitSubmission,
    PretreatmentPermitSubmission) - Use this XML generator to insert, update, or delete POTW and pretreatment data
  9. Biosolids Permit Component (BiosolidsPermitSubmission) - Use this XML generator to insert, update, or delete biosolids facility and management data. These data are gathered on NPDES Permit Application Form 2S.
  10. Narrative Conditions and Permit Schedules (NarrativeConditionScheduleSubmission,
    HistoricalPermitScheduleEventsSubmission) - Use this XML generator to insert, update, or delete data on narrative conditions or schedule events. This spreadsheet also includes historical permit schedules.
  11. DMR Form-Level Electronic Submission Type Code (DischargeMonitoringReportSubmission) - Use this XML generator to insert, update, or delete the “Electronic Submission Type” code for one or more DMRs. EPA uses this code to track how DMRs were collected by EPA Regions and states. Please note that this code is automatically created by NetDMR and then shared with ICIS-NPDES.
  12. DMR Form-Level NODI (DischargeMonitoringReportSubmission) - Use this XML generator to insert, update, or delete the “No Data Indicator” or NODI code for one or more DMRs. This NODI code covers the entire DMR form, which is identified by the unique combination of NPDES ID, Permitted Feature number, Limit Set Designator, and Monitoring Period End Date.
  13. State Compliance Monitoring (ComplianceMonitoringSubmission) - Use this XML generator to insert, update, or delete the state compliance monitoring data.
  14. Violations (DMRViolationSubmission, ScheduleEventViolationSubmission, SingleEventViolationSubmission) - Use this XML generator to insert, update, or delete violation data. This spreadsheet covers manually created DMR and schedule event violations as well as single event violations. This spreadsheet also covers the RNC data (detection and resolution codes and dates) for these violation data.

The original XML Generator tool was developed by Steve Rubin in 2015 for mapping Excel data into the ICIS XML Schema format (Refer to the following 2 XLSX attachments):

  1. ICIS Meta data.xlsx : provides information (e.g., size and the ICIS ref table for that field) on the data needed for the XML mapping.
  2. ICIS Mapping XML Template.xlsx :  Instructions are provided in the template.

Please be aware that the default transaction type generated in the Template Excel is a Replace type, so if the intention is to only change a few tags/data elements in ICIS, please change the R to a C, where applicable.  A Replace transaction type wipes out ALL data for a record that is not included in the XML (more information in the User's Guide: https://www.exchangenetwork.net/schema/ICIS/5/ICIS-NPDES_Batch_User_Guide_v5.9.pdf).  A Change transaction, however, only updates the data provided in the XML, as long as it includes the mandatory tags for a record.

 

In addition, here is a general guide on converting Excel/CSV rows to XML format using Excel's CONCATENATE function: https://icis.zendesk.com/hc/en-us/articles/360049802652-Tip-in-generating-XML-text-from-Excel-or-CSV-files- .

 

If you have any questions please contact Carey Johnston (johnston.carey at epa.gov).

 

Article revised on 10/22/2021.

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request