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

751–760 of 834 posts

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

#751

Post by hunter2_ »

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?

I haven't used it much myself, but I think Access is meant to answer this question.

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

#752

Post by rossdavidh »

mcfedr wrote: post #594
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…

If they had used the newer format, we'd be talking about this in a few weeks time when the larger limit was reached. This is a problem of bad coding, and using the wrong tool for the job. A defensive coding practice would have prevented this from going unseen. Using a database to store data would have prevented such arbitrary limits.

Yeah, something like "Missing Covid-19 test data was caused by using spreadsheet instead of database", would be more accurate.

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

#753

Post by matt-attack »

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…

I believe excel considered itself a finance system not a data system. Hence the clear bias towards numbers over strings.

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

#754

Post by hunter2_ »

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?

With US ZIP (postal) codes, padding with zeroes can actually be ambiguous because we have "zip+4" which is often stored as two fields, but just as often concatenated into a single representation. If you have a zip+4 of "05555-0444" in two fields that each lose leading zeroes, and then concatenate to "5555444" you can't reconstruct it with certainty (00555-5444 or 05555-0444 or 55554-0044).

I had a package get seriously delayed one time because it kept being sent to a city whose 5 digit zip code was equal to [my 5 digit zip code, less its leading 0, and the first digit of my +4]. Fun times.

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

#755

Post by [deleted] »

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

[deleted]

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

#756

Post by zentiggr »

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…

But Excel has never been a data platform. It's a spreadsheet.

It's user's faults for using it in ways that it was never designed for.

Excel has always been about sticking numbers in boxes and calculating with them.

If you want unmodified string input, input strings into a tool intended to handle them.

Project specifications can be hard. Using 1) .xls files after they were superseded, 2) ANY data transfer method without considering capacity or truncation issues, speaks of incompetence.

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

#757

Post by Bukhmanizer »

codegladiator wrote: post #574
Bukhmanizer wrote: post #546

Earlier quoted context omitted.

I think similar to “code smells” in software engineering, using excel for critical data pipeline work can be considered a “data smell”. It’s not bad in and of itself, but it’s a signal that something may have gone seriously wrong with the data engineering process.

On the other hand, its more like "nobody ever got fired for using excel"

I think in this case it’s more like “nobody who would know better than to use excel had the authority to make the decision not to use excel”. Given that this was a government project, my guess is that there are very few technical people involved at the decision making level.

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

#758

Post by baud147258 »

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.

> subcontracting this work to the lowest bidder

knowing a little how things (don't) work in the UK, it's likely subcontracted, but to a company belonging to a mate of the director in charge of the whole thing

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

#759

Post by jasode »

collyw wrote: post #745
dageshi wrote: post #652

Earlier quoted context omitted.

People start using excel when the requirements are unknown, they use excel to understand the requirements while still being functional. To build a "proper solution", you frontload all the requirement discovery to build a system which only you or another developer will be able to update/change. We blame excel, but excel is really just being used for prototyping and nobody takes a decision at a certain point to move on…

I would say that people use Excel because they don't understand how to set up a relational database. It's usually non-technical users that set up Excel, then at a later stage I get asked to create a proper database from it.

>I would say that people use Excel because they don't understand how to set up a relational database. It's usually non-technical users that set up Excel,

I disagree that lack of db knowledge is the primary reason. I'm a programmer and I usually use MS Excel because it's easier than relational databases. I prefer Excel even though my skillset includes:

+ Oracle DBA certification and working as a real db administrator for 2 years

+ MySQL and MS SQL Server programming with raw "INSERT/UPDATE/DELETE" or with ORMs

+ SQLite and programming with its API in C/C++/C#

+ MS Access databases and writing VB for enterprises

The problem is none of the above databases (except for MSAccess) come with a GUI datagridview for easy inputting data, sorting columns, coloring cells, printing reports, etc.

Yes, there are some GUI tools such as SQLyog, Jetbrains DataGrip, Navicat, etc... but none of those have the flexibility and power of Excel.

Yes, a GUI frontend to interact with backend databases can be built and to that point, I also have in my skillset: Qt with C++ and Windows Forms with C#.

But my GUI programming skills also don't matter because for most data analysis tasks, I just use Excel if it's less than a million rows. Databases have a higher level of friction and all of my advanced skills don't really change that. Starting MS Excel with a blank worksheet and start typing immediately into cell A1 is always faster than spinning up a db instance and entering SQL "CREATE TABLE xyz (...);" commands.

Of course, if it's a mission-critical enterprise program, I'll recommend and code a "real" app with a relational database. However, the threshold for that has to be really high. This is why no corporate IT department can develop "real database apps" as fast as Excel users can create adhoc spreadsheets. (My previous comment about that phenomenon: https://news.ycombinator.com/item?id=15756400)

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

#760

Post by mantap »

rutthenut wrote: post #744
ivanhoe wrote: post #686

Earlier quoted context omitted.

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?

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.
Post reply on HN