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

661–670 of 834 posts

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

#661

Post by dragonwriter »

petre wrote: post #636
jugg1es wrote: post #538

I work in healthcare and by far the biggest production issues we've ever run into are people who run Excel spreadsheets through our file processing where the leading zero's have been removed from patient identifiers because that's Excel's default behavior and you CANNOT TURN IT OFF! EDIT: I have no idea who downvoted my post because what I said is 100% true. We have to tell customers to stop opening CSVs in Excel and…

Just format the identifier cells as text. I've also had this problem, this is how I solved it.

> Just format the identifier cells as text

CSV is text. If you mean in Excel, if you opened it in Excel (rather than importing and choosing non-default options), you've already lost the data so formatting doesn't help you.

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

#662

Post by dragonwriter »

_pmf_ wrote: post #609
vertere wrote: post #599

Earlier quoted context omitted.

Perhaps by "correct way" you meant "dodgy hack to make Excel happy and risk breaking more sensible implementations"? Excel may predate the RFC but AFAIK MS didn't invent or coin the term CSV, so you can't just say whatever Excel does is correct. The RFC is loose because of nonsense like this, it doesn't mean it was ever a good idea.

Do you want to be a useless sophist or develop software?

For CSV that isn’t solely targeting Excel, hacks around the way Excel works are useless.

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

#663

Post by tomatocracy »

jackjeff wrote: post #405
Reason077 wrote: post #329

Earlier quoted context omitted.

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.

This is the test and trace system for the worst pandemic this country has ever faced, six months in. I’m sorry Excel spreadsheets won’t cut it. This is a management failure. PHE has capable coders. And if not, they could hire some. The NHSX team has managed to write the dang COVID-19 app twice. Once not using the Apple/Google contact API (because management) and a second time properly. It’s all open source and it loo…

As an aside on the Covid-19 app - it is open sourced in the "occasionally throw some code over the wall" kind of way - eg it looks like the github was last updated on 24 September vs the app which has more recent updates. It's not clear they open sourced everything needed to actually build the app either looking through the issues.

This is very very far from the only project doing that but disappointing nonetheless given the amount of public money which was spent on it.

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

#664

Post by Cyberspy »

eCa wrote: post #640
jugg1es wrote: post #538

I work in healthcare and by far the biggest production issues we've ever run into are people who run Excel spreadsheets through our file processing where the leading zero's have been removed from patient identifiers because that's Excel's default behavior and you CANNOT TURN IT OFF! EDIT: I have no idea who downvoted my post because what I said is 100% true. We have to tell customers to stop opening CSVs in Excel and…

I used to work with books, which obviously includes ISBNs. They are 13 digit numbers, helpfully turned into scientific notation by Excel.

The same problem happens with UK phone numbers, where the leading 0 gets chopped off Solvable if there's a space between the dialling code and the number, but when individuals enter their number, they don't always do this

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

#665

Post by gpmcadam »

politelemon wrote: post #349
jl6 wrote: post #315

It’s fun to point and laugh, but is this really just the consequence of an unprecedented health emergency demanding a rush job? As far as bugs go, it doesn’t sound that bad. They didn’t lose data - they just processed it late? And they spotted it within days/weeks, and have a workaround/correction already? And it’s only the reporting that was wrong, not the more important part where they inform people of results? I’d…

They had a budget of GBP 36 million. It's pretty bad.

You need to back that number up with real facts, as this article is regarding the PHE handling of reporting.

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

#666

Post by regularfry »

_trampeltier wrote: post #543
swiley wrote: post #528

Earlier quoted context omitted.

Is it really that hard to spend 5 minutes setting up a decent database? Use sqlite if you really want something simple. I guess you still have to understand locking (especially on distributed filesystems.) I've certainly seen people mess that up with spreadsheets.

The problem is people can't do this in a office. Nobody has admin rights on there computers. Everybody know how yo use Excel. We all know, if you have to make a ticket to your office IT for such things, you mostly have to wait a long long time and it never, never works the first time. I guess that is one of the main reasons, why the world runs on Excel (and MS Access). It just works, it's just there, no dealing with…

Sqlite is a bit of a special case, in my eyes. It's so ubiquitous that it's probably already installed somewhere on the average machine, just not as a top-level application.

Of course, there's a bit of a gap between "it's there on the machine" and "we can rely on it for useful work", but baby steps...

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

#667

Post by [deleted] »

pjmlp wrote: post #583
jugg1es wrote: post #538

I work in healthcare and by far the biggest production issues we've ever run into are people who run Excel spreadsheets through our file processing where the leading zero's have been removed from patient identifiers because that's Excel's default behavior and you CANNOT TURN IT OFF! EDIT: I have no idea who downvoted my post because what I said is 100% true. We have to tell customers to stop opening CSVs in Excel and…

I just upvoted you. You are fully correct, I have seen plenty of stuff like that in life sciences projects.

[deleted]

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

#668

Post by account42 »

sheetjs wrote: post #567
yawaramin wrote: post #563

Earlier quoted context omitted.

Quoting doesn't work. Try opening this CSV with Excel: a,b "01",01 Excel interprets both as the same number–1.

The correct way to generate a CSV cell with a leading 0 is ="01" You can verify this with 01,"01",="01"

Not even Excel uses that syntax when exporting to CSV (at least by default).

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

#669

Post by benhurmarcel »

bitlevel wrote: post #190
TwoBit wrote: post #161

Earlier quoted context omitted.

It's hard to say from here what would be the best thing for them to use, but simply using a modern spreadsheet format instead of the ancient xls seems fine for interchange.

There's not much to stop the devs from using open source tools to create a PHP web frontend and db backend for this data - and secure it as well. Heck, if they still need to export, they could do that from the data too. Sure - use Excel for POC, but get that DB backend up pronto.

I work in an office that relies way too much on Excel.

To consider your solution, first showstopper, it needs a server. We don't have a server, nor anyone who knows how to manage one. We'd need to ask IT, that will take months and they'll require a budget transfer, so we'd need to request it to management (which will need a business case to convince) and involve the finance guys. We can't just plug a RaspberryPi into the wall, not only that would get me fired, but also it wouldn't be able to connect to anything without the company's certificates for the proxy or whatever.

Second, we need people who can code in PHP (and their backups when they leave). Probably in practice we'd need IT to do that, so that's more months and budget required.

Obviously anything stored in the cloud is out of the question, just the authorizations and contracts to do that would take a year.

So in the end it ends up as a shared spreadsheet.

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

#670

Post by gpmcadam »

stoobs wrote: post #86

WTF, they have a big Azure deal with Microsoft, why the hell would the be using Excel as the data source? IT ineptitude of the highest order, although I'm not surprised having been involved with government IT previously.

So easy to pass judgement on critical software decisions from the outside, with absolutely no knowledge of how they work or their training, the pressure from Government, the previous conditions that they found themselves in when a pandemic hit.

Who are "they"? The Government? GDS? PHE? NHS England? NHS Digital? NHSx?

Post reply on HN