CSV clean and dedupe with a change log

What it does

Cleans CSV exports (customer lists, data from legacy systems) without touching the original file.

• Detects the encoding (UTF-8, UTF-8 with BOM, Shift_JIS/CP932, EUC-JP) and the delimiter (comma, tab, semicolon, pipe)
• Profiles each column: empty cells, value kinds, cells with full-width ASCII or half-width katakana, edge spaces, ragged rows
• Normalizes width (full-width ASCII to half-width, half-width katakana to full-width, ideographic space to a normal space) and trims spaces, while keeping symbols such as circled digits
• Unifies dates to YYYY-MM-DD, including 2024/1/5, 20240105, Japanese year-month-day notation and Japanese era dates such as R6.1.5
• Unifies phone number separators and +81 (choose digits only, hyphenated, or +81 format)
• Finds duplicates: exact rows, an exact business key (email, customer ID), and fuzzy matches on names that ignore legal-entity suffixes
• Logs every changed cell in changes.csv (row, column, before, after, rule)

How to use

python scripts/csv_clean.py profile input.csv
python scripts/csv_clean.py clean input.csv --out-dir cleaned/ --key "email" --fuzzy-key "company"

The agent explains the profile first, confirms the date and phone columns and duplicate keys with you, then cleans. Duplicate candidates are never removed silently; you review them together.

Requirements

• Python 3 (standard library only)

Caveats

• The input file is only read, never overwritten or deleted. Existing output files are not replaced without --force.
• Output defaults to UTF-8 with BOM so Excel opens it correctly. Use --out-encoding cp932 for Shift_JIS; characters that Shift_JIS cannot hold cause an error.
• Dates like 01/02/2024 whose day/month order is unknown are left unchanged unless you specify the order.
• Fuzzy matches are candidates only and may include branch offices. Above 5,000 rows, raise the limit or use an exact key.