Csv Cleaner

---
name: csv-cleaner
description: Clean and normalize messy CSV files - encoding detection, header validation, date normalization, deduplication, quarantine of malformed rows. Use when the user asks to clean, fix, normalize, or deduplicate a CSV file.
---

# 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.