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

Re:Missing Covid-19 test data was caused by the ill-thought-... by mjw1007 on Monday October 05, 2020 @02:59PM (#24690835)

So, what we know is:

« PHE had set up an automatic process to pull this data together into Excel templates [...] When [the row limit] was reached, further cases were simply left off. »

The terrible thing here is dropping data rather than reporting an error and refusing to run.

It isn't clear what piece of software was behind this "automatic process".

Clearly the responsible humans are to blame.

If the software that dropped data rather than failing has that as its default behaviour, or is easily configured to do that, then I think that software (and its authors) are also to blame.

Is there anything in Excel itself that behaves like that?

[ Parent | on HN ]

Re:Missing Covid-19 test data was caused by the ill-thought-... by jsty on Monday October 05, 2020 @03:14PM (#24690987)

> Is there anything in Excel itself that behaves like that?

I'd imagine the default error-handling behaviour of 9/10 Excel macros is to throw away data

[ Parent | on HN ]

Re:Missing Covid-19 test data was caused by the ill-thought-... by piannucci on Monday October 05, 2020 @03:39PM (#24691209)

Oh, my God. "On Error Resume Next" is one of my earliest memories of learning to program for Windows. It never occurred to me that it might result in deaths.

[ Parent | on HN ]

Re:Missing Covid-19 test data was caused by the ill-thought-... by redwall_hp on Monday October 05, 2020 @03:52PM (#24691336)

That's an interesting ethical point in CS: sure, you didn't intend the software you hacked together in an afternoon to be used in anything with life-and-death consequences. But that doesn't mean someone isn't going to come along later and use it, without ever even looking at the source, for something very critical down the line.

I wonder how many bleeding edge master branches of GitHub repos, pulled in blindly by someone cobbling something together to meet a deadline, are running in places they probably shouldn't be.

[ Parent | on HN ]

Re:Missing Covid-19 test data was caused by the ill-thought-... by ClumsyPilot on Monday October 05, 2020 @04:21PM (#24691591)

People have died from using unsuitable contruction materials or devices, that were never designed to be used that way.

It's not the fault of the original designer if he was clearly targetting a different purpose.

[ Parent | on HN ]

Re:Missing Covid-19 test data was caused by the ill-thought-... by rbirkby on Monday October 05, 2020 @04:31PM (#24691683)

https://github.com/ReactiveX/RxJava/wiki/Error-Handling-Oper...

Lots of love for it in Rx

[ Parent | on HN ]

Re:Missing Covid-19 test data was caused by the ill-thought-... by willtim on Monday October 05, 2020 @03:40PM (#24691225)

Yes and "on error resume next" is what I usually see in VBA code

[ Parent | on HN ]

Re:Missing Covid-19 test data was caused by the ill-thought-... by alexbrower on Tuesday October 06, 2020 @01:56AM (#24695077)

Curious, in what context are you all still using VBA?

[ Parent | on HN ]

Re:Missing Covid-19 test data was caused by the ill-thought-... by willtim on Tuesday October 06, 2020 @03:29AM (#24695467)

I, personally, wouldn't use VBA at all, ever. I would only consider Excel and Word for very simple use cases. For example, I refuse to use Word for technical documentation. But Office and Excel are everywhere in large corporates; and simple tactical spreadsheets turn into monsters over time. One former employer had a spreadsheet "application" where triggering a sheet recalc (accidentally) would take out an entire grid of machines. I've never been in any position to stop this stuff from happening.

[ Parent | on HN ]

Re:Missing Covid-19 test data was caused by the ill-thought-... by Ma8ee on Monday October 05, 2020 @04:48PM (#24691862)

I had to import data from Excel documents into MS SQL Server. Since this was supposed to be a one time thing I used the import wizard. Some values where quietly ignored because the cells happened to have different formats than the surrounding cells. They looked exactly the same, so it took me a little while to figure it out. And if I wouldn't have done some simple sanity checks those errors would definitely not have been discovered before it was too late.

[ Parent | on HN ]

Re:Missing Covid-19 test data was caused by the ill-thought-... by Closi on Tuesday October 06, 2020 @05:39AM (#24696100)

> It isn't clear what piece of software was behind this "automatic process".

They probably mean a semi-automated process within excel, where each tab is a days extract or something similar and they are using external references to other sheets. In any vaguely up-to-date version of excel the way you would do this is via 'get and transform' which does not have any of these limitations (including the 1m record limit that the news article suggests).

The funny thing is that the latest versions of Excel are brilliant at aggregating and analyzing data and are more than suitable for this task if used correctly (i.e. using PowerQuery). It's just that way less than 1% of users are aware of this functionality - I would assume that even most hacker news readers probably don't know about PowerQuery/PowerPivot, writing M in excel and setting up data relationships e.t.c.

[ Parent | on HN ]