The Monday morning file that won't go in any more
The supplier's email arrives with an Excel attachment, the same one as every month, same name and same look, and the person who gets it does what they always do, opens the sheet, runs the macro that brings it into the business software, and this time what should happen doesn't happen
Either it stops with a warning that says nothing useful, or, and this is the worse version, it runs all the way through quite happily and writes numbers, except they're the wrong numbers, because the supplier slipped a column into the middle and now the barcode is sitting in the price box
Anyone looking at the sheet spots it in a second, the new column is there to see, the header written a different way is there to see, so you put things right by hand and carry on, the following month you do it all again, and nobody calls it a problem because each single time is too small to complain about
Why the file changes even though nobody told you
An Excel or CSV file that arrives from outside isn't an agreement, it's the result of an export that somebody at the other end set up once and then changed whenever they needed to, maybe because they added a field to their own business software, or because they switched software vendor, or because the person who runs the export changed and ticked a different option
From where they stand nothing serious has happened, they added a piece of information, and nobody is thinking about you feeding that file to a program, so nobody warns you, and it isn't even ill will, it simply doesn't cross their mind
The things that move are nearly always the same, a new column in the middle, a header that goes from Amount to Net amount, the decimal separator that switches from a comma to a point, the date that turns the other way round, two title rows added at the top, the sheet given another name
The difference is in how the file gets read
The fragile way of reading goes by position, you take column C because the price sits in column C, and as long as it sits there it works perfectly, then the day somebody slips a column in before C the price slides to D, but the program carries on reading C, and it reads it without any trouble, because there is a value in there, it's just a different one
The way of reading that holds up goes by header, it looks for the column called Price and doesn't care whether it's the third or the seventh, if the supplier adds two in the middle nothing changes, the name is still there and the column gets found wherever it ended up
Then comes the part that matters even more, what happens when the header it expects isn't there, because sooner or later that happens, and at that point the system stops, says it can't find the Price column in this month's file, and makes no attempt to guess which one it is
The other nets you put around it
The header on its own isn't enough, because sometimes the name stays exactly the same and it's the content that changes, so you also check that what's inside is what should be there, a price has to be a number, a date has to be a plausible date, a code has to exist in your own records
And then there's the check worth more than all the others put together, if the file carries a total, the sum of the rows read has to come to that total, and if it doesn't then something in the reading went wrong, even if you don't yet know what
On price lists you also set a variance threshold, so a price that moves more than you decided doesn't go through on its own, because the computer doesn't know that one point twenty turning into twelve is a mistake, while the threshold rule knows it perfectly well
What it takes to build it
We need the real files, not just one, and preferably from a few different months, because it's by comparing them that you see what in that file is stable and what wobbles, and the stable part is bigger than it looks when you're annoyed
The supplier will go on changing his file, there's no avoiding that, and the aim isn't for nothing ever to change, the aim is that when it does change you know straight away, instead of finding out much later while looking at a report that doesn't add up
If you want to see how file reading works inside a whole job, look at the page on Excel and PDF, and if your problem looks more like the sheet with macros nobody dares touch, there's a guide just for that on the Excel macros that break