#!/usr/bin/env python3 """Zoho CRM Accounts export -> Apify input -> "Update existing Accounts only" CSV. prepare: read a Zoho CRM Accounts export CSV (Setup -> Data Administration -> Export, or Accounts -> ... -> Export), keep accounts 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 zoho_import.csv keyed by Record Id, for Import -> "Update existing Accounts only" -> find existing records by Record Id. Existing Phone / Email values are never overwritten (the export value is written back unchanged); no-reply addresses are dropped; email is left blank when the domain has no MX. Record Id: Zoho documents the system-generated ID format as starting with "zcrm_". IDs are written in that form (bare all-digit IDs get the prefix; use --no-prefix to keep them as exported). IDs a spreadsheet turned into scientific notation (4.15087E+18) have lost digits and are skipped. Column names: "Record Id" (or "Record ID", "Id", "Account Id"), "Account Name", "Website", "Phone", and your email field (default "Email"; override with --email-col, --phone-col). Standard library only. No token or login is read or stored. """ import csv, json, re, sys 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) SCI = re.compile(r"^\d(\.\d+)?e\+?\d+$", re.I) def opt(flag, default): return sys.argv[sys.argv.index(flag) + 1] if flag in sys.argv else default PHONE, EMAIL = opt("--phone-col", "Phone"), opt("--email-col", "Email") PREFIX = "--no-prefix" not in sys.argv def norm(d): d = (d or "").strip().lower() d = re.sub(r"^https?://", "", d).split("/")[0].split("?")[0].split(":")[0] return d[4:] if d.startswith("www.") else d def col(row, *names): for n in names: if n in row and row[n] is not None: return row[n].strip() return "" def raw_id(r): return col(r, "Record Id", "Record ID", "Id", "ID", "Account Id", "Account ID") def zid(i): """Return (id_for_import, problem). Keeps zcrm_ IDs, prefixes bare digit IDs.""" i = (i or "").strip() if not i: return "", "no Record Id (re-export without excluding Record Id)" if SCI.match(i): return "", f"Record Id {i} was turned into scientific notation; re-export" if i.lower().startswith("zcrm_") and i[5:].isdigit(): return "zcrm_" + i[5:], "" if i.isdigit(): return ("zcrm_" + i if PREFIX else i), "" return "", f"unrecognised Record Id {i!r}" def read_export(path): with open(path, newline="", encoding="utf-8-sig") as f: return list(csv.DictReader(f)) def prepare(export, out="websites.json", all_rows=False): seen, sites, skipped = set(), [], [] for r in read_export(export): rid, problem = zid(raw_id(r)) who = raw_id(r) or col(r, "Account Name") d = norm(col(r, "Website")) if problem: skipped.append((who, problem)); 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(sites)} websites -> {out}") for who, why in skipped: print(f" skipped account {who}: {why}") def merge(export, dataset, out="zoho_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 fields = ["Record Id", "Account Name", "Website", PHONE, EMAIL, "Email Deliverability", "Contactability Score"] n = kept = prefixed = 0 with open(out, "w", newline="", encoding="utf-8") as f: w = csv.DictWriter(f, fieldnames=fields); w.writeheader() for r in read_export(export): rid, problem = zid(raw_id(r)) it = by.get(norm(col(r, "Website"))) if problem or not it: 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) prefixed += rid != raw_id(r) w.writerow({ "Record Id": rid, "Account Name": col(r, "Account Name"), "Website": col(r, "Website"), 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), }); n += 1 print(f"{n} rows -> {out} (existing values kept: {kept}, zcrm_ prefix added: {prefixed})") 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: zoho_enrich_prep.py prepare export.csv [--all] | " "merge export.csv dataset.json [--phone-col NAME] [--email-col NAME] [--no-prefix]")