Samlify

One Word document per row in Excel

By the Samlify team · · 6 min read · Word, Excel

Use Word's mail merge if the letters go to a printer or an inbox. Microsoft's pages list print, email and "Edit Individual Documents" as the endings, and none says each letter is saved as its own file. For one .docx and one PDF per row, use the Python script below. We ran it on sample data we made ourselves.

Step 1: pick the route

Microsoft's page on Finish & Merge lists "Edit Individual Documents," "Print Documents" and "Send Email Messages" on Windows. On Mac it lists Print Documents and Edit Individual Documents. The page does not say that each letter is saved as its own file.

Use the merge for a printer or an inbox. Use the script when the files themselves are the product, for example a contract or a certificate saved as 042 Ada Lind.docx.

Step 2: in Word, connect the sheet

Microsoft's Excel mail merge page applies to Word for Microsoft 365, 2024, 2021 and 2019 on Windows and Mac, and to Word 2016. It asks for this:

  • All data is on the first sheet of the workbook.
  • Percentages, currencies and postal codes are "correctly formatted in the spreadsheet so that Word can properly read their values."
  • All changes to the sheet are finished before you connect it.

Then use Edit Recipient List to clear anyone who should not get a letter. Add fields with Address Block, Greeting Line or Insert Merge Field. Use Preview Results with Next and Previous. Finish with Finish & Merge. We did not run these Word steps. They come from Microsoft's page.

Step 3: in Word, add text only some rows get

Microsoft's rules page gives the steps. Put the cursor where the text goes. Choose Mailings, Rules, If...Then...Else. Pick the field name and comparison. Type the value in "Compare to", the text for when the condition is met, and the text for when it is not. Skip Record If, in the same menu, leaves a row out of the merge.

Step 4: in Word, fix dates and prices

Merged dates and prices can look different from the sheet. Microsoft's article on this says the cause is that "the data is displayed in its native, stored format". The article is written for Word 2010. It gives two examples of a picture switch inside the field code:

{MERGEFIELD number \# $####,0.0}
{MERGEFIELD date \@ "MMMM d, yyyy"}

The article says "The picture switch is case-sensitive" and that you must put the picture string in quotation marks. Do this and also format the cells in Excel.

Step 5: for the script, install the libraries

You need Python 3 and a terminal. In an empty folder:

python3 -m venv venv
source venv/bin/activate            # on Windows: venv\Scripts\activate
pip install docxtpl openpyxl

docxtpl fills a Word file that you design in Word. It uses python-docx and Jinja2. We ran docxtpl 0.20.2, openpyxl 3.1.5 and Jinja2 3.1.6 on Python 3. For PDFs the script calls LibreOffice (soffice). Without it, leave out --pdf and you still get the Word files.

Step 6: write the template in Word

Write the letter once in Word and type the placeholders where values go. Each line is its own paragraph:

{{ company }}
Dear {{ name }},
Your {{ plan }} plan renews on {{ renewal }}. The list price is {{ amount }}.
{%p if discount %}
We apply a {{ discount }} discount, so you pay {{ total }}.
{%p endif %}
{%p if overdue %}
Our records show an unpaid invoice.
{%p endif %}
Billing ZIP code on file: {{ zip }}.
Kind regards,
Example Software

The docxtpl docs say the first and last paragraphs holding {%p ... %} tags "will never appear in generated docx", so the paragraphs between them show only when the condition is true. The docs also say these tags "are only to be used inside the same run of a same paragraph". Type each tag in one go and do not bold half of it. For table rows, the docs list {%tr ... %}.

Step 7: run the script

Save this as merge.py. It reads the first sheet, or the one named with --sheet. It makes one file per row, named with the row number so two people with the same name get two files. It skips rows whose file already exists. A bad row goes into report.csv with the reason, and the other rows still get their files.

#!/usr/bin/env python3
"""Make one Word document per spreadsheet row from a docxtpl template, optionally with PDFs.

Usage: python merge.py sample.xlsx template.docx out/ [--sheet Customers] [--force] [--pdf]
Re-runs skip rows whose file already exists; --force rebuilds them. A row with a problem
is reported in out/report.csv and skipped; the other rows still get their documents.
"""
import argparse, csv, datetime as dt, os, re, shutil, subprocess, sys, tempfile
from pathlib import Path

from docxtpl import DocxTemplate
from jinja2 import Environment, StrictUndefined
from openpyxl import load_workbook

REQUIRED = ["Name", "Company", "Plan", "Renewal", "Amount", "Total"]
FILE_NAME = "{row:03d} {Name} - {Company}"  # the row number keeps two "Ada Lind" rows apart


def money(v):
    return f"${v:,.2f}"


def build_context(r):
    """Turn one sheet row into the values the template prints. Raises ValueError on bad data."""
    for col in REQUIRED:
        if r.get(col) is None or str(r[col]).strip() == "":
            raise ValueError(f"empty {col}" + (" (formula never calculated: open and save the"
                             " file in Excel or LibreOffice first)" if col == "Total" else ""))
    renewal = r["Renewal"]
    if not isinstance(renewal, (dt.date, dt.datetime)):
        raise ValueError(f"Renewal is not a date: {renewal!r}")
    for col in ("Amount", "Total"):
        if not isinstance(r[col], (int, float)):
            raise ValueError(f"{col} is not a number: {r[col]!r}")
    zip_code = r.get("ZIP")
    if isinstance(zip_code, (int, float)):        # a number cell has lost its leading zero
        zip_code = f"{int(zip_code):05d}"
    return {
        "name": str(r["Name"]).strip(),
        "company": str(r["Company"]).strip(),
        "plan": str(r["Plan"]).strip(),
        "renewal": f"{renewal:%B} {renewal.day}, {renewal.year}",   # November 4, 2026
        "amount": money(r["Amount"]),
        "discount": f"{r['Discount']:.0%}" if r.get("Discount") else "",
        "total": money(r["Total"]),
        "overdue": str(r.get("Overdue") or "").strip().lower() == "yes",
        "zip": str(zip_code or ""),
    }


def safe_name(text):
    text = re.sub(r'[\\/:*?"<>|\x00-\x1f]', "-", text)   # characters Windows refuses in file names
    return re.sub(r"\s+", " ", text).strip(" .")[:120]


def read_rows(path, sheet):
    wb = load_workbook(path, data_only=True, read_only=True)  # data_only: saved values, not formulas
    ws = wb[sheet] if sheet else wb.worksheets[0]
    rows = ws.iter_rows(values_only=True)
    header = next(rows, None)
    if not header:
        sys.exit("The sheet is empty.")
    header = [str(h).strip() if h is not None else "" for h in header]
    missing = [c for c in REQUIRED if c not in header]
    if missing:
        sys.exit(f"Missing columns: {', '.join(missing)}. Found: {', '.join(header)}")
    for n, values in enumerate(rows, start=2):
        if all(v is None or str(v).strip() == "" for v in values):
            continue                                   # blank rows at the end of the sheet
        yield n, dict(zip(header, values))


def main():
    p = argparse.ArgumentParser()
    p.add_argument("data"); p.add_argument("template"); p.add_argument("out")
    p.add_argument("--sheet"); p.add_argument("--force", action="store_true")
    p.add_argument("--pdf", action="store_true", help="also make a PDF of each document with LibreOffice")
    a = p.parse_args()
    out = Path(a.out); out.mkdir(parents=True, exist_ok=True)
    env = Environment(undefined=StrictUndefined, autoescape=True)  # typo in a tag = error, & stays &
    report, made, done, skipped, failed = [], [], [], 0, 0

    for n, row in read_rows(a.data, a.sheet):
        try:
            ctx = build_context(row)
            target = out / (safe_name(FILE_NAME.format(row=n, **{k: str(v) for k, v in row.items()})) + ".docx")
            done.append(target)
            if target.exists() and not a.force:
                skipped += 1
                report.append([n, "skipped: exists", target.name, ""])
                continue
            doc = DocxTemplate(a.template)             # a fresh copy per row: render() changes it
            doc.render(ctx, env)
            fd, tmp = tempfile.mkstemp(prefix=".part-", suffix=".docx", dir=out)
            os.close(fd)
            try:
                doc.save(tmp)
                os.replace(tmp, target)                # no half-written file if the run dies here
            finally:
                if os.path.exists(tmp):
                    os.remove(tmp)
            made.append(target)
            report.append([n, "made", target.name, ""])
        except Exception as e:                         # one bad row must not stop the rest
            failed += 1
            report.append([n, "error", "", f"{type(e).__name__}: {e}"])

    with open(out / "report.csv", "w", newline="", encoding="utf-8") as f:
        csv.writer(f).writerows([["row", "status", "file", "error"], *report])
    print(f"made {len(made)}, skipped {skipped}, errors {failed}; see {out / 'report.csv'}")

    todo = [d for d in done if d in made or not d.with_suffix(".pdf").exists()]
    if a.pdf and todo:                                 # also PDFs a stopped run never made
        soffice = shutil.which("soffice") or shutil.which("libreoffice")
        if not soffice:
            sys.exit("--pdf needs LibreOffice (soffice) on the PATH; the .docx files are made.")
        for i in range(0, len(todo), 50):              # batches keep the command line short
            subprocess.run([soffice, "--headless", "--convert-to", "pdf", "--outdir", str(out),
                            *map(str, todo[i:i + 50])], check=True, capture_output=True)

    sys.exit(1 if failed else 0)


if __name__ == "__main__":
    main()

Run it with python merge.py customers.xlsx template.docx letters --pdf. To fit your own sheet, change the REQUIRED columns, the FILE_NAME pattern and the keys that build_context returns. The dates and prices are written in US style (December 3, 2026, $1,200.50); change build_context for another style. LibreOffice's start parameters describe --headless ("without user interface") and --convert-to.

What the script did on sample data

All data below is sample data we made up in this run: 50 customers on a sheet called Customers, with names such as Åsa Öberg and José Núñez, and a Total column holding formulas. We planted problems: one company is Holm & Sons <AB>, another Berg/Ek Ltd., one Plan cell is empty, one Amount is the text 1.234,50 (a decimal comma), one ZIP code is the text 02134, and one Overdue value is yes with a trailing space.

First run, on the file as written by openpyxl:

made 0, skipped 0, errors 50; see out1/report.csv
row,status,file,error
2,error,,ValueError: empty Total (formula never calculated: open and save the file in Excel or LibreOffice first)

openpyxl's data_only option reads the value stored the last time a spreadsheet program saved the file. A file written by a script has none. We opened and saved it once with LibreOffice and ran again:

made 48, skipped 0, errors 2; see out2/report.csv
row,status,file,error
6,error,,ValueError: empty Plan
9,error,,"ValueError: Amount is not a number: '1.234,50'"

We opened all 48 Word files with python-docx. None had a leftover tag. The overdue paragraph was in 12 files and the discount paragraph in 34, the same counts we got by counting the sheet rows separately, and the yes row with the trailing space was counted. The file for Holm & Sons <AB> read Holm & Sons <AB> on its first line, and its name was 004 Ada Lind - Holm & Sons -AB-.docx. The ZIP code 02134 kept its zero. The names with accents came through in both the letters and the file names. 48 PDFs were made too.

Running the same command again made 0 files and skipped 48. The first run with PDFs took about 5.5 seconds and the second about 0.2 seconds on a cloud Linux machine, one run each. On an empty workbook the script stopped with The sheet is empty., and on a sheet missing columns it stopped with Missing columns: Plan, Renewal, Amount, Total. Found: Name, Company.

When this is not enough

Use Word's merge for a one-off batch of letters that goes to a printer: it has a preview and needs no setup. The script reads only .xlsx files and saved values, so a sheet with formulas must be saved once by a spreadsheet program first. Amounts typed with a decimal comma as text are rejected, not converted, so fix them in Excel. The script skips a row when its file exists, so a corrected price needs --force or deleting that one file. Insert new rows at the bottom, because the row number is part of each file name. The PDFs come from LibreOffice, not Word, and we did not compare them with Word's own output. We also did not open the generated files in Word.

How we made this guide

We read the Microsoft, docxtpl, Jinja2 and LibreOffice pages listed below on 18 November 2026. We made a 50-row sample workbook and a template with Python, ran the script above on it with Python 3, docxtpl 0.20.2 and LibreOffice 24.2.7.2 on 18 November 2026, and checked every output file with python-docx. We had no copy of Word, so the Word steps are Microsoft's and were not tried.

Sources