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

711–720 of 834 posts

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

#711

Post by eru »

eCa wrote: post #708
eru wrote: post #642

Earlier quoted context omitted.

Don't ISBNs also contain an X sometimes?

The old ISBN10 standard could get X (meaning 10) as the last character (the last number is a check digit), but ISBN13 (which prefixes 978 or 979 to ) is all numbers. The check digit is calculated differently. One obvious advantage is that the ISBN13 is barcode compatible. I would guess that most modern actors in the book business has been primarily using ISBN13 for at least the last decade.

Thanks! The last time I read about ISBNs was probably more than a decade ago.

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

#712

Post by mikorym »

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…

It is sometimes possible to write code to alter your CSVs to retain the strings.

The first thing would be to write them as: entry1,"0123456789",entry2 rather than entry1,0123456789,entry2. This has worked for me in some instances in Excel whereby I have to escape certain things inside a string, but I would not be surprised if Excel still messes this up. For example, giving the triangle exclamation mark box and then helpfully suggest to convert to number.

If you want to go further, you can do something like write a routine that alters the CSV, such as entry1,hospitalString(0123456789),entry2. Sure, there are problems with this too, but Excel can break a lot of things and the above examples I do use in practise (the first example I put the double quotes to escape single quotes in foreign language unicode).

Another thing Excel can do is break your dates, by switching months (usually only for dates = 13th) or convert dates to integers.

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

#713

Post by swiley »

ako wrote: post #572
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.

Things are never 5 minutes. And it is just as easy to make mistakes with sql as it is with excel.

I have definitely just spent 5 minutes importing csv tables into sqlite so I can run a query against them.

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

#714

Post by tomp »

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…

So what's the big deal? Just read back the numbers, if it's less than 10 digits prepend zeros. Am I missing something (probably, which means you're leaving something out in the description of the issue)

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

#715

Post by jasode »

Maha-pudma wrote: post #585
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…

Every single problem I've ever had with excel stems from excel trying to be too clever. Nearly all my problems with importing data into it could be solved if excel left everything as is until I asked it to format values a certain way.

>Every single problem I've ever had with excel stems from excel trying to be too clever. Nearly all my problems with importing data into it could be solved if excel left everything as is

We're biased to bash on Microsoft for being "too clever" but maybe we need a reality check by looking at the bigger picture.

Examples of other software not written by Microsoft that also drops the leading zeros and users asking questions on how to preserve them:

- Python Pandas import csv issue with leading zeros: https://stackoverflow.com/questions/13250046/how-to-keep-lea...

- R software import csv issue with leading zeros: https://stackoverflow.com/questions/31411119/r-reading-in-cs...

- Google Sheets issue with leading zeros: https://webapps.stackexchange.com/questions/120835/importdat...

Conclusion: For some compelling reason, we have a bunch of independent programmers who all want to remove leading zeros.

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

#716

Post by baobabKoodaa »

theshrike79 wrote: post #639
corty wrote: post #600

Earlier quoted context omitted.

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, nev…

I vaguely remember a discussion about this and the problem boils down to Excel sheets being impossible to unit-test. There might be, for example, a formula going through 20k lines in column F. But the one on line 1138 has a typo and the formula references an incorrect cell. No human will ever go through all the lines to check. Excel itself doesn't check stuff like that. And there are no tools for it either.

You can easily create tests for cases like that in excel. For example, if you know that all cells in a column must conform to specific conditions, you can create a new cell with a formula that validates the other cells. I have a bookkeeping excel where the "validation cell" becomes red when it detects errors.

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

#717

Post by logicOnly »

sueders101 wrote: post #517
logicOnly wrote: post #515

Earlier quoted context omitted.

I had Excel error out when I tried to open 10 million rows.

Excel tops out a little more over a million rows without specifically handling of larger files. I’m not sure excusing it, just a heads up to all the junior analysts out there who haven’t dealt with the problem(not implying you’re a junior analyst, just wanted to point something out to those who aren’t familiar with the issue).

No worries, Excel is nice for small/medium sized data and has the UI/Visualization which is nice.

The story I mentioned was because I wanted to look at the data before I started parsing it. I had full expectations to use either sqlite or Pandas.

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

#718

Post by edejong »

dt3ft wrote: post #592
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.

Excel devs, do you hear me? Repent for your sins! Go back to Microsoft and fix those problems. The cost of Excel mishandling numbers and dates must be many orders of magnitude higher than your lifetime salary.

Thank you.

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

#719

Post by baobabKoodaa »

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…

How on earth is this the top rated comment? I would downvote it if I could. As other people have noted, you can import CSVs values as strings instead of numbers.

Furthermore, losing preceding zeroes in number-typed values is not unique to excel; it is a common feature in all typed programming languages.

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

#720

Post by higeorge13 »

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…

Excel is not bad, the users and institutions which use it for any use case are to blame.

The only correct usages of excel (and google sheets) are for user input to other processes, and visualization of data (which should be definitely stored in raw format in some database). And always assuming frequent backups of all sheets and of course manageable datasets. Anything else that includes external processes/scripts which append/ovewrite data in sheets is terrible practice and very error-prone.

Post reply on HN