Rejecting Files That Do Not Match the Template Cleanly

A loader's first job is to say no, and to say it well. A file that does not match the template, if loaded anyway, puts wrong data into the database, and the cost shows up later and elsewhere. A file that is rejected with "Error" costs the sender an hour and costs you a support call. The skill is in rejecting precisely: stopping at the right moment, saying what is wrong, where, and what to do. This article sets out how to build that, from the order of the checks to the shape of the response and what to do with the file afterward. It builds on checking an incoming workbook against a template by code, which provides the checks, and on checking an Excel upload before reading it, which comes before them.

Stage the checks

Run checks in an order that rejects bad files as cheaply as possible, and stop when a stage fails. Later stages assume earlier ones passed.

1. Transport: the file exists, is not empty, is under the size limit, has an allowed extension.

2. Container: it is a real xlsx (zip signature, required parts, no macro parts, sane archive limits).

3. Version: the template version is present and supported.

4. Structure: the data sheet or table exists; the header row matches; no merged cells in the data; columns in the expected positions.

5. Content: each cell parses as its column's type; required cells are filled; values are in their lists; keys are unique.

6. Business rules: cross-field and cross-table checks (a location belongs to a site; a SKU exists in the master).

Stages 1 to 4 are about the file's shape and each failure is a single message. Stages 5 and 6 are about values and produce a list. Do not run stage 5 on a file that failed stage 4: the errors would be noise. A file with the wrong header gets one clear message, not two hundred "column mismatch" errors for rows that would be fine in the right layout.

The rejection response

Every rejection returns three things.

A stable code. not_xlsx, too_large, unsupported_version, missing_sheet, bad_header, merged_cells, invalid_values. The code is for programs and support: a dashboard counts them, a help desk looks them up, a front end maps them to translated text. Codes never change meaning.

A message for the sender. Plain language, specific, with the next step. "The sheet named 'data' was not found. The workbook has sheets: Instructions, Sheet1. Please use the template, or rename your sheet to 'data'." Not: "KeyError: 'data'".

A log entry for the team. The code, the file hash, size, sender, time, template version and, for value errors, the count. No file contents.

A small class carries them:

class Reject(Exception):
    def __init__(self, code, message, details=None):
        super().__init__(message)
        self.code, self.message, self.details = code, message, details or []

def reject_response(r):
    return {"status": "rejected", "code": r.code, "message": r.message, "details": r.details[:200]}

For value errors, details holds rows of (row, column, reason, value). Cap the list (200 is plenty) and say how many more there are.

Write messages that help

Specific beats general. Compare:

Put the location first (row and column as Excel shows them), the problem second and the fix third. Use the sender's words ("column", "row", "cell") and the names they see. If a sheet name or a value might contain markup or odd characters, escape it for the channel (HTML, email, chat) and truncate long values. Do not leak internals: no stack traces, paths or SQL.

Group where it helps. "34 rows have a site that is not in the list; first: row 5, 'Nth'. Allowed: North, South, East." One message for a recurring cause is easier to act on than 34 lines.

Provide the same detail as a downloadable file for large lists: a CSV or an annotated copy of their workbook with the problem cells highlighted. The sender can fix the file in place and resend.

All or nothing, or partial

When values fail, decide whether the good rows load.

All or nothing. Any content error rejects the file. Pros: simple; the database never contains a partial submission; the sender fixes and resends one file. Cons: one typo blocks 5,000 good rows.

Partial. Good rows load; bad rows are returned. Pros: progress; the sender corrects only the rejects. Cons: the submission is incomplete until the rejects are resolved; reports run on partial data; duplicate handling gets harder when the corrected rows come back later.

A reasonable rule: all-or-nothing for small files and anything that must be complete as a set (a weekly count for a site), partial for large feeds where each row stands alone. If you choose partial, mark the submission as incomplete until its rejects are cleared, and show that to the people who use the data. Structural failures (stages 1 to 4) always reject the whole file.

Quarantine, do not leave around

A rejected file is still a file you were sent. Treat it as untrusted input: store it with a generated name in a quarantine location with restricted access and no execute permission, keep its hash and metadata, and set a retention period (say 30 days) after which it is deleted. Do not leave it in the upload folder where a later process might pick it up. Keep it long enough to investigate a complaint ("we sent that file, why was it rejected?") and not so long that it becomes a store of data nobody governs. If it may contain personal data, the retention rule matters for compliance (removing personal data from an Excel file before sharing covers the sharing side).

A hash lets you recognize the same file arriving again and answer "you already sent this one, and here is the result".

Make the retry short

The sender fixes the file and sends it again. Make that cheap. Accept the corrected file at the same address. Tie the new attempt to the old one (by sender and period) so the history shows reject, then accept. Avoid requiring a new login, a new form or a new template download if the template did not change. If the rejection contained an annotated copy, let them upload that, with the highlights ignored.

Rate-limit retries gently, so a script bug does not flood you with a thousand attempts, and with a limit high enough that a person fixing errors one by one is never blocked.

Count the rejections

The distribution of rejection codes is the best feedback you have on the template. Chart them weekly. A spike of bad_header after a template release means the announcement failed. A steady invalid_values on one column means the instructions for that column are unclear or the dropdown lacks a value. A rise in not_xlsx from one sender means they are exporting CSV from their system and renaming it. Fix the top cause each month: change the template, the instructions or the message. The goal is a falling rejection rate, which means people are succeeding, not that you are checking less.

Track time to acceptance too: the hours between the first attempt and the accepted file. A long time means the loop of rejection and fix is slow, and the messages or the retry path need work.

Test every rejection

For each code, keep a fixture that triggers it and a test that asserts the code, the message text (or at least its key parts) and that nothing was loaded into the database. Add a test that the response never contains stack traces or filesystem paths. Add a test that a rejected file lands in quarantine with its hash recorded. And keep one test for the happy path, so that a tightening of the checks cannot silently reject everything.

When rejecting a file is the wrong response

Rejecting is wrong when the problem can be fixed unambiguously and the sender has no easy way to resend: a trailing space in a code, a duplicate row that is byte-identical. Trim and dedupe, and tell the sender what you did. It is wrong when the file is the only evidence of a deadline met: a regulatory submission rejected at 23:59 needs a human fallback and an immediate message. And it is wrong when the cause is your template: if forty senders trip on the same column, fix the template. For the checks themselves, see checking an incoming workbook against a template by code, and for the status codes if the check sits behind an HTTP endpoint, the HTTP semantics specification, RFC 9110 describes 400 and 422 responses and their intended use. The OWASP file upload cheat sheet covers the container-level side.