---
name: fill-template
description: >
  Bulk-fill ONE master template — a Word (.docx) letter/form or an Excel (.xlsx)
  form — from a data table, one filled file per row (mail-merge). Also the
  reverse: extract a reusable tokenised .docx template + data-table skeleton
  from a filled example document, confirm the mapping, then fill. Use whenever
  the user wants to "fill in this letter for each person", "mail merge",
  "generate letters for this list", "turn this filled letter into a template",
  or hands over a template plus a list. The skill reads the master, proposes a
  TOKENISED version (varying parts become {{tokens}}), confirms the mapping,
  then regenerates one output per data row, preserving the master's layout
  exactly. Data is an .xlsx or .csv (one row per output); outputs are one named
  file per record. Never invents: a token with no data is a VISIBLE flag, never
  a silent blank. Runs fully local and generates files only — it does not send,
  post or sign. Extract is .docx only this round (not .xlsx). Not a substitute
  for a hand-crafted single letter.

license: MIT
metadata:
  version: 1.0.0
  author: moonlight-lupin
  platforms: [linux, macos, windows]
---

# Fill Template (mail-merge)

Turn **one master template** + **a data table** into **many filled copies** — one per row — swapping
only the parts that vary and leaving the master's layout and styling untouched. Document generation
only: nothing is sent, posted or signed.

## Scope and routing

Use this skill when the user has **one** template and **a list**, and wants a filled copy per row;
or when they have a **filled/example .docx** and want a reusable tokenised template derived from it
(`extract_template`). Do **not** use it to produce a single bespoke letter (just edit one document),
or where the host environment mandates a different document pipeline. Extract of `.xlsx` forms is
out of scope this round.

## Inputs the user provides

1. **The master template** — a `.docx` letter or form, **or** a `.xlsx` form. One master per run.
   This is the source of truth for layout and styling.
2. **The data table** — a `.xlsx` or `.csv`, **one row per output**, with a header row. Columns supply
   the values that vary (names, amounts, dates, references…).
3. **(Optional) a naming pattern** — how each output file is named, e.g. `Letter_{Name}`. Defaults to
   `<template>_<row-number>`.

## Workflow (do this, in order)

If the user hands you a **filled example** rather than a master, run **extract first** (step 0), show
`ext["mapping"]`, and wait for confirmation before generating. Otherwise start at step 1.

0. **Extract from a filled .docx (optional reverse path).**
   `extract_template(src, out_dir, name=None)` writes `<name>_tokenised.docx` and `<name>_data.csv`
   (name defaults to the source stem) and returns a report. Varying values become `{{Token}}` using
   the same run-aware replacement as `tokenise`. The CSV has one row per repeating instance. Show
   `report["mapping"]` (literal → token → where) and any `report["uncertain"]` entries — those spans
   were left literal rather than guessed. Token names come only from five typed shapes:
   after `Dear ` → `Recipient`; `$…` → `Amount`; `INV-123` → `InvoiceRef`; dates like
   `12 Mar 2026` → `Date`; `ACME-0042` → `AccountRef`. Collisions become `Amount2`, ….
   Untyped variation is left literal — a generic `Value1`/`Value2` fallback is **not**
   implemented; fall back to manual `tokenise` for those fields. Then continue from
   step 3 with the extracted template and skeleton, or let the user edit the mapping
   and re-tokenise.
   ```python
   from fill_template import (
       read_content, tokenise, tokens_in, load_rows, generate, extract_template,
   )

   ext = extract_template("filled_letters.docx", "extracted")
   print(ext["mapping"])       # confirm-before-reuse digest
   print(ext["uncertain"])     # left literal; do not invent
   ```

1. **Analyse the master.** Read it with `read_content(path)` and identify the parts that **vary** row
   to row — names, salutations, amounts, dates, reference numbers — versus the boilerplate that stays
   fixed. `read_content` shows repeated paragraph text once with a `(×N)` count — that's how many
   places `tokenise` will replace it (e.g. a name in both the body and the header). See
   `references/tokenising-guide.md` for what to tokenise and how to name tokens well.
2. **Propose a tokenised template.** Build a `mapping` of each varying phrase → a token name, and
   present it to the user as a plain list ("`Ms Jordan Lee` → `{{Name}}`, `$1,000,000` →
   `{{Amount}}`, …"). Token names should match the data file's column headers where possible — that
   makes the mapping automatic.
3. **Confirm with the user.** Show the proposed tokens (and, if helpful, save the tokenised template
   and show its text) and **wait for explicit confirmation** before generating anything. This is the
   review gate — get the template right once, then fan it out.
   ```python
   from fill_template import (
       read_content, tokenise, tokens_in, load_rows, generate, extract_template,
   )

   print(read_content("Letter_master.docx"))                       # step 1

   rep = tokenise("Letter_master.docx",                            # step 2
                  "Letter_tokenised.docx",
                  [{"find": "Ms Jordan Lee", "token": "Name"},
                   {"find": "$1,000,000",    "token": "Amount"},
                   {"find": "01 Jul 2026",   "token": "EffectiveDate"},
                   {"find": "REF-0001",      "token": "Reference"}])
   # rep["not_found"] lists any phrase that wasn't located — fix those before generating.
   print(tokens_in("Letter_tokenised.docx"))
   ```
   `tokenise` finds each exact phrase and replaces it with `{{Token}}`, **preserving the formatting of
   the run/cell it sits in** (a bold figure stays bold). It reports a hit count per token and flags any
   phrase it could not find, so a typo in the `find` text is caught before you generate 200 letters.
4. **Load the data and check the mapping.** `headers, rows = load_rows("recipients.xlsx")`. The
   token→column map defaults to *token name == column header* (case-insensitive); override any that
   differ. Any template token with no column, or a mapped column blank for a given row, becomes a
   visible `«MISSING: Token»` flag — never a guess.
5. **Generate one file per row.**
   ```python
   report = generate(
       "Letter_tokenised.docx", rows,
       token_to_column={"Name": "Recipient"},   # only the ones whose token != column
       outdir="out",
       name_pattern="Letter_{Recipient}",
   )
   ```
   `report` lists every file written, the rows skipped (e.g. a name-pattern column missing from the
   data), any `unmapped_tokens`, and per-file `missing` tokens.
6. **Report back honestly.** Tell the user how many files were produced and **where**, and surface
   every `missing` flag and `unmapped_tokens` entry so blanks are dealt with before the batch is used.
   Outputs are **drafts for a person to review** before they go out.

## How tokenising and replacement work

- **Token syntax** is `{{TokenName}}` (spaces inside the braces are ignored).
- **`.docx`** — replacement is run-aware: the value lands in the run where the token begins (inheriting
  its formatting) and any other runs the token spans are trimmed; runs outside the token are left
  exactly as they were. Body text, tables (including nested), and header/footer text are all covered.
- **`.xlsx`** — tokens inside any cell on any sheet are replaced; all other cell content, formulas and
  formatting are preserved (openpyxl). Put a token anywhere the value should appear, e.g. cell `B5` =
  `{{Name}}`.
- **Dates** in the data render as **DD MMM YYYY** by default; whole numbers drop a trailing `.0`. For
  currency or specific number formats, **format the column in the data file** — the helper inserts the
  value as supplied.
- **Never invent.** A token with no value for a row is written as `«MISSING: Token»` and recorded in
  the report. Fix the data (or the mapping) and re-run; don't hand-edit blanks into invented values.

## Safety

- **Files only.** Generates documents; never sends, posts, emails, signs or files anything.
- **One master per run.** Don't mix two different templates in a single batch.
- **Match the master.** Preserve the supplied template's layout and styling; only swap tokens.
- **Confirm before fan-out.** Get explicit sign-off on the tokenised template and mapping before
  generating the batch.
- **Surface every blank.** Report all `«MISSING:…»` flags and unmapped tokens; never paper over them.

## Files

- `scripts/fill_template.py` — the engine: `extract_template` (filled .docx → template + skeleton) ·
  `read_content` (analyse) · `tokenise` / `tokens_in` · `load_rows` (.xlsx/.csv) · `generate` (one
  file per row + report). `generate` accepts a list of row-dicts or the `(headers, rows)` tuple from
  `load_rows`.
- `references/tokenising-guide.md` — how to choose and name tokens, letters vs forms, the confirm
  step, and the MISSING-flag rule.
- `examples/example-run.md` — a worked end-to-end run (bring your own master + data; no binaries are
  shipped).

## Principles

- **Drafts, not advice** — outputs are drafts for a person to review, not finished correspondence.
- **Never invent** — a missing value is a visible `«MISSING: Token»` flag, never a silent blank or a
  guess.
- **Deterministic where it counts** — tokenising, mapping and generation are deterministic and
  reported; the LLM only proposes the token list for the user to confirm.
- **Honesty and calibration** — surface every missing/unmapped token and where files were written.
- **Workspace hygiene** — write outputs to a clear folder; keep the master, the filled source, and
  the data untouched. `extract_template` never writes back to its input file.

## Data handling

Runs **fully local** — the template and data stay on your machine and nothing crosses to an external
tool, so names, amounts and references are handled in place. If any step *would* send data to an
external or third-party service, de-identify sensitive personal data first and confirm the egress with
the user. This skill itself needs no network and no credentials.

## Pitfalls

1. **`find` text must be exact** — copy the phrase verbatim (punctuation, currency symbols); check the
   `not_found` list before generating.
2. **Token == column header** wherever possible — then the mapping is automatic; otherwise pass
   `token_to_column`.
3. **Confirm before fan-out** — fixing the template once beats re-issuing a whole batch.
4. **MISSING flags are not failures to hide** — surface them; fix the data/mapping and re-run.
5. **The data table is a separate file** from an `.xlsx` form template — don't confuse the two.

## Verification checklist

- [ ] Master analysed; varying parts identified vs boilerplate. Or extract_template run on a
  filled .docx and mapping confirmed.
- [ ] Token list proposed and **confirmed by the user** before generating.
- [ ] `not_found` list from `tokenise` is empty (or resolved). `uncertain` from extract surfaced.
- [ ] Token→column mapping checked; defaults verified.
- [ ] Batch generated; file count and output folder reported.
- [ ] Every `«MISSING:…»` and `unmapped_tokens` entry surfaced to the user.

## Requirements

- Python 3.8+
- `pip install python-docx openpyxl`
- No network access and no credentials — fully local file I/O.