A decades-old lesson on not inserting Excel where it doesn't belong
- Reference: 1602062106
- News link: https://www.theregister.co.uk/2020/10/07/who_me/
- Source link:
Our tale comes from a reader we shall call "Jim", not because of the Regomiser, but because this writer has fond memories of the television series Yes, Minister from back in the day (although the fictionalised events of the show have long been trumped by reality).
Jim's story takes us back to the early 2000s, when then PM Tony Blair's administration was in charge of things in the UK.
"I was working as a senior software tester," he told us, "consulting at a Government department."
For the sake of this story, we'll refer to the lumbering juggernaut of a department as "X". Such was the size and byzantine structures within that it was a law unto itself. The political masters of the time therefore had little real control over it, fulfilling instead the role of media punching bags when things went wrong.
"The project involved matching personnel with job titles," explained Jim, "and was intended to be a daily report to the permanent secretary and ministers."
Jim had one tester on this team, and scripts aimed at guiding validation were being written when an Oracle consultant approached him.
"He was, of course, getting a huge amount of money daily to do whatever it is Oracle consultants do," Jim added. In the interest of balance, we're pretty sure that consultants from other vendors less Big and Red equally coined it while onlookers pondered their purpose.
"We can't understand it," he said, "We're losing around 25,000 personnel out of the daily report."
Jim raised an eyebrow, and the consultant went on.
"There are supposed to be 90,000-odd personnel in the report, but we're only getting around 65,000."
It sounded suspiciously close to one of those magic numbers in IT.
"Would the exact number of personnel you're getting be 65,536?" Jim asked carefully.
The consultant blinked, impressed at Jim's David Blaine-like magical powers of perception, and said, "Yes! How did you know?"
Rather than keep his tricks to himself, like the git wizard above, Jim smiled and said: "You must be transferring the personnel to an Excel spreadsheet, correct?"
Again, the consultant confirmed Jim's hypothesis, and asked what the heck Excel had to do with the problem.
Smugly, Jim replied: "The limit of rows in an Excel spreadsheet is 65,536.
"The rest of your people are being dropped silently."
This being the very early 2000s, the error is perhaps more forgivable than if one was using a similarly outdated format, say, two decades later and [3]trying to manage a pandemic with it .
Testing had saved the day yet again. The consultant went away to remove the data-munching spreadsheet step before returning to whatever it is consultants for UK government IT projects actually do.
"I never got the credit for the solution," sighed Jim, "but the kicker is that the project was dropped before any testing could occur. Department X tried to stiff the consulting firm I worked for over its fee, arguing that since no testing was done, no fee was owed.
"They were still fighting when I left for pastures new."
Spotted something in the news that has triggered a long-forgotten bit of IT idiocy? Or noted that cockups tend to repeat themselves throughout history? Of course you have, and you should share your memories in an email to [4]Who, Me? ®
Get our [5]Tech Resources
[1] https://www.theregister.com/2020/10/05/excel_england_coronavirus_contact_error/
[2] https://www.theregister.com/Tag/who-me
[3] https://www.theregister.com/2020/10/05/excel_england_coronavirus_contact_error/
[4] mailto:whome@theregister.com
[5] https://whitepapers.theregister.com/
I've actually lived through something like that. I was called in to develop a data export from a production database to a system on another platform. I was all ready to go via CSV, but I was specifically told that the export should be in an Excel file.
This was when Office 2010 was already out, so I didn't have much chance of hitting the million row limit.
So I did the job as per spec, created the code that grabbed the data, opened a new Excel sheet and plonked it in, row by row, then saved and closed Excel, grabbed the file and FTP'd it to wherever it had to go.
A few years later I got a call from that same customer. They remembered that I had done the job and now it wasn't working anymore, could I come in and fix the problem ? Sure.
So I went and checked the code. Nothing had changed in my code, so I asked to be able to run a test. With permission I ran the code in debug mode and, lo and behold, when Excel was asked to open a spreadsheet the code halted, there was no spreadsheet to be had.
After explaining the problem, a server admin used his access to check the server in question and reported that Office was no longer installed on the server. Long story short, it turns out that the beancounters were checking lists of Office licenses against users and, since that license didn't have a person associated to it, they cut the license. Apparently it would be useless trying to explain that that license was a business requirement. It was internal procedure that every Office license had to correspond to an actual, breathing human being.
Solution ? Could you please modify the code to use the CSV format ? Sure. That way the recipient will just have to bung it into Excel on his license. Problem solved.
Another day at the coalface.
To be more accurate, it was an XLS document that has that 65,000odd limit. XLSX has a far higher limit. Wouldn't have helped the OP, mind (XLSX came out in 2007) but would have helped the current government who made the exact same mistake 13 years after a file format that replaced the aging XLS format came out.
Thingies cat
Did the government make the mistake or did it’s consultants tasked with building this solution make the mistake?
Anon because....
Best Excel story I've personally been involved with. About ten years ago, my manager at that time sent out the annual appraisal spreadsheet so we could fill in our self assessment for him to rubbish. He'd forgotten to remove the tabs that i) had all the team's annual salaries and ii) the company's true feelings for each and every individual*.
* Good God that man was a colossal arse.
A prestigious university in London, Student Union is on new version of Excel, College Administration is still on old. Financial data is forwarded on spreadsheets (because of incompatible systems - Pegasus Opera vs something written in PowerHouse). Of course, at some point, the 2^16 row limit is breached. The old version doesn't complain but merely drops the excess. Fortunately the receiving bean counter runs their own tests on the data and realises there is a problem - but not what it is. Took a while for the underlying issue to be recognised (and then for the admin departments to move forward).
65536
How could anyone in IT (*) miss that telltale sign?
(*) Oh, an Oracle ✔ Consultant ✔ , I see...
spreadsheets!
Just make sure all of your rows are selected for your calculations, nothing like coming up short on an order... My manual count of switches needed for project X was 28 switches, my boss got 26 because his formula didn't include the last row, which had been added later! I really have to ask why a spreadsheet was needed to count to less than 30...
Who has been in this business for a while and not seen Excel inserted as a gaffer tape fix on a system that’s been rushed in and not quite finished, and the temporary Excel fix got forgotten and went on a bit longer than it should?
I’ve seen a this often, even in highly regulated systems. And I’ve seen far worse.