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

681–690 of 834 posts

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

#681

Post by blablabla123 »

swiley wrote: post #528
totaldude87 wrote: post #491

All previous major spreadsheet screwups were costing money (largest being $6 billion in losses due to a excel formula mishap) , but this one is playing with lives :( 5 – London Olympics Oversells Swimming Event by 10,000 Tickets 4- Banking powerhouse Barclay’s accidentally bought 179 more contracts than they intended in their purchase of Lehman Brothers assets in 2008. Someone hid cells containing the unwanted contra…

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.

Sure, Excel is instantly available. But also you get charting and formulas. Especially with formulas they've done something really right. You can transform a column in a few seconds while keeping the old one, tweaking the new one and having a graphical preview through the chart feature at the same time. Of course every Spreadsheet software can do this, but to me that's the ultimate killer feature

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

#682

Post by diarrhea »

vhold wrote: post #310
diarrhea wrote: post #210

Each test result creating several rows of data seems like a problem too. In clean data, every observation is one row. It makes working with the dataset much easier. In this scenario, I would expect one observation to correspond to one test result. The multiple rows are then better off pivoted into columns.

I'd say it depends on the data. If the schema is consistent between rows, and it turns out a test result is made up of several rows because the test is composed of several stages, I would leave it as is until reporting time. If you pivot prematurely, you could end up dropping data because there are new stages didn't exist when you implemented the pivot.

> If the schema is consistent between rows, and it turns out a test result is made up of several rows because the test is composed of several stages, I would leave it as is until reporting time.

In that scenario, indeed no action seems to be needed, because each row is one observation: every test stage is an observation. So it would seem to make sense.

One could then argue that each patient deserves their own table (observational unit).

But as other commenters pointed out, this is all speculation.

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

#683

Post by jp0d »

jaza wrote: post #575
jp0d wrote: post #532

You'd be surprised to hear how many big organisations still use Excel for their reporting needs. I used to worked at a company with 35000 employees. Their payroll reports were generated using a MS Access database and reported using Excel. It was a massive manual job every week as they'd weekly, fortnightly and monthly payroll cycles! It's very easy to lose files and/or make an error in the calculations. But nobody bo…

See, they had far less than 65,536 employees, so Excel was fine for their workflow!

lol.. These days it can support up to a million employees! :D

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

#684

Post by disgruntledphd2 »

rjtavares wrote: post #644
computerfriend wrote: post #598

Earlier quoted context omitted.

Also the post-2008 financial crisis austerity programs based on Excel analysis. https://mathbabe.org/2013/04/17/global-move-to-austerity-bas...

Austerity wasn't based on that study, and it didn't go away once that error was found.

That study was the intellectual justification for the policy.

To be fair, I'm not sure if any of the proponents actually believed (or had read) the study, but it was definitely wheeled out in debates against Keynesians.

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

#685

Post by stoobs »

gpmcadam wrote: post #670
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?

Yes, with direct previous experience and knowledge of large UK Government IT projects, how they were integrated and how staff were trained, I feel I'm well placed to comment harshly.

"They" in this case was the UK Government, and part of the goal was creating secure backend systems specifically for the NHS as a data store, Public Health England would absolutely have access to it.

Even the most basic of checks at the start of the project would have highlighted that Excel was not a proper solution for the application (Even if the developer(s?) had used the newer(?!?) XLSX format rather than XLS) - which highlights that there was no proper oversight as to how the system was constructed.

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

#686

Post by ivanhoe »

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…

Couldn't you validate the data on upload or if it's such a common problem just auto-fix it by left padding w/ zeros?

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

#687

Post by rutthenut »

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…

Agree with the problem reported here - the fact that Excel will drop leading zeroes and will turn phone numbers into Exponential format causes many live issues where customers use Excel to work on 'CSV' files :(

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

#688

Post by TacoSteemers »

altacc wrote: post #651
TacoSteemers wrote: post #621

Earlier quoted context omitted.

Maybe it can be solved by prepending "ID:" for exports, and removing it on import. It could be proposed as a convention for all HC software companies.

Introducing standards across many companies is incredibly difficult but your main problem is that a lot of healthcare IT systems are very old and not actively maintained, they're essentially on life support. So changing the format of a file, or requiring new import cleaning, is going to break integrations and data sharing in many places. Basically it'd create far more problems than it's attempting to solve.

That is understandable.

I guess it depends on which country and which specific part of healthcare you are active in. In the 'care' domain in the Netherlands I also see that some of our integrations are one-off, but the most important ones do have industry wide standardization that receives updates based on law.

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

#689

Post by rutthenut »

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…

Interesting to see just how many responses to this real-world problem are suggesting that people rewrite CSV files to work with Excel.

They clearly do not understand system integration and the use of CSV text files for data interchange between multiple systems and application. Hey, JSON and Javascript libraries are the answer to that, eh

There are already enough potential issues with CSV interpretation on wrapping strings, escaping characters and so on, but changing the content when a delimiter is found should not be added to that list.

You bold point is the most important, the default behaviour of Excel when opening a plain text CSV file is to alter the content for display, applying magic and often-unwanted formatting rules. That should be optional.

It should be possible to open a text CSV file in Excel, view the contents in columns but the same textual form, save the file in CSV format and open it in another viewer/editor and still see the same content as the original file.

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

#690

Post by eythian »

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.

I used to do migrations between library systems, and often they'd export from the old system as CSV. One rule I gave them was "never open this file in excel", because they'd always try to be helpful by tidying something and I'd end up with scientific notation ISBNs, dates that were in one form if they could be coerced into US-style infix, otherwise left in the original form, barcodes losing zeros, etc. etc.
Post reply on HN