---
name: spreadsheet_automation
description: "Run scripted batch work over many spreadsheet files with Python: merge, split, clean, and convert a folder of .xlsx and .csv files, apply the same transform to every file, and reconcile row counts and totals before and after. Use when the same operation must run over many workbooks or files, repeatably, with a per-file log."
metadata: { "includeInPrompt": true }
---

# Spreadsheet Automation

## Purpose

Do spreadsheet work as a script that runs over files, so the same job can run over 4 files or 400 files, run again next week with the same result, and prove its own output with row counts and totals. This skill covers the batch layer: merging a folder of workbooks into one file, splitting one workbook into parts, converting between .xlsx and .csv, cleaning values the same way in every file, and logging what happened to each file.

Scope:
- spreadsheets-mastery is interactive formulas, pivots, and financial models inside a sheet, statement-bookkeeping is bank statement categorization for books. This skill owns repeatable scripts over files, not formulas in a sheet.
- This skill reads and writes cell values. It does not build formulas, pivot tables, or models inside a workbook, and it does not categorize transactions for accounting.
- One-off edits to a single workbook, where no repeat run is expected, are usually faster by hand in a spreadsheet app than as a script. Script the job when it repeats, when the file count is large, or when the result needs a reconciliation record.

## Workflow

1. Define the batch job in writing before touching files.
   Fill in every field. A field left blank becomes a guess the script makes silently.
   - Input: the folder holding the source files, and which extensions count (.xlsx, .csv, or both).
   - Expected shape: the header row each file must have, in order, and which worksheet counts for .xlsx files (the batch script in this skill uses the active sheet; say so in the job note if the workbooks carry several sheets).
   - Key columns: the column used for totals (this skill uses `amount`), and any column that must stay unique across the merged output, such as an id.
   - Cleaning rules: what gets fixed on the way in. Examples used by the script below: strip surrounding spaces from text cells, skip fully blank rows, skip and count rows whose amount does not parse as a number.
   - Output: one merged workbook, one output file per input file, or a set of split parts. Write the output name and folder down. Outputs go to a different folder from the inputs when practical; if the output must sit in the input folder, the script must exclude it from its own input list.
   - Reconciliation targets: which numbers must match before and after (row count and the sum of the amount column in this skill). A batch with no reconciliation target has no way to prove it worked.

2. Check the environment before writing or running code.
   - Run: `python3 -c "import openpyxl; print(openpyxl.__version__)"`
   - Verified on the drafting machine on 2026-10-14: openpyxl 3.1.2 is installed, and the latest release on PyPI on that date was 3.1.5. Quote those numbers with that date, or recheck the installed version before quoting any version at all. The workflow needs features present in 3.1.2, so a newer install is fine, but record what actually ran.
   - Do not build the workflow on pandas on this machine. Importing pandas there fails with `ValueError: numpy.dtype size changed, may indicate binary incompatibility`. That is a property of that machine's install, recorded here so the failure is not rediscovered mid-job. The workflow in this skill uses openpyxl plus the Python `csv` module from the standard library, which covers merge, split, clean, and convert work without pandas.
   - Confirm the Python version the same way: `python3 --version` (3.12.3 on the drafting machine).

3. Inspect the inputs before the first full run.
   - List the folder and read the header and first few rows of two or three files, including one .csv if both formats are present. Headers that look the same on screen can differ in case, spacing, or column order, and the batch script treats a different header as a failed file.
   - Check for Excel lock files (names starting with `~$`) and hidden files. The script skips both. A `~$` file in the folder usually means the workbook is open somewhere; close it before the run so the saved file on disk is the current one.
   - Note the types each source gives you, because they differ by format. openpyxl returns typed values from .xlsx files (numbers as numbers, dates as dates). The `csv` module returns every field as a string, so a merged output can hold an integer id from one file and a string id from another unless the script converts fields on purpose. In the verified failure fixture in the Operating Rules section, the merged output held `1` (number, from .xlsx) and `'12'` (string, from .csv) in the same id column. Decide in the job note whether that matters for the job; if ids must compare equal across formats, convert them in the cleaning step.

4. Save the batch script and run it on a copy of the inputs first.
   - Save the script from the Operating Rules section as `batch_merge.py`, next to the job, not inside the input folder.
   - Adapt only the named constants at the top: `EXPECTED` (the header, in order) and `AMOUNT_COL` (the index of the totals column inside `EXPECTED`). If the job header has more columns, extend `EXPECTED` and the cleaning rules together, and re-run the fixture in the Operating Rules before running real files.
   - Run it against a copied folder first, not the originals. The script never writes to its inputs, but a test run on a copy catches header mismatches and bad rows before the real run, and the log from the copy is directly comparable to the log from the real run.
   - Run: `python3 batch_merge.py INPUT_DIR OUTPUT.xlsx`, redirecting output to a log file when the job needs a record: `python3 batch_merge.py INPUT_DIR OUTPUT.xlsx > batch_log.txt 2>&1`

5. Read the log line by line, then the reconciliation.
   - One `OK` or `ERROR` line per input file, in sorted filename order, then the batch totals. A file with no line was never seen: check the extension filter and the folder path before assuming the file was empty.
   - `rows_in` and `total_in` are summed from the files as they were read. The script then reopens the written output file and counts `rows_out` and `total_out` from the file on disk, not from memory. `reconcile: PASS` means the output file holds exactly the rows and total the inputs produced, within half a cent on the total. Reopening the output is the point: a count taken from the in-memory rows would pass even if the save had failed or written a different file.
   - Exit codes: 0 means reconcile PASS and no failed files. 1 means reconcile PASS but at least one file failed, so the output is a partial merge and the log says which files are missing from it. 2 means reconcile FAIL, no usable input files, or a usage error, and the output should not be used. Record the exit code with the log; a PASS with exit 1 is a different result from a PASS with exit 0.
   - Tolerances are guidance tied to the data, not a universal rule. Half a cent suits currency totals stored as decimal values in the source files. If amounts arrive as long decimal strings from .csv files, floating point addition can drift by tiny amounts across many rows; if a reconcile fails by a fraction of a cent on a large batch, check for drift before assuming rows were lost, and consider summing with integer cents or the `decimal` module for that job.

6. Handle failed files as their own sub-job.
   - The batch continues past a failed file by design, so one corrupt workbook does not kill a run over hundreds of files. The cost of that design is that failures are easy to ignore; treat every `ERROR` line and every `bad_rows` count as work left to do, not as noise.
   - Common failure causes, in the order to check them: the file is not really a workbook (a renamed .csv, a truncated download), the header differs (extra column, renamed column, header not in row 1), the file is password protected or still being written by another process.
   - Fix the cause at the source file when the source is wrong, or record a deliberate exclusion when the file does not belong in the batch. Then rerun the whole batch from the original inputs. Do not patch the merged output by hand to add a missing file: a hand patch has no log line and breaks the reconciliation.
   - Bad rows inside an otherwise good file are counted on that file's log line as `bad_rows=N`. Open the source, look at the rows whose amount does not parse, and decide whether they are data errors to fix or a format the cleaning rules should accept. Change the rules in the script, not the output.

7. Split one workbook into parts when the job calls for parts instead of a merge.
   Use the same primitives (verified on the drafting machine: splitting the 11 data rows of the worked fixture into chunks of 5 produced three files with 5, 5, and 1 data rows). Save as `split_rows.py` and adapt the chunk size and the grouping rule.
   - Split by row count: read all rows once, keep the header, write chunks of N data rows, each into a new workbook that repeats the header.
   - Split by a column value (one file per region, per month, per category): group rows by that column's value first, sort the group keys so reruns produce the same file set, and name each output after its key with characters that are unsafe in filenames replaced.
   - Reconcile a split the same way as a merge: the sum of data rows across all parts must equal the source data row count, and the sum of the amount column across all parts must equal the source total. Count the parts from the files on disk after writing them.
   ```python
   #!/usr/bin/env python3
   """Split a workbook into chunks of N data rows. Stdlib + openpyxl only."""
   import sys
   from pathlib import Path
   from openpyxl import Workbook, load_workbook

   src, out_dir, n = Path(sys.argv[1]), Path(sys.argv[2]), int(sys.argv[3])
   wb = load_workbook(src, data_only=True)
   rows = list(wb.active.iter_rows(values_only=True))
   wb.close()
   header, body = rows[0], rows[1:]
   out_dir.mkdir(parents=True, exist_ok=True)
   for i in range(0, len(body), n):
       out = Workbook()
       ows = out.active
       ows.append(header)
       for r in body[i:i + n]:
           ows.append(r)
       out.save(out_dir / f"part_{i // n + 1}.xlsx")
   print(f"source rows: {len(body)}")
   ```
   Run: `python3 split_rows.py merged.xlsx parts 5`

8. Convert between formats with the same read and write primitives.
   - .xlsx to .csv: read rows with openpyxl and write them with the `csv` module, writing an empty string for `None` cells so empty cells stay empty fields instead of the text `None`. Verified on the drafting machine against the fixture file a.xlsx, producing a header plus its 3 data rows.
   ```python
   #!/usr/bin/env python3
   """Convert the first sheet of an .xlsx file to .csv. Stdlib + openpyxl only."""
   import csv, sys
   from openpyxl import load_workbook

   wb = load_workbook(sys.argv[1], data_only=True, read_only=True)
   ws = wb.active
   with open(sys.argv[2], "w", newline="", encoding="utf-8") as f:
       w = csv.writer(f)
       for row in ws.iter_rows(values_only=True):
           w.writerow(["" if c is None else c for c in row])
   wb.close()
   ```
   Run: `python3 xlsx_to_csv.py input.xlsx output.csv`
   - .csv to .xlsx: read with the `csv` module and write with openpyxl. The batch merge script already does this for every .csv input, so a folder-level convert can run the merge and then split by source if separate outputs are needed. Remember the type difference from step 3: values read from .csv arrive as strings, so a converted workbook holds text where the original held numbers unless the conversion parses numbers and dates on purpose.
   - Format limits to state in the job note: .csv holds one sheet and no styling, so an .xlsx to .csv conversion keeps values only, one sheet at a time. Converting back does not restore the lost sheets or styling.

9. Apply the same transform to each file separately when the job is per-file, not a merge.
   - Keep the per-file shape of the batch script: discover files in sorted order, process one file inside a try block, log one line per file, continue past failures, and write each output under a new name or into an output folder. Never write the output over the input file in the same step that reads it; if a later step fails, the original is gone.
   - Derive output names mechanically, for example `name_clean.xlsx` in a sibling `cleaned` folder, so a rerun overwrites its own previous outputs and nothing else.
   - Reconcile per-file jobs by comparing the per-file counts: for each file, rows and totals before the transform against rows and totals after it, with the allowed difference written in the job note (for example: rows may only drop by the number of blank rows the cleaning removed, totals must not change at all).
   - A transform that adds a column, such as a computed flag, changes the header. Write the new expected header in the job note, because the next batch run over those outputs will fail its header check against the old `EXPECTED` value.

10. Make reruns safe and boring.
    - Sorted file order, a fixed header, and outputs that are rebuilt from the inputs on every run make the batch idempotent: running it twice over the same inputs gives the same output and the same log. The worked fixture in the Operating Rules was run twice in a row and produced identical logs and `reconcile: PASS` both times.
    - The script writes the output to a temporary file in the output folder and replaces the real output with it only after the write finishes, so an interrupted run leaves the previous good output in place instead of a half-written workbook.
    - Keep inputs read-only in practice: the script opens workbooks with `read_only=True` and `data_only=True` and never saves back to an input path. If a job needs modified inputs, write the modified files as a new batch output and keep the originals.
    - Save the log with the output. The log plus the job note from step 1 is the record that lets a later run explain any difference in the numbers.

## Output Contract

Deliver these items for a batch job:

1. The output workbook or workbooks, named as written in the job note, in the output folder named there.
2. The batch log: one `OK` or `ERROR` line per input file with its row count and total, then `files_ok`, `files_failed`, `rows_in`, `total_in`, `rows_out`, `total_out`, and the `reconcile` line, plus the run's exit code. On the worked fixture in the Operating Rules section the reconciliation values are: files_ok 4, files_failed 0, rows_in 11, total_in 84.25, rows_out 11, total_out 84.25, reconcile PASS, exit code 0.
3. A failure list: every file that logged `ERROR` and every `bad_rows` count, each with the cause found and the action taken (fixed at source, deliberately excluded, or still open). An empty failure list is stated as empty, not omitted.
4. The job note from Workflow step 1: input folder, expected header, totals column, cleaning rules, output names, and the reconciliation targets, so the run can be repeated or audited without reconstructing the decisions.

## Operating Rules

1. Script the repeat work, hand-check the exceptions. Files that follow the expected shape go through the script. Files that fail go through step 6. Hand-editing the merged output is not a step in any job.
2. Confirm every library imports cleanly before building a job on it. On the drafting machine, the pandas install fails on import with a numpy binary incompatibility (Workflow step 2), so this workflow uses openpyxl plus the `csv` module and does not depend on pandas. On a different machine the same import check decides; do not assume either library works until it has been imported.
3. Reconcile every run, from the output file on disk. Row count and the totals column, before and after, reopened after writing. A run with no reconciliation is reported as unverified, whatever the script printed while writing.
4. One bad file must not kill the batch, and a partial batch must not look complete. Per-file failures are caught, logged, and counted, the batch finishes the remaining files, and exit code 1 marks the partial result.
5. Inputs are never modified. Outputs are rebuilt from inputs on each run and written through a temporary file, so reruns are idempotent and interruptions leave the last good output standing.
6. Formulas are values the script cannot compute. openpyxl does not evaluate formulas. Loading a workbook with `data_only=True` returns the value a spreadsheet app last calculated and cached in the file; a formula cell in a file that no spreadsheet app has calculated holds no cached value and reads back as `None`. Verified on the drafting machine: a cell written as `=SUM(A1:A2)` by openpyxl read back as `None` with `data_only=True` and as the formula string `=SUM(A1:A2)` without it. Totals over a column of formulas are therefore only as good as the cached values in the source files; when totals matter, confirm the sources were calculated and saved by a spreadsheet app first, or compute the total from the underlying value columns in the script.
7. Round trips lose things. A load and save cycle with openpyxl keeps cell values, formulas, and common formatting, but features it does not model can be dropped or altered. Treat that as a risk to check, not a fixed list: before running a batch over workbooks that carry charts, images, or heavy styling, run one file through a load and save cycle and compare the result visually against the original. When styling must survive exactly, write values into a copy made by a spreadsheet app instead of round-tripping through openpyxl.
8. Keep the numbers honest in the log. Totals are printed to two decimals from the values actually read, bad rows are counted rather than silently dropped, and blank rows are the only rows skipped without a count. If a job needs different cleaning, change the script and the job note together, then rerun the fixture.
9. Use the batch merge script below for merge jobs, and as the starting point for per-file transform jobs. Save it as `batch_merge.py`, prepare a folder of inputs in the format shown under it, then run it. Report counts from its output, not from a hand count.

### Batch merge (batch_merge.py)

Input format: a folder of .xlsx and .csv files. Every file has the header `id,item,amount` in row 1 (case-insensitive, surrounding spaces ignored). For .xlsx files only the active sheet is read. Amounts in the worked fixture are multiples of 0.25, so the totals below are exact.

Worked fixture (the files used for the expected output below):

- a.xlsx: rows (1, Apples, 10.50), (2, Bananas, 5.25), (3, Cherries, 7.00)
- b.xlsx: rows (4, Dates, 12.00), (5, Eggs, 3.50)
- c.xlsx: rows (6, Flour, 8.25), (7, Grapes, 9.75), (8, Honey, 20.00), (9, Icing, 1.00)
- d.csv: rows (10, Jam, 4.50), (11, Kale, 2.50)

Run: `python3 batch_merge.py fixture merged.xlsx`

Expected output for that fixture (reproduced exactly on the drafting machine, twice in a row, exit code 0 both times):

```text
OK a.xlsx: rows=3 total=22.75
OK b.xlsx: rows=2 total=15.50
OK c.xlsx: rows=4 total=39.00
OK d.csv: rows=2 total=7.00
files_ok: 4
files_failed: 0
rows_in: 11
total_in: 84.25
rows_out: 11
total_out: 84.25
reconcile: PASS
```

Failure behaviour, also reproduced on the drafting machine. The folder held a.xlsx from the fixture, a broken.xlsx containing plain text instead of a workbook, a messy.csv with the right header plus one row with the amount `oops`, one blank row, and one row with spaces around the item, and a wrong.csv with the header `x,y,z`. The run exited with code 1:

```text
OK a.xlsx: rows=3 total=22.75
ERROR broken.xlsx: File is not a zip file
OK messy.csv: rows=2 total=7.00 bad_rows=1
ERROR wrong.csv: header ['x', 'y', 'z'] does not match expected ['id', 'item', 'amount']
files_ok: 2
files_failed: 2
rows_in: 5
total_in: 29.75
rows_out: 5
total_out: 29.75
reconcile: PASS
```

The `ERROR` wording after the filename comes from the library that tried to open the file, so the exact text varies with the failure. The messy.csv line shows the cleaning rules at work: the spaced item was stripped and kept, the unparseable amount was skipped and counted in `bad_rows`, and the blank row was skipped.

batch_merge.py (Python 3, standard library plus openpyxl). Save this code as batch_merge.py:

```python
#!/usr/bin/env python3
"""Merge a folder of .xlsx and .csv files into one workbook. Stdlib + openpyxl only.

Every input file must share the header id,item,amount (case-insensitive,
surrounding spaces ignored). The active worksheet of each .xlsx file is read.
Cleaning: text cells are stripped of surrounding spaces, fully blank rows
are skipped, and a row whose amount does not parse as a number is skipped
and counted as a bad row for that file.

A file that cannot be read, or whose header does not match, is logged as
ERROR and the batch continues with the remaining files.

Usage: python3 batch_merge.py INPUT_DIR OUTPUT.xlsx
Exit codes: 0 = reconcile PASS and no failed files,
1 = reconcile PASS but at least one file failed,
2 = reconcile FAIL, no usable input files, or a usage error.
"""
import csv
import os
import sys
from pathlib import Path

from openpyxl import Workbook, load_workbook

EXPECTED = ["id", "item", "amount"]
AMOUNT_COL = 2  # index of "amount" in EXPECTED


def norm_header(row):
    return [str(c).strip().lower() if c is not None else "" for c in row]


def clean_row(row):
    out = []
    for c in row:
        out.append(c.strip() if isinstance(c, str) else c)
    return out


def read_xlsx(path):
    wb = load_workbook(path, data_only=True, read_only=True)
    try:
        ws = wb.active
        return [tuple(r) for r in ws.iter_rows(values_only=True)]
    finally:
        wb.close()


def read_csv(path):
    with open(path, newline="", encoding="utf-8-sig") as f:
        return [tuple(r) for r in csv.reader(f)]


def process_file(path):
    """Return (rows, total, bad_rows). Raise ValueError/OSError on file failure."""
    rows_raw = read_xlsx(path) if path.suffix.lower() == ".xlsx" else read_csv(path)
    if not rows_raw:
        raise ValueError("file is empty, no header row")
    header = norm_header(rows_raw[0])
    if header != EXPECTED:
        raise ValueError(f"header {header} does not match expected {EXPECTED}")
    rows, total, bad = [], 0.0, 0
    for raw in rows_raw[1:]:
        if raw is None or all(c is None or (isinstance(c, str) and c.strip() == "") for c in raw):
            continue
        if len(raw) != len(EXPECTED):
            bad += 1
            continue
        row = clean_row(raw)
        try:
            amount = float(row[AMOUNT_COL])
        except (TypeError, ValueError):
            bad += 1
            continue
        row[AMOUNT_COL] = amount
        rows.append(row)
        total += amount
    return rows, total, bad


def reconcile(output_path, rows_in, total_in):
    wb = load_workbook(output_path, data_only=True, read_only=True)
    try:
        ws = wb.active
        data = [r for r in ws.iter_rows(values_only=True)]
    finally:
        wb.close()
    body = data[1:] if data else []
    rows_out = len(body)
    total_out = sum(float(r[AMOUNT_COL]) for r in body)
    ok = rows_out == rows_in and abs(total_out - total_in) < 0.005
    return rows_out, total_out, ok


def main():
    if len(sys.argv) != 3:
        print("usage: python3 batch_merge.py INPUT_DIR OUTPUT.xlsx", file=sys.stderr)
        return 2
    in_dir = Path(sys.argv[1])
    out_path = Path(sys.argv[2])
    if not in_dir.is_dir():
        print(f"error: input dir {in_dir} does not exist", file=sys.stderr)
        return 2
    out_resolved = out_path.resolve()
    files = []
    for p in sorted(in_dir.iterdir(), key=lambda x: x.name.lower()):
        if p.suffix.lower() not in (".xlsx", ".csv"):
            continue
        if p.name.startswith("~$") or p.name.startswith("."):
            continue
        if p.is_file() and p.resolve() != out_resolved:
            files.append(p)
    if not files:
        print("error: no .xlsx or .csv files found", file=sys.stderr)
        return 2

    all_rows, rows_in, total_in = [], 0, 0.0
    files_ok, files_failed = 0, 0
    for p in files:
        try:
            rows, total, bad = process_file(p)
        except Exception as e:
            print(f"ERROR {p.name}: {e}")
            files_failed += 1
            continue
        suffix = f" bad_rows={bad}" if bad else ""
        print(f"OK {p.name}: rows={len(rows)} total={total:.2f}{suffix}")
        all_rows.extend(rows)
        rows_in += len(rows)
        total_in += total
        files_ok += 1

    print(f"files_ok: {files_ok}")
    print(f"files_failed: {files_failed}")
    print(f"rows_in: {rows_in}")
    print(f"total_in: {total_in:.2f}")
    if files_ok == 0:
        print("reconcile: FAIL (no usable input files)")
        return 2

    out_path.parent.mkdir(parents=True, exist_ok=True)
    wb = Workbook()
    ws = wb.active
    ws.title = "merged"
    ws.append(EXPECTED)
    for r in all_rows:
        ws.append(r)
    tmp_path = out_path.with_suffix(".tmp.xlsx")
    wb.save(tmp_path)
    os.replace(tmp_path, out_path)

    rows_out, total_out, ok = reconcile(out_path, rows_in, total_in)
    print(f"rows_out: {rows_out}")
    print(f"total_out: {total_out:.2f}")
    print(f"reconcile: {'PASS' if ok else 'FAIL'}")
    if not ok:
        return 2
    return 1 if files_failed else 0


if __name__ == "__main__":
    raise SystemExit(main())
```