CSV Cleaner

csv-cleaner

Workflow

1. Sniff encoding and delimiter. Try, in order: utf-8-sig, utf-8, gbk, cp1252. Use csv.Sniffer on the first 4KB for the delimiter; if sniffing fails, fall back to comma and say so in the summary.
2. Validate the header. Compare against the expected columns the user named (or infer from the first row). If a required column is missing, STOP and report expected vs actual header verbatim — do not guess column meanings from position.
3. Clean rows, applying in order:
   • trim surrounding whitespace in every cell
   • normalize dates to ISO-8601 (YYYY-MM-DD); accept D/M/Y, M/D/Y, YYYY年M月D日; if a date is ambiguous (e.g. 03/04/2021), keep the original value and flag the row in the summary
   • deduplicate by the user-named key column; when the user names no key, deduplicate only fully identical rows
4. Quarantine, never guess. A row that fails parsing goes to <name>.errors.csv with a reason column; processing continues. If more than 20% of rows are quarantined, ABORT and report the count — the file likely has a structural problem the user must see first.
5. Write output to <name>.clean.csv. NEVER overwrite the input file. End with a summary: rows in / rows out / quarantined / deduplicated / dates normalized / dates flagged ambiguous.

Failure modes

• Empty or header-only file → report it, produce no output file.
• Mixed delimiters across lines → quarantine affected lines, note pattern.
• Unknown encoding after all four attempts → stop, ask the user; do not transcode lossily.

Never do

• Never silently drop a row: every removed row is either in the errors file or counted as a deduplicate in the summary.
• Never reorder columns unless the user asks.
• Never guess what a column means from its values.