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

791–800 of 834 posts

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

#791

Post by anonymou2 »

mFixman wrote: post #607
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.

Every time I see a problem like this I can't stop thinking that computer education is terrible everywhere. We are able to teach almost everyone how to use complex software like Word and Excel. Why can't we teach people how to use a terminal, SQLite, or how to create a very simple Python script?

Well maybe it has something to do with the Windows monopoly and that they only want you to use their proprietary tools. If you learn too much then you will want to be free!!

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

#792

Post by Isthatablackgsd »

lr wrote: post #737
mewpmewp2 wrote: post #733

Earlier quoted context omitted.

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.

Excel have lots of different uses. It can be used as inventory tracking, sales, catalog, mileages, contact list, and the list goes on. It is useful in many different fields and industries.

You have to remember, Excel is extremely powerful beast. It have many specialized features that will handle any data it encountered with. I used Excel for 15 years and I am still finding features that made the process quicker. Of course, Excel have its limits and I am well aware of that.

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

#793

Post by oauea »

pif wrote: post #736
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?

> Unless you instruct it to interpret the field as a string.

Except double clicking a CSV does not give you the option to do this. At this point Excel already decided to corrupt your data. And guess how most users open CSV files? That's right, they double click.

> I'm wondering, if you expect 10 digits and you get less than that, how difficult is it to add some padding zeroes?

If you expect 10 digits and get less than that, you have corrupted input data. Trying to "fix" this is exactly the sin excel is committing. Don't do that.

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

#794

Post by oauea »

petre wrote: post #732
dragonwriter wrote: post #661

Earlier quoted context omitted.

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

Nearly all end-users open a CSV like this:

1. They see a file (they have file extensions turned off, which is the default, so they probably don't even know what a CSV is)

2. They double click it

Excel now corrupted the data. That is the problem. Good luck teaching all end-users how to use Excel properly.

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

#795

Post by SCNP »

throwaway_pdp09 wrote: post #783
thedudeabides5 wrote: post #773

Earlier quoted context omitted.

You want Excel to auto-interpret a table of numbers, that come from a CSV, as entirely text? Looking forward to the first time anyone tries to use your excel on a table of numbers and then immediately has to multiply everything by *1 (in a separate table) just to get it back into numbers...

Oh yes please. At least you would know what's happening and be in control of it "Hey, is that a date? I bet that's a date!" - Aaaargh Noooo!

YES!! I seem to remember a few versions of Excel ago there was a way to import data and set the data type for each column in a preview window to see what it would look like. And to your point, it's very easy to select an existing column in Excel and change the data type anyway. So why not just make everything a string by default?

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

#796

Post by dragonwriter »

oauea wrote: post #794
petre wrote: post #732

Earlier quoted context omitted.

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

Nearly all end-users open a CSV like this: 1. They see a file (they have file extensions turned off, which is the default, so they probably don't even know what a CSV is) 2. They double click it Excel now corrupted the data. That is the problem. Good luck teaching all end-users how to use Excel properly.

> They have file extensions turned off, which is the default, so they probably don't even know what a CSV is

And if, also by default, Excel is setup with an association with CSVs, the CSV file will, in addition to not having an extension to identify it, will have an icon which identifies it with Excel.

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

#797

Post by michaelcampbell »

lima wrote: post #721
jasode wrote: post #715

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

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

> has to guess the type

I believe that's the point, it certainly does NOT need to.

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

#799

Post by iso1631 »

reportgunner wrote: post #728
iso1631 wrote: post #701

Earlier quoted context omitted.

All you need to do is scroll through 20,000 lines looking at each one

20,000 is not a lot of lines though, I used to scroll 300k sometimes. Anything below 500k rows is a 'small table' still.

The point is that's a manual, tedious, process that will be performed with error or completely skipped.

We have computers to do that sort of work.

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

#800

Post by yawaramin »

ComodoHacker wrote: post #596
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.

I'd take that bet ;-) From the article:

> > And it appears that Public Health England (PHE) was to blame, rather than a third-party contractor.

Post reply on HN