import os import pandas as pd SHEET_NAME = "BLOCK LIST_SORTED" # MCP panel extra descriptions (26 rows) PANEL_EXTRA_DESCRIPTIONS = [ "120VAC CB01 AUX", "RECP CB02 AUX", "PS CB03 AUX", "RACK1 CB04 AUX", "RACK2 CB05 AUX", "ENET SW CB06 AUX", "PNL 24 CB07 AUX", "MCP RESET PB", "MCP START PB", "PS1 MONITORING", "ENET SWITCH MONITORING", "LAMP TEST PB", "PANEL E-STOP PB", "PANEL STOP PB", "MAINTENANCE MODE PB", "MASTER_ESTOP/EVAC SIGNAL", "PANEL STOP PB LIGHT", "PS1 OK LIGHT", "SYSTEM START LIGHT", "RESET BUTTON LIGHT", "MAINT MODE ON LIGHT", "SYSTEM ALARM LIGHT", "VFD FAULT LIGHT", "DC FAULT LIGHT", "PANEL STACKLIGHT", "BUILDING E-STOP LIGHT", "REMOTE E-STOP LIGHT" ] # PANEL extra names (26 rows) PANEL_EXTRA_NAMES = [ # 7 rows group 1 "R02S00:1:I.0", "R02S00:1:I.1", "R02S00:1:I.2", "R02S00:1:I.3", "R02S00:1:I.4", "R02S00:1:I.5", "R02S00:1:I.6", # 5 rows group 2 "R02S00:2:I.0", "R02S00:2:I.1", "R02S00:2:I.2", "R02S00:2:I.3", "R02S00:2:I.4", # 3 rows group 3 "R02S00:3:I.Pt00Data", "R02S00:3:I.Pt01Data", "R02S00:3:I.Pt02Data", "R02S00:3:I.Pt03Data", # 8 rows group 4 "R02S00:4:O.0", "R02S00:4:O.1", "R02S00:4:O.2", "R02S00:4:O.3", "R02S00:4:O.4", "R02S00:4:O.5", "R02S00:4:O.6", "R02S00:4:O.7", # 3 rows group 5 "R02S00:5:O.Pt00Data", "R02S00:5:O.Pt01Data", "R02S00:5:O.Pt02Data", ] # APF/VFD suffixes for last 5 rows SI_SO_SUFFIXES = [ "SI.In00Data", "SI.In01Data", "SI.In02Data", "SI.In03Data", "SO.Out00Output" ] # ====================================================== # KEYWORD RULES # ====================================================== INPUT_KEYWORDS = [ "E.STOP", "EMERGENCY", "PULL CORD", "EPC", "SSP", "PHOTOEYE", "PE", "TPE", "SDP", "SSDP", "PB", "PUSHBUTTON", "BUTTON", "ACK", "DEBRIS", "PROX", "PROXIMITY", "RECEIVE", "SEND" ] OUTPUT_KEYWORDS = [ "BEACON", "BCN", "RED BEACON", "HORN", "BUZZER", "STATION LIGHT", "LIGHT", "LT", "DCM", "DCM_CABLE", "DIVERT CONTROL", "OILER", "PUMP", "PUMP SIGNAL", "OILER PUMP" ] def is_output(desc): U = desc.upper() return any(k in U for k in OUTPUT_KEYWORDS) def is_input(desc): U = desc.upper() return any(k in U for k in INPUT_KEYWORDS) # keep original device name, only replace "-" with "_" def normalize_name(name): return name.replace("-", "_") def process_file(filepath): file = os.path.basename(filepath) subsystem = file.split("_")[0] print(f"\nProcessing: {file}") try: df = pd.read_excel(filepath, sheet_name=SHEET_NAME, dtype=str) except Exception as e: print(" āŒ Error reading:", e) return df = df.fillna("") df["OriginalIndex"] = df.index TAG = df.iloc[:, 0] DESCA = df.iloc[:, 4] DESCB = df.iloc[:, 5] df["Description"] = (DESCA.str.strip() + " " + DESCB.str.strip()).str.strip() df["TAGNAME"] = TAG output = pd.DataFrame(columns=["Name", "Description", "Subsystem"]) groups = df.groupby("TAGNAME", sort=False) # ====================================================== # ADD 26 MCMP PANEL INPUT/OUTPUT ROWS AT TOP # ====================================================== for name_value, desc in zip(PANEL_EXTRA_NAMES, PANEL_EXTRA_DESCRIPTIONS): full_desc = f"{subsystem} {desc}" output.loc[len(output)] = [name_value, full_desc, subsystem] # ====================================================== # MAIN DEVICE PROCESSING # ====================================================== for device_name, group in groups: group = group.sort_values("OriginalIndex").reset_index(drop=True) rows = len(group) # normalize device name device_norm = normalize_name(device_name) # ====================================================== # RULE 1: APF/VFD (11 rows) # ====================================================== if rows == 11: # First 4 inputs for i in range(4): name_value = f"{device_norm}:I.In_{i}" output.loc[len(output)] = [name_value, group.loc[i, "Description"], subsystem] # IO_0 / IO_1 for i in range(4, 6): desc_value = group.loc[i, "Description"] desc_upper = desc_value.upper() if "[IN]" in desc_upper: io = "I" elif "[OUT]" in desc_upper: io = "O" elif desc_upper.strip() in ("", "SPARE"): io = "I" elif is_output(desc_value): io = "O" elif is_input(desc_value): io = "I" else: io = "I" io_number = i - 4 name_value = f"{device_norm}:{io}.IO_{io_number}" output.loc[len(output)] = [name_value, desc_value, subsystem] # SI / SO suffix rows for idx, suffix in enumerate(SI_SO_SUFFIXES): name_value = f"{device_norm}:{suffix}" output.loc[len(output)] = [ name_value, group.loc[6 + idx, "Description"], subsystem ] # ====================================================== # RULE 2: SIO (16 rows) # ====================================================== elif rows == 16 and "SIO" in device_name.upper(): for i in range(8): name_value = f"{device_norm}:I.Pt{str(i).zfill(2)}Data" output.loc[len(output)] = [name_value, group.loc[i, "Description"], subsystem] for i in range(8): name_value = f"{device_norm}:O.Pt{str(i).zfill(2)}Data" output.loc[len(output)] = [ name_value, group.loc[8 + i, "Description"], subsystem ] # ====================================================== # RULE 3: FIO (16 rows) # ====================================================== elif rows == 16 and "FIO" in device_name.upper(): for i in range(16): desc = group.loc[i, "Description"] if is_output(desc): io = "O" else: io = "I" name_value = f"{device_norm}:{io}.Pt{str(i).zfill(2)}.Data" output.loc[len(output)] = [name_value, desc, subsystem] # ====================================================== # RULE 4: Unknown ā†’ keyword-based fallback # ====================================================== else: for i in range(rows): desc = group.loc[i, "Description"] if is_output(desc): io = "O" elif is_input(desc): io = "I" else: io = None if io is None: output.loc[len(output)] = ["", desc, subsystem] else: name_value = f"{device_norm}:{io}.{i}" output.loc[len(output)] = [name_value, desc, subsystem] # ====================================================== # SAVE OUTPUT FILE # ====================================================== current_folder = os.path.dirname(os.path.abspath(__file__)) parent_folder = os.path.dirname(current_folder) io_tool_folder = os.path.join(parent_folder, "IO_TOOL") os.makedirs(io_tool_folder, exist_ok=True) out_name = subsystem + ".csv" out_path = os.path.join(io_tool_folder, out_name) output.to_csv(out_path, index=False) print(f" āœ… Created: {out_path}") def main(): base = os.path.dirname(os.path.abspath(__file__)) print("Scanning directory:", base) for file in os.listdir(base): if file.startswith("~$"): continue if file.lower().endswith((".xls", ".xlsx", ".xlsm")): process_file(os.path.join(base, file)) print("\nāœ” DONE") if __name__ == "__main__": main()