Guide

Automating Reporting WorkflowsDeep dive

Error Handling and Logging in Excel Automation

Make scheduled Excel jobs survivable — structured logging, retries with backoff, atomic writes, permission and lock errors, output validation, exit codes and alerts that mean something.

A report that runs on your laptop needs to work. A report that runs at 6am on a server needs to fail well: to say what broke, to leave last week's file intact, to retry the things worth retrying, and to tell someone in a form they will read. This guide, part of Automating Reporting Workflows, covers the patterns that turn a working script into an unattended job.

Install the dependencies

Bash

pip install pandas openpyxl

Everything else in this guide is standard library: logging, pathlib, tempfile, smtplib and time.

Fail fast on the environment

The cheapest failure is the one that happens before any work. Check the things a job depends on in the first few lines, and say precisely what is missing:

Python

from pathlib import Path

SOURCE = Path("/data/incoming/orders.xlsx")
OUTPUT_DIR = Path("/data/reports")

def preflight():
    problems = []
    if not SOURCE.exists():
        problems.append(f"source file not found: {SOURCE}")
    elif SOURCE.stat().st_size == 0:
        problems.append(f"source file is empty: {SOURCE}")
    if not OUTPUT_DIR.exists():
        problems.append(f"output directory missing: {OUTPUT_DIR}")
    elif not OUTPUT_DIR.is_dir():
        problems.append(f"output path is not a directory: {OUTPUT_DIR}")
    if problems:
        raise SystemExit("preflight failed:\n  " + "\n  ".join(problems))

preflight()

Half an hour into a job is a bad moment to discover the output directory does not exist, because everything computed so far is thrown away. Preflight also gives you somewhere obvious to check credentials, disk space and the presence of an SMTP host — all the things that are constant during a run and fatal if absent.

Logging that answers questions

The purpose of a log is to answer "what happened at 06:04?" without re-running anything. That means one line per meaningful step, with the identifiers a person would need:

Python

import logging
from pathlib import Path

def setup_logging(name="report", logdir="logs", level=logging.INFO):
    Path(logdir).mkdir(parents=True, exist_ok=True)
    logger = logging.getLogger(name)
    logger.setLevel(level)
    logger.handlers.clear()

    fmt = logging.Formatter(
        "%(asctime)s %(levelname)-8s %(name)s %(message)s",
        datefmt="%Y-%m-%d %H:%M:%S",
    )
    file_handler = logging.FileHandler(Path(logdir) / f"{name}.log", encoding="utf-8")
    file_handler.setFormatter(fmt)
    stream_handler = logging.StreamHandler()
    stream_handler.setFormatter(fmt)

    logger.addHandler(file_handler)
    logger.addHandler(stream_handler)
    return logger

log = setup_logging()
log.info("run started source=%s", SOURCE)

Two handlers, deliberately. The file survives the run for later investigation; the stream is what a scheduler captures and what you watch while testing. Clearing logger.handlers first makes the function safe to call twice, which matters in a job that is also imported by tests.

Log the shape of the data at each boundary — it turns most investigations into a single glance:

Python

import pandas as pd

df = pd.read_excel(SOURCE, sheet_name="Orders")
log.info("read source rows=%d columns=%d", len(df), len(df.columns))

clean = df.dropna(subset=["Order_ID"])
log.info("after cleaning rows=%d dropped=%d", len(clean), len(df) - len(clean))

When a report is wrong three days later, "rows=48211 dropped=0" on Monday and "rows=12 dropped=48199" on Tuesday tells you exactly where to look, without a debugger and without the source file.

Retry only what is worth retrying

A locked file usually unlocks; a missing column never appears. Retrying the wrong class of failure wastes time and buries the real message:

Python

import time

TRANSIENT = (PermissionError, OSError, TimeoutError)

def with_retries(fn, attempts=4, base_delay=2.0, logger=None):
    """Retry transient failures with exponential backoff. Re-raise everything else."""
    for attempt in range(1, attempts + 1):
        try:
            return fn()
        except TRANSIENT as exc:
            if attempt == attempts:
                if logger:
                    logger.error("giving up after %d attempt(s): %s", attempts, exc)
                raise
            delay = base_delay * (2 ** (attempt - 1))
            if logger:
                logger.warning("attempt %d/%d failed (%s) — retrying in %.0fs",
                               attempt, attempts, exc, delay)
            time.sleep(delay)

import pandas as pd
df = with_retries(lambda: pd.read_excel(SOURCE, sheet_name="Orders"), logger=log)

The exponential delay matters when the cause is a colleague with the file open in Excel: retrying four times in four seconds fails four times, while waiting 2, 4 and 8 seconds often succeeds. A ValueError from a validation check passes straight through, which is exactly right — no amount of waiting will fix a missing column.

Permission and lock errors deserve their own handling

PermissionError on Windows almost always means the file is open in Excel, and the message a reader needs is that sentence rather than the traceback:

Python

from pathlib import Path

def save_workbook(wb, target, logger=log):
    target = Path(target)
    try:
        wb.save(target)
    except PermissionError:
        lock = target.with_name(f"~${target.name}")
        hint = " (it looks open in Excel)" if lock.exists() else ""
        logger.error("cannot write %s%s — close the file and re-run", target, hint)
        raise SystemExit(2)
    logger.info("wrote %s (%.1f KB)", target, target.stat().st_size / 1024)

Checking for the ~$ lock file turns a guess into a statement. It is also worth writing reports to a path nobody browses to, and copying the finished file to the shared folder afterwards — a job that writes directly into a directory people work in will hit this every few weeks.

Write atomically so a crash cannot destroy the last good file

The default failure mode of "open the output and write into it" is a truncated workbook where a working one used to be. Write beside it and rename:

Python

import os
from pathlib import Path

def atomic_save(wb, target):
    target = Path(target)
    temp = target.with_name(f".{target.name}.tmp")
    wb.save(temp)                       # a crash here leaves the original untouched
    os.replace(temp, target)            # atomic on the same filesystem
    return target

os.replace is atomic within a filesystem, so a reader either sees the old file or the new one and never a half-written one. Keeping the temporary file in the same directory is what guarantees that — a temp file in /tmp and a target on a network share means a copy, not a rename, and the atomicity is lost.

Check the output before anyone receives it

A job can succeed technically and produce a useless file. Assert the properties a good report has, and refuse to deliver one that fails:

Python

from pathlib import Path

import pandas as pd

def validate_output(path, min_rows=1, min_bytes=5_000, expected_sheets=("Summary",)):
    path = Path(path)
    problems = []
    if not path.exists():
        return [f"{path} was not created"]
    if path.stat().st_size < min_bytes:
        problems.append(f"file is only {path.stat().st_size} bytes")

    book = pd.ExcelFile(path)
    for sheet in expected_sheets:
        if sheet not in book.sheet_names:
            problems.append(f"missing sheet {sheet!r}")
            continue
        frame = book.parse(sheet)
        if len(frame) < min_rows:
            problems.append(f"sheet {sheet!r} has {len(frame)} row(s), expected >= {min_rows}")
    return problems

issues = validate_output("reports/march.xlsx")
if issues:
    log.error("output validation failed: %s", "; ".join(issues))
    raise SystemExit(1)
log.info("output validated")

This is the check that stops the classic embarrassment: a source system returned nothing, every stage handled the empty frame gracefully, and a beautifully formatted workbook of zeros went out to forty people. Comparing against the previous run — row counts within a sensible band — catches the subtler version, where the file is not empty but is missing a third of the data.

Exit codes and alerts a human will read

Distinguish outcomes so a scheduler can act, and alert on the outcome rather than on the exception:

Python

import smtplib
import sys
from email.message import EmailMessage

OK, DATA_PROBLEM, ENVIRONMENT_PROBLEM = 0, 1, 2

def alert(subject, body, to="ops@example.com", host="smtp.example.com"):
    msg = EmailMessage()
    msg["Subject"] = subject
    msg["From"] = "reports@example.com"
    msg["To"] = to
    msg.set_content(body)
    with smtplib.SMTP(host, 587, timeout=30) as smtp:
        smtp.starttls()
        smtp.send_message(msg)

def main():
    try:
        preflight()
        report = build_report()                 # your pipeline
        issues = validate_output(report)
        if issues:
            alert("Report NOT sent — output failed validation",
                  "The March report was not delivered.\n\n" + "\n".join(issues))
            return DATA_PROBLEM
        deliver(report)                          # email, upload, copy to share
        log.info("run complete report=%s", report)
        return OK
    except SystemExit:
        raise
    except Exception:
        log.exception("unhandled failure")       # traceback goes to the log
        alert("Report job failed", "See logs/report.log for the traceback.")
        return ENVIRONMENT_PROBLEM

if __name__ == "__main__":
    sys.exit(main())

log.exception records the traceback where an engineer will look; the email says what a reader needs. Sending the traceback itself to a distribution list is how alerting becomes noise — after a few weeks the messages are filtered away and the next real failure goes unnoticed. The emailing Excel reports guide covers the delivery side properly.

Isolate failures in a batch

When one job produces many reports — one per region, one per client — a single bad input should not take the others down. Process each unit inside its own error boundary and summarise at the end:

Python

def run_batch(units):
    succeeded, failed = [], []
    for unit in units:
        try:
            output = build_report_for(unit)          # your per-unit pipeline
            issues = validate_output(output)
            if issues:
                raise ValueError("; ".join(issues))
            succeeded.append((unit, output))
            log.info("unit=%s ok output=%s", unit, output)
        except Exception as exc:                      # noqa: BLE001 - boundary is deliberate
            failed.append((unit, f"{type(exc).__name__}: {exc}"))
            log.exception("unit=%s failed", unit)

    log.info("batch complete ok=%d failed=%d", len(succeeded), len(failed))
    if failed:
        alert(
            f"{len(failed)} of {len(units)} reports failed",
            "\n".join(f"{unit}: {reason}" for unit, reason in failed),
        )
    return succeeded, failed

The broad except here is a deliberate boundary rather than laziness — it exists precisely so that one region's malformed file does not deprive the other eleven of their reports. Everything inside it is still logged with a full traceback, and the alert names each failing unit with its reason, so the follow-up is targeted rather than a re-run of the whole batch.

One rule keeps this honest: a batch that fails entirely should exit non-zero even though every individual failure was handled. Twelve failures out of twelve is not a partial success, and a scheduler that sees exit code 0 will never tell anyone.

Make the job safe to re-run

Anything scheduled will eventually be run twice — by a retry, by a person, or by two schedulers that both think they own it. Design for it:

Python

from pathlib import Path

def already_done(marker_dir="state", key="2026-03"):
    marker = Path(marker_dir) / f"{key}.done"
    if marker.exists():
        log.info("run for %s already completed at %s", key, marker.read_text().strip())
        return True
    return False

def mark_done(marker_dir="state", key="2026-03"):
    from datetime import datetime, timezone
    Path(marker_dir).mkdir(exist_ok=True)
    (Path(marker_dir) / f"{key}.done").write_text(datetime.now(timezone.utc).isoformat())

Deterministic output filenames help too: writing march_2026.xlsx rather than report_20260302_060411.xlsx means a second run replaces the file instead of adding a near-duplicate that someone will later email by mistake. Where a timestamped archive is genuinely wanted, keep both — a stable current name and a dated copy.

Keep the logs from becoming the problem

A daily job left running for two years produces a log file nobody can open. Rotation solves it, and choosing the right rotation says something about how you investigate:

Python

import logging
from logging.handlers import RotatingFileHandler, TimedRotatingFileHandler
from pathlib import Path

def add_rotating_file(logger, path="logs/report.log", by="size"):
    Path(path).parent.mkdir(parents=True, exist_ok=True)
    if by == "size":
        handler = RotatingFileHandler(path, maxBytes=5_000_000, backupCount=5, encoding="utf-8")
    else:
        handler = TimedRotatingFileHandler(path, when="midnight", backupCount=30, encoding="utf-8")
    handler.setFormatter(
        logging.Formatter("%(asctime)s %(levelname)-8s %(message)s", "%Y-%m-%d %H:%M:%S")
    )
    logger.addHandler(handler)
    return logger

Size-based rotation suits a job whose output volume varies; time-based rotation suits a scheduled report, because "the log for the 3rd of March" is exactly how you will think about it when someone asks why that day's numbers looked odd. Thirty daily files is a month of history for a few megabytes, which is almost always the right trade.

Two habits keep the content useful as well as the size manageable. Log at INFO for the steps a reader would want to reconstruct and at DEBUG for anything voluminous, so raising the level in an emergency gives you more detail rather than a different program. And never log the contents of the data — a row of customer records in a log file is a data-protection problem that outlives the report it came from.

Record a run summary someone can scan

A log is a narrative; a summary is a fact sheet. Writing one row per run into a small CSV gives you a history that answers "has this been getting slower?" without reading anything:

Python

import csv
import time
from datetime import datetime, timezone
from pathlib import Path

def record_run(status, rows, output, started, path="logs/runs.csv"):
    Path(path).parent.mkdir(parents=True, exist_ok=True)
    new = not Path(path).exists()
    with open(path, "a", newline="", encoding="utf-8") as handle:
        writer = csv.writer(handle)
        if new:
            writer.writerow(["finished_utc", "status", "rows", "seconds", "output"])
        writer.writerow([
            datetime.now(timezone.utc).isoformat(timespec="seconds"),
            status, rows, round(time.perf_counter() - started, 1), output,
        ])

started = time.perf_counter()
record_run("ok", 48_211, "reports/march.xlsx", started)

Five columns is enough. When the row count halves or the runtime triples, the trend is visible in a spreadsheet — which is, appropriately, the tool your readers already have open.

Common failures and how to respond

Failure	Class	Response
`PermissionError` on save	Transient	Retry with backoff; report the lock file if present
`FileNotFoundError` on the source	Environment	Fail in preflight with the exact path
`KeyError` on a column	Data	Stop; validate structure at the boundary instead
Empty result set	Output	Block delivery, keep the previous file
SMTP timeout	Transient	Retry; if it persists, write the report and alert separately
Corrupt workbook	Data	Quarantine the file, continue with the rest of the batch
Disk full	Environment	Fail fast; atomic write means the old file survives

Test the failure paths

Error handling is the code least likely to have been exercised, because the failures it handles are rare by definition. Simulating them takes a few lines and is the difference between a handler that works and one that has never run:

Python

import pytest


def test_preflight_names_the_missing_file(tmp_path, monkeypatch):
    monkeypatch.setattr("report.SOURCE", tmp_path / "nope.xlsx")
    with pytest.raises(SystemExit) as excinfo:
        preflight()
    assert "source file not found" in str(excinfo.value)


def test_retry_gives_up_and_reraises():
    calls = {"n": 0}

    def always_locked():
        calls["n"] += 1
        raise PermissionError("locked")

    with pytest.raises(PermissionError):
        with_retries(always_locked, attempts=3, base_delay=0)
    assert calls["n"] == 3


def test_validation_blocks_an_empty_report(tmp_path):
    import pandas as pd
    path = tmp_path / "empty.xlsx"
    pd.DataFrame(columns=["Region"]).to_excel(path, sheet_name="Summary", index=False)
    assert validate_output(path, min_rows=1, min_bytes=0)

Passing base_delay=0 keeps the retry test instant while still exercising the loop. These three tests cover the paths that actually fire at 6am on a bad morning, and they run in milliseconds — there is no reason for a scheduled job not to have them.

Key takeaways

Check the environment before doing any work, and name the exact path or credential that is missing.
Log one line per step with counts and identifiers; "error occurred" costs someone an hour.
Retry transient failures with exponential backoff, and never retry a data problem.
Treat PermissionError as "someone has it open" and say so, checking for the ~$ lock file.
Write to a temporary file in the same directory and os.replace it into place, so a crash never destroys the last good report.
Validate the output — sheets present, rows above a floor, size sane — before anything is delivered.
Use distinct exit codes, alert on outcomes rather than tracebacks, and make the whole job safe to run twice.

Frequently asked questions

Should a report job retry automatically? Retry transient failures — a locked file, a network share that blinked, an SMTP timeout. Never retry a validation failure or a missing column, because the second attempt fails identically and hides the real problem.

What belongs in a log line? What the job was doing, which file, how many rows, and what happened. A line that says "error occurred" tells the person reading it at 7am nothing they can act on.

Why write to a temporary file first? So a crash halfway through cannot leave a truncated workbook where the last good one was. Write to a temp name in the same directory, then rename it into place.

How do I stop alerts being ignored? Alert on outcomes, not on exceptions. One message saying "March report not sent — source file missing" is read; forty tracebacks a week are filtered into a folder nobody opens.

Up to the parent guide:

Automating Reporting Workflows — the wider pipeline these safeguards protect.

Go deeper here:

Handle Permission Denied When Writing Excel in Python — the most common scheduled-job failure.
Log Python Excel Script Output to a File — handlers, rotation and what to record.
Retry a Failed Excel Report Job in Python — backoff, jitter and which errors qualify.
Validate an Excel Report Before Sending It — the last gate before delivery.

Related areas:

Scheduling Python Excel Scripts with cron — where these exit codes are interpreted.
Emailing Excel Reports with smtplib — delivery, and alerting when it fails.
Validating Excel Data with Python — the input-side checks that prevent data failures.