sheet-merger

---
name: sheet-merger
description: Turns several spreadsheets or CSV exports with different columns into one clean list, plus a report of what did not map and a file holding every row that was not merged.
---

# Five messy spreadsheets, one clean list, and nothing lost

You point it at a folder of spreadsheets. You get back one merged file with the columns you agreed, a plain report of what did not map, and a separate file containing every row that did not make it, with the reason written next to it.

Run it on your own machine, over your own files, in one pass:

```
codex exec -C "the folder" - < the-prompt.md
```

## What it does

1. **Inventory before anything is merged:** open every file in the folder, and write `01-inventory.csv` listing one row per source file with the file name, sheet name, the row number the real header sits on, the number of data rows, and every column heading exactly as written. Never assume the header is row 1: exports from tills, banks and booking systems commonly put a title and a blank row above it.
2. **Write the column map and stop:** create `02-column-map.csv` with four columns, `source_file`, `source_column`, `target_column`, `confidence`, where confidence is one of `exact`, `renamed`, `guessed` or `unmapped`. Every heading found in step 1 appears exactly once. Anything marked `guessed` or `unmapped` is listed in the report for the owner to correct. Nothing is merged until this file exists.
3. **Pick the target columns from the fullest file, not the first one:** the agreed column set comes from whichever source has the fewest empty cells across the columns it shares with the others, because the first file in a folder is usually just the oldest. Say in the report which file it came from.
4. **Read every cell as text:** identifiers keep their leading zeros. A UK postcode, a phone number starting 0, a sort code, a barcode and a product code all become wrong numbers the moment a spreadsheet reader guesses they are numeric, and `1/2` silently becomes a date. Convert to a number only for a column the owner has named as a number.
5. **Normalise only two things, and nothing else:** dates are written `YYYY-MM-DD`, and leading or trailing spaces are trimmed. Broman and Woo's spreadsheet guidance singles out `YYYY-MM-DD` because every other order is ambiguous between countries. Do not retitle, re-case, reformat currency or "tidy" names: that is editing the owner's data without being asked.
6. **Put every rejected row in `04-not-merged.csv`, never in the bin:** each rejected row is written out whole, with three extra columns added at the front, `source_file`, `source_row_number` and `reason`. Reasons are specific, for example `no value in the key column` or `column count does not match the header`. A row is never dropped, never quietly repaired, and never counted twice.
7. **Handle duplicates by moving them, not merging them:** ask the owner which column is the key, for example email or product code. The first occurrence goes to the clean file, later ones go to `05-duplicates.csv` with their source file and row number. Never combine two rows into one, because you cannot tell which of two conflicting phone numbers is the current one.
8. **Mark missing cells with a code and a key:** a cell that has no value gets `[x]` if the source never held it, and `[z]` if the column does not apply to that source. The report carries the key. The UK Government Analysis Function guidance is explicit that a dash or a full stop is not good enough, since neither says why the cell is empty.
9. **Write the report last, in `03-report.md`:** the target columns and where they came from, every `guessed` and `unmapped` heading, the row arithmetic from the checks below, the missing-value key, and one worked example of a merged row shown beside its source rows.

## Then it checks

1. The row arithmetic balances exactly: rows in the clean file, plus rows in `04-not-merged.csv`, plus rows in `05-duplicates.csv`, equals the total data rows counted in `01-inventory.csv`. Print the sum in the report. If it does not balance, a row was lost.
2. Every heading in `01-inventory.csv` appears exactly once in `02-column-map.csv`, and nothing appears in the map that was not in the inventory.
3. The clean file has one header row, no blank rows, no blank columns and no merged cells, which is what the Analysis Function guidance requires of a table anyone else has to read.
4. Five rows chosen at random from the clean file are compared cell by cell against the source file and row number they came from. Every value matches character for character.
5. The longest identifier in the file still has all its characters, and any value that began with a zero still begins with a zero.
6. Every row in `04-not-merged.csv` has a reason written in it, and no reason is blank or says only "error".

Any check fails: name it, redo that step once. Failed twice: say what is wrong and stop.

## Rules
- Public information only. This job needs no internet: the files stay on the owner's machine, and nothing is uploaded, emailed or pasted into any online service.
- Never invent a fact, a number or a quote. An empty cell stays empty and gets a marker. Never fill a gap with a likely value, a lookup from another row, or an average.
- **Never silently drop a row.** If a row cannot go in the clean file it goes in `04-not-merged.csv` with a reason. A merge that ends with fewer rows than it can account for has failed, however clean the output looks.
- Never write to, rename or re-save a source file. All output goes into a new folder, and the source folder is opened read only.
- If the sheets hold personal details such as names, emails or phone numbers, say so in the report. A merged list of people is personal data under UK GDPR, and the merge may change what it can lawfully be used for. This skill prepares the file so a professional can check it. It is not legal advice, and it must never be presented as such.

## Built from
- anthropics/skills, the `xlsx` skill, https://github.com/anthropics/skills/blob/main/skills/xlsx/SKILL.md, 174,998 stars read from GitHub on 7 September 2026: took its instruction to follow the spec literally with exact column headers, its rule that every assumption and hardcoded number is documented where the reader will see it, and its warning that a run finishing without errors proves the steps executed, not that the result is right.
- CSV Data Summarizer by @coffeefuelbump, https://github.com/coffeefuelbump/csv-data-summarizer-claude-skill, found through the ComposioHQ skills directory at https://github.com/ComposioHQ/awesome-claude-skills, which showed 74,627 stars when read from GitHub on 7 September 2026: took its one-pass behaviour, inspect the file and produce the whole result without stopping to ask what the owner wants, and its missing-data reporting. Rejected its habit of generating charts, which is not what a merge is for.
- Broman KW and Woo KH, "Data organization in spreadsheets", The American Statistician, 2018, tutorial at https://kbroman.org/dataorg/: took the `YYYY-MM-DD` date rule, one thing per cell, make it a rectangle, and never use font colour or highlighting as data.
- UK Government Analysis Function, "Releasing statistics in spreadsheets", published 30 June 2021, https://analysisfunction.civilservice.gov.uk/policy-store/releasing-statistics-in-spreadsheets/: took "Remove all blank rows and blank columns within tables", "Restructure your tables so there are no split cells, merged cells or nested tables", and the shorthand markers with a key instead of dashes or full stops for missing values.