DEV Community

Quill Datafix
Quill Datafix

Posted on

How to Clean Any Messy CSV or Excel Export Without Opening Excel (One Evening)

Every data pipeline I've inherited starts the same way: someone exported a CSV from a tool that shall remain nameless, and the export is a mess. Not a hard mess — a boring one. That's what makes it annoying: you fix the same four things every single time.

The four mess types (and the 10-line fix)

import pandas as pd

df = pd.read_csv("messy_export.csv", dtype=str)  # read as strings first — see gotcha #1

df.columns = (df.columns.str.strip()
                          .str.lower()
                          .str.replace(r"\s+", "_", regex=True))   # headers nobody agrees on
df = df.apply(lambda s: s.str.strip())                               # trailing/leading spaces
df = df.drop_duplicates()                                            # the classic
for c in ("date", "order_date", "created_at"):                       # whatever your date cols are
    if c in df.columns:
        df[c] = pd.to_datetime(df[c], errors="coerce", dayfirst=False)

df.to_csv("clean.csv", index=False)
Enter fullscreen mode Exit fullscreen mode

That's 90% of it. The remaining 10% is where the gotchas live.

Three gotchas that silently corrupt your data

1. Letting pandas guess types on read. If a column has "1,234" in row 500, pandas decides the whole column is a string — or worse, silently NaNs it. Read everything as dtype=str, clean, then convert deliberately.

2. Mixed date formats in one column. 2026/03/01 vs 01-03-2026 vs Excel's 45560 serial numbers, sometimes in the same column. pd.to_datetime(..., errors="coerce") turns the unparseable ones into NaT — count them before you trust the column: df[c].isna().mean(). If more than a few percent fail, the column has a format war and you need to handle formats explicitly, not guess.

3. Duplicates that aren't duplicates. " Acme " and "Acme" and "ACME" are the same customer to a human and three rows to drop_duplicates(). Normalize the key columns (casefold + collapse spaces) into a helper column, dedupe on that, drop the helper.

If you don't code (or don't want to babysit formats)

The steps above are exactly what I packaged into the Excel Batch Cleaner — drop in CSV/XLSX/JSON exports, it runs the space-trim, date fixing, dedupe and header normalization in your browser (files never leave your machine), and writes clean files out. No install, no account.

Why it's $1: launch pricing while I collect the first honest reviews — the full cleaner, not a trial. If it misses a mess type you've hit, tell me which pattern and I'll add it. Price goes up once the first reviews are in: https://datafixtools.sell.app/product/excel-batch-cleaner

Sample columns and formats vary by export; always sanity-check row counts and a few converted dates before you trust any cleaning step, mine included.

Top comments (0)