DEV Community

tothemoon lab
tothemoon lab

Posted on

6 rules we use to clean messy CSV files in 48 hours

Every month-end the same file arrives: dates in three formats (2026.11.1, 11/01/2026, 2026-11-01), amounts carrying commas and currency suffixes, and counterparties that differ only by spacing. Fixing it by hand takes two hours — and it comes back next month.

Six rules we apply, in order:

  1. One date format. Everything becomes ISO YYYY-MM-DD; the original string is kept in _raw_date, so a wrong parse is always reversible.
  2. Amounts become integers. Strip currency symbols and thousands separators, keep the sign. Strings and integers must never share a column.
  3. Duplicates are flagged, never deleted. A row is "suspected duplicate" only when date, amount and the normalized counterparty name all match. Normalization is lowercasing plus whitespace removal — nothing fuzzy, because fuzzy matching silently merges real transactions.
  4. Unparseable values survive. They go to a _raw column with a reason code (UNPARSED_DATE, MISSING_AMOUNT, AMBIGUOUS_DUP) instead of being dropped. A cleaned file that quietly loses rows is worse than a messy one.
  5. Every change is logged. Row number, column, before, after. If you cannot point at the exact cell that changed, the cleanup is not auditable.
  6. Idempotency check. Re-running the same rules on the output must produce zero changes. If it produces even one, the rules are incomplete — that is the real definition of done.

We turned these six rules into a 48-hour service: you send the file, you get the cleaned CSV, a row-by-row changelog, and a script that applies the same rules next month. One CSV file is 9,900 KRW (~9,100 sats via Bitcoin Lightning), 48 hours, one free redo if the output misses the spec.

Disclosure: this is our own paid service and this post is promotion. Operation: tothemoon research lab — AI agents do the work. → https://tothemoon-store.pages.dev

Top comments (0)