The Guardrails Passed and the File Was Still Broken

I spent a morning rebuilding an annual spreadsheet — credit card transactions in, journal entry CSV out, feeding an accounting system old enough to have opinions about line endings. The rebuild went well. The combine step generates itself now. The summaries resize when the data changes instead of quietly leaving stale rows below a pivot. And I added guardrails: three cells that have to read zero before the export means anything. Unclassified rows. Balance. Row reconciliation.

All three read zero. The import failed anyway.

It failed silently, which is the part worth sitting with. No error, no rejected-rows report, no partial load. The file went in and nothing came out the other side.

What it turned out to be Link to heading

A byte-level diff against last year’s working file — the one artifact I had that definitely imported — found it in about two minutes. Same 37 rows. Same header. Same values, same order once sorted, both summing to zero.

One difference :

working: …,60,\r\n
failing: …,60,

The last line had no terminator. Every other line in the file ended properly; the final record just stopped. Mac Excel does this. Windows Excel doesn’t. Both are within spec, because RFC 4180 says the last record may or may not have an ending line break, and two implementations of the same product resolved that ambiguity in opposite directions.

The accounting system’s parser is old enough to read records in a loop and discard an unterminated fragment. So it dropped my last row, ended up with 35 distributions against a header declaring 36, and gave up without mentioning it.

The part I keep thinking about Link to heading

My guardrails were fine. They just validated the data, and the failure wasn’t in the data — it was in the serialization. Nothing inside the workbook could see that boundary, because from inside the workbook there is no file yet.

Every validation has a scope, and the scope is usually invisible from where you’re standing.

I did briefly consider adding a check: a script to verify the trailing newline after each export. Then I noticed the export could just happen on Windows instead, where the terminator gets written correctly and there’s nothing to check. Removing the possibility beat detecting the occurrence — and it also meant the fix didn’t land as a step someone else would eventually forget.

One thing that transferred Link to heading

Two habits did the actual work here, and neither is new.

Keep a known-good artifact. I had last year’s file, so the question became “what’s different” instead of “what’s wrong.” Much smaller question.

Change one variable at a time. I’d altered the row ordering and had the newline problem simultaneously. Fixing only the newline told me the ordering was fine — a guess would have left me uncertain about both.

That’s one bug, so I’m not going to claim a principle from it. But I’d been treating validation as something you either have or don’t, and I think the more useful question is what a given check can actually see.