#!/usr/bin/env python3 """Clean phone numbers in a CRM export before re-importing into HubSpot or Salesforce. Usage: python crm_phone_prep.py export.csv [DEFAULT_COUNTRY] (needs: pip install phonenumbers) Reads a CSV with a `phone` column (optional `country` column, ISO-2 like US/GB/DE, used per row for numbers written without +). Writes: crm_import.csv rows whose number is valid: original columns + phone_e164 (extension kept as " ext N") crm_review.csv invalid / unparseable / duplicate rows, with a reason, for a human to fix Duplicates = a later row whose number is the same E.164 as an earlier row (different spelling). Nothing is deleted: duplicates go to review so you can merge the records in the CRM. """ import csv, sys import phonenumbers as pn src = sys.argv[1] fallback = (sys.argv[2] if len(sys.argv) > 2 else "US").upper() ERR = {0: "INVALID_COUNTRY_CODE", 1: "NOT_A_NUMBER", 2: "TOO_SHORT_AFTER_IDD", 3: "TOO_SHORT_NSN", 4: "TOO_LONG"} rows = list(csv.DictReader(open(src, newline="", encoding="utf-8-sig"))) first_seen, ok, review = {}, [], [] for i, r in enumerate(rows, start=2): # line 2 = first data row in the file raw = (r.get("phone") or "").strip() region = ((r.get("country") or "").strip().upper() or fallback) try: n = pn.parse(raw, region) except pn.NumberParseException as e: review.append({**r, "phone_e164": "", "reason": f"unparseable ({ERR.get(e.error_type, e.error_type)})"}); continue if not pn.is_valid_number(n): review.append({**r, "phone_e164": "", "reason": f"invalid for region {region}"}); continue e164 = pn.format_number(n, pn.PhoneNumberFormat.E164) value = e164 + (f" ext {n.extension}" if n.extension else "") if e164 in first_seen: review.append({**r, "phone_e164": value, "reason": f"duplicate of line {first_seen[e164]}"}); continue first_seen[e164] = i ok.append({**r, "phone_e164": value}) def write(path, items, extra): cols = list(rows[0].keys()) + extra with open(path, "w", newline="", encoding="utf-8") as f: w = csv.DictWriter(f, fieldnames=cols); w.writeheader(); w.writerows(items) write("crm_import.csv", ok, ["phone_e164"]) write("crm_review.csv", review, ["phone_e164", "reason"]) print(f"{len(rows)} rows -> {len(ok)} to import, {len(review)} to review")