""" check_license_status.py ----------------------- Reads call signs from ATT_Mobility_Network_Definition_v8.csv, looks up each one in the FCC ULS HD.dat file, and writes a results spreadsheet with license status for every path. Usage (run from D:\\ATT\\FCC_data or adjust paths below): python check_license_status.py Output: ATT_Mobility_Network_Definition_v9.csv (same folder as this script) """ import csv import sys from pathlib import Path from collections import defaultdict # ── Paths ────────────────────────────────────────────────────────────────────── BASE_DIR = Path(r"D:\ATT\FCC_data") XLSX_IN = BASE_DIR / "ATT_Mobility_Network_Definition_v8.csv" HD_DAT = BASE_DIR / "HD.dat" XLSX_OUT = BASE_DIR / "ATT_Mobility_Network_Definition_v9.csv" # ── FCC HD.dat field indices (pipe-delimited) ────────────────────────────────── HD_CALL_SIGN = 4 HD_STATUS = 5 HD_RADIO_SERVICE = 6 HD_GRANT_DATE = 7 HD_EXPIRED_DATE = 8 HD_CANCEL_DATE = 9 HD_EFFECTIVE_DATE = 42 HD_LAST_ACTION = 43 HD_LICENSEE_CHANGE = 49 # last field; line may be shorter, handle gracefully STATUS_LABELS = { "A": "Active", "C": "Cancelled", "T": "Terminated", "E": "Expired", "L": "Pending", "P": "Pending", "X": "Cancellation Pending", } def load_hd(hd_path: Path) -> dict: """ Parse HD.dat into a dict keyed by call sign (upper-case). If a call sign has multiple records, keep the one with status 'A' (Active); otherwise keep the last one seen (most recent action date tends to be last). """ print(f"Loading {hd_path} …", flush=True) records = {} # call_sign -> dict of fields with open(hd_path, "r", encoding="latin-1", errors="replace") as fh: for lineno, line in enumerate(fh, 1): parts = line.rstrip("\n").split("|") if len(parts) <= HD_STATUS: continue cs = parts[HD_CALL_SIGN].strip().upper() status = parts[HD_STATUS].strip().upper() if not cs: continue def get(idx): return parts[idx].strip() if idx < len(parts) else "" rec = { "call_sign": cs, "status_code": status, "status_label": STATUS_LABELS.get(status, status or "Unknown"), "radio_service": get(HD_RADIO_SERVICE), "grant_date": get(HD_GRANT_DATE), "expired_date": get(HD_EXPIRED_DATE), "cancel_date": get(HD_CANCEL_DATE), "effective_date":get(HD_EFFECTIVE_DATE), "last_action": get(HD_LAST_ACTION), } existing = records.get(cs) if existing is None: records[cs] = rec elif existing["status_code"] != "A" and status == "A": # Prefer the Active record records[cs] = rec elif existing["status_code"] == status: # Same status — keep later last_action date if rec["last_action"] >= existing["last_action"]: records[cs] = rec if lineno % 500_000 == 0: print(f" … {lineno:,} HD lines read, {len(records):,} call signs so far", flush=True) print(f"HD.dat loaded: {len(records):,} unique call signs.", flush=True) return records def main(): try: from openpyxl import load_workbook import openpyxl except ImportError: sys.exit("Please install openpyxl: pip install openpyxl") # ── Load HD.dat ──────────────────────────────────────────────────────────── hd = load_hd(HD_DAT) # ── Read source file (CSV or XLSX) ──────────────────────────────────────── print(f"Reading {XLSX_IN} …", flush=True) if XLSX_IN.suffix.lower() == ".csv": import csv as _csv with open(XLSX_IN, newline="", encoding="utf-8-sig") as fh: reader = _csv.reader(fh) rows = [tuple(r) for r in reader] else: wb_in = load_workbook(XLSX_IN, read_only=True, data_only=True) ws_in = wb_in.active rows = list(ws_in.iter_rows(values_only=True)) wb_in.close() header = list(rows[0]) data = rows[1:] # Column indices in source sheet col = {name: i for i, name in enumerate(header)} A_CS = col["A_Call_Sign"] B_CS = col["b_Call_Sign"] # ── Build output CSV ─────────────────────────────────────────────────────── import csv as _csv out_header = ( header + [ "A_Status_Code", "A_Status_Label", "A_Radio_Service", "A_Grant_Date", "A_Expired_Date", "A_Cancel_Date", "A_Last_Action", "B_Status_Code", "B_Status_Label", "B_Radio_Service", "B_Grant_Date", "B_Expired_Date", "B_Cancel_Date", "B_Last_Action", "Path_Overall_Status", ] ) missing_cs = set() status_summary = defaultdict(int) out_rows = [] for row in data: row = list(row) a_cs = str(row[A_CS]).strip().upper() if row[A_CS] else "" b_cs = str(row[B_CS]).strip().upper() if row[B_CS] else "" def lookup(cs): if not cs: return ["", "No call sign", "", "", "", "", ""] rec = hd.get(cs) if rec is None: missing_cs.add(cs) return [cs, "NOT FOUND", "", "", "", "", ""] return [ rec["status_code"], rec["status_label"], rec["radio_service"], rec["grant_date"], rec["expired_date"], rec["cancel_date"], rec["last_action"], ] a_info = lookup(a_cs) b_info = lookup(b_cs) # Overall path status: Active only if BOTH ends are Active a_label = a_info[1] b_label = b_info[1] if a_label == "Active" and b_label == "Active": overall = "Active" elif "NOT FOUND" in (a_label, b_label): overall = "Unknown (call sign not in HD.dat)" else: overall = f"Issue: A={a_label}, B={b_label}" status_summary[overall] += 1 out_rows.append(row + a_info + b_info + [overall]) with open(XLSX_OUT, "w", newline="", encoding="utf-8-sig") as fh: writer = _csv.writer(fh) writer.writerow(out_header) writer.writerows(out_rows) # ── Summary ──────────────────────────────────────────────────────────────── print(f"\nDone. Results written to:\n {XLSX_OUT}\n") print("── Path status summary ──────────────────────────────────") for label, count in sorted(status_summary.items(), key=lambda x: -x[1]): print(f" {count:>5} {label}") if missing_cs: print(f"\n── {len(missing_cs)} call signs not found in HD.dat ──") for cs in sorted(missing_cs)[:20]: print(f" {cs}") if len(missing_cs) > 20: print(f" … and {len(missing_cs)-20} more") if __name__ == "__main__": main()