Python Doc & Data Automation

Generate One DOCX per Row from Excel

Three hundred rows in a spreadsheet, one letter each. The first attempt produces a directory that does not match:

df = pd.read_excel("in/recipients.xlsx")
for _, row in df.iterrows():
    template = DocxTemplate("templates/letter.docx")
    template.render(row.to_dict())
    template.save(f"out/{row['Name']}.docx")

The output has 287 files for 300 rows, several dates read 2026-03-04 00:00:00, amounts appear as 1234.5600000000002, and one filename contains a slash that quietly wrote into a subdirectory that did not exist.

Root Cause

Three separate problems overlap. First, row.to_dict() passes pandas types straight into the template: a Timestamp renders with its time component, a float renders with full binary precision, and NaN renders as the string nan rather than an empty cell. Second, filenames built from a data column collide whenever two rows share a name and break whenever a value contains /, \, : or a trailing dot — 300 rows became 287 files because thirteen overwrote earlier ones. Third, the column headings in the spreadsheet rarely match the placeholder names in the template, so a heading with a trailing space or different capitalisation silently renders as blank. Each is easy to fix; together they account for nearly every bad mail merge.

From spreadsheet row to delivered document Read the spreadsheet with all columns as strings where possible, then normalise the headings to the names the template expects. Coerce each field to a presentation-ready string, formatting dates and money explicitly and turning missing values into blanks. Validate that required fields are present, quarantining rows that are not. Render the template per row. Write to a filename built from a stable identifier and sanitised, with a counter suffix if it already exists. Read the spreadsheet keep raw values Normalise headings match template names Coerce types dates, money, blanks Validate required fields quarantine bad rows Render per row one document each Write a safe filename unique, sanitised

Minimal Diagnostic

Compare what the template asks for with what the spreadsheet offers, before rendering anything.

# pip install pandas openpyxl docxtpl
import re
from pathlib import Path
import pandas as pd
from docxtpl import DocxTemplate

XLSX, TEMPLATE = Path("in/recipients.xlsx"), Path("templates/letter.docx")

def normalise(name: str) -> str:
    return re.sub(r"[^a-z0-9]+", "_", str(name).strip().lower()).strip("_")

def merge_report() -> None:
    df = pd.read_excel(XLSX)
    wanted = DocxTemplate(TEMPLATE).get_undeclared_template_variables()
    available = {normalise(column): column for column in df.columns}
    print(f"rows: {len(df)}  columns: {list(df.columns)}")
    for name in sorted(wanted):
        source = available.get(normalise(name))
        print(f"  {name:<20} <- {source!r}" if source else f"  {name:<20} <- MISSING")
    for column in df.columns:
        sample = df[column].dropna().head(1)
        kind = type(sample.iloc[0]).__name__ if len(sample) else "empty"
        blanks = int(df[column].isna().sum())
        print(f"  column {column!r}: {kind}, {blanks} blank(s)")
    duplicates = df.duplicated(subset=[df.columns[0]], keep=False).sum()
    print(f"  duplicate values in {df.columns[0]!r}: {duplicates}")

if __name__ == "__main__":
    merge_report()

rows: 300  columns: ['Name ', 'Account Ref', 'Amount Due', 'Due Date', 'Region']
  account_ref          <- 'Account Ref'
  amount_due           <- 'Amount Due'
  customer_name        <- MISSING
  due_date             <- 'Due Date'
  column 'Name ': str, 0 blank(s)
  column 'Amount Due': float, 4 blank(s)
  column 'Due Date': Timestamp, 0 blank(s)
  duplicate values in 'Name ': 13

Everything the merge needs to know is here: one placeholder has no column, the name column has a trailing space, four amounts are blank, and thirteen names are duplicated — the thirteen files that went missing.

Fix: Normalise, Coerce, Then Render

Map headings to placeholder names, format every value explicitly, and build filenames that cannot collide.

# pip install pandas openpyxl docxtpl
import re, unicodedata
from dataclasses import dataclass
from pathlib import Path
import pandas as pd
from docxtpl import DocxTemplate

COLUMN_MAP = {"name": "customer_name", "account_ref": "account_ref",     # changed: explicit mapping
              "amount_due": "amount_due", "due_date": "due_date", "region": "region"}
REQUIRED = {"customer_name", "account_ref", "due_date"}

def normalise(name: str) -> str:
    return re.sub(r"[^a-z0-9]+", "_", str(name).strip().lower()).strip("_")

def safe_filename(value: str, maxlen: int = 80) -> str:
    text = unicodedata.normalize("NFKD", str(value))
    text = re.sub(r"[^\w\s.-]", "", text, flags=re.UNICODE).strip().strip(".")
    text = re.sub(r"[\s_]+", "-", text).strip("-").lower()
    return (text or "untitled")[:maxlen]                                  # changed: never empty, never a path

def present(value) -> str:
    """Turn a spreadsheet cell into text fit for a document."""
    if value is None or (isinstance(value, float) and pd.isna(value)) or value is pd.NaT:
        return ""                                                          # changed: blank, not "nan"
    if isinstance(value, pd.Timestamp):
        return value.strftime("%-d %B %Y")                                 # changed: no 00:00:00
    if isinstance(value, float) and value.is_integer():
        return str(int(value))
    if isinstance(value, float):
        return f"{value:,.2f}"                                             # changed: no binary noise
    return str(value).strip()

@dataclass
class MergeOutcome:
    written: list[Path]
    skipped: list[tuple[int, str]]

def merge(xlsx: Path, template_path: Path, outdir: Path) -> MergeOutcome:
    df = pd.read_excel(xlsx)
    df.columns = [COLUMN_MAP.get(normalise(c), normalise(c)) for c in df.columns]
    outdir.mkdir(parents=True, exist_ok=True)
    written, skipped, used = [], [], set()
    for position, row in enumerate(df.to_dict("records"), start=2):        # start=2: spreadsheet row number
        context = {key: present(value) for key, value in row.items()}
        empty = sorted(field for field in REQUIRED if not context.get(field))
        if empty:
            skipped.append((position, f"missing {', '.join(empty)}"))
            continue
        stem = f"{safe_filename(context['account_ref'])}-{safe_filename(context['customer_name'])}"
        candidate, counter = stem, 1
        while candidate in used:                                           # changed: collisions impossible
            counter += 1
            candidate = f"{stem}-{counter}"
        used.add(candidate)
        template = DocxTemplate(template_path)                             # changed: fresh per row
        template.render(context)
        destination = outdir / f"{candidate}.docx"
        template.save(destination)
        written.append(destination)
    return MergeOutcome(written, skipped)

if __name__ == "__main__":
    outcome = merge(Path("in/recipients.xlsx"), Path("templates/letter.docx"), Path("out/letters"))
    print(f"{len(outcome.written)} written, {len(outcome.skipped)} skipped")
    for row_number, reason in outcome.skipped[:10]:
        print(f"  row {row_number}: {reason}")

Reloading the template inside the loop matters: DocxTemplate mutates its document when rendering, so reusing one instance renders the second row into the already-rendered first. Starting the row counter at 2 makes the skip messages match what the spreadsheet shows, which is what the person fixing the data needs.

What each spreadsheet type needs before it reaches the template A pandas Timestamp renders with a midnight time component and needs explicit date formatting. A float amount renders with full binary precision and needs currency formatting. A blank numeric cell becomes NaN and renders as the text nan rather than an empty space. A whole number stored as a float gains a trailing point zero. A boolean renders as True or False rather than words a reader expects. Every one of these is fixed by formatting in Python before rendering. Cell value Renders as Should be Timestamp 2026-03-04 00:00:00 4 March 2026 float 1234.56 1234.5600000000002 1,234.56 blank numeric nan (empty) float 7.0 7.0 7 True True Yes leading zeros 7 from 007 007 as text

Variant Fix 1: Reading the Spreadsheet Without Losing Data

Some columns must never be parsed. Account references with leading zeros, postcodes and phone numbers all lose information when pandas infers a numeric type:

# pip install pandas openpyxl
import pandas as pd

TEXT_COLUMNS = ["Account Ref", "Postcode", "Phone"]

def read_rows(xlsx, sheet: str | int = 0) -> pd.DataFrame:
    df = pd.read_excel(
        xlsx, sheet_name=sheet,
        dtype={column: "string" for column in TEXT_COLUMNS},   # keep as typed, no inference
        keep_default_na=False, na_values=[""],                 # "NA" stays the literal text NA
    )
    return df.loc[:, ~df.columns.str.startswith("Unnamed")]    # drop stray empty columns

keep_default_na=False is the one that surprises people: by default pandas treats NA, N/A, null and None as missing, so a region genuinely called NA — North America — becomes a blank. Restricting missing values to the empty string keeps the data as the spreadsheet author wrote it. Dropping Unnamed: columns removes the empty trailing columns Excel creates when someone formats past the data.

Variant Fix 2: One Merged Document Instead of Many

Sometimes the output should be a single file — 300 letters to print in one go:

# pip install docxtpl docxcompose python-docx
from docx import Document
from docxcompose.composer import Composer

def merge_into_one(parts: list[Path], out: Path) -> Path:
    base = Document(parts[0])
    composer = Composer(base)
    for part in parts[1:]:
        base.add_page_break()                     # each letter starts a new page
        composer.append(Document(part))
    composer.save(out)
    return out

docxcompose merges styles and numbering properly, which plain concatenation does not — appending documents by copying body elements produces a file whose second letter inherits the first's list counters and whose styles collide. For a print run, one file of 300 pages also removes the risk of a printer reordering separate jobs.

Choosing the output shape If each document goes to a different recipient, produce one file per row named from a stable identifier. If the documents will be printed together, merge them into one file with page breaks so the print job stays in order. If they will be emailed or archived, convert each to PDF, which fixes the layout and prevents accidental edits. If they will be handed to another team, a zip archive with a manifest listing row number, recipient and filename saves a round of questions. How will the documents be used? decide before generating one per recipient One DOCX per row stable printed together Merge with docxcompose page break between emailed or archived Convert to PDF fixed layout handed over Zip plus manifest row

Keeping a Manifest

A merge that produces 300 files should also produce a record of what it did. Without one, the only way to answer "did row 214 go out?" is to guess from filenames.

# stdlib only
import csv
from pathlib import Path

def write_manifest(outcome: MergeOutcome, path: Path) -> None:
    with path.open("w", newline="", encoding="utf-8") as handle:
        writer = csv.writer(handle)
        writer.writerow(["row", "status", "file_or_reason"])
        for destination in outcome.written:
            writer.writerow(["", "written", destination.name])
        for row_number, reason in outcome.skipped:
            writer.writerow([row_number, "skipped", reason])

The manifest is also what makes the job re-runnable. Fixing four bad rows in the spreadsheet and re-running the whole merge regenerates 300 files, most of them identical; filtering to the rows the manifest recorded as skipped regenerates four. On a job that also converts to PDF, that difference is minutes rather than an hour.

Handling Rows That Should Not Produce a Document

Not every row in a supplied spreadsheet is a document. Totals rows, notes the author left at the bottom, and rows whose status column says the account was closed all look like data to read_excel. Filtering them explicitly is better than hoping the required-field check catches them:

# pip install pandas
import pandas as pd

def eligible(df: pd.DataFrame) -> pd.DataFrame:
    frame = df.copy()
    frame = frame[frame["account_ref"].notna()]                   # a real row has a reference
    frame = frame[~frame["customer_name"].str.contains(r"^(total|subtotal)\b", case=False, na=False)]
    if "status" in frame.columns:
        frame = frame[frame["status"].str.lower() != "closed"]
    return frame

Each filter should be visible in the run log with a count, because a filter that silently removes two hundred rows is indistinguishable from a merge that worked. Print how many rows each step dropped and the total that remain; anyone reading the log can then tell a 300-row spreadsheet producing 4 documents from one producing 296.

Verification

Check the outputs against the inputs before anything is sent.

# pip install pandas python-docx
from pathlib import Path
import pandas as pd
from docx import Document

def verify_merge(xlsx: Path, outdir: Path, skipped: int) -> None:
    expected = len(pd.read_excel(xlsx)) - skipped
    files = sorted(outdir.glob("*.docx"))
    assert len(files) == expected, f"{len(files)} file(s), expected {expected}"
    assert len({f.name for f in files}) == len(files), "duplicate filenames — collision suffix failed"
    problems = []
    for path in files:
        text = "\n".join(p.text for p in Document(path).paragraphs)
        if "{{" in text or "{%" in text:
            problems.append(f"{path.name}: unrendered tag")
        if "nan" in text.split() or "NaT" in text:
            problems.append(f"{path.name}: missing value leaked as text")
        if "00:00:00" in text:
            problems.append(f"{path.name}: unformatted timestamp")
        if not text.strip():
            problems.append(f"{path.name}: empty document")
    assert not problems, "\n".join(problems[:10])
    print(f"{len(files)} document(s) verified, {skipped} row(s) skipped")

if __name__ == "__main__":
    verify_merge(Path("in/recipients.xlsx"), Path("out/letters"), skipped=4)

Scanning for nan, NaT and 00:00:00 catches the type problems that produce a valid document containing nonsense. The file-count assertion catches the collision problem, which is invisible any other way — 287 files in a directory look exactly like a finished job.

FAQ

Should I use iterrows or to_dict("records")?to_dict("records") — it is faster and gives plain Python values rather than a Series, which avoids a class of pandas type surprises.

How do I handle several sheets? Pass sheet_name=None to get a dict of frames, then merge each with its own template if the shapes differ.

Can the template vary per row? Yes — pick the template path from a column, and validate each one's variables at startup rather than per row.

How do I convert all the outputs to PDF? Run the batch conversion described in batch convert DOCX to PDF in parallel.

Part of Dynamic Mail Merge with Python.