Versioning Template Layouts So Old Files Still Import

Monday morning, a file arrives from a site that has not sent one in three months. It has the old layout: no lead_time_days column, and unit is called uom. The loader, updated in September, expects the new layout and fails with "missing columns". The sender did nothing wrong; they used the template they had. Templates change, and files made from old versions keep arriving long after the new one is out. Versioning is how the loader tells the difference between a mistake and an old file. This article gives a practical scheme: where to put the version, which changes need a new one, how to migrate old files and how long to keep supporting them. It extends building Excel templates that import cleanly and works with the schema approach in checking an incoming workbook against a template by code.

Put the version in the file

A version in the file name ("stock_count_v3.xlsx") is useful for people and unreliable for programs. People rename files, save copies and attach them to emails under new names. A version inside the file travels with the content. There are three places to put it.

A named cell on the instructions sheet. A cell called template_version holding 2026.3. Visible to the sender, easy to read with openpyxl: wb.defined_names["template_version"], then resolve the destination. It is also easy for a sender to overwrite by accident, so lock the instructions sheet.

A custom document property. Excel stores custom properties in docProps/custom.xml. They are invisible unless you look under File, Info, Properties, and they survive saves. openpyxl exposes wb.custom_doc_props. Less visible, harder to edit by accident, and a copy-paste of the data into a new workbook loses it.

A cell in a fixed place of the data sheet, such as a hidden-looking last column or a comment. Avoid: it mixes metadata and data.

The named cell is the simplest. Set it from the generator, and read it first in the loader. Format the version as YYYY.N (year and sequence) or semantic major.minor. The format matters less than being consistent and sortable.

from openpyxl.workbook.defined_name import DefinedName

wb.defined_names["template_version"] = DefinedName("template_version", attr_text="Instructions!$B$2")
wb["Instructions"]["B2"] = "2026.3"

(In openpyxl 3.1 defined names are a mapping on the workbook; older versions used wb.defined_names.append.) In the loader, read the cell with a direct address if you prefer the simplest: wb["Instructions"]["B2"].value.

Which changes need a new version

Not every edit to a template is a breaking change. Sort them.

Compatible (minor): adding an optional column at the right end; adding a value to a dropdown list; adding a note to the instructions; extending a date window; relaxing a limit. Old files still load, and the loader fills the new column with its default.

Breaking (major): renaming a column; removing a column; reordering columns, if the loader reads by position; changing a column's type or meaning; making an optional column required; tightening a rule such as lowering a maximum; changing the sheet name or table name; changing the unit (kilograms to grams) without changing the name.

The last one is the dangerous kind. A column weight that meant kilograms last year and grams this year loads without error and doubles or halves every figure by a factor of a thousand. Whenever a meaning changes, change the name too (weight_kg to weight_g) so the break is visible.

Record the class of each change in the changelog. Breaking changes bump the major number (or the year), compatible ones the minor.

Migrate old files in the loader

For each supported old version, the loader has a migration: a small function that turns the old layout into the current one.

def migrate_2026_2_to_2026_3(rows):
    out = []
    for r in rows:
        r = dict(r)
        r["unit"] = r.pop("uom", None)          # renamed
        r.setdefault("lead_time_days", None)     # new optional column
        out.append(r)
    return out

MIGRATIONS = {"2026.2": migrate_2026_2_to_2026_3}
CURRENT = "2026.3"

def to_current(version, rows):
    if version == CURRENT:
        return rows
    if version in MIGRATIONS:
        return MIGRATIONS[version](rows)
    raise UnsupportedVersion(version)

Migrations chain if there are several steps (2026.1 to 2026.2 to 2026.3), each simple and tested. Migrate after reading and before validation, so validation runs once against the current schema. Log every migration: "file used 2026.2; migrated". The count of migrated files shows how many senders are behind.

Do not guess. If the old version is not in the table, reject. A file claiming version 2025.9 that the loader has never heard of is not safely readable.

Do not migrate silently when the change affects meaning. If an old file's weight was in kilograms and the new weight_g is in grams, the migration multiplies by 1,000 and records that it did. Better, reject old files for changes of that kind, and require a resend on the new template.

A support window

Supporting every version forever turns the loader into archaeology. Set a rule: the current version and the previous one. A new version is announced with an end date for the one before ("2026.2 files accepted until 2026-12-31"), and the loader, after that date, rejects it with a message that links to the current template. Put the date in the configuration, not in the code logic, so ending a version is a one-line change.

Between announcement and end date, the loader warns: "This file uses template 2026.2, which will stop being accepted on 2026-12-31. Please switch to 2026.3." The warning goes in the result the sender sees, not only the log.

The rule keeps the migration code small, and it gives senders a clear expectation. Emergencies (a regulator's deadline, a partner who cannot update) are exceptions, handled with a manual conversion and not an everlasting code path.

Version the pieces together

The template, the schema, the checker and the target table are one thing in four forms. Keep them in one repository, with a single version number. A change to the schema regenerates the template and the checker's expectations; a test confirms the generated template passes its own checker. Tag each release (template-2026.3) and keep the generated template file as a release artifact, so that the exact file that senders received can be recovered. When a sender reports a problem with a file, you can reproduce the checker version that applied.

The changelog is part of the repository and part of the communication. A short entry per version: the date, the class of change (compatible or breaking), what changed, what the sender must do. Publish it where senders find the template.

2026.3  (2026-10-01)  breaking
  - Column "uom" renamed to "unit".
  - New optional column "lead_time_days".
  - Sender action: download the new template; 2026.2 accepted until 2026-12-31.
2026.2  (2026-07-01)  compatible
  - Added "Other" to the unit list.

Versioning also interacts with naming. Because column names are keys, a rename is the commonest breaking change, so keep names stable from the start (naming sheets and columns for machines in Excel templates). And because the version is only useful if rejected files are handled well, the messages for an unsupported or missing version belong in the same set as the other rejections (rejecting files that do not match the template cleanly).

What to tell the sender

The first the sender sees of versioning is a rejection, so make it clear. "This file was made with template 2026.1. We now accept 2026.2 and 2026.3. Download the current template here: <link>. You can copy your rows into it." If the loader can detect the version from the file and a header mismatch both, say both: "Missing columns: unit (renamed from uom in 2026.3)". The more specific, the fewer support emails.

Make the current template easy to find: a stable URL that always serves the latest version, with the version in the file name and inside the file. Old links should still work and point to a page that says what is current.

Handle the file with no version

Some files will arrive without a version: made before versioning existed, or built by hand by someone who deleted the instructions sheet. Treat "no version" as a case, not an exception. Options: reject with a message; or infer the version from the header (if the header matches exactly the layout of 2026.1, treat it as 2026.1). Inference is acceptable for a short transition and should be removed once the old layout is retired.

Test the migrations

Keep a fixture file per supported version, generated from the old schema (store the generator for each released version, or save the generated files in the repository). A test loads each one and asserts that the migrated result equals the expected current-layout rows. A test with an unknown version asserts rejection. A test with the end date in the past asserts the "no longer accepted" message. Add a test that the current template, freshly generated, loads with zero errors.

When versioning is more than you need

A template used by one team that you can message in five minutes does not need migration code: announce the change, ask everyone to switch and reject the old one. A template that changes once a year can rely on a version cell and a strict check, with no migrations. The machinery above pays when senders are many, outside your control, slow to update or tied to deadlines, which is true of supplier portals and regulatory submissions. Even then, start with the version cell and the changelog, and add migrations only when the first stale file proves you need one. The semantic versioning specification describes the major, minor distinction for software, and the openpyxl documentation on defined names explains reading and writing named cells.