DEV Community

tothemoon lab
tothemoon lab

Posted on

I ran a stdlib-only cleanup on 3 messy CSV exports — here's what actually broke

I cleaned three real-world CSV exports with a zero-dependency Python script and compared it to what people usually reach for. Here's what actually broke — and what the edge cases taught me.

The three files

  1. A CRM export: dates in three formats in the same column (2026-1-5, 05/01/2026, Jan 5)
  2. A payments ledger: quoted fields containing commas, plus a header written by hand (typos in two column names)
  3. An analytics dump: duplicate rows that are NOT byte-identical (trailing whitespace, \r\n on half the lines)

What broke (and what didn't)

  • Date parsing is the whole game. Two of three files had mixed formats in one column. A single to_datetime call silently coerced one format into garbage. Doing the date normalization explicitly per format, then unifying, was the only way to keep the values real.
  • Quoted commas survive a naive split, but not a naive regex. The csv module handled it. My own "quick" split didn't. Lesson: don't hand-roll what the stdlib already parses correctly.
  • Whitespace-only differences are the most invisible data loss. Dropping "duplicates" that differ only by trailing whitespace silently merged rows that were actually distinct entries in two of the systems feeding the file. The fix was to normalize, then compare — not compare, then normalize.
  • Header typos propagate everywhere. Two hand-written headers (email_adress, total_amt) became 6 downstream failures. Renaming on ingest, with a small known-typos map, cost 15 minutes and removed the entire class of error.

The takeaway

Most "dirty CSV" pain isn't exotic encoding. It's dates, quoted commas, whitespace-adjacent duplicates, and headers humans typed. A stdlib-only script that handles those four deterministically is enough for the majority of exports — and it's auditable, because there's no magic left.

I keep a browser-only version of this cleaner running (same rules, same order of operations) if you want to try the edge cases yourself: tothemoon CSV cleaner

Disclosure: that link is my own tool — a free cleaner with a paid batch kit on the same page.

Top comments (1)

Collapse
 
veritaproof profile image
Jack •

The issue "05/01/2026" is the one that really concerns me because it is impossible to correct this issue based only on this value. Anyway, it is a correct date. What helps me is considering the entire column and saying that if any of the values in the column has its first part greater than 12, the order of values is day-first, while if not – we cannot determine it in this file and should consider how it was exported by the source system.

Your point about the typos in the header is a good one as well. My next problem is that the cleanup script cleans the file in front of us, but next month's export can arrive with a new typo, a renamed column, or a filter applied, and the script still works successfully. My checklist would contain comparing the file with the previous one, i.e., the same columns, an approximate number of rows and a date range.