{"text_format":"1","workbook":"xlsx","n_sheets":3,"sheets":["COLUMN DEFINITIONS","INVENTORY","STATE"],"not_rendered":{"charts":0,"images":0,"macros":1,"external_links":0,"defined_names":29,"comments":0,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"COLUMN DEFINITIONS","index":0,"n_rows":29,"n_cols":7,"rows_kept":29,"cut":false,"hidden":false,"merged":[[2,0,2,6],[3,0,3,6],[4,0,4,6],[1,0,1,6],[0,0,0,6]]}
["Why do Public Water Systems (PWSs) need to provide this information? "]
["Under Section §141.84 of the Lead and Copper Rule Revision (LCRR), all community Public Water Systems (PWS) “must develop an inventory to identify the materials of service lines connected to the public water distribution system. The inventory must include all service lines connected to the public water distribution system regardless of ownership status.” \n\n"]
["What specifically does the LCRR materials inventory require? "]
["Subparagraph §141.84 (a) (4) of the LCRR states that “each service line, or portion of the service line where ownership is split, must be categorized” as belonging to one of the following groups: lead; non-lead; lead status unknown; or galvanized requiring replacement. "]
[]
["COLUMN \nLETTER","COLUMN NAME","OPTIONAL/\nREQUIRED","COLUMN DESCRIPTION","VALID VALUES","VALID VALUES DESCRIPTION","CSV HEADER NAME"]
["A","SITE ID","OPTIONAL","PWSs that do not wish to report site addresses to the department in column B may want to use this \ncolumn to “link” their materials inventory table to a separate service address table for their own use. \n","Any value",null,"site_id"]
["B","LOCATIONAL IDENTIFIER","REQUIRED","Sub-subparagraph §141.84 (a)(8)(i) of the LCRR requires a location identifier associated with each service line. The locational identifier can be the site address, or it can be a block, intersection, or landmark.   \n\nThe locational identifier MUST be unique.  The locational identifier, once assigned, should NEVER change.  MassDEP will use the locational identifier to uniquely identify the location.  It will be used in our database as the unique primary key.  ","Any value","Must be unique","locational_identifier"]
["C","CONNECTOR (GOOSENECK/PIGTAIL) CURRENTLY PRESENT?","REQUIRED","Is there a gooseneck, lead connector or pigtail present?\n","YES\nNO\nUNK","YES = Yes, Known with Certainty\nNO = No, Known with Certainty\nUNK = Unknown, Not Known with Certainty","connector_present"]
["D","CONNECTOR (GOOSENECK/PIGTAIL) MATERIAL ","CONDITIONALLY REQUIRED","Identify the connector material if known.  If column C is \"YES\" then this column is required.\n","L\nC\nB\nS\nPVC\nHDPE\nO\nUNK-NOLG","L = Lead\nC = Copper\nB = Brass\nS = Steel\nPVC = Plastic (PVC)\nHDPE = Plastic (HDPE)\nO = Other, does not contain lead or lead solder\nUNK-NOLG = Unkown, definitely does not contain lead or galvanized","connector_material"]
["E","IS THE SERVICE LINE COMPRISED OF MULTIPLE MATERIALS?","REQUIRED","Is the service line made up of more than one material? If \"YES\" then \"CURRENT BUILDING SERICE LINE MATERIAL (1) - UPSTREAM\" (column F) and \"CURRENT BUILDING SERVICE LINE MATERIAL (2) - DOWNSTREAM\" (column G) are required.  If \"NO\" then only  \"CURRENT BUILDING SERICE LINE MATERIAL (1) - UPSTREAM\" (column F) is required.","YES\nNO","YES = Yes, the service line is made up of more than one material\nNO = No, the service line is made up of ONLY ONE material","multiple_materials"]
["F","CURRENT BUILDING SERVICE LINE MATERIAL (1) - UPSTREAM","REQUIRED","The current upstream service line pipe material.\n","L\nC\nG\nPVC\nHDPE\nDI\nCI-L\nCI-U\nB\nOTH\nUNK-LG\nUNK-NOLG","L = Confirmed Lead\nC = Confirmed Copper\nG = Confirmed Galvanized\nPVC = Plastic (PVC)\nHDPE = Plastic (HDPE)\nDI = Ductile Iron \nCI-L = Lined Cast Iron\nCI-U = Unlined Cast Iron\nB = Brass\nOTH = Other, definitely does not contain lead or galvanized\nUNK-LG = Unknown, may contain lead and/or galvanized\nUNK-NOLG = Unknown, definitely does not contain lead or galvanized","current_sl_material_one"]
["G","CURRENT BUILDING SERVICE LINE MATERIAL (2) - DOWNSTREAM","CONDITIONALLY REQUIRED","The current downstream service line pipe material.  If column E is \"YES\" then required.\n","L\nC\nG\nPVC\nHDPE\nDI\nCI-L\nCI-U\nB\nOTH\nUNK-LG\nUNK-NOLG","L = Confirmed Lead\nC = Confirmed Copper\nG = Confirmed Galvanized\nPVC = Plastic (PVC)\nHDPE = Plastic (HDPE)\nDI = Ductile Iron \nCI-L = Lined Cast Iron\nCI-U = Unlined Cast Iron\nB = Brass\nOTH = Other, definitely does not contain lead or galvanized\nUNK-LG = Unknown, may contain lead and/or galvanized\nUNK-NOLG = Unknown, definitely does not contain lead or galvanized","current_sl_material_two"]
["H","WAS THE BUILDING SERVICE LINE MATERIAL (1), COLUMN F, EVER PREVIOUSLY LEAD?","REQUIRED","Was the upstream service line ever previously lead? \n","YES\nNO\nUNK","YES = Yes, Known with Certainty\nNO = No, Known with Certainty\nUNK = Unknown, Not Known with Certainty","sl_prev_lead"]
["I","BUILDING SERVICE LINE INSTALL DATE (YYYY)","REQUIRED","The four-digit year that the utility-side service was installed (e.g. 1974). If the exact year is not known, enter the decade (e.g 1970s, 2000s). If unknown, was the service line installed Pre-Lead-Ban (PLB) or After-Lead-Ban (ALB).\n","PLB\nALB\n\nValid Regular Expression Patterns:\n^((1{1}(?:[89])[0-9]{1})|(2{1}(?:[0])[0-2]))[0]{1}'?s{1}$\n\n^[1-2]{1}[0-9]{3}$","PLB = Pre lead ban\nALB = After lead ban\n\n\nE.g., 1910's, 1910s, 1920's, 1920s\n\nE.g., 1974, 1993, 2003, 2020","sl_install_date"]
["J","VERIFICATION METHOD","REQUIRED","Methodology used for verification of service line material.  If more than one verification method was used pick the most accurate/highest verification method.  Other methods can be reported in column K.\n","F\nV\nA\nC\nS\nO","F = Field Inspection by PWS: This is considered the most accurate verification method that uses a physical and visual inspection by a trained staff person. Typically, at the time of meter replacement, service line replacement, or special inspections such as pot holing and vacuum excavation.\nV = Records Review: This verification method includes review of PWS current or past records. Including Tap/tie cards, distribution system main replacement or leak detection or any projects where service line material may have been recorded by PWS.  Other potential sources of information in a community might include plumbing and building permits, or inspectional services records, or year of construction.\nA = Statistical Analysis: This verification method a qualified consultant uses statistical modeling and machine learning to predict the presence of lead service lines saving time and resources looking for lead service lines.\nC = Customer Self-Identification: This verification method uses information collected from building occupants, and typically in includes photos of the service line.  The MassDEP Crowdsourcing application or a similar software solution can be used to collect the and verify the information.\nS = Sequential Monitoring: This verification method can be used if the physical inspections, records review or statistical analysis are not feasible. This method employs a process of taking five 1-liter samples and analyzing the samples for lead to determine if the service line is likely lead. \nO = Other MassDEP-Approved Method: Alternative methods. Examples – electrical resistance acoustic wave, eddy current, other technologies that may be developed. DEP can and will review these approaches for validity and accuracy.","verification_method"]
["K","OTHER MASSDEP APPROVED VERIFICATION METHOD","CONDITIONALLY REQUIRED","Describe the other MassDEP-Approved verification method.  Required if O (Other) is selected for column J.\n",null,null,"other_verification_method"]
["L","ENTIRE SERVICE LINE CLASSIFICATION","REQUIRED","This is a calculated value.  It will be automatically calculated based on changes made to the following columns:\n  - CURRENT BUILDING SERVICE LINE MATERIAL (1) - UPSTREAM (COLUMN F)\n  - CURRENT BUILDING SERVICE LINE MATERIAL (2) - DOWNSTREAM (COLUMN G)\n  - WAS THE BUILDING SERVICE LINE MATERIAL (1), COLUMN F, EVER PREVIOUSLY LEAD? (COLUMN H)\n\nTo calculate the value select one or more of the cells in the column for ENTIRE SERVICE LINE CLASSIFICATION and then right-click.  From the context menu select DEP: Inventory Col. Options > Column L > Calculate Entire Service Line.","LEAD\nNON-LEAD\nGALVANIZED REQUIRING REPLACEMENT\nLEAD STATUS UNKNOWN",null,"entire_service_line_classification"]
["M","SERVICE LINE CURRENT USE","REQUIRED","Identify if servcice is used for potable or non-potable use.","P \nNP","P = Potable\nNP = Non-Potable","sl_current_use"]
["N","COMMENTS","OPTIONAL","Additional information about this service location.\n","Any value",null,"comments"]
["O","BUILDING SERIVCE LINE SIZE","OPTIONAL","Service line pipe diameter in inches (e.g. 3/4\", 0.75), from the water main to curb stop. \nThis column may be used by PWSs that wish to track information required in a single document.\n","3/8\"\n1/2\"\n5/8\"\n3/4\"\n1\"\n1 1/4\"\n1 1/2\"\n1 3/4\"\n2\"\n3\"\n4\"\n6\"\n8\"\n10\"\n12\"\n14\"\n16\"\nOTH",null,"sl_size"]
["P","BUILDING TYPE","REQUIRED","The type of structure that is served water by the service connection. \n\nAt a minimum, the PWS must identify the connection either SF, MF or NONRES.\n","SF\nMF\nSCH/CC\nRES/CC\nNONRES\nMIX\nO","SF = Single-Family Residence\nMF = Multi-Person Dwelling\nSCH/CC = School/Child Care Center\nRES/CC = Residential & In-Home Child Care\nNONRES = Nonresidential, Non-School, Non-Childcare\nMIX = Mixed Residential & Nonresidential\nO = Other","building_type"]
["Q","LCR SAMPLING LOCATION?","REQUIRED","Is this location a Lead and Copper Rule (LCR) sample site?\n","YES\nNO",null,"lcr_location"]
["R","POINT-OF-ENTRY OR POINT-OF-USE TREATMENT PRESENT?","CONDITIONALLY REQUIRED","Is the cold-water kitchen and/or bathroom tap treated by point-of-entry or point-of-use softening, filter or other treatment? Required if column C is \"YES\".\n","YES\nNO\nUNK","YES = Yes, Known with Certainty\nNO = No, Known with Certainty\nUNK = Unknown, Not Known with Certainty","poe_pou_treatment"]
["S","BUILDING PLUMBING MATERIAL 1","CONDITIONALLY REQUIRED","The type of plumbing material(s) inside the structure for serving water. Required if column C is \"YES\".\n","L\nC\nCLS\nB\nPEX\nO\nUNK","L = Lead\nC = Copper\nCLS = Copper w/ Lead Solder\nB = Brass\nPEX = Cross-Linked Polyethylene\nO = Other, does not contain Lead or Lead Solder\nUNK = Unknown","building_plumbing_material_1"]
["T","BUILDING PLUMBING MATERIAL 2","OPTIONAL","If there is more than one known plumbing material type.\n","L\nC\nCLS\nB\nPEX\nO\nUNK","L = Lead\nC = Copper\nCLS = Copper w/ Lead Solder\nB = Brass\nPEX = Cross-Linked Polyethylene\nO = Other, does not contain Lead or Lead Solder\nUNK = Unknown","building_plumbing_material_2"]
["U","PLUMBING MATERIAL INSTALL DATE (YYYY)","CONDITIONALLY REQUIRED","The four-digit year, or decade (e.g., 1960's), that the interior premise plumbing was installed.  \n\nIf the decade is in 1980s, enter one of: 1983-1984, <1983, or >1984.  Required if column C is \"YES\".\n","< 1983\n1983-1984\n> 1984\n\nValid Regular Expression Patterns:\n^((1{1}(?:[89])[0-9]{1})|(2{1}(?:[0])[0-2]))[0]{1}'?s{1}$\n\n^[1-2]{1}[0-9]{3}$","\n\n\n\n\nE.g., 1910's, 1910s, 1920's, 1920s\n\n\nE.g., 1974, 1993, 2003, 2020","building_plumbing_material_install_date"]
["V","BUILDING PLUMBING RENOVATIONS OR REPAIR DATES","OPTIONAL","List dates on which building plumbing renovations or repairs were performed, separated by semicolons, e.g. 12/01/2020; 1/24/1999;",null,"Dates formatted as MM/DD/YYYY, e.g.05/01/2020.  Note, use two-digit months and days using a leading zero where applicable.","plumbing_renovations_repair_dates"]
["W","SAMPLING SITE TIER","CONDITIONALLY REQUIRED","The Tier classification for the LCR Sampling Location.\n\nThis is a calculated value based on the following columns:\n  - CONNECTOR (GOOSENECK/PIGTAIL) CURRENTLY PRESENT? (COLUMN C)\n  - CONNECTOR (GOOSENECK/PIGTAIL) MATERIAL (COLUMN D)\n  - WAS THE BUILDING SERVICE LINE MATERIAL (1), COLUMN F, EVER PREVIOUSLY LEAD? (COLUMN H)\n  - ENTIRE SERVICE LINE CLASSIFICATION (COLUMN L)\n\nTo calculate the value select one or more of the cells in the column for SAMPLING SITE TIER and then right-click.  From the context menu select DEP: Inventory Col. Options > Column W > Calculate Sampling Site Tier.","TIER 1\nTIER 2\nTIER 3\nTIER 4\nTIER 5\nNA",null,"tier"]

{"sheet":"INVENTORY","index":1,"n_rows":1,"n_cols":23,"rows_kept":1,"cut":false,"hidden":false,"merged":[]}
["SITE ID","LOCATIONAL IDENTIFIER","CONNECTOR (GOOSENECK / PIGTAIL)  CURRENTLY PRESENT?","CONNECTOR (GOOSENECK /PIGTAIL) MATERIAL","IS THE SERVICE LINE COMPRISED OF MULTIPLE MATERIALS?","CURRENT BUILDING SERVICE LINE MATERIAL (1) - UPSTREAM","CURRENT BUILDING SERVICE LINE MATERIAL (2) - DOWNSTREAM","WAS THE BUILDING SERVICE LINE MATERIAL (1), COLUMN F, EVER PREVIOUSLY LEAD?","BUILDING SERVICE LINE INSTALL DATE\n(YYYY)","VERIFICATION\nMETHOD","OTHER MASSDEP APPROVED VERIFICATION METHOD","ENTIRE SERVICE LINE CLASSIFICATION","SERVICE LINE CURRENT USE","COMMENTS","BUILDING SERVICE LINE SIZE","BUILDING TYPE","LCR SAMPLING LOCATION?","POINT-OF-ENTRY OR POINT-OF-USE TREATMENT PRESENT?","BUILDING PLUMBING MATERIAL 1","BUILDING PLUMBING MATERIAL 2","BUILDING PLUMBING MATERIAL INSTALL DATE\n(YYYY)","BUILDING PLUMBING RENOVATIONS OR REPAIR DATES","SAMPLING SITE TIER"]

{"sheet":"STATE","index":2,"n_rows":1,"n_cols":3,"rows_kept":1,"cut":false,"hidden":true,"merged":[]}
["",false,true]