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

591–600 of 834 posts

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

#591

Post by throwaway_pdp09 »

andreareina wrote: post #577
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…

IME numeric identifiers are just asking for trouble. Some systems are sensitive to leading zeros, some systems will zero-pad, loses zero-padding, etc. Worked with a client once with references to the same entity scattered across two databases, one of which zero-padded the identifiers and the other didn't. Oy vey.

> IME numeric identifiers are just asking for trouble

That's a failure of the system if it can't be told to not interpret the data. However you're saying the world is as it is; can't argue.

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

#592

Post by dt3ft »

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 have no idea who downvoted my post

That would be the Excel devs working at Microsoft. They read HN. I can feel it.

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

#593

Post by starky »

bencollier49 wrote: post #133

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

There are a surprising number of people out there that still save their office documents in the old formats for whatever reason they have convinced themselves of. Just a couple weeks ago I had a coworker that sent me a manual for a piece of software he wrote as a .doc file.

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

#594

Post by mcfedr »

rossdavidh wrote: post #427

While I am not in the habit of defending Microsoft, I think it should be pointed out that it wasn't using "Excel" that was the biggest problem, but using "13-years obsolete format for Excel". Not that there was any reason to be using Excel for this, but it's a bit unfair to blame Microsoft for something they never claimed XLS could do, and provided a much better capacity in XLSX 13 years ago. Again, it's hard to cry…

If they had used the newer format, we'd be talking about this in a few weeks time when the larger limit was reached.

This is a problem of bad coding, and using the wrong tool for the job.

A defensive coding practice would have prevented this from going unseen. Using a database to store data would have prevented such arbitrary limits.

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

#595

Post by throwaway_pdp09 »

edanm wrote: post #589

As a data engineer, I think blaming Excel for this is the wrong framing. The real problem here is with the entire process, and specifically, the lack of testing. Excel is great for many use cases, especially if you need people to enter data somewhere. Its UI is unparalleled in terms of quickly giving something to users that they can understand, mess around with, and verify. It's a very common use case to then need to…

I don't think data engineering is hard or needs much resources. It does need people who care and who will think about things.

Anyone who comes at a problem with the mindset 'this is going to he hard' probably lacks experience and will throw big-data frameworks at it, really screwing things up. The most significant, and valuable, resource needed is thought first, and knowledge+experience second.

All IMO anyway.

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

#596

Post by ComodoHacker »

rossdavidh wrote: post #427

While I am not in the habit of defending Microsoft, I think it should be pointed out that it wasn't using "Excel" that was the biggest problem, but using "13-years obsolete format for Excel". Not that there was any reason to be using Excel for this, but it's a bit unfair to blame Microsoft for something they never claimed XLS could do, and provided a much better capacity in XLSX 13 years ago. Again, it's hard to cry…

You get CSV files as input and have to upload them into the database. You don't need Excel at all, neither in XSL, nor in XSLX format.

My bet is the biggest problem here is subcontracting this work to the lowest bidder, presumably from some developing country.

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

#597

Post by rsanders »

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"

If CSV were being used just to exchange data with Excel, we probably wouldn't be using CSV. Many systems neither need nor know that ="01" should be treated as the string "01".

If Excel were the only intended consumer, .xlsx would be a preferable file format. At least it's mostly unambiguous.

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

#598

Post by computerfriend »

tsjq wrote: post #518
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…

and one more: "Scientists rename human genes to stop Microsoft Excel from misreading them as dates. Sometimes it’s easier to rewrite genetics than update Excel" https://www.theverge.com/2020/8/6/21355674/human-genes-renam...

Also the post-2008 financial crisis austerity programs based on Excel analysis.

https://mathbabe.org/2013/04/17/global-move-to-austerity-bas...

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

#599

Post by vertere »

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"

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.

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

#600

Post by corty »

edanm wrote: post #589

As a data engineer, I think blaming Excel for this is the wrong framing. The real problem here is with the entire process, and specifically, the lack of testing. Excel is great for many use cases, especially if you need people to enter data somewhere. Its UI is unparalleled in terms of quickly giving something to users that they can understand, mess around with, and verify. It's a very common use case to then need to…

Disagree on the "users can verify" part. Excel is very hard to verify, because of corner cases like date conversion. Formulas and macros, maybe spread out all over multiple sheets in hidden away white-on-white cells, make things very opaque and impossible to verify for most users. After a long gone colleague has passed the usage of some excel file, it is treated as an arcane and unchangeable wisdom from the past, never to be really understood or changed. Just maybe used as a copy-paste value import to the next generation.

Also, recognized good practices for software development (which any excel sheet that does more than just a sum() will be) like commenting and versioning are quite hard to impossible in excel. So even if you endavour to do it "right" but with excel, it is just the wrong tool for the job.

Post reply on HN