# CSV Cleanroom

Repeatable, offline CSV cleanup for people who work with customer and operations exports.

Turn one UTF-8 CSV into a clean import file, a duplicate/conflict list, a missing-key review queue, a cell-change log and a readable run report. Reuse the saved recipe next time. No subscription, package installation, API key or network connection.

## Requirements

Python 3.9 or newer and basic terminal familiarity. Runs on macOS, Windows and Linux using only the Python standard library. Automated verification was performed on macOS; Windows and Linux have not been independently tested. Not an Excel add-in or a graphical editor. Maximum input size: 50 MB; files are processed in memory, so use smaller exports on memory-constrained machines.

## Try the included example

Open a terminal in this folder. On macOS/Linux:

```sh
python3 cleanroom.py examples/contacts.csv --out sample-output --recipe examples/contacts-recipe.json
```

On Windows, use `py -3` in place of `python3`. Open `sample-output/report.html` in your browser. The six example records become four retained rows, one duplicate/conflicting record and one row needing review. All examples use fictional contacts.

Each run needs a new output folder. To repeat the demonstration, use `--out sample-output-2`.

## Your own export

```sh
python3 cleanroom.py "customer export.csv" --out reviewed-customers --key email --trim --casefold-key
```

Use your CSV's exact column name in place of `email`. By default, only identical full rows are deduplicated. `--key email` groups records by email and keeps the first record. `--trim` removes outer whitespace from every cell and logs each change. `--casefold-key` ignores letter case for key comparison only; it does not lowercase stored cell values.

Review conflicts before importing. A shared email can belong to multiple legitimate contacts; a duplicate key does not prove the record should be discarded. The tool records the candidates for your decision.

## What you receive in every output folder

| File | Purpose |
| --- | --- |
| clean.csv | First record for each distinct key; machine-readable strings |
| duplicates.csv | Other matching records, original record numbers and a conflict reason |
| review.csv | Records missing any selected key; not silently merged together |
| changes.csv | Every trimmed cell, including its original and resulting value |
| spreadsheet-*.csv | Viewing copies with apostrophes before formula-like cells |
| report.html | Local summary and blank-cell counts |
| summary.json | Counts, settings and SHA-256 source fingerprint |
| recipe.json | Reusable cleanup settings |

Input rows always equal retained + duplicate + review rows. Record numbers count the header as record 1; a quoted multiline cell is still one record. Your input file is never modified.

Machine CSVs preserve formula-like strings; do not open untrusted machine CSVs directly as spreadsheets. The spreadsheet copies prefix values beginning with formula-like characters, including negative numbers, with an apostrophe. This changes the viewing copy intentionally and does not guarantee every spreadsheet application's import behavior. Import columns as text to preserve identifiers such as `001`.

## Repeat the same rules next week

```sh
python3 cleanroom.py next-export.csv --out next-reviewed --recipe reviewed-customers/recipe.json
```

A recipe locks the detected delimiter. If the next export uses a different delimiter or changed headers, update the recipe or generate a new one. Recipe settings cannot be mixed with command-line cleanup flags.

## Common recipes

Exact full-row dedupe without changing cell contents:

```sh
python3 cleanroom.py export.csv --out exact-review
```

Inventory rows keyed by account and SKU:

```sh
python3 cleanroom.py inventory.csv --out inventory-review --key account --key sku
```

Semicolon-delimited export:

```sh
python3 cleanroom.py export.csv --out semicolon-review --delimiter ";"
```

## Limitations and troubleshooting

- UTF-8 only; a UTF-8 byte-order mark is supported. Convert other encodings first.
- Comma, semicolon, tab and pipe detection is heuristic. Supply `--delimiter` when needed.
- Headers must be unique and nonblank. `source_record`, `kept_record` and `reason` are reserved audit columns.
- Malformed row widths and unterminated quotes stop processing before output creation. Fix the source copy rather than guessing which cell was intended.
- No fuzzy name matching, email deliverability checks, phone validation, date parsing, encoding repair or XLSX support. Values remain strings; leading zeros and embedded newlines are preserved by the CSV files themselves.
- No automatic merging of conflicting values. The first matching record wins; use the duplicate audit to reconcile conflicting records manually.
- The source and report may contain private business data. Keep the output folder wherever you normally store those exports.

## Verify the package

```sh
python3 -m unittest discover -s tests -v
```

Version 1.0.0. Source code and tests are included. See LICENSE.txt for use and redistribution terms.