Missing Covid-19 test data was caused by the ill-thought-out use of Excel • Hacker News Board

Re: Missing Covid-19 test data was caused by the ill-thought-out use of Excel

#371

Post by jonplackett » Mon, Oct 05, 2020, 9:18 PM UTC

Smoosh wrote: post #350

ImaCake wrote: post #305

Earlier quoted context omitted.

Yep. If the UK is anything like Australia, they will have a small army of contractors who specialise in building SQL databases for various important and pointless requirements for the government. These contractors are used to working on giant, sensitive data, databases with insufficient allocated time. It should have been pretty much trivial for the UK govt to solve this.

And yet, I have first-hand witnessed a scenario like the one described in an AU government department. A small system developed by business (one person who had subsequently moved on) using Excel and Word and macros to keep track of information and generate letters to "customers" had become an essential tool, but unsupported and undocumented. When we (IT) recommended redeveloping it in Java with an SQL database (and d…

Was that system working on the single most important problem facing the country at that time though?

Re: Missing Covid-19 test data was caused by the ill-thought-out use of Excel

#372

Post by srtjstjsj » Mon, Oct 05, 2020, 9:18 PM UTC

rbanffy wrote: post #361

gxs wrote: post #334

Earlier quoted context omitted.

Not to mention the "system" solution will have to go through its own debugging and will undoubtedly have bugs in it for some time after initial release. On top of that, there is STILL room for human error as a user is still the one actually inputting data and designing the experiment/data flow. I'm going to get mauled on this forum given the audience, but parent comment reeks of the technical elitism on this forum an…

> Not to mention the "system" solution will have to go through its own debugging and will undoubtedly have bugs in it for some time after initial release. Doing code reviews in Excel is hard if the developers are pathologically disciplined. It's impossible most of the time. And so is debugging. It's very unlikely any such system would face these limitations and would silently ignore data the same way Excel did.

Excel will silently corrupt data, not just drop. It loves corrupting anything that resembles a date, and there no way to say "stop it" globally.

Re: Missing Covid-19 test data was caused by the ill-thought-out use of Excel

#373

Post by srtjstjsj » Mon, Oct 05, 2020, 9:21 PM UTC

ImaCake wrote: post #305

jonplackett wrote: post #288

Earlier quoted context omitted.

Come on, this is a government department of a first world country. They have databases for all sorts of things. They're not using excel for any other NHS databases, or the terrorism watchlist. There are plenty of people capable of making a good decision here. I think you let them off too easily by just assuming they're dumb. This a bad decision by people who definitely should have known better.

Yep. If the UK is anything like Australia, they will have a small army of contractors who specialise in building SQL databases for various important and pointless requirements for the government. These contractors are used to working on giant, sensitive data, databases with insufficient allocated time. It should have been pretty much trivial for the UK govt to solve this.

The people building stuff like that generally need a 6-12 lead time from project proposal to delivery.

Re: Missing Covid-19 test data was caused by the ill-thought-out use of Excel

#374

Post by srtjstjsj » Mon, Oct 05, 2020, 9:22 PM UTC

Smoosh wrote: post #350

ImaCake wrote: post #305

Earlier quoted context omitted.

Yep. If the UK is anything like Australia, they will have a small army of contractors who specialise in building SQL databases for various important and pointless requirements for the government. These contractors are used to working on giant, sensitive data, databases with insufficient allocated time. It should have been pretty much trivial for the UK govt to solve this.

And yet, I have first-hand witnessed a scenario like the one described in an AU government department. A small system developed by business (one person who had subsequently moved on) using Excel and Word and macros to keep track of information and generate letters to "customers" had become an essential tool, but unsupported and undocumented. When we (IT) recommended redeveloping it in Java with an SQL database (and d…

Why are there infrastructure costs for a database server? Once you have one it can host all your small databases.

Re: Missing Covid-19 test data was caused by the ill-thought-out use of Excel

#375

Post by TeMPOraL » Mon, Oct 05, 2020, 9:23 PM UTC

beagle3 wrote: post #358

TeMPOraL wrote: post #339

Earlier quoted context omitted.

But the negative externalities don't seem all that big, considering. For every problem like this, you get countless millions of person-hours and dollars saved in work that wouldn't have happened at all, or would have been delayed, because of the necessity and cost of creating complex IT systems to support it. If Excel is a sword tornado, it's one happening in an environment where everyone knows to be super vigilant a…

> For every problem like this, you get countless millions of person-hours and dollars saved in work that wouldn't have happened at all, I've found errors in every Excel spreadsheet I've ever looked at, and I'm not some master-excel user; I usually find them because - if I care about the results, I rewrite them as a Python script, so I get to go through everything. The fact that Excel is effectively not auditable is a…

I agree with what you wrote. It does cause a lot of small mess everywhere, and an occasional big mess. But there really isn't any better suite of tools available that hits all the non-database requirements.

Re: Missing Covid-19 test data was caused by the ill-thought-out use of Excel

#376

Post by Aeolun » Mon, Oct 05, 2020, 9:24 PM UTC

srtjstjsj wrote: post #354

gxs wrote: post #334

Earlier quoted context omitted.

Not to mention the "system" solution will have to go through its own debugging and will undoubtedly have bugs in it for some time after initial release. On top of that, there is STILL room for human error as a user is still the one actually inputting data and designing the experiment/data flow. I'm going to get mauled on this forum given the audience, but parent comment reeks of the technical elitism on this forum an…

> Goes through the same process of QA and line-by-line checking of requirements. That's simply not possible unless you have a whole system around excel running a year harness. You can maybe do it if you are super dedicated with locked cells and formal double-blind QA passes which maybe exist somewhere but not in the vast majority of operations.

> formal double-blind QA passes

I don’t know what you think I have in my unit tests, but it’s almost certainly not whatever you mentioned.

Using excel as an alternative to CSV is fine. It’s not like anyone was doing anhthing complicated here. Just storing data.

Re: Missing Covid-19 test data was caused by the ill-thought-out use of Excel

#377

Post by gridlockd » Mon, Oct 05, 2020, 9:24 PM UTC

shadowgovt wrote: post #141

gridlockd wrote: post #119

Earlier quoted context omitted.

The way I heard it, it was an Excel problem: They had a CSV file that had more than 1 million lines, and Excel dropped the lines beyond 1 million. I can imagine an automated process that appended to the CSV and then Excel saving back to that same file with the lines stripped. I hope we can all agree that this is not the way Excel or any other software should behave, whether the user is incompetent or not.

If the conversion was failing silently, it's a Microsoft software problem, but I bet you money the conversion was failing in a way that fell into a `catch(Exception e) { }` somewhere in their conversion script. Or even a `catch(Exception e) { log e; }`, and the log is stored to local storage on a machine that no human being ever logs into.

There's nothing to convert, Excel natively supports CSV. It just doesn't read beyond a million lines, it presumably won't warn you about that, and if you save the file again (as CSV), the lines will just be gone.

Re: Missing Covid-19 test data was caused by the ill-thought-out use of Excel

#378

Post by srtjstjsj » Mon, Oct 05, 2020, 9:24 PM UTC

Reason077 wrote: post #329

happytoexplain wrote: post #296

Earlier quoted context omitted.

Frankly I think you're being optimistic/pessimistic (depending on your point of view). A person can be a "decent coder" by very reasonable definitions of the term and still not realize that Excel can't handle this particular volume of data.

Excel can handle the volume of data just fine. The (very outdated) .xls file format with its 65,536 row limit was the problem here.

Excel will also happily screw it up and not tell you, as happened here.

Re: Missing Covid-19 test data was caused by the ill-thought-out use of Excel

#379

Post by strangeattractr » Mon, Oct 05, 2020, 9:25 PM UTC

akho wrote: post #248

sails wrote: post #132

Earlier quoted context omitted.

Would be interested in hearing more about that Excel tool.

Come work at a bank. Any bank.

Can attest to this. Some of the things I've seen excel spreadsheets contorted to do horrify me.

I once got spreadsheet dumped on me to debug because it wasn't working. A colleague used the spreadsheet to 'generate' interest rates that were then input into a mainframe. Dug into the VBA spaghetti mess, turns out this 15 year old script that took 15 minutes to run originally hit a bunch of Oracle/External APIs and performed calculations was now just copying the rates in from a csv file on a shared drive.

The error was caused by an excel formula ticking over a new year causing it to look for a directory it did not need to access that didn't exist. I thought it was pretty funny until I heard that the entire asset finance business had been unable to write any loans for days because of this.

Re: Missing Covid-19 test data was caused by the ill-thought-out use of Excel

#380

Post by pilsetnieks » Mon, Oct 05, 2020, 9:32 PM UTC

srtjstjsj wrote: post #360

sjansen wrote: post #307

Earlier quoted context omitted.

Probably not while complying with privacy laws. I would be much more upset to find out a governemnt employ on a deadline used Google Sheets or Airtable to share my medical data instead of an Excel doc on a secure government file server.

> HIPAA " For customers who are subject to the requirements of the Health Insurance Portability and Accountability Act (HIPAA), G Suite and Cloud Identity can also support HIPAA compliance" https://support.google.com/a/answer/3407054?hl=en > secure "UHS says all U.S. facilities affected by apparent ransomware attack Computer systems at Pennsylvania-based Universal Health Services began to fail over the weekend, leadi…

Not that HIPAA applies in the UK (and at this point it's not even certain that Google can be GDPR compliant even it they wanted to) but that bit you quoted applies to G Suite customers who have to enable that thing in the account settings.

It is implausible that every hospital, clinic, lab and other medical organization in the UK would sign up to G Suite and deploy it to every of their employees; or that the British government would negotiate some kind of procurement contract with Google quickly for all that to be practical.

Absent those issues, Google Sheets has a limit of 18k columns and 5m total cells, in this case the issue was that they hit Excel's 16k column limit. Not much of an upgrade there.