Cleaning a messy contacts CSV in 5 steps (with a short pandas script)

Most "messy CSV" problems in small businesses come down to the same five fixes. Here they are on a tiny example, with a short pandas script you can run yourself.

The example (synthetic data, made up for this post)

Before (contacts_raw.csv):

Name,Phone,Email,Signup Date,City
" jane DOE ",[phone],[e-mail] ,2026/03/04,waterloo
JANE DOE,519.555.0142,[e-mail],04-03-2026,Waterloo
Raj Patel,[phone],[e-mail],March 7 2026,TORONTO
,,,,
mei lin,5195550177,[e-mail],2026-03-09, kitchener

After (contacts_clean.csv):

first_name,last_name,phone,email,signup_date,city
Jane,Doe,[phone],[e-mail],2026-03-04,Waterloo
Raj,Patel,[phone],[e-mail],2026-03-07,Toronto
Mei,Lin,[phone],[e-mail],2026-03-09,Kitchener

Five changes made the difference:

1. Trim and normalize case. Stray spaces and ALL CAPS break matching. Names go to title case, emails to lowercase.
2. One phone format. Strip everything except digits, then format as +1-AAA-EEE-NNNN (North American numbers in this example).
3. One date format. Parse each value explicitly and write ISO YYYY-MM-DD. Ambiguous values such as 04-03-2026 need a rule you decide once (here: day-month-year, because the same person's other row says March 4).
4. Drop empty rows.
5. Deduplicate on a normalized key, here the cleaned email, not the raw name.

The script

import re
import pandas as pd

df = pd.read_csv("contacts_raw.csv", dtype=str).fillna("")
df = df.apply(lambda col: col.str.strip())
df = df[(df != "").any(axis=1)]  # drop fully empty rows

name = df["Name"].str.split().str.join(" ").str.title()
df["first_name"] = name.str.split().str[0]
df["last_name"] = name.str.split().str[1:].str.join(" ")

def phone(p):
    d = re.sub(r"\D", "", p)[-10:]
    return f"+1-{d[:3]}-{d[3:6]}-{d[6:]}" if len(d) == 10 else ""

df["phone"] = df["Phone"].map(phone)
df["email"] = df["Email"].str.lower()

def date(s):
    for fmt in ("%Y-%m-%d", "%Y/%m/%d", "%d-%m-%Y", "%B %d %Y"):
        try:
            return pd.to_datetime(s, format=fmt).strftime("%Y-%m-%d")
        except ValueError:
            pass
    return ""  # flag for manual review instead of guessing

df["signup_date"] = df["Signup Date"].map(date)
df["city"] = df["City"].str.title()

out = df.drop_duplicates(subset="email")[
    ["first_name", "last_name", "phone", "email", "signup_date", "city"]
]
out.to_csv("contacts_clean.csv", index=False)

Two habits worth keeping: never overwrite the original file, and write anything you could not parse as blank and review it, rather than guessing.

No-code option: run the same steps in your browser

If you'd rather not run Python, there's a version of these same steps that runs entirely in your browser, so the file is never uploaded anywhere: https://csv.thetrilliumcompany.com/tool/ (disclosure: we built it). You choose the dedupe column, the name column to split and the province/state column. It shows the full change log and the first 10 cleaned rows for free, and downloading the cleaned file plus the flagged, duplicate and change-log files costs USD 19. It also copes with QuickBooks-style exports that have title lines above the header row.

If you'd rather not do it yourself

I'm Peter at The Trillium Company (Waterloo, Ontario). We do exactly this as a small fixed-price job:

• USD 40 per file: one CSV or Excel/Google Sheets file, up to 5,000 rows and 20 columns
• What's included: trimming and case, phone/email/date/address formats made consistent, empty rows removed, duplicates removed on a key you choose, columns split or merged, and a change log listing every rule applied
• Turnaround: same business day once we've confirmed the scope and you've sent the file
• Payment: by card through a Stripe link, only after you've reviewed the result and agreed it matches the scope

To start, either leave a comment here describing your file in general terms (rough row count, columns, what is wrong with it; no real data), and I'll reply in the comments with a scope and a yes/no on whether it fits, or email [e-mail] with a 10–20 row sample with personal details replaced or redacted. Please don't post real customer data or files in the comments.

Comments

[Sujal Khatri]

Really liked the " jane DOE " → Jane,Doe before/after — that makes the pain concrete in one line. And your point that most small-business messy CSVs boil down to the same five fixes rings true; it feels like everyone re-solves the same mess over and over.

Curious: for the small businesses you see, how were they handling this before — just living with messy lists, or someone burning hours fixing it by hand? Roughly how much time does a typical messy contacts file eat?

Honest question: if someone built a small tool that fixed exactly this for you (no code needed), what would you honestly pay for it?