Make an Excel Report Job Safe to Rerun

Every scheduled report job gets rerun eventually. The source data was late and the numbers need refreshing; a bug was fixed and last month needs regenerating; the job crashed after writing the file but before emailing it; someone ran it by hand not knowing the scheduler already had. A job that is safe to rerun — idempotent, in the jargon — produces the same outcome however many times it runs for the same period: one file with the right numbers, one set of database rows, one email to each recipient. Most jobs are not like that by default. They name files after the time they ran, append to tables, and send email unconditionally, so a rerun leaves two files, doubled totals and a confused inbox. This page builds a small report job and makes each of its steps safe to repeat, then crashes it half-way and shows a rerun finishing the work without repeating any of it. It belongs to Error Handling and Logging in Excel Automation.

Prerequisites

pip install pandas openpyxl

SQLite and the rest are in the standard library. The job reads a sales workbook, writes a monthly summary workbook, loads the summary rows into a reporting table, and "sends" the report — recorded rather than emailed, to keep the example self-contained.

Step 1 — Take the period as a parameter, never "now"

The first source of non-repeatable runs is the clock. A job that reports on "last month" by computing it from today's date produces a different report when rerun on the 2nd of next month than on the 1st. Make the period an explicit argument, and default it from the clock only at the very edge:

import argparse
from datetime import date

def last_month(today):
    first = today.replace(day=1)
    prev = date.fromordinal(first.toordinal() - 1)
    return f"{prev:%Y-%m}"

parser = argparse.ArgumentParser()
parser.add_argument("--period", help="YYYY-MM to report on; defaults to last month")
args = parser.parse_args(["--period", "2026-09"])
period = args.period or last_month(date.today())
print("reporting period:", period, "| default if omitted on 2026-10-02:", last_month(date(2026, 10, 2)))

Everything downstream takes period and nothing else reads the date. Rerunning September in November, or in a test, then gives exactly September — the same rows, the same file name, the same keys. Filtering the input by period rather than "the last 30 days" matters for the same reason.

Step 2 — Deterministic names and atomic writes

Name output files after the period, not the run time, and write them so a crash can never leave half a file under the final name:

import os
import tempfile
from pathlib import Path
import pandas as pd

OUT = Path("out")
OUT.mkdir(exist_ok=True)

def build_summary(period):
    sales = pd.read_excel("sales.xlsx")
    month = sales[sales["date"].dt.strftime("%Y-%m") == period]
    return month.groupby("region", as_index=False)["amount"].sum().assign(period=period)

def write_atomically(df, target):
    fd, tmp = tempfile.mkstemp(dir=target.parent, suffix=".xlsx")
    os.close(fd)
    try:
        df.to_excel(tmp, index=False)
        os.replace(tmp, target)
    except BaseException:
        os.unlink(tmp)
        raise

summary = build_summary(period)
report = OUT / f"sales_summary_{period}.xlsx"
write_atomically(summary, report)
print(summary.to_string(index=False))

Writing to a temporary file in the same folder and then calling os.replace swaps the finished file in under its final name in one step, so readers see either the previous complete file or the new complete file, never a partial one. A rerun simply replaces the period's file instead of adding sales_summary_2026-10-02T07-15.xlsx next to it. The atomic write is covered in detail in Write Excel Files Atomically in Python.

Step 3 — Replace the period's rows, in one transaction

Appending the summary to a reporting table doubles it on every rerun. Instead, delete the period's rows and insert the new ones inside a single transaction:

import sqlite3

db = sqlite3.connect("state.db")
db.execute("CREATE TABLE IF NOT EXISTS sales_summary (period TEXT, region TEXT, amount REAL, PRIMARY KEY (period, region))")
db.execute("CREATE TABLE IF NOT EXISTS sends (period TEXT, recipient TEXT, sent_at TEXT, PRIMARY KEY (period, recipient))")

def load(db, df, period):
    with db:
        db.execute("DELETE FROM sales_summary WHERE period = ?", (period,))
        db.executemany("INSERT INTO sales_summary VALUES (?, ?, ?)", df[["period", "region", "amount"]].itertuples(index=False))

for _ in range(3):
    load(db, summary, period)
print(db.execute("SELECT period, region, amount FROM sales_summary").fetchall())

Loaded three times, the table still holds one row per region for September. The with db: block commits both statements together or rolls both back, so a failure part-way through never leaves the period empty or half-loaded. Deleting by period, rather than replacing the whole table, keeps other months untouched. The primary key on (period, region) is a safety net: if a bug ever tried to insert a duplicate, the load would fail loudly instead of silently doubling a figure. Databases with MERGE or INSERT … ON CONFLICT DO UPDATE can upsert instead, but delete-and-insert has one advantage: a region that disappeared from the source also disappears from the table.

Step 4 — Side effects go through a ledger

Emails, uploads and API calls cannot be "replaced"; once sent, they are sent. Record each one under a key that identifies it — here period and recipient — and skip anything already recorded:

from datetime import datetime

def send_once(db, period, recipient, path):
    with db:
        cur = db.execute("INSERT OR IGNORE INTO sends VALUES (?, ?, ?)", (period, recipient, datetime.now().isoformat(timespec="seconds")))
    if cur.rowcount == 0:
        return f"skip {recipient} (already sent for {period})"
    # deliver(recipient, path)  — the real email or upload goes here
    return f"sent {path.name} to {recipient}"

for recipient in ["finance@example.com", "sales@example.com"]:
    print(send_once(db, period, recipient, report))
print(send_once(db, period, "finance@example.com", report))

The ledger row is written before the email is sent. If sending then fails, the row must be removed (or marked failed) so the rerun tries again — the trade-off is that a crash in the instant between "recorded" and "sent" can lose that one message, whereas recording afterwards risks sending it twice. For reports, an occasional duplicate is usually more acceptable than a lost email, in which case record after sending; for anything with money attached — payment files, invoices — record first. A separate delivery queue makes this decision explicit; see Queue and Retry Failed Report Emails.

Step 5 — Crash half-way, then rerun

Put the steps together, make the job crash after loading but before sending, and run it again:

RECIPIENTS = ["finance@example.com", "sales@example.com", "board@example.com"]

def run(period, crash_after_load=False):
    summary = build_summary(period)
    write_atomically(summary, OUT / f"sales_summary_{period}.xlsx")
    load(db, summary, period)
    if crash_after_load:
        raise RuntimeError("simulated crash before sending")
    return [send_once(db, period, r, OUT / f"sales_summary_{period}.xlsx") for r in RECIPIENTS]

try:
    run("2026-10", crash_after_load=True)
except RuntimeError as e:
    print("first run:", e)
for line in run("2026-10"):
    print("rerun:", line)
for line in run("2026-09"):
    print("september again:", line)
print("files:", sorted(p.name for p in OUT.iterdir()))
print("rows:", db.execute("SELECT period, COUNT(*) FROM sales_summary GROUP BY period").fetchall())
db.close()

The first October run wrote the file and loaded the rows, then crashed. The rerun rewrites the same file, replaces the same rows and sends to all three recipients — nothing is duplicated, because nothing before the send had any lasting effect that could be doubled. Running September once more sends only to the board address, the one recipient that had not received it, and skips the two that had. The output folder holds exactly one file per period and the table one set of rows per period. That is the property to aim for: whatever happened before, running the job again for a period finishes the work and does nothing twice. Two further protections complete the picture — stopping two copies running at the same moment, in Prevent Overlapping Runs of an Excel Report Job, and skipping work entirely when the input has not changed, in Skip a Report When the Input File Has Not Changed.

Common pitfalls

• date.today() deep in the code. Pass the period in; default it only at the entry point.
• Timestamped file names. Name by period and replace atomically.
• Appending rows. Delete and insert the period in one transaction.
• Unconditional emails. Record sends under a unique key and check before sending.
• Assuming the last run finished. Design every step to be repeated after a crash.

Conclusion

Make a report job safe to rerun by giving every step a stable identity: the period is a parameter, files are named by period and swapped in atomically, database rows for the period are deleted and reinserted in one transaction, and emails and uploads go through a ledger keyed by period and recipient. Then a rerun — after a crash, a fix or a mistake — finishes whatever was left and repeats nothing.

Frequently asked questions

What does it mean for a report job to be idempotent? Running it several times for the same period has the same outcome as running it once: one file, one set of rows and one email per recipient.

How do I avoid duplicate rows when rerunning a load? Delete the period's existing rows and insert the new ones inside a single transaction, or upsert on a key that includes the period.

How do I stop a rerun from emailing people twice? Record each send in a table with a unique key such as period and recipient, and skip recipients already recorded.

Why should the reporting period be a parameter? Because a job that works out the period from today's date reports on a different period when rerun later; passing it in makes reruns reproducible.

Related

• Up one level: Error Handling and Logging in Excel Automation — reliable Excel jobs.
• Retry a Failed Excel Report Job in Python — retrying the steps that can be retried.
• Quarantine Bad Excel Files in a Batch Job — handling bad input files.
• Write Excel Files Atomically in Python — the atomic write in detail.