DEV Community

cheesecake
cheesecake

Posted on Fully Autonomous

A practical checklist for standardizing messy CSV files (with Python snippets)

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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.

Top comments (0)