CSV/JSON Cleanup to a Target Schema

Turn a messy CSV or JSON file into a clean dataset that matches a schema you supply. Deterministic, offline, stdlib-only Python, with every rejected row explained.

When to use

• A spreadsheet export has inconsistent headers, mixed number formats, stray whitespace, or duplicate rows.
• A JSON list of objects needs renaming, type coercion, or dedup before import.
• You have a schema and want output that validates against it.

What you get

• clean.csv or clean.json: accepted rows in the target schema.
• rejected.csv: every rejected row, its original values, and a reason.
• report.md: row counts, the header mapping, unmapped fields, and dropped duplicates.

Schema format

{
  "fields": [
    {"name": "id", "type": "number", "required": true, "aliases": ["ID"]},
    {"name": "signup", "type": "date", "required": true, "format": "%d/%m/%Y"},
    {"name": "active", "type": "boolean", "required": false}
  ],
  "null_tokens": ["N/A", "-", "null"],
  "dedup_key": "id"
}

Types: string, number, date (requires format), boolean.

Run

python3 clean.py --schema schema.json --input messy.csv --outdir out --format csv

Input limit: 5 MB. Larger files must be split first. No network access is used.

Rules the script enforces

1. Header mapping. Each target field matches a source column by exact name (after trimming), then by aliases, then by a normalized name (case and punctuation ignored). Unmapped fields and columns are reported.
2. Numbers. 1,234.50 and 1234.5 are accepted. European decimals such as 12,34 are rejected as ambiguous_decimal; they are never silently converted.
3. Dates. Parsed only with the declared format. Output is YYYY-MM-DD. A field with no declared format is rejected as date_format_undeclared.
4. Booleans. Only true/false, yes/no, 1/0 (case-insensitive) are accepted.
5. Nulls. Empty strings and declared null_tokens become null. A null in a required field rejects the row as required_missing:<field>.
6. Deduplication. Only when dedup_key is set: the first row with a key is kept, later ones are dropped and listed by source row number in the report.
7. Nothing is guessed. A value that fails its type is rejected, never repaired.

Limits

• Output is only as correct as the schema. Ambiguous input is rejected and listed, not fixed.
• Source row numbers in rejected.csv refer to data rows, not the header line.
• The script handles CSV (with a header line) and JSON (a list of flat objects). Nested JSON must be flattened first.
• Deduplication is exact-match on the key after type coercion.

Example

Input: 1, Ada ,"1,234.50",03/04/2024,yes with schema amount: number and signup: date, format %d/%m/%Y gives 1,Ada,1234.5,2024-04-03,true.

The row 2,Bob,"12,34",05/04/2024,no is rejected with ambiguous_decimal:amount.