Every team that imports CSVs from more than one source ends up with the same mess: the CRM export calls it Phone, the spreadsheet from sales calls it Mobile No., dates arrive as 03/04/2026, 2026-04-03 and 3 Apr 26, and somebody spends Friday afternoon fixing it by hand.
Here's a practical checklist for standardizing CSV data before it hits your database or BI tool, with small Python snippets you can drop into a script.
1. Map column names to one schema first
Decide on a target schema and map every incoming header to it. Normalize headers before matching so small differences don't break things:
import re def norm(h: str) -> str: return re.sub(r"[^a-z0-9]", "", h.lower()) ALIASES = { "phone": {"phone", "mobile", "mobileno", "phonenumber", "contactnumber"}, "email": {"email", "emailaddress", "mail"}, "signup_date": {"signupdate", "createdat", "datejoined"}, } def map_headers(headers): out = {} for h in headers: for target, names in ALIASES.items(): if norm(h) in names: out[h] = target return out 2. Parse dates explicitly, never by guessing per row
03/04/2026 is March 4 in the US and April 3 almost everywhere else. Pick the format per source, not per row, and fail loudly on anything that doesn't match:
from datetime import datetime def parse_date(value: str, fmt: str) -> str: return datetime.strptime(value.strip(), fmt).date().isoformat() # always ISO 8601 out 3. Normalize phone numbers to E.164
Strip formatting and store one canonical form. The phonenumbers library handles country rules properly:
import phonenumbers def to_e164(raw: str, default_region: str = "US") -> str | None: try: n = phonenumbers.parse(raw, default_region) return phonenumbers.format_number(n, phonenumbers.PhoneNumberFormat.E164) if phonenumbers.is_valid_number(n) else None except phonenumbers.NumberParseException: return None 4. Clean names and free text conservatively
Trim whitespace, collapse double spaces and fix casing only when the whole value is upper- or lower-case. "McDonald" and "van der Berg" should survive.
5. Currencies: separate the number from the symbol
Store amounts as decimals plus an ISO currency code (1234.50, EUR), not €1.234,50. Watch for locales that swap . and ,.
6. Validate every row and keep the rejects
Don't silently drop bad rows. Write them to a separate file with the reason, so whoever owns the source can fix it upstream.
7. Make it repeatable
The real win is doing this the same way every time. Save the mapping and rules per source and run them on every new file rather than re-deciding each week.
Disclosure: I'm part of the team behind Data Standardize, a web app that does the steps above without code: you define a template once (target columns plus formats), and every new CSV is mapped, normalized and validated against it. There's a free demo with no card required. If you'd rather keep it in code, the snippets above are a solid starting point.
What's the messiest CSV format you've had to deal with? I'd love to hear the edge cases.

