Skip a Report Run When the Input File Has Not Changed

A report scheduled every hour to catch a file that arrives once a day rebuilds the same workbook twenty-three times and, worse, may email it twenty-three times. The obvious fix — compare the file's hash with the last run's — fails with Excel, because saving a workbook changes its bytes even when no value changed. This guide, part of Error Handling and Logging in Excel Automation, builds a change check that looks at the data rather than the file, keeps it cheap for large inputs, and makes sure a failed run is never mistaken for a finished one.

Prerequisites

pip install pandas openpyxl

A sample input, written the way an upstream export would write it:

import hashlib
import json
import os
import time
from datetime import datetime, timedelta, timezone
from pathlib import Path

import pandas as pd

orders = pd.DataFrame({
    "order_id": range(1, 201),
    "region": ["North", "South", "East", "West"] * 50,
    "amount": [round(20 + i * 1.7, 2) for i in range(200)],
})
orders.to_excel("orders.xlsx", index=False)

Step 1 — Why the file hash is the wrong fingerprint

Hashing the bytes looks like the natural check. Save the same DataFrame again a moment later and compare:

def file_hash(path):
    return hashlib.sha256(Path(path).read_bytes()).hexdigest()[:12]

first = file_hash("orders.xlsx")
time.sleep(1.1)
orders.to_excel("orders.xlsx", index=False)                     # same data, saved again
print(first == file_hash("orders.xlsx"))
# False

An .xlsx records when it was saved in docProps/core.xml, so every save produces new bytes. In testing, excluding that part made two saves from the same library match — but a re-save through another application (LibreOffice here, Excel in practice) rewrote styles and other parts, and the hash changed again. Someone opening the export to look at it and pressing Ctrl+S is enough to trigger a full rerun.

Step 2 — Fingerprint the data instead

Read every sheet and hash what the report actually uses: sheet names, column names and values. pandas.util.hash_pandas_object hashes each row quickly and deterministically:

def data_fingerprint(path):
    digest = hashlib.sha256()
    for name, frame in sorted(pd.read_excel(path, sheet_name=None).items()):
        digest.update(name.encode())
        digest.update(json.dumps([str(c) for c in frame.columns]).encode())
        digest.update(pd.util.hash_pandas_object(frame, index=False).values.tobytes())
    return digest.hexdigest()[:16]

before = data_fingerprint("orders.xlsx")
orders.to_excel("orders.xlsx", index=False)                     # re-save: same data
print(before == data_fingerprint("orders.xlsx"))
# True
changed = orders.copy()
changed.loc[4, "amount"] = 999.0
changed.to_excel("orders.xlsx", index=False)                    # one value changed
print(before == data_fingerprint("orders.xlsx"))
# False

The same fingerprint survived a LibreOffice re-save in testing, where every byte-level hash changed. Hash only what the report reads: if it uses one sheet, pass sheet_name="Orders"; if a volatile cell such as a "generated at" timestamp sits on the sheet, drop it before hashing, or every export looks new.

Step 3 — Check metadata first, hash only when needed

Reading a large workbook just to discover it has not changed wastes the time the check was meant to save. File size and modification time cost nothing: if both match the last run, the file was not touched and there is nothing to read. Only when they differ does the data fingerprint decide:

STATE = Path("report_state.json")

def load_state():
    return json.loads(STATE.read_text()) if STATE.exists() else {}

def check_input(path):
    """Return (needs_run, record) where record describes the input now."""
    stat = os.stat(path)
    seen = load_state().get(str(path), {})
    meta = {"size": stat.st_size, "mtime_ns": stat.st_mtime_ns}
    if seen and all(seen.get(k) == v for k, v in meta.items()):
        return False, seen                                      # untouched: no read at all
    record = {**meta, "data": data_fingerprint(path)}
    return record["data"] != seen.get("data"), record

When the metadata changed but the data did not, the run is skipped and the new size and time are saved, so the next check takes the fast path again.

Step 4 — Record the fingerprint only after success

The order matters. Save the fingerprint before building the report, and a run that crashes halfway marks the input as processed — the next run skips it, and the report never arrives. Save it after the output is written and delivered:

def save_record(path, record):
    state = load_state()
    state[str(path)] = {**state.get(str(path), {}), **record}   # keeps changed_at
    tmp = STATE.with_suffix(".tmp")
    tmp.write_text(json.dumps(state, indent=2))
    tmp.replace(STATE)                                          # atomic on the same disk

def build_report(path):
    summary = pd.read_excel(path).groupby("region")["amount"].sum()
    summary.to_excel("region_summary.xlsx")

def run(path, force=False):
    needs_run, record = check_input(path)
    if not (needs_run or force):
        save_record(path, record)                               # refresh size/mtime
        return "skipped"
    build_report(path)                                          # raises → nothing recorded
    save_record(path, {**record, "changed_at": datetime.now(timezone.utc).isoformat()})
    return "ran"

print(run("orders.xlsx"))                                       # ran
print(run("orders.xlsx"))                                       # skipped: untouched
changed.to_excel("orders.xlsx", index=False)
print(run("orders.xlsx"))                                       # skipped: re-saved only

Writing to a temporary file and replacing the state file keeps it valid if the process dies mid-write. The force flag gives people a way to resend a report on purpose — after fixing a template, for example — without deleting the state file.

Step 5 — Do not read a file that is still arriving

A large export copied over a network share appears before it is complete. Reading it then gives a truncated zip — BadZipFile — or, worse for this check, a fingerprint of partial data that gets recorded. Wait until the size stops changing, and leave files alone while Excel has them open, which it signals with a ~$ lock file beside the workbook:

def ready(path, settle=2.0):
    path = Path(path)
    if (path.parent / f"~${path.name}").exists():
        return False                                            # open in Excel
    size = path.stat().st_size
    time.sleep(settle)
    return path.stat().st_size == size

print(ready("orders.xlsx", settle=0.2))
# True

Use a settle time longer than the gaps in the copy — a few seconds for a local share, longer for slow links. A more reliable fix, when you control the producer, is for it to write under a temporary name and rename the file when done, because a rename is atomic.

Step 6 — Notice when the input stops changing

Skipping is correct for a few runs; skipping for three days on a daily feed means the upstream export has stopped. The run function already stores changed_at each time the report actually runs, and save_record merges records so a skip keeps it. Alert when it is older than a threshold:

def stale_for(path, now, limit=timedelta(days=2)):
    changed_at = load_state().get(str(path), {}).get("changed_at")
    if changed_at and now - datetime.fromisoformat(changed_at) > limit:
        return now - datetime.fromisoformat(changed_at)
    return None

three_days_on = datetime.now(timezone.utc) + timedelta(days=3)
print(stale_for("orders.xlsx", three_days_on) is not None)
# True

Choose the threshold from the feed's real rhythm: a weekday export needs a limit that survives a weekend, or every Monday starts with a false alarm. A stale input is a failure of something upstream, so route it through the same channel as job failures, described in Send an Alert When an Excel Report Job Fails.

Common pitfalls

• Fingerprinting before cleaning. If the report ignores a column, a change in it should not trigger a rerun. Hash the columns the report reads, not everything.
• One state file for several inputs keyed by name alone. Key by full path, or two orders.xlsx files in different folders overwrite each other's record.
• Comparing modified time only. Some copy tools preserve the source's timestamp, so a new file can arrive with an old time. Size plus time plus the data fingerprint on any difference is the safe combination.
• Deleting the state to force a run. It also loses the stale-input history. Use the force flag.
• Changing the fingerprint code without a migration. Hash a different set of columns, or upgrade pandas to a version that hashes a type differently, and every stored fingerprint stops matching: the next run rebuilds everything once. That is harmless for one report and a surprise for fifty — store a version number next to the fingerprints and expect the one-off rerun.

Conclusion

A workbook's bytes change on every save, so compare the data instead: hash sheet names, columns and values with pandas. Check size and modified time first so untouched files cost nothing, record the fingerprint only after the report is delivered, wait for files that are still arriving, and alert when the input has not changed for longer than it should.

Frequently asked questions

Why does the hash of an Excel file change when the data is the same? Every save writes a new modified timestamp into the workbook's docProps/core.xml, and a different application also rewrites styles and other parts. In a test, the same DataFrame saved twice two seconds apart produced two different file hashes but identical data hashes.

How can I tell if the data in an Excel file changed? Read every sheet with pandas and hash the sheet names, column names and values, for example with pandas.util.hash_pandas_object. That fingerprint stays the same across re-saves and changes when any value changes.

Is checking the file's modified time enough? It is a good first check, because it costs nothing. If size and modified time match the last run, the file was not touched. If they differ, the file may still hold the same data, so confirm with a content fingerprint before rerunning.

When should the new fingerprint be saved? Only after the report has been built and delivered. Saving it before means a run that fails halfway marks the input as processed, and the next run skips it.

Related

• Up one level: Error Handling and Logging in Excel Automation — making report jobs dependable.
• Retry a Failed Excel Report Job in Python — when the file is late rather than unchanged.
• Handle Permission Denied When Writing Excel in Python — the other side of Excel's file locks.
• Validate an Excel Report Before Sending It — checks to run once the report does run.