#!/usr/bin/env python3 """monday CRM board export -> Apify input -> "Set duplicate behavior: Update" import file. prepare: read a monday.com board export (.xlsx from "Export board to Excel", or the same sheet saved as .csv), keep items that have a website and are missing a Phone or Email (or all with --all), dedupe, skip social/directory domains, write websites.json for shephouse/website-contact-enricher. merge: read the export + the Actor's dataset JSON, write monday_import.csv for "Import items" into the same board with duplicate behavior "Update", matched on the Name (item) column, or on --match-col. Why Name: monday's import help lists the column types supported when mapping (Name, Status, Link, Email, Long Text, Numbers, Text, Phone, Date, Dropdown, Timeline, People). Item ID is not in that list, so this script does not rely on it. With "Update", rows that match nothing are added as NEW items, so the match value must be copied exactly and must be unique: items whose match value appears more than once in the export are skipped. Safety: existing Phone / Email values are written back unchanged (never overwritten); no-reply addresses are dropped; email is left blank when the domain has no MX. Export layout: the first column whose header equals the match column ("Name") starts a table and a blank row ends it; a board export can repeat that header row per group, and title rows above it are ignored. Website column: "Website", "Domain", "Company domain" or "Link" (or --website-col). Standard library only. No token or login. """ import csv, json, re, sys, zipfile import xml.etree.ElementTree as ET SKIP = {"facebook.com", "instagram.com", "linkedin.com", "linktr.ee", "yelp.com", "google.com", "x.com", "twitter.com", "wixsite.com", "business.site"} NOREPLY = re.compile(r"^(no-?reply|do-?not-?reply|mailer-daemon)@", re.I) NS = {"m": "http://schemas.openxmlformats.org/spreadsheetml/2006/main"} def opt(flag, default): return sys.argv[sys.argv.index(flag) + 1] if flag in sys.argv else default MATCH = opt("--match-col", "Name") PHONE, EMAIL = opt("--phone-col", "Phone"), opt("--email-col", "Email") SITE_COLS = [opt("--website-col", "Website"), "Website", "Domain", "Company domain", "Link"] def norm(d): d = (d or "").strip().lower().split()[0] if (d or "").strip() else "" d = re.sub(r"^https?://", "", d).split("/")[0].split("?")[0].split(":")[0] return d[4:] if d.startswith("www.") else d def xlsx_rows(path): """First worksheet of an .xlsx as lists of strings (stdlib zipfile + XML).""" with zipfile.ZipFile(path) as z: shared = [] if "xl/sharedStrings.xml" in z.namelist(): for si in ET.fromstring(z.read("xl/sharedStrings.xml")).findall("m:si", NS): shared.append("".join(t.text or "" for t in si.iter(f"{{{NS['m']}}}t"))) sheets = sorted(n for n in z.namelist() if re.match(r"xl/worksheets/sheet\d+\.xml$", n)) root = ET.fromstring(z.read(sheets[0])) rows = [] for r in root.iter(f"{{{NS['m']}}}row"): vals = {} for c in r.findall("m:c", NS): ref = re.match(r"([A-Z]+)", c.get("r", "A")).group(1) idx = 0 for ch in ref: idx = idx * 26 + ord(ch) - 64 v, t = c.find("m:v", NS), c.get("t") if t == "s" and v is not None: val = shared[int(v.text)] elif t == "inlineStr": val = "".join(x.text or "" for x in c.iter(f"{{{NS['m']}}}t")) else: val = v.text if v is not None else "" vals[idx - 1] = val or "" rows.append([vals.get(i, "") for i in range(max(vals) + 1)] if vals else []) return rows def read_export(path): if path.lower().endswith(".xlsx"): raw = xlsx_rows(path) else: with open(path, newline="", encoding="utf-8-sig") as f: raw = list(csv.reader(f)) items, header = [], None for row in raw: cells = [c.strip() for c in row] if cells and cells[0] == MATCH: header = cells; continue if not any(cells): header = None; continue # blank line ends a group's table if header is None: continue # board title / group title rows above a header row items.append(dict(zip(header, cells))) return items def col(row, *names): for n in names: if row.get(n): return row[n].strip() return "" def counts(rows): c = {} for r in rows: k = col(r, MATCH) c[k] = c.get(k, 0) + 1 return c def prepare(export, out="websites.json", all_rows=False): rows = read_export(export) dup = counts(rows) seen, sites, skipped = set(), [], [] for r in rows: who = col(r, MATCH) d = norm(col(r, *SITE_COLS)) if not who: skipped.append(("(blank)", f"no {MATCH} value")); continue if dup[who] > 1: skipped.append((who, f"{MATCH} appears {dup[who]}x; Update would hit the wrong item")); continue if not d or "." not in d: skipped.append((who, "no website")); continue if any(d == s or d.endswith("." + s) for s in SKIP): skipped.append((who, "platform domain " + d)); continue if not all_rows and col(r, PHONE) and col(r, EMAIL): skipped.append((who, "already has phone and email")); continue if d in seen: skipped.append((who, "duplicate " + d)); continue seen.add(d); sites.append(d) with open(out, "w") as f: json.dump({"websites": sites, "maxPagesPerDomain": 6, "defaultCountry": "US", "delaySecs": 1}, f, indent=2) print(f"{len(rows)} items read, {len(sites)} websites -> {out}") for who, why in skipped: print(f" skipped item {who!r}: {why}") def merge(export, dataset, out="monday_import.csv"): by = {} with open(dataset, encoding="utf-8") as f: for it in json.load(f): if it.get("domain"): by[norm(it["domain"])] = it rows = read_export(export) dup = counts(rows) fields = [MATCH, PHONE, EMAIL, "Email Deliverability", "Contactability Score", "Contact Source"] n = kept = ambiguous = 0 with open(out, "w", newline="", encoding="utf-8") as f: w = csv.DictWriter(f, fieldnames=fields); w.writeheader() for r in rows: key = col(r, MATCH) it = by.get(norm(col(r, *SITE_COLS))) if not key or not it: continue if dup[key] > 1: ambiguous += 1; continue phones = [p.get("e164") for p in it.get("phones") or [] if p.get("e164")] status = (it.get("deliverability") or {}).get("status", "UNKNOWN") emails = [e for e in it.get("emails") or [] if not NOREPLY.match(e)] old_p, old_e = col(r, PHONE), col(r, EMAIL) kept += bool(old_p) + bool(old_e) w.writerow({ MATCH: key, PHONE: old_p or (phones[0] if phones else ""), EMAIL: old_e or (emails[0] if emails and status != "NO_MX" else ""), "Email Deliverability": status, "Contactability Score": it.get("contactabilityScore", 0), "Contact Source": "https://" + norm(it["domain"]), }); n += 1 print(f"{n} rows -> {out} (match column: {MATCH}; existing values kept: {kept}; " f"ambiguous {MATCH} skipped: {ambiguous})") if __name__ == "__main__": if len(sys.argv) >= 3 and sys.argv[1] == "prepare": prepare(sys.argv[2], all_rows="--all" in sys.argv) elif len(sys.argv) >= 4 and sys.argv[1] == "merge": merge(sys.argv[2], sys.argv[3]) else: sys.exit("usage: monday_enrich_prep.py prepare export.xlsx|.csv [--all] | " "merge export.xlsx|.csv dataset.json [--match-col NAME] " "[--website-col NAME] [--phone-col NAME] [--email-col NAME]")