Verify the deliverable, not the library that wrote it

Re-reading a generated file with the library that wrote it proves almost nothing. What an independent validity check needs to cover, and why one rule catches one defect.

A process generates a spreadsheet. To check it, the process loads the file back and confirms the figures are right. They are. The file is sent.

The recipient opens it and is told the file has a problem with its content.

This is a common and instructive failure, and the reason it happens is that loading a file with the same library that wrote it tests almost nothing. The library round-trips its own output faithfully, including output the destination application will refuse. The two are not checking the same thing: one is checking whether the data survived, the other is checking whether the file is valid. A generated artefact needs the second kind of check, and it has to be independent of the thing that produced it.

01 · A styled cell with no value is not an empty cell

The most common instance. Applying formatting to a cell that has no content can produce a cell declared as holding a number while holding nothing at all. The file is structurally invalid.

It arises innocently: a loop that styles a column touches a row where that column has no value, or a header block is styled across a range wider than the values written into it.

The check — after generation, scan for cells that carry a type declaration and no value. Fix at source by writing an empty string into any cell before styling it.

02 · Text beginning with an equals sign becomes a formula

Spreadsheet libraries generally store any string starting with = as a formula. This is correct behaviour and it is a trap for explanatory text, because notes describing a calculation naturally begin that way. The note is then stored in the formula slot as prose, and the file is invalid.

The check — reject any formula containing lower-case letters outside quotation marks. Formulas generated by a process are conventionally upper-case, so prose stands out sharply against them. Fix by writing the note without the leading symbol.

03 · A dead link looks exactly like a live one

Where a review sheet references source documents, making the reference clickable is the difference between a sheet that gets worked through and one that gets skimmed. It also introduces a failure mode with no visible symptom: a link to a path that does not resolve looks identical on the sheet to one that does.

Two specific hazards are worth knowing. Characters that have meaning inside a workbook — the hash symbol in particular — are interpreted as a location within the file rather than as part of the path, so any document whose name contains one silently links nowhere. And building links as formula text rather than as proper link relationships means every file name has to survive quote escaping, which reintroduces the previous problem.

The check — decode every link target and assert the file exists. Nothing else will ever catch this, because there is no visible difference to catch.

04 · Relative or absolute is a decision, not a default

Links written as absolute paths embed the generating machine’s directory structure into a file that leaves the building. They break for every recipient and they disclose more than intended.

Relative paths, with the workbook saved alongside the referenced files, survive the folder being copied and disclose nothing. The trade-off is that sending the workbook on its own breaks the links — which nobody would guess, so it belongs written on the sheet rather than known only to whoever built it.

The check — confirm targets are relative, and state the constraint in the deliverable itself.

One rule catches one defect

The most useful lesson from all of this is about how these faults present.

Applications tend to report every structural problem in a file with the same generic message. So when that message appears again after a fix, the natural conclusion — that the fix did not work — is usually wrong. It far more often means there is a second, unrelated defect, and the first fix was fine.

Two consequences follow. Diagnose by inspecting the file’s internals, never by reloading it; the loader is the thing that cannot see the problem. And treat the validator as an accumulating set of specific rules rather than a single pass-or-fail gate, because each rule closes exactly one failure and none of them generalise to the others.

Where the check belongs

In the build, failing it — not as a separate step someone remembers to run.

A deliverable that does not open is discovered by the client, and it costs more than the defect warrants: it is the kind of error that makes a recipient wonder what else was not checked. A generated artefact that has not been validated independently of its generator should not exist long enough to be attached to an email.

What independence actually requires

“Independent” is easy to claim and easy to lose without noticing, so it is worth being concrete about what breaks it.

A check is not independent if it uses the same library to read the file that was used to write it — the original point here, and the clearest case. A round trip through one library proves that the library is self-consistent, which it was always going to be.

It is also not independent if it re-uses the writer’s own understanding of the format. A check written by the same person, from the same specification, at the same time, inherits every misreading of that specification. This is why a passing test suite is weaker evidence than it feels: tests share an origin with the code, and the failures they cannot catch are precisely the ones where the author was wrong about what correct meant.

Nor is it independent if it compares the output against a value derived from the output. Totalling a column and checking it against the sum printed at the bottom of the same file confirms arithmetic and nothing else.

What does count is narrow but not difficult: open the artefact with the tool the recipient will actually use. Recompute a figure from the source records rather than from the deliverable. Have a second implementation read the same input and compare. Each of these can disagree with the process being checked, and the capacity to disagree is the entire property being bought.

The general form of the mistake

Strip out the spreadsheet and what remains is a pattern worth recognising anywhere in a firm’s processes: a system was asked to assess itself, and reported that it was fine.

It appears in more places than it looks. An extraction engine reporting its own confidence. A coding tool reporting an exception count it generated. A vendor quoting an accuracy figure measured on a sample they chose. A reconciliation that ties because a difference was plugged. In each case the assurance and the thing being assured share an origin, so a shared mistake is invisible to both.

The fix is always structurally the same, and it is usually cheap: introduce something with a different origin. A second independent reading. Arithmetic recomputed from the source rather than from the output. The recipient’s own tool, opening the file. None of these are sophisticated, and all of them can disagree with the process being checked — which is the only property that matters.

For a firm evaluating automation, this is the question worth asking ahead of any accuracy claim: when this is wrong, what tells me? A system whose only answer is its own confidence has not answered.

The applied version of that question is in how to audit an automated coding run, and the vendor comparison it implies is in template OCR vs layout-aware invoice extraction.

Any process producing a file for someone else — a report, an export, an import package — needs a check the destination would recognise, run by something other than the code that wrote it. Building that into the pipeline is among the cheapest reliability improvements available, and it is standard in month-end close automation.