Reads the CSV and Excel files clients actually send you, detects how they are really encoded and formatted, cleans and joins them according to a small configuration file, and writes a correct CSV or XLSX back out. Every change, every dropped row and every format decision is written to a report, with the row number the row had in the original file. If the row counts do not reconcile at the end, the run fails instead of handing you a file nobody can check.
input detect clean join output
───── ────── ───── ──── ──────
orders_*.csv ─┐ encoding, separator, trim whitespace, match on a key, orders_clean.csv
prices.xlsx ─┼──► header row, number ──► rename, cast types, ──► report every ──► orders_clean.xlsx
(glob, many) ─┘ and date format required fields, unmatched row report.json
per file duplicates report.txt
│ │ │
└────────────────────────┴───────────────────────┘
every decision is recorded, nothing is dropped silently
python -m venv .venv && source .venv/bin/activate # Python 3.11 or newer
pip install -e ".[dev]"
# What is actually in this file? Changes nothing, writes nothing.
table-pipeline inspect examples/data/shop_export.csv
# Clean, join and export, using the configuration below.
table-pipeline run -c examples/pipeline.yamlinspect is the thirty second answer to "why does your export not import
anywhere":
examples/data/shop_export.csv
8 data rows, 9 columns
encoding cp1252 (not UTF-8; cp1252 yields 8 typical Western European characters)
delimiter semicolon (semicolon gives 9 columns in 82% of the sampled lines)
header row column names found in row 3, 2 line(s) above it ignored
unnamed column column 9 had no title, called 'column_9'
column looks like detail
Bestellnr text 6 value(s) with a leading zero, must stay text
Menge number no separators in the sample, parsing is unambiguous
Einzelpreis number German (1.234,56), votes 9 against 0, based on 3 unambiguous value(s)
Bestelldatum date 7 of 7 values match %d.%m.%Y
PLZ text 2 value(s) with a leading zero, must stay text
Every item on this list is detected automatically and named in the report.
| What arrives | What the tool does |
|---|---|
| Semicolon instead of comma, or tab, or pipe | Picks the separator that yields a stable column count, and says how stable |
| UTF-8, UTF-8 with BOM, Latin-1, Windows-1252 | Byte order mark first, then a strict UTF-8 check, then the 0x80-0x9F range that only Windows-1252 uses. The BOM never ends up in the first column name |
German 1.234,56 against English 1,234.56 |
Decided per column and per file: a value carrying both separators settles it, repeated separators mean thousands. Files in one glob may use different conventions |
31.12.2025, 12/24/2026, 2026-01-03 |
Format chosen per column. A component above 12 settles day against month, and a genuinely ambiguous column is resolved by configuration and flagged as ambiguous in the report |
| Leading zeros in postcodes and article numbers | Everything is read as text and only cast where the configuration says so. 01067 stays 01067, in the CSV and in the XLSX cell |
| Headers that start in row 3 | The header row is located by looking for the first row of unique, non-numeric labels that matches the width of the body. Preamble lines are reported, not silently skipped |
| Merged cells in Excel | Unmerged on read, the top left value is copied into every cell of the range, so grouped exports become real rows |
| Completely empty columns and rows | Removed, counted and listed by name and row number |
| Columns without a title, or with the same title twice | Named column_7, second occurrence suffixed, both reported |
| Rows with more values than the header has columns | Kept, with the surplus values quoted in the report |
Accounting notation: (99,90), 89,90-, 1.234,56 € |
Parsed as -99.90, -89.90 and 1234.56 |
Values that are not numbers at all (k.A., auf Anfrage) |
Per column: empty the cell, keep the text, or drop the row. Always with the row number |
| The same record twice, spelled differently | Duplicates on normalized keys: case, umlauts (Müller = mueller), punctuation and spacing folded away |
| A join that quietly multiplies rows | Fan-out is counted and reported as extra rows, so a total that suddenly grows has an explanation |
| Rows that match nothing on the other side | Reported one by one, with their key, whether the join keeps them or not |
The old binary .xls |
Refused with a sentence that tells the client what to do about it |
One file, YAML or TOML, paths relative to the configuration file
(examples/pipeline.yaml in full):
sources:
- name: orders
path: data/shop_export.csv # semicolon, Windows-1252, two preamble lines
- name: prices
path: data/preise.xlsx
sheet: Preisliste # header sits in row 3, title merged across A1:C1
columns:
- name: Bestellnr
rename_to: order_no
type: text # text, or 00010045 turns into 10045
required: true
- name: Einzelpreis
rename_to: unit_price
type: decimal # 1.249,00 and 1,249.00 both land as 1249.00
on_error: empty # empty the cell, keep the row, report it
- name: Bestelldatum
rename_to: order_date
type: date
day_first: true # only consulted when a date is truly ambiguous
dedupe:
keys: [order_no, sku]
normalize: true # case, umlauts, punctuation and spacing folded
keep: first
merge:
left: orders
right: prices
left_key: sku
how: left # keep every order, even without a price row
output:
csv: out/orders_clean.csv
xlsx: out/orders_clean.xlsx
report_json: out/report.json
report_text: out/report.txt
date_format: "%d.%m.%Y"
decimal_comma: trueColumn types are text, integer, decimal, date and keep. Anything not
declared is kept as trimmed text, unless keep_undeclared_columns: false says
otherwise. An unknown key in the configuration is an error, not a shrug.
Printed after every run, and written as report.txt and report.json:
Detected
[orders] encoding: cp1252 (not UTF-8; cp1252 yields 8 typical Western European characters)
[orders] delimiter: semicolon (semicolon gives 9 columns in 82% of the sampled lines)
[orders] header row: column names found in row 3, 2 line(s) above it ignored
[orders] unnamed column: column 9 had no title, called 'column_9'
[orders] missing column: declared but not in shop_export.csv (expected when the column comes from another source): Artikelbezeichnung, Listenpreis
[orders] empty column: removed 1 column(s) without a single value: column_9
[orders] number format: Menge: no separators in the sample, parsing is unambiguous
[orders] number format: Einzelpreis: German (1.234,56), votes 9 against 0, based on 3 unambiguous value(s)
[orders] date format: Bestelldatum: 7 of 7 values match %d.%m.%Y
[prices] merged cells: 1 merged range(s) unmerged, the top left value was copied into every cell
[prices] header row: column names found in row 3, 2 line(s) above it ignored
[prices] missing column: declared but not in preise.xlsx (expected when the column comes from another source): Bestellnr, Kunde, Menge, Einzelpreis, Bestelldatum, Lieferort, PLZ
[prices] empty column: removed 1 column(s) without a single value: Notiz
[prices] number format: Listenpreis: English (1,234.56), votes 2 against 0, based on separator position only
[pipeline] join: left join of 'orders' and 'prices' on sku: 7 matched, 0 without a partner
[pipeline] duplicates: 1 duplicate row(s) removed on normalized order_no + sku, kept the first
Changed
parsed as integer Menge 7 cell(s)
parsed as date Bestelldatum 7 cell(s)
parsed as decimal Einzelpreis 6 cell(s)
parsed as decimal Listenpreis 3 cell(s)
trimmed Kunde 1 cell(s)
Dropped rows: 3
1 row is completely empty (orders row 10)
1 missing required field 'order_no' (orders row 9)
1 duplicate of orders row 5 on order_no + sku (orders row 6)
Emptied values: 1
1 unparsable number in 'Einzelpreis', value emptied (orders row 8)
Totals
read 12 dropped 3 used as lookup 4 added by a join 0 written 5
Nothing was discarded without a row number and a reason.
The totals are a guarantee, not a summary: read - dropped - used as lookup + added by a join has to equal written, or the run raises instead of writing
files. report.json carries the same information per row for anyone who wants
to process it further.
- CSV: separator and encoding of your choice, UTF-8 with BOM by default so Excel on a German machine opens the umlauts correctly. Dates in the configured format, decimals with a comma if you want one, leading zeros intact.
- XLSX: text columns written as text with format
@, so01067stays01067when the client opens the file. Dates as real dates with a date format, numbers as numbers, bold header, frozen first row, sensible column widths.
pytest84 tests. The fixtures in tests/fixtures/ are deliberately broken sample
files, all invented, no client data: a Windows-1252 semicolon export with a
two line preamble, German numbers, an exact duplicate, an empty row, a row
without its key and an unparsable price; the same columns exported by an
English locale with a BOM; a Latin-1 customer list with umlauts; and an Excel
sheet with a merged title, a header in row 3, merged cells in the body and an
empty column. python tests/fixtures/make_fixtures.py rebuilds them.
src/table_pipeline/
detect.py encoding, separator, header row, number and date formats
ingest.py reading CSV and XLSX into plain text, row numbers kept
transform.py trimming, casting, required fields, duplicates, joins
writers.py CSV and XLSX export that does not undo the cleaning
report.py findings, row issues, totals, the reconciliation guard
config.py the YAML and TOML schema, validated eagerly
pipeline.py the run itself, plus `inspect`
cli.py command line
Python 3.11 or newer, pandas, openpyxl, PyYAML. MIT licensed.