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

#191

Post by TeMPOraL » Mon, Oct 05, 2020, 7:10 PM UTC

londons_explore wrote: post #5

Mvandenbergh wrote: post #2

It's unlikely this will have had much impact since most reported cases were only a few days late but come on guys, really? Using an Excel sheet as a database? In week 1, that would be "not great" but could be accepted as being a fast solution that everyone could work with. This far into a pandemic I think we can expect a little more professionalism in data handling.

People use excel sheets as databases all the time because it doesn't require any specialist knowledge to build or use. Sure, we techy types prefer a proper database with a web frontend and an API, but that requires significantly more skill to build than an excel file. No database has the flexibility and flat learning curve that excel has. Until a database manages to meet these goals, it remains a good tool for certai…

Don't forget the formulas! Excel is essentially a functional reactive programming REPL. No RDBMS I know lets you easily define a table in which cells in a row depend on values from previous cells in a row, and keep this as an invariant.

People working on Excel replacements need to remember about this aspect of spreadsheets too.

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

#192

Post by usmannk » Mon, Oct 05, 2020, 7:12 PM UTC

SV_BubbleTime wrote: post #176

>As a consequence, each template could handle only about 65,000 rows of data Oh? Like 65,535 or so? That seemed weird a first that even as old as xls is that only 16bits were allocated to max rows. But then maybe not. Each row might get an ID, so that's 16bits * rows you have. I wonder if when XLS was designed they considered it very unlikely many people would have 500MB of db of empty rows and then would needed more…

XLS does in fact have a 16 bit row counter :/

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

#193

Post by isodev » Mon, Oct 05, 2020, 7:12 PM UTC

bencollier49 wrote: post #133

Good lord. Old XLS files. I'd love to have seen how this was concocted.

“Let’s see if StackOverflow has something”

...

“Oh nice! Exactly what I need!”

,

...

“Woah it builds! Ship it!”

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

#194

Post by dan-robertson » Mon, Oct 05, 2020, 7:12 PM UTC

SV_BubbleTime wrote: post #176

>As a consequence, each template could handle only about 65,000 rows of data Oh? Like 65,535 or so? That seemed weird a first that even as old as xls is that only 16bits were allocated to max rows. But then maybe not. Each row might get an ID, so that's 16bits * rows you have. I wonder if when XLS was designed they considered it very unlikely many people would have 500MB of db of empty rows and then would needed more…

The xls format is pretty old. The newer formats (xlsx, xlsb, and so on) allow for larger spreadsheets.

The reason the spreadsheets in the old format couldn’t grow was probably compatibility. The reason for the limit in the first place was probably about reducing memory on disk and in main memory.

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

#195

Post by shadowgovt » Mon, Oct 05, 2020, 7:12 PM UTC

TeMPOraL wrote: post #166

shadowgovt wrote: post #159

Earlier quoted context omitted.

The silent part there is the real surprising part to me. You'd think the exporter would die noisily if it still had data in the buffer and it ran out of file.

I'm betting the exporter isn't running out of file, but out of loop counter.

I mean, same difference. "I cannot generate a file representing state of data in RAM" is a 'fail noisy' error, not a 'fail silent' error.

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

#196

Post by makomk » Mon, Oct 05, 2020, 7:14 PM UTC

fuzzy2 wrote: post #52

Mvandenbergh wrote: post #46

Earlier quoted context omitted.

I was being serious. Distributionally, most of the cases were 1-2 days before the error was spotted. To be clear though, when I said "not much impact", I didn't mean that people might not die because of it, only that it wouldn't affect the large scale functioning (such as it is) of the tracing operation.

> Distributionally, most of the cases were 1-2 days before the error was spotted. That’s always true though if the virus is spreading exponentially (dunno whether it currently does in the UK).

This is more true than it would be from pure exponential growth alone, especially the relatively slow exponential growth the UK as a whole currently seems to be seeing (doubling time maybe about a fortnight?) The issue was effectively dropping all results after a certain number from a particular location, so at the start relatively few results were dropped and it was spotted around the time that number started to become substantial. It's that hard cutoff where results suddenly start to be dropped that means most of them are in the last few days.

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

#197

Post by jsty » Mon, Oct 05, 2020, 7:14 PM UTC

mjw1007 wrote: post #181

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 d…

> 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

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

#198

Post by supercanuck » Mon, Oct 05, 2020, 7:15 PM UTC

beagle3 wrote: post #85

This is indeed user incompetence, but so far every news source quoted the official PR and said it was an "Excel Problem". Microsoft PR was caught unprepared - I wonder how they'll re-spin it in the next few days (and for the first time that I can recall, a Microsoft product was wrongly blamed...) Pay attention, how every time there's a Windows virus or worm, it's a "Computer Virus", but in the (extremely rare) occasi…

Could also be a lack of budget funding. The newer version of excel supports: 1,048,576 rows

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

#199

Post by jononor » Mon, Oct 05, 2020, 7:16 PM UTC

Always use checked pre- and post-conditions in a data pipeline.

This simple post-condition would have caught this issue: The sheet after merge operation must have a number of rows equal to the sum of number of rows for all merged sheets.

Assuming this is a merge of a standardized input, then another post-condition might be: The number of columns in output shall equal the number of columns in the input. Might want to check header names, and order as well.

Thinking in terms of universal properties, and putting the checks into production, is better than unit-testing.

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

#200

Post by mark-r » Mon, Oct 05, 2020, 7:17 PM UTC

whimsicalism wrote: post #182

mark-r wrote: post #153

Earlier quoted context omitted.

Have you seen the kind of mess Excel can make with a CSV file? The names of several genes were recently changed so that Excel would stop mangling them.

CSV != Excel. I work CSVs regularly and can't recall the last time I opened up Excel (intentionally)

Doesn't Excel by default capture the .csv extension so that it gets called automatically when you try to open the file?

Since Excel is one of the few standard pieces of software that knows how to open CSV, it gets used a lot of times when it shouldn't. There's another post I made comparing Excel to a swiss army knife, and there's a reason for that.