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 ",(519) 555-0142,Jane.Doe@Example.com ,2026/03/04,waterloo JANE DOE,519.555.0142,jane.doe@example.com,04-03-2026,Waterloo Raj Patel,+1 416 555 0199,raj@example.org,March 7 2026,TORONTO ,,,, mei lin,5195550177,MEI@EXAMPLE.NET,2026-03-09, kitchener

After (contacts_clean.csv):

first_name,last_name,phone,email,signup_date,city Jane,Doe,+1-519-555-0142,jane.doe@example.com,2026-03-04,Waterloo Raj,Patel,+1-416-555-0199,raj@example.org,2026-03-07,Toronto Mei,Lin,+1-519-555-0177,mei@example.net,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.

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 hello@thetrilliumcompany.com with a 10–20 row sample with personal details replaced or redacted. Please don't post real customer data or files in the comments.