Skip to content
Portfolio/Capabilities

CAPABILITY 07 / 30

A messy export becomes a workbook

Ingest the export nobody wants to clean, and hand back a workbook with the rejects listed.

IN PRODUCTION

This runs in client systems today. The sample below was regenerated for a fictional company.

Real exports arrive with mixed date formats, currency symbols inside numbers, duplicate ids, and a header row in the wrong place. This reads them, applies named rules, and writes a formatted workbook with live formulas and charts. Anything it will not import is listed with the reason, so nothing disappears quietly.

TRY IT / RUNS IN YOUR BROWSER

Use it here.

The mechanism is the production one. The company, the catalogue, and every figure are fictional.

Spreadsheet ingestion

Four hundred messy rows in, one workbook out

A real export is never clean. This one arrives with five date formats, dollar signs inside the numbers, one city spelled five ways, six products arriving under twenty seven spellings, and a header row that is not the first row. The ingester fixes what it can name a rule for, holds back what it cannot, and writes both the workbook and the record of what it did. Nothing is discarded quietly.

Loading the run record.
A value a rule rewrote A blank filled from a lookup The value that stopped the row
Beforeraw-orders.csv, as exported
Afteras imported to Clean data

The two panels scroll together, line for line, and each one scrolls sideways for the rest of its columns. Hover a highlighted cell to see which rule touched it.

Download the workbook

Six sheets, two native charts, every figure a live formula.

Apex Instruments is fictional and every figure here is demo data. The source file, the ingester, the workbook and this view are all in one folder, so the numbers on this page and the numbers in the workbook come from the same run.

THE OUTPUT / GENERATED FOR APEX INSTRUMENTS

What comes out.

XLSX
Apex Instruments Q3 2026 order bookWorkbook · 6 pages · Six sheets: Read me, Summary, By month, By product, Clean data, Rejected rows. Every figure on Summary, By month and By product is a formula over Clean data, not a pasted value. Native line chart and bar chart, colour scale plus a below target rule on the margin column, named styles, fitted column widths, frozen header, auto filter, no merged cells anywhere.Download the workbook ↗
PY
The ingester and workbook writerData · Python with openpyxl. Finds the header row, applies thirteen named cleaning rules, validates against eight rejection codes, writes the workbook plus data/clean-report.json and data/preview.json. Prints the run to stdout.Open the file ↗
PY
The messy source generatorData · Seeded, so the source file is reproducible. Builds four hundred fictional order rows and plants every defect the ingester has to survive, including three duplicate order ids and two negative quantities.Open the file ↗
CSV
Raw order export, as the source system writes itData · 405 lines. Four preamble lines, the header on line 5, then 400 dirty order rows.Open the file ↗
CSV
Partner lookupData · Eight partner ids with name, region and site city. The ingester joins on this and refuses any row whose partner id is not here.Open the file ↗
JSON
Machine readable record of the runData · Counts in and out, every rule with its fire count and example, every rejected row with its reason, the value distributions before and after for ship city and product, and the quarter totals by month and by product.Open the file ↗
JSON
Row level before and afterData · All 400 source rows with their raw display cells, the per cell problem flags, and either the cleaned values or the rejection code. The widget renders this, it does not re-implement the cleaning.Open the file ↗
PY
Workbook verificationData · Reads the xlsx back, recomputes the totals from the stored input columns, and evaluates every formula with a small built in evaluator because nothing in the file is cached. 137 checks.Open the file ↗

THE MECHANISM / SHOW THE SHAPE, NOT THE SOURCE

How it works.

What a skill is
  1. 01Find the data

    The header row is located rather than assumed.

  2. 02Normalize

    Dates, numbers, and names go through named rules, each one counted.

  3. 03Reject loudly

    A row that cannot be trusted is held back and listed with its reason.

  4. 04Build

    A workbook comes out with real formulas, native charts, and formats applied.

  5. 05Explain

    A read-me sheet records what was cleaned and what was refused.

WHAT IT READS

  • The raw export, however it arrived
  • Lookup tables for the codes it contains
  • A cleaning rule set with a reason per rule

WHAT IT CHECKS BEFORE IT SHIPS

  • Every rejected row is listed with a reason
  • Summary cells are formulas, not pasted values
  • No merged cells inside a data range
  • The file opens without a repair prompt
In production: workbook ingestion in both company systems, including one workbook of 44 program tabs distilled into a fact table.

The mechanism runs inside client systems. Everything on this page was regenerated for Apex Instruments, a company that does not exist. Client names, source files, and internal wording stay private.

What it producesWhat is being built next