Quarantine Bad Excel Files in a Batch Job

A job that processes every workbook dropped into a folder — branch returns, supplier price lists, timesheets — will sooner or later meet a file it cannot handle: one saved as CSV with an .xlsx name, one cut short by a failed upload, one where someone renamed a column, one full of text where numbers should be. The two common reactions are both bad. Letting the exception stop the job means one person's broken file delays everybody's data. Catching and logging it, then carrying on, means the bad file sits in the inbox and fails again on every run, and nobody tells its sender. The pattern that works is quarantine: move the bad file out of the way, next to a short note saying exactly what is wrong, and keep going. This page builds that for a folder of Excel files, with error classification, notes people can act on, a run summary and a way to resubmit fixed files. It belongs to Error Handling and Logging in Excel Automation.

Prerequisites

pip install pandas openpyxl

Everything else is the standard library. The sample inbox holds six branch returns: two good ones, one with a renamed column (Qty instead of Units), one with words in the Units column, one that is really a CSV file with an .xlsx name, and one truncated half-way through — a cross-section of what turns up in real shared folders.

Step 1 — Folders for each state

Give each state of a file its own folder on the same file system, so that moving a file between them is an atomic rename:

from pathlib import Path

ROOT = Path("drop")
INBOX, WORK, DONE, QUARANTINE = (ROOT / n for n in ("inbox", "processing", "done", "quarantine"))
for d in (WORK, DONE, QUARANTINE):
    d.mkdir(exist_ok=True)

def move(path, folder):
    target = folder / path.name
    if target.exists():
        target = folder / f"{path.stem}.{datetime.now():%Y%m%d%H%M%S}{path.suffix}"
    path.replace(target)
    return target

from datetime import datetime
print(sorted(p.name for p in INBOX.glob("*.xlsx")))

Path.replace is an atomic rename when source and target are on the same file system, so a file is always in exactly one folder — never half-copied, never in two places. Moving each file into processing before touching it claims it: if a second copy of the job starts, it will not pick up the same file, and if the job crashes, the file left in processing shows exactly where. Files that collide with an earlier one of the same name get a timestamp rather than overwriting it. Skipping Excel's ~$ lock files when listing the inbox is covered in Ignore Excel Lock Files When Processing a Folder.

Step 2 — Classify what can go wrong

The note in quarantine is only useful if it says something a person can act on. Translate exceptions into a few categories with plain-language messages, and raise your own for validation failures:

import zipfile
import pandas as pd

REQUIRED = ["Branch", "Product", "Units", "Revenue"]

class BadFile(Exception):
    def __init__(self, kind, message):
        super().__init__(message)
        self.kind = kind

def load_return(path):
    if not zipfile.is_zipfile(path):
        head = path.read_bytes()[:64]
        if b"," in head and head.isascii():
            raise BadFile("wrong-format", "This is a CSV file saved with an .xlsx name. Open it in Excel and save as Excel Workbook.")
        raise BadFile("unreadable", "The file is damaged or incomplete — please upload it again.")
    try:
        df = pd.read_excel(path, keep_default_na=False, na_values=[""])
    except (zipfile.BadZipFile, KeyError, ValueError) as e:
        raise BadFile("unreadable", f"Excel could not read the file ({type(e).__name__}) — please upload it again.")
    missing = [c for c in REQUIRED if c not in df.columns]
    if missing:
        raise BadFile("columns", f"Missing column(s) {', '.join(missing)}. Found: {', '.join(map(str, df.columns))}.")
    units = pd.to_numeric(df["Units"], errors="coerce")
    bad = df.loc[units.isna() & df["Units"].notna(), "Units"]
    if len(bad):
        rows = ", ".join(f"row {i + 2} ('{v}')" for i, v in bad.items())
        raise BadFile("values", f"Units must be a number: {rows}.")
    return df.assign(Units=units, source=path.name)

A truncated .xlsx is no longer a valid zip archive, which zipfile.is_zipfile detects before pandas produces a more obscure error; a CSV in disguise is recognised by its plain-text first bytes. Missing columns are reported together with the columns that were found, which usually makes the cause obvious — here, Qty instead of Units. Reading with keep_default_na=False and only "" as a missing value matters for validation: by default pandas silently turns text such as "n/a", "NA" or "null" into missing values, which would let them slip past the numeric check as if the cells were simply empty. Value problems name the rows as Excel numbers them (data row i is sheet row i + 2, after the header), so the sender can go straight to the cell. Any exception not anticipated here is still caught in the next step and quarantined as "unexpected", with its type and message.

Step 3 — Process every file, quarantine failures, keep going

import json
import traceback
import hashlib

def quarantine(path, kind, message, exc=None):
    folder = QUARANTINE / datetime.now().strftime("%Y-%m-%d")
    folder.mkdir(exist_ok=True)
    target = move(path, folder)
    note = {"file": path.name, "kind": kind, "message": message, "quarantined_at": datetime.now().isoformat(timespec="seconds"),
            "sha256": hashlib.sha256(target.read_bytes()).hexdigest()}
    if exc is not None and kind == "unexpected":
        note["detail"] = traceback.format_exception_only(exc)[-1].strip()
    target.with_name(target.name + ".error.json").write_text(json.dumps(note, indent=2))
    return note

def run_batch():
    loaded, failed = [], []
    for path in sorted(INBOX.glob("*.xlsx")):
        claimed = move(path, WORK)
        try:
            df = load_return(claimed)
        except BadFile as e:
            failed.append(quarantine(claimed, e.kind, str(e)))
        except Exception as e:
            failed.append(quarantine(claimed, "unexpected", "The file could not be processed; the team has been told.", e))
        else:
            loaded.append(df)
            move(claimed, DONE)
    combined = pd.concat(loaded, ignore_index=True) if loaded else pd.DataFrame(columns=REQUIRED)
    return combined, failed

combined, failed = run_batch()
print(f"loaded {combined['source'].nunique()} files, {len(combined)} rows; quarantined {len(failed)}")
for note in failed:
    print(f"  {note['file']:<20} {note['kind']:<13} {note['message']}")

Each file is processed inside its own try, so a failure affects only that file. The else branch runs only when loading succeeded, which keeps the "move to done" step out of the error handling. Two files load and four are quarantined, each with a message the branch could act on: re-upload, save as a workbook, rename the column, fix rows 2 and 4 — the second being the "n/a" that default parsing would have hidden. The note sits beside the file as name.xlsx.error.json, with a hash so you can tell whether a resubmitted file is actually different from the one that failed.

Step 4 — Report the run and resubmit fixed files

A quarantined file that nobody hears about is only slightly better than a lost one. Summarise every run, and make resubmission as simple as moving the fixed file back to the inbox:

def summary(failed):
    lines = [f"{len(failed)} file(s) need attention:"]
    lines += [f"- {n['file']}: {n['message']}" for n in failed]
    return "\n".join(lines)

print(summary(failed))

fixed = QUARANTINE / datetime.now().strftime("%Y-%m-%d") / "hull_2026-09.xlsx"
df = pd.read_excel(fixed).rename(columns={"Qty": "Units"})
df.to_excel(INBOX / "hull_2026-09.xlsx", index=False)
fixed.unlink(); fixed.with_name(fixed.name + ".error.json").unlink()
combined, failed = run_batch()
print("rerun loaded:", combined["source"].unique().tolist(), "| quarantined:", len(failed))
print("done folder:", sorted(p.name for p in DONE.iterdir()))

The summary is ready to email to the people who submit files or post to a channel — grouping by sender, if the file names or a lookup tell you who sent what, turns it into a personal to-do list for each branch. After the Hull return is fixed and put back in the inbox, the next run loads it and the done folder now holds three returns. Because loaded files leave the inbox, rerunning the job never loads the same file twice. Sending the summary is covered in Send an Alert When an Excel Report Job Fails; untrusted uploads deserve the extra precautions in Read Untrusted Excel Files Safely in Python.

Common pitfalls

• Letting one file stop the batch. Wrap each file in its own try.
• Logging and leaving the file in place. It fails on every run; move it.
• Messages only developers understand. Say what the sender should do.
• Copy then delete. Use an atomic rename within one file system.
• No summary. Tell the people who can fix the files.

Conclusion

Process a folder of workbooks one file at a time: claim each by moving it to a processing folder, load and validate it, and move it to done or to a dated quarantine folder with a JSON note that classifies the problem and tells the sender what to do. Catch unexpected errors too, so nothing stops the batch, summarise every run for the people who can act, and let them resubmit by putting the fixed file back in the inbox.

Frequently asked questions

How do I stop one bad Excel file from crashing a batch job? Process each file inside its own try block, and on failure move the file to a quarantine folder with a note instead of letting the exception end the job.

How do I detect a corrupt or truncated xlsx file? An xlsx is a zip archive, so zipfile.is_zipfile returns False for truncated files and for CSV or text files given an xlsx name.

Where should a quarantined file's error message go? In a small JSON file next to it with the failure kind, a plain-language message and a hash of the file, and in a run summary sent to the people who submit files.

How do I resubmit a file after fixing it? Move the corrected file back into the inbox folder; the next run picks it up like any new file.

Related

• Up one level: Error Handling and Logging in Excel Automation — reliable Excel jobs.
• Validate an Excel Report Before Sending It — checks on the output side.
• Make an Excel Report Job Safe to Rerun — reruns without side effects.
• Watch a Folder and Process New Excel Files with Python — triggering the batch when files arrive.