A leads export is never clean. The same person appears twice with different capitalisation, someone typed gmial.com, a phone number is (415) 555-0134 in one row and 415-555-0134 in the next, and there is a bob@@initech that no mail server will ever accept. Mailing that list means bounces, and bounces hurt your sending reputation.

This script reads a CSV with the columns name, company, email, phone and writes a clean one. Standard library only.

What it does, in order:

  • Trims and lowercases emails, removes stray spaces, angle brackets, trailing punctuation and mailto:.
  • Fixes a short list of common domain typos (gmial.com, gmail.con, hotmial.com and a few more). The list is a plain dictionary; add the typos you see in your own data.
  • Validates the shape of each address with one regular expression and drops what fails.
  • Drops duplicates by cleaned email, keeping the first row. Duplicates are found after cleaning, so John.Smith@GMIAL.com and john.smith@gmail.com count as the same person.
  • Normalises phones to +<digits>. A number with 10 digits and no plus gets a default country code (1; change default_cc for your market). Anything shorter than 8 or longer than 15 digits becomes empty rather than a wrong number.
  • Flags role addresses (support@, info@, sales@) in a separate column so you decide whether to mail them. 
#!/usr/bin/env python3 """Clean a messy leads CSV: trim, fix emails, normalise phones, drop duplicates. Standard library only. Usage: python clean_leads.py leads.csv [clean.csv]""" import csv, re, sys EMAIL = re.compile(r"^[a-z0-9._%+-]+@[a-z0-9-]+(\.[a-z0-9-]+)*\.[a-z]{2,}$") TYPOS = {"gmial.com": "gmail.com", "gmail.con": "gmail.com", "gamil.com": "gmail.com", "hotmial.com": "hotmail.com", "outlok.com": "outlook.com", "yaho.com": "yahoo.com"} ROLE = {"info", "admin", "support", "sales", "noreply", "no-reply", "contact", "hello"} def clean_email(raw): e = re.sub(r"\s+", "", raw or "").lower().strip("<>.,;:'\"") e = e.replace("mailto:", "") if "@" in e: user, dom = e.rsplit("@", 1) e = f"{user}@{TYPOS.get(dom, dom)}" return e def clean_phone(raw, default_cc="1"): digits = re.sub(r"\D", "", raw or "") if (raw or "").strip().startswith("+") or len(digits) > 10: pass elif len(digits) == 10: digits = default_cc + digits return ("+" + digits) if 8 <= len(digits) <= 15 else "" def clean_name(raw): s = re.sub(r"\s+", " ", raw or "").strip() return " ".join(w.capitalize() if w.isupper() or w.islower() else w for w in s.split(" ")) def main(src, dst="clean.csv"): seen, out, dropped = set(), [], {"bad_email": 0, "duplicate": 0} with open(src, newline="", encoding="utf-8-sig") as f: for row in csv.DictReader(f): row = {k.strip().lower(): v for k, v in row.items() if k} email = clean_email(row.get("email")) if not EMAIL.match(email): dropped["bad_email"] += 1; continue if email in seen: dropped["duplicate"] += 1; continue seen.add(email) out.append({"name": clean_name(row.get("name")), "company": clean_name(row.get("company")), "email": email, "phone": clean_phone(row.get("phone")), "role_address": "yes" if email.split("@")[0] in ROLE else ""}) with open(dst, "w", newline="", encoding="utf-8-sig") as f: w = csv.DictWriter(f, fieldnames=["name", "company", "email", "phone", "role_address"]) w.writeheader(); w.writerows(out) print(f"kept {len(out)}, dropped {dropped['bad_email']} bad emails and {dropped['duplicate']} duplicates -> {dst}") if __name__ == "__main__": if len(sys.argv) < 2: sys.exit(__doc__) main(*sys.argv[1:3])

A five-row test file:

Name , Company,Email,Phone JOHN smith ,ACME INC, John.Smith@GMIAL.com ,(415) 555-0134 Mary O'Neil,globex,mary@globex.io,+44 20 7946 0958 john smith,Acme Inc,john.smith@gmail.com,415-555-0134 Bob,Initech,bob@@initech,555 Support Team,Hooli,<Support@hooli.com>, 
python clean_leads.py leads.csv clean.csv kept 3, dropped 1 bad emails and 1 duplicates -> clean.csv 
name,company,email,phone,role_address John Smith,Acme Inc,john.smith@gmail.com,+14155550134, Mary O'Neil,Globex,mary@globex.io,+442079460958, Support Team,Hooli,support@hooli.com,,yes

Limits worth knowing:

  1. A well-formed address can still bounce. There is no MX lookup and no mailbox check; for that you need a verification service.
  2. Name capitalisation is naive. It handles JOHN and smith, but will not fix mcdonald into McDonald.
  3. The phone logic assumes one default country. If your list mixes countries, add the code to the source column before cleaning.
  4. Duplicates are matched by email only. Two different emails for the same person stay as two rows.

Run it on a copy of your file first and compare the counts; it prints how many rows it dropped and why.

More ready-to-use prompts and scripts: https://swarmagency.shop/kit.html

S.W.A.R.M / swarm_agency