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

731–740 of 834 posts

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

#731

Post by lr »

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…

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 columns that contain leading zeros. The ability exists, but I have found scant few people who know how to actually use the product properly.

Oh, and I also work in healthcare.

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

#732

Post by petre »

dragonwriter wrote: post #661
petre wrote: post #636

Earlier quoted context omitted.

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.

Yes, I mean Excel. We have CSV to XLS import scripts/forms that format identifier cells as text. The data format is standardised. Using templates to do the imports was the dumb part. Microsoft has a Power BI tool if ones doesn't want to write or use scrips. Use that. I assume a government agency has the resporces to pay for it and for data scientists.

https://powerbi.microsoft.com/en-us/

Thanks for bothering to respond instead of downvoting.

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

#733

Post by mewpmewp2 »

lr wrote: post #731
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…

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…

As a lay-person, what would you use for non numbers tabular data?

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

#734

Post by ineedasername »

lr wrote: post #731
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…

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.

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

#735

Post by baobabKoodaa »

SifJar wrote: post #724
baobabKoodaa wrote: post #719

Earlier quoted context omitted.

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.

> 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.

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

#736

Post by pif »

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…

> 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?

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

#737

Post by lr »

mewpmewp2 wrote: post #733
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…

As a lay-person, what would you use for non numbers tabular data?

People love to use Excel to look at (with their eyes, or a screen reader, i.e., non programmatically) all kinds of data. They use it to list exports from data stores, and then take notes on what they discovered, show and hide data using the filter option, etc. It is just far too easy to use (and misuse) that it ends up being used for any type of data that fits into a table.

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

#739

Post by pif »

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…

> If I paste something into Excel, I want what I pasted to be what's in Excel.

And that's what you have in Excel. What gets displayed is a separate issue.

And no, you don't want to see exactly what you typed in, not in the general case.

And no, I can't believe I am defending Excel!

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

#740

Post by rutthenut »

robk wrote: post #707
rutthenut wrote: post #689

Earlier quoted context omitted.

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

The problem is windows doesn't have a preloaded double click viewer for csv except excel if you have Office. The average user just double clicks. If notepad or wordpad did any kind of formatting for viewing it would be less an issue. So you have to solve for excel as a dev knowing that's what 99% of people will use.

If you 'solved for Excel', it will be broken for the other applications that may want to use data from those Excel-specific CSV files, which is generally why CSV was chosen in the first place.
Post reply on HN