Build a Spreadsheet Cleaner

A spreadsheet cleaner that reads a file, asks a model to fix it, and writes the result back is a data loss incident with a progress bar. The version that is safe to run does three things differently: it types columns by voting rather than by guessing, it parses deterministically and calls a model only for the cells that fail, and its output is a diff somebody approves — never the file.

The rule: never write back in place

Every transformation is a proposal until a human accepts it. This is not caution for its own sake: in a 40,000-row file, a rule that is right 99.5 per cent of the time silently corrupts two hundred rows, and nobody finds out until a report is wrong three months later.

The output of every run is therefore three artefacts:

• A proposal file — one row per change: row id, column, old value, new value, rule that produced it, confidence.
• A summary — counts per column and per rule, so the reviewer knows where to look before opening anything.
• The cleaned file, written only after approval, and never over the input. This is a human-in-the-loop design rather than an automation, and saying so up front sets the right expectation with whoever asked for it.

Column typing by voting

Do not ask a model what a column contains. Try every parser on every cell and count. It is faster, free, and it produces a number you can reason about.

# clean.py
import csv, re
from datetime import datetime
from decimal import Decimal, InvalidOperation

def try_int(s):
    s = s.strip().replace(",", "")
    return int(s) if re.fullmatch(r"-?\d+", s) else None

def try_decimal(s):
    t = s.strip().replace(",", "").replace("£", "").replace("$", "").replace("%", "")
    try:
        return Decimal(t)
    except (InvalidOperation, ValueError):
        return None

DATE_FORMATS = ["%Y-%m-%d", "%d/%m/%Y", "%m/%d/%Y", "%d-%b-%Y",
                "%Y/%m/%d", "%d.%m.%Y"]

def try_date(s):
    s = s.strip()
    for f in DATE_FORMATS:
        try:
            return datetime.strptime(s, f).date(), f
        except ValueError:
            pass
    return None

BOOLS = {"yes": True, "no": False, "true": True, "false": False,
         "y": True, "n": False, "1": True, "0": False}

PARSERS = {"int": try_int, "decimal": try_decimal,
           "date": try_date, "bool": lambda s: BOOLS.get(s.strip().lower())}

def type_column(values, blank=("", "-", "n/a", "na", "null", "none", "#n/a")):
    real = [v for v in values if v.strip().lower() not in blank]
    if not real:
        return "empty", 1.0, len(values)
    scores = {name: sum(1 for v in real if p(v) is not None)
              for name, p in PARSERS.items()}
    best = max(scores, key=scores.get)
    ratio = scores[best] / len(real)
    return (best if ratio >= 0.90 else "text"), ratio, len(values) - len(real)

The 0.90 threshold is the decision. Above it, the column has a type and the cells that do not parse are errors to repair. Below it, the column is genuinely mixed and forcing a type destroys information — a “quantity” column that is 60 per cent numbers and 40 per cent “see notes” is a text column with a data-entry problem, and the honest report says so.

Deterministic first, model second

Sort the cells into three buckets and handle each differently. This is the whole cost story: the model sees the third bucket only.

Write the second bucket’s rules as named functions and record which one fired on each cell. When the reviewer sees eight hundred changes from strip_currency_symbol they can approve the rule rather than the cells, which is the difference between a fifteen-minute review and an unusable one.

What the model is actually good for here

Not parsing — a regex parses better and for free. The model is for judgements a rule cannot express:

• Entity resolution. “Acme Ltd”, “ACME Limited”, “acme ltd.” are one company. Cluster candidates by string similarity first, then ask the model to confirm each cluster — do not ask it to scan the whole column.
• Ambiguous units. A weight column containing “2.5kg”, “900g” and “1 lb” needs a decision per cell about what the number means. Ask for the value and the unit as separate fields, and normalise in code.
• Free-text categorisation. Mapping a messy “reason” column onto a fixed vocabulary is a classification task, and one the model does well when the vocabulary is in the prompt — as long as the output is constrained to the vocabulary rather than merely asked for.
• Explaining what is wrong. Sometimes the most useful output is not a repair but a sentence: “this column mixes dates in two different orders and cannot be disambiguated”.

REPAIR = """A CSV column is typed as {coltype}. Here are cells that failed
to parse, with the row number. For each, return JSON:

{"row": n, "value": "the normalised value, or null if it cannot be
 recovered", "confidence": 0.0-1.0, "note": "under 15 words"}

Rules:
- Never invent a value. Missing data stays null.
- Only reformat what is present. Do not infer from other rows.
- If a cell means "no value" (n/a, -, unknown), return null with note
  "explicit blank"."""

“Do not infer from other rows” is the instruction that stops the worst behaviour. Given a column of dates with one blank, a model will happily interpolate. That is not cleaning, it is fabrication, and it produces a file whose errors are undetectable because they look reasonable.

The diff is the product

row,column,old,new,rule,confidence
14,amount,"£1,204.00",1204.00,strip_currency_symbol,1.0
14,date,"14/03/2024",2024-03-14,date_dmy,1.0
27,amount,"(85.50)",-85.50,parenthesised_negative,1.0
27,supplier,"ACME Limited","Acme Ltd",entity_resolution,0.86
39,amount,"1.204,00",1204.00,decimal_comma_locale,0.72
41,weight,"2 stone",null,model_repair,0.31

Row 39 is the one to notice. A European-formatted number and an American one are indistinguishable in isolation — 1.204,00 and 1,204.00 are the same amount but 1.204 alone could be either a thousand or one point two. The correct handling is to decide once for the whole column from the majority pattern, mark every affected cell with the same rule, and confess the ambiguity in the report rather than resolving it per cell.

1. Sort the diff by rule, then by confidence ascending. The reviewer reads the worst first and can stop when it becomes boring.
2. Group identical transformations. “312 cells: strip currency symbol” is one decision.
3. Let the reviewer reject a whole rule, not just a cell. That is the control that makes a large diff tractable.
4. Apply only the approved rules, write to a new file, and keep the approval record next to it. The provenance of a cleaned dataset is part of the dataset.

Testing a tool whose input is always new

Every file is different, so you cannot test against expected output. You can test against properties that must hold whatever the input is, which is a stronger form of test and takes about an hour to set up.

• Row count is preserved. Cleaning never adds or removes rows. If it does, that is a bug and not a judgement call.
• Every change appears in the diff. Re-read the output file, compare cell by cell with the input, and assert that the set of differing cells is exactly the set of applied diff rows. This one test catches every silent mutation, including the ones a library performs on your behalf when writing a file.
• Idempotence. Cleaning a cleaned file produces no further changes. A cleaner that keeps finding work is oscillating, usually between two normalisation rules that disagree.
• Round-trip on unchanged cells. A cell the cleaner did not touch must come out byte-identical — including leading zeros, trailing spaces inside quotes, and the original line endings.
• Value preservation for numbers. For every changed numeric cell, assert that the new value parses to the same quantity the old string denoted, under the rule that was applied. That is a weaker claim than “correct” and it is checkable.

Then keep a corpus of real files that broke it, with the bug each one exposed, and run the whole property suite over all of them on every change. It grows by one file every time somebody reports a problem, it never needs labelling, and after six months it is the most valuable artefact the project has.

Traps specific to spreadsheets

• The header is not on row one. Title rows, blank rows, merged cells. Detect the header as the first row where most cells are short, non-numeric and unique, and say which row you chose.
• Numbers stored as text and text stored as numbers. A leading-zero product code that a spreadsheet has helpfully turned into an integer has lost information you cannot recover. Detect it — a code column whose values are all integers of varying length — and report it rather than cleaning it.
• Excel serial dates. A date column containing 45383 is a serial number, and the epoch differs between the two historical conventions. Detect the range and ask, rather than guessing the epoch.
• Encoding. A file that opens with mojibake is not a cleaning problem; it is the wrong encoding. Try UTF-8, then UTF-8 with BOM, then the common single-byte encodings, and pick by counting replacement characters. Never run a model over mojibake.
• Trailing total rows. The last row is often a sum, not a record. Including it in a mean is the classic silent corruption. Detect a final row where a numeric column equals the sum of the column above it.

Data quality checks that run continuously are the next step: a one-off clean is worth less than a check that fails the pipeline the next time bad data arrives from the same source.