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

761–770 of 834 posts

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

#761

Post by Maha-pudma »

jasode wrote: post #715
Maha-pudma wrote: post #585

Earlier quoted context omitted.

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 quest…

I'm certainly not singling out excel, this thread is just about excel so thought I'd share my frustration with that. I've had similar experiences with pandas certainly. I've not used the rest of your examples, but do have similar issues with libreoffice calc. Regards pandas it is certainly easier sorting out the assumptions independent programmers make by including 'dtype=str'.

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

#762

Post by rustybelt »

salamander014 wrote: post #726
lima wrote: post #721

Earlier quoted context omitted.

The issue is that CSVs are untyped and software has to guess the type, sometimes unsuccessfully.

You are right on the cause of the issue. But the software does not HAVE to guess the type. If I paste something into Excel, I want what I pasted to be what's in Excel. Changing the type and inadvertently affecting the data isn't something any data software should do by default. And the fact that OP said they can't turn it off means that this is beyond a design mistake, it is a FEATURE. If Excel took itself seriously…

You can format the column as text before you paste or you can load data from text and set the types for every column. Excel has features to handle all of these issues. I think it's biggest problem is that it's just too easy to use. It will allow novices to do all sorts of things with their data without providing guardrails for common mistakes.

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

#763

Post by jugg1es »

ivanhoe wrote: post #686
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?

These aren't fixed-width fields with a known length. The length of the field is unique to the person sending the file and they don't tell us how long it's supposed to be.

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

#764

Post by zentiggr »

ineedasername wrote: post #734
lr wrote: post #731

Earlier quoted context omitted.

First off, I “hate” Excel, but only because people misuse it. It, like so many other products of its kind, are for numbers! Yet I would be hard-pressed to find someone actually using it for numbers. People absolutely insist on using it as a generic table system for holding non-number data. I recently had to show a 20+ year Excel-using fanatic how to import data from a CSV file so that they could select as type Text c…

Excel supports a variety of data types other than numbers and includes many built in functions for dealing with non-numeric data. Whatever it's history, it is not accurate to say that it is only for numbers.

Regardless of its _additional_ support for other types, those are only in support of its primary role as a spreadsheet. It is designed around the concept of plugging in _numbers_ and calculating with them. Everything else is to make those calculations well notated and human friendly.

Any other use of Excel is bending it into a role it wasn't intended for, and user beware.

And it is all too easy to just go there since there are soooo many convenience features for those who don't want to laern how to do the tasks well.

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

#765

Post by [deleted] »

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…

[deleted]

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

#766

Post by jugg1es »

tomp wrote: post #714
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)

The 10 digits was just an example. There like 100 different ID types with different formats/lengths in healthcare.

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

#767

Post by carlmr »

lr wrote: post #743
pif wrote: post #736

Earlier quoted context omitted.

> Excel interprets a number and then formats it as a number, Unless you instruct it to interpret the field as a string. > but in healthcare, 10 digit numbers are really strings. I'm wondering, if you expect 10 digits and you get less than that, how difficult is it to add some padding zeroes?

This would assume people (many who have over a decade of experience using Excel) actually know how to do that. The largest problem with Excel is people.

Their database system afterwards could do that.

Or prepend a letter when producing the CSV to avoid EXCEL doing what it does.

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

#768

Post by jugg1es »

baobabKoodaa wrote: post #735
SifJar wrote: post #724

Earlier quoted context omitted.

> you can import CSVs values as strings instead of numbers Of course, but the problem isn't that the person who posted the comment doesn't know this - it's that many users of their systems don't know it. Most people are just going to accept whatever defaults Excel suggests and not know any better, causing problems down the line.

No, that's not correct. The parent poster specifically made this false claim: "that's Excel's default behavior and you CANNOT TURN IT OFF!" The false claim is the part that the poster wrote in all caps. He said that this behavior can not be turned off, when in fact it can be turned off.

How? Where is the setting to turn it off when initially loading a file?

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

#769

Post by zentiggr »

mantap wrote: post #760
rutthenut wrote: post #744

Earlier quoted context omitted.

Validating on upload is sensible and should be done anyway. However, left-padding is not always going to work, it depends on the data. Common problems I see are with phone numbers. UK mobiles begin 07nnn. Excel kindly drops the leading zero and then really helpfully treats the long number as being an exponential-format floating point number, which cannot be turned back to the original number. In our systems, we requi…

One wonders why Excel doesn't store data as strings (or equivalent lossless format) and process it as numbers like SQLite does for instance.

Because Excel is a spreadsheet, for calculating with numbers.

Then there's additional code for dealing with all the inconvenient ways people format things, or want to add text labels, or do things closer to numerical/financial analysis, or all the other extras wrapped around the core "put numbers in boxes and do math".

That misunderstanding is at the core of Excel misuse.

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

#770

Post by rustybelt »

lr wrote: post #743
pif wrote: post #736

Earlier quoted context omitted.

> Excel interprets a number and then formats it as a number, Unless you instruct it to interpret the field as a string. > but in healthcare, 10 digit numbers are really strings. I'm wondering, if you expect 10 digits and you get less than that, how difficult is it to add some padding zeroes?

This would assume people (many who have over a decade of experience using Excel) actually know how to do that. The largest problem with Excel is people.

Excel rules. I still use it daily despite having access to big boy tools (python, SQL, R, etc.) Few tools make it easier to answer quick, simple questions about a dataset. Plus it's still the best way to browse SSAS cubes. Blaming excel for these sorts of problems is like blaming a hammer for not being a screwdriver.

I also work in healthcare.

Post reply on HN