Reserving Notes in a Separate Sheet of Import Templates

A warehouse clerk counts 40 boxes and wants to say that three are damaged. The template has columns for site, SKU and quantity, and nothing else. So the clerk types "3 damaged" in the quantity cell, or puts it in the next free column, or writes it above the header. The loader meets a text value in a number column, an unexpected column K or a title in row 1. The clerk was doing the right thing: reporting something useful. The template had nowhere to put it. This article describes where notes should go, how to keep the data area clean and how the loader should treat what it finds outside it. It is one of the layout rules in building Excel templates that import cleanly, and it supports controlling what goes into a spreadsheet.

Where notes come from

Notes are a signal that the process has needs the template does not meet. Common ones:

• A caveat about one record: "damaged", "approx", "recounted".

• An explanation of an adjustment: why a quantity changed from last week.

• A question to the receiver: "please check this SKU".

• A remark about the whole file: "site closed on the 14th, so counts are for 13 days".

• Instructions the sender wrote for themselves: "remember to send to finance".

• A reminder or a calculation scratch: arithmetic in a spare cell.

Each has a different right home, and the template can offer them all.

A notes column for record-level remarks

Add a notes column to the table, at the right end, with a clear header and a length limit (matching the database column, say 500 characters). Record remarks go there as free text. The loader stores them in a text column and does not interpret them.

Mark it optional. If the column is only sometimes used, say so in the instructions: "Use Notes for anything unusual about this row, such as damage or a recount." Keep it free of rules. A notes column that people are told to fill in becomes a column of "n/a".

If a note carries business meaning that someone will filter or report on, it is not a note. "3 damaged" is data: a damaged_qty column. Watch the notes for patterns. If a hundred rows say "damaged", add a column, and the loader no longer needs to parse prose. Review the notes every quarter with that in mind (designing tables from a messy Excel sheet).

A Notes sheet for file-level remarks

For remarks about the whole submission, give the workbook a sheet named Notes with a single large text area, or a short two-column list of topic and text. The loader reads it as free text and stores it with the submission record. It is not part of the table.

The Notes sheet also removes the temptation to type a paragraph in the data sheet's first row.

Cell comments for remarks tied to a cell

Excel's comments (notes in newer versions, threaded comments for discussions) attach to a cell and appear on hover. They live in separate parts of the file. A loader reading cell values never sees them. They are good for remarks that matter to a human reviewer and not to the system: "Recounted twice, was 38". If you want to capture them, openpyxl exposes them as cell.comment.text; but then they are data, and should have been a column.

Threaded comments (the newer kind) are stored separately again, and openpyxl does not read them. If a pipeline relies on comments, test with the Excel version your senders use.

Instructions on their own sheet

The first sheet is Instructions. It holds what the file is for, who to contact, the due date, the meaning of each column, an example row and the rules, in a screen or less. People read the first sheet, and the data sheet stays a table. Do not put instructions above the header or in a box to the right; they push the header down and widen the used range, which confuses loaders and printing.

A common trap: instructions in the data sheet's cell comments, repeated on every header. They are too many and are hidden. A single sheet is better.

Keep the edges clear

Define the table's rectangle and leave the space outside empty: no content to the right of the last column, no content below the last row, nothing above the header. State it: "Do not write anything outside the table." Then the loader can treat anything outside as an error or ignore it deliberately.

An Excel table helps: the loader reads the table by name, whatever else is on the sheet. In openpyxl, ws.tables["Count"].ref gives the range, and the loader reads only that. A table also makes the intended extent unmistakable to the person typing, because Excel draws it. For files without a table, read a fixed range: header row 1, columns A to H, data from row 2 to the last non-empty row in column A.

What the loader does with strays

Decide the policy and apply it the same way every time.

Ignore silently. Fast and friendly, and you never learn what people wanted to say. Not recommended on its own.

Ignore and report. Read the table, ignore the rest, and include in the report: "Ignored content outside the table in cells K1:K4." The sender learns, and nothing breaks.

Reject. Strict, appropriate when stray content signals a broken layout (a column inserted, a header shifted) and the risk of misreading is high. The message names the cells.

To detect strays, compare the sheet's used range with the table: in openpyxl, ws.dimensions gives the used range, which you can compare with the table reference:

from openpyxl.utils import range_boundaries

t_min_col, t_min_row, t_max_col, t_max_row = range_boundaries(ws.tables["Count"].ref)
u_min_col, u_min_row, u_max_col, u_max_row = range_boundaries(ws.dimensions)
if (u_min_col, u_min_row, u_max_col, u_max_row) != (t_min_col, t_min_row, t_max_col, t_max_row):
    outside = f"used range {ws.dimensions} differs from table {ws.tables['Count'].ref}"

The used range sometimes extends past real content because of formatting; check that cells outside have values, not only styles.

Free text hazards

Whatever the home for notes, text in them is untrusted input. Limit length, strip control characters and store it as text, never as markup or SQL. When notes are exported again to CSV or xlsx, neutralize leading formula characters (formula injection in Excel exports). If the notes may contain personal data, apply the same handling as the rest of the file, and think about who will read them.

Encourage the right use

A notes column that nobody knows about stays empty, and the notes appear elsewhere. Mention it in the instructions with an example. Make the column wide, with wrapped text, so it invites writing. Respond to notes. If senders see that their remarks are read and acted on, they use the column; if they see nothing happen, they stop and the information disappears. A weekly look at the notes by whoever owns the process, and a standing question "what do these notes tell us about the template?", turns the free text into improvements.

Test it

Fixtures: a clean file; a file with a note typed in the quantity cell; a file with a stray column K; a file with text above the header; a file with a very long note; a file with a comment on a cell. Assert the loader's behavior for each: the note in the quantity cell is a type error; the stray column is ignored and reported (or rejected, per policy); the title row is a header mismatch; the long note is truncated or rejected per the column limit; the comment is ignored.

When notes belong in the data

Notes belong in the data when they are structured. A reason code for an adjustment, a flag for "estimated", an approver's name: these are columns with types and lists. Using notes for them hides the information from queries. They also belong in the data when the audit trail needs them: a regulator who asks why a figure changed wants the reason in the record, not in a comment that disappears when the file is saved as CSV. If you find the notes column is the busiest part of the template, the design is missing columns. The openpyxl documentation on comments covers reading and writing them, and the XlsxWriter worksheet documentation shows how to add them to a generated template.