{"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":33,"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":32,"n_cols":7,"rows_kept":32,"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","LCR SAMPLING LOCATION?","REQUIRED","Is this location a Lead and Copper Rule (LCR) sample site?\n","YES\nNO",null,"lcr_location"]
["D","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"]
["E","CONNECTOR (GOOSENECK/PIGTAIL) MATERIAL ","CONDITIONALLY REQUIRED","Identify the connector material if known.  If column D 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"]
["F","CURRENT PUBLIC SERVICE LINE MATERIAL","REQUIRED","The current utility-side service line pipe material, from the water main to curb stop.\n","L\nC\nG\nPVC\nHDPE\nDI\nCI-L\nCI-U\nB\nUNK-LG\nUNK-NOLG","L = Lead\nC = Copper\nG = Galvanized\nPVC = Plastic (PVC)\nHDPE = Plastic (HDPE)\nDI = Ductile Iron \nCI-L = Lined Cast Iron\nCI-U = Unlined Cast Iron\nB = Brass\nUNK-LG = Unknown, may contain lead and/or galvanized\nUNK-NOLG = Unknown, definitely does not contain lead or galvanized","current_public_sl_material"]
["G","WAS PUBLIC SERVICE LINE MATERIAL EVER PREVIOUSLY LEAD?","REQUIRED","Was the 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","public_sl_prev_lead"]
["H","PUBLIC SERVICE 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,"public_sl_size"]
["I","PUBLIC SERVICE LINE INSTALL DATE","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","public_sl_install_date"]
["J","CURRENT PRIVATE SERVICE LINE MATERIAL","REQUIRED","Service line pipe material, from the curb-stop to water meter. \n","L\nC\nG\nPVC\nHDPE\nDI\nCI-L\nCI-U\nB\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\nUNK-LG = Unknown, may contain lead and/or galvanized\nUNK-NOLG = Unknown, definitely does not contain lead or galvanized","current_private_sl_material"]
["K","PRIVATE SERVICE LINE SIZE","OPTIONAL","Service line pipe diameter, in inches (e.g. 3/4\", 0.75), from the curb-stop to water meter. \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,"private_sl_size"]
["L","PRIVATE SRVICE LINE INSTALL DATE","REQUIRED","The four-digit year that the customer side service line was installed (e.g. 1974). \nIf the exact year is not known, enter the decade (e.g. 1970s, 2000s). If unknown, was the service line \ninstalled 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","private_sl_install_date"]
["M","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 PUBLIC SERVICE LINE MATERIAL (COLUMN F)\n  - WAS PUBLIC SERVICE LINE MATERIAL EVER PREVIOUSLY LEAD? (COLUMN G)\n  - CURRENT PRIVATE SERVICE LINE MATERIAL (COLUMN J)\n\nTo manually 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 M > Calculate Entire Service Line.","LEAD\nNON-LEAD\nGALVANIZED REQUIRING REPLACEMENT\nLEAD STATUS UNKNOWN",null,"entire_service_line"]
["N","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 and/or additional methods can be reported in column O.\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"]
["O","OTHER MASSDEP APPROVED VERIFICATION METHOD","CONDITIONALLY REQUIRED","Describe the other MassDEP-Approved verification method or, if more than one verification method listed in Column M options was used, then list the method(s) in this column.\n"]
["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\nMF = Multi-Family\nSCH/CC = School/Childcare\nRES/CC = Residential & In-Home Child Care\nNONRES = Nonresidential, Non-School, Non-Childcare\nMIX = Mixed Residential & Nonresidential \nO =  Other","building_type"]
["Q","POE/POU 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"]
["R","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"]
["S","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"]
["T","PLUMBING MATERIAL INSTALL DATE","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"]
["U","SAMPLING PLAN LOCATION","CONDITIONALLY REQUIRED","If the location is part of the sampling plan choose between Routine or Alternative site. Required if column C is \"YES\".\n","R\nA","R = Routine Site\nA = Alternative Site","sampling_plan_location"]
["V","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  - ENTIRE SERVICE LINE CLASSIFICATION (COLUMN M)\n  - BUILDING TYPE (COLUMN P)\n  - CONNECTOR (GOOSENECK/PIGTAIL) CURRENTLY PRESENT? (COLUMN D)\n  - CONNECTOR (GOOSENECK/PIGTAIL) MATERIAL (COLUMN E)\n  - WAS PUBLIC SERVICE LINE MATERIAL EVER PREVIOUSLY LEAD?  (COLUMN G)\n  - BUILDING PLUMBING MATERIAL INSTALL DATE (COLUMN T)\n\nTo manually 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 V > Calculate Sampling Site Tier.","TIER 1\nTIER 2\nTIER 3\nTIER 4\nTIER 5\nNA",null,"tier"]
["W","REPLACED LSL PUBLIC OR PRIVATE SIDE?","OPTIONAL","Status of lead service line replacement performed during or after 2024.\n","PUBLIC\nPRIVATE\nBOTH","PUBLIC =  Yes, public side of LSL\nPRIVATE = Yes, private side of LSL\nBOTH = Yes, both public and private side of LSL","replaced_sl"]
["X","NEW REPLACED MATERIAL","OPTIONAL","The material of the new service line.\n","C\nP\nOTH","C = Copper\nP = Plastic\nOTH = Other Material","replaced_material"]
["Y","LSL REPLACEMENT DATE","OPTIONAL","Date when lead service line was replaced.\n",null,null,"replacement_date"]
["Z","COMMENTS","OPTIONAL","Additional information about this service location.\n","Any value",null,"comments"]

{"sheet":"INVENTORY","index":1,"n_rows":2,"n_cols":26,"rows_kept":2,"cut":false,"hidden":false,"merged":[]}
["SITE ID","LOCATIONAL IDENTIFIER","LCR SAMPLING LOCATION?","CONNECTOR (GOOSENECK / PIGTAIL)  CURRENTLY PRESENT?","CONNECTOR (GOOSENECK /PIGTAIL) MATERIAL","CURRENT PUBLIC SERVICE LINE MATERIAL","WAS PUBLIC SERVICE LINE MATERIAL EVER PREVIOUSLY LEAD?","PUBLIC SERVICE LINE SIZE\n(inches)","PUBLIC SERVICE LINE INSTALL DATE\n(YYYY)","CURRENT PRIVATE SERVICE LINE MATERIAL","PRIVATE SERVICE LINE SIZE\n(inches)","PRIVATE SERVICE LINE INSTALL DATE\n(YYYY)","ENTIRE SERVICE LINE CLASSIFICATION","VERIFICATION\nMETHOD","OTHER MASSDEP APPROVED VERIFICATION METHOD","BUILDING TYPE","POINT-OF-ENTRY OR POINT-OF-USE TREATMENT PRESENT?","BUILDING PLUMBING MATERIAL 1","BUILDING PLUMBING MATERIAL 2","BUILDING PLUMBING MATERIAL INSTALL DATE\n(YYYY)"," SAMPLING PLAN LOCATION","SAMPLING SITE TIER","REPLACED LSL PUBLIC OR PRIVATE SIDE?  (DURING OR AFTER 2024)","NEW REPLACED SERVICE LINE MATERIAL ","LSL REPLACEMENT DATE\n(MM/DD/YYYY)","COMMENTS"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,""]

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