Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

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

bbc.com

Post reply on HN
Print view View original

121–130 of 834 posts

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

#121

Post by indy »

callamdelaney wrote: post #105
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…

It's not a user issue, it's a government incompetence issue. Who was the technical genius who thought 'right, we need to store thousands of lines of critical and important data, I know! Excel!'

I think you'd be shocked by how many of our financial and social institutions rely on emailing Excel spreadsheets to one another.

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

#122

Post by WrtCdEvrydy »

callamdelaney wrote: post #105
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…

It's not a user issue, it's a government incompetence issue. Who was the technical genius who thought 'right, we need to store thousands of lines of critical and important data, I know! Excel!'

Alternatively...

"We need to store some data for a virus" "Where does the data come from?" "Every hospital sends us an excel sheet" "Well, let's merge all of it into a bigger sheet and display it or export it"

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

#123

Post by libraryatnight »

callamdelaney wrote: post #105
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…

It's not a user issue, it's a government incompetence issue. Who was the technical genius who thought 'right, we need to store thousands of lines of critical and important data, I know! Excel!'

In my org excel might have been the start, the intent being to move over to a DB once someone got it stood up. Then nobody would stand up the DB - because not urgent, we have the excel for now. The DB would be completely forgotten, and 3 years down the road everybody would be wondering wtf we have this huge excel file for. Then we would have six weeks of meetings to discuss the file until something else comes up and we forget it for another 3 years.

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

#124

Post by kolaente »

cma wrote: post #118

Why does Excel still have such low row limits in 2020? When they increased it from 64K, did they decide to only go to a million to promote Azure for "Big Data"?

You seem to assume the government used a modern version of Excel

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

#125

Post by codeulike »

gridlockd wrote: post #119
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…

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 you read the article (perhaps its been updated?), they were using the old XLS format and they hit the limit at 64,000 lines

PHE had set up an automatic process to pull this data together into Excel templates so that it could then be uploaded to a central system and made available to the NHS Test and Trace team as well as other government computer dashboards.

The problem is that the PHE developers picked an old file format to do this - known as XLS.

As a consequence, each template could handle only about 65,000 rows of data rather than the one million-plus rows that Excel is actually capable of.

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

#126

Post by codeulike »

karmakaze wrote: post #109

TL;DR [I found it hard to find which actual limit was hit.] > The problems are believed to have arisen when labs sent in their results using CSV files, which have no limits on size. But PHE then imported the results into Excel, where documents have a limit of just over a million lines. > The technical issue has now been resolved by splitting the Excel files into batches.

Further down, maybe the article has been updated

PHE had set up an automatic process to pull this data together into Excel templates so that it could then be uploaded to a central system and made available to the NHS Test and Trace team as well as other government computer dashboards.

The problem is that the PHE developers picked an old file format to do this - known as XLS.

As a consequence, each template could handle only about 65,000 rows of data rather than the one million-plus rows that Excel is actually capable of.

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

#128

Post by sct202 »

codeulike wrote: post #125
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 you read the article (perhaps its been updated?), they were using the old XLS format and they hit the limit at 64,000 lines PHE had set up an automatic process to pull this data together into Excel templates so that it could then be uploaded to a central system and made available to the NHS Test and Trace team as well as other government computer dashboards. The problem is that the PHE developers picked an old fil…

Did they pick XLS because it's easier to parse or something? I can't imagine why someone would pick a format that was largely replaced 10 years ago.

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

#129

Post by codeulike »

The BBC updated the article with technical details, see about halfway down. Here's the tech details:

(PHE = Public Health England)

Analysis by Leo Kelion, Technology desk editor -----------------------------------------------

The BBC has confirmed the missing Covid-19 test data was caused by the ill-thought-out use of Microsoft's Excel software. Furthermore, PHE was to blame, rather than a third-party contractor.

The issue was caused by the way the agency brought together logs produced by the commercial firms paid to carry out swab tests for the virus.

They filed their results in the form of text-based lists, without issue.

PHE had set up an automatic process to pull this data together into Excel templates so that it could then be uploaded to a central system and made available to the NHS Test and Trace team as well as other government computer dashboards.

The problem is that the PHE developers picked an old file format to do this - known as XLS.

As a consequence, each template could handle only about 65,000 rows of data rather than the one million-plus rows that Excel is actually capable of.

And since each test result created several rows of data, in practice it meant that each template was limited to about 1,400 cases. When that total was reached, further cases were simply left off.

Until last week, there were not enough test results being generated by private labs for this to have been a problem - PHE is confident that test results were not previously missed because of this issue.

And in its defence, the agency would note that it caught most of the cases within a day or two of the records slipping through its net.

To handle the problem, PHE is now breaking down the data into smaller batches to create a larger number of Excel templates in order to make sure none hit their cap.

But insiders acknowledge that their current clunky system needs to be replaced by something more advanced that does not involve Excel.

edit: also longer version by Leo here https://www.bbc.co.uk/news/technology-54423988

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

#130

Post by secondcoming »

daanlo wrote: post #16

We did a bit of work for laboratories this year and csv is not an uncommon exchange format between labs. In general almost all exchange formats are text based, with labs saying they will upgrade to „modern xml formats“ at some point in the future. So seen in this context a csv or an excel file doesn‘t really surprise me and should probably also be seen in this context.

XML would fix the max rows issue, but open you up to OOM issues instead!
Post reply on HN