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

721–730 of 834 posts

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

#721

Post by lima »

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…

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

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

#722

Post by edejong »

_pmf_ wrote: post #609
vertere wrote: post #599

Earlier quoted context omitted.

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.

Do you want to be a useless sophist or develop software?

I guess the HN audience has voted. The result is: hacking around Excel stupidities is itself a stupid idea.

If you want Excel to understand your output, perhaps use a library which can write xlsx files.

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

#724

Post by SifJar »

baobabKoodaa wrote: post #719
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.

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

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

#725

Post by higeorge13 »

commandlinefan wrote: post #393
lordnacho wrote: post #247

The problem is that anyone who isn't a decent coder think excel is a sensible choice for this, because they've seen it in their school or work. So when the proposals come in, you're going to see one guy who says it's all common sense and we use familiar old excel for everything, and another lunatic who says something called "pigsqueal" is actually the standard, connected to a "frontend" which for some reason is now s…

> another lunatic who says something called "pigsqueal" And it doesn't help that there's a 25-year-old who says, "piqsqueal is outdated anyway, modern organizations use MonkeyDB and Hand Goop".

rofl

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

#726

Post by salamander014 »

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.

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 as a data platform, it would not make a change to any text by default.

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

#727

Post by desas »

jonplackett wrote: post #288
lordnacho wrote: post #247

The problem is that anyone who isn't a decent coder think excel is a sensible choice for this, because they've seen it in their school or work. So when the proposals come in, you're going to see one guy who says it's all common sense and we use familiar old excel for everything, and another lunatic who says something called "pigsqueal" is actually the standard, connected to a "frontend" which for some reason is now s…

Come on, this is a government department of a first world country. They have databases for all sorts of things. They're not using excel for any other NHS databases, or the terrorism watchlist. There are plenty of people capable of making a good decision here. I think you let them off too easily by just assuming they're dumb. This a bad decision by people who definitely should have known better.

> They're not using Excel for any other NHS databases

I assure you that you're wrong. The SaaS I work for replaces a suite of independently re-invented Excel files used in conjunction with other SaaS. NHS trusts are our main customers, I can only assume the NHS is full of excel spreadsheets.

(Technically speaking PHE is not part of the NHS, it has more in common with the civil service in some ways.)

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

#728

Post by reportgunner »

iso1631 wrote: post #701
reportgunner wrote: post #678

Earlier quoted context omitted.

Excel actually checks that and gives you an 'Inconsistent formula' warning with a little green corner similar to when you comment a cell. All you need to do is scroll through the worksheet you have just made.

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.

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

#730

Post by thirdsun »

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.

Does it? I think it should treat everything as a string since it can't be certain about the correct type. It could also suggest the correct type rather than applying it without user confirmation.
Post reply on HN