Excel may have caused loss of 16,000 Covid tests in England | Hacker News Reader

Excel may have caused loss of 16,000 Covid tests in England

theguardian.com

39 pointsshocks24 comments

Since it looks like they hit the spreadsheet row limit, this is a friendly reminder that the "Data Model" or "Power Pivot" mode [1] of excel scales far beyond [2] the spreadsheet mode.

[1] https://support.office.com/client/power-pivot-powerful-data-...

[2] https://support.office.com/client/Data-Model-specification-a...

Blaming Excel for this or any of the other examples given in the article are like blaming the screwdriver when you strip a screw.

It's like blaming the screwdriver when you should have been using a hammer. Who uses Excel to manage vital data like this?

(Don't answer; I already know: far too many people.)

Hammers and screwdrivers are generally used for completely different purposes. When loosening a screw a screwdriver is the correct tool. You need to know which screwdriver to use and how to use it appropriately. A hammer is generally for compeltely different purposes.

Excel is more similar to the screwdriver. It can be a fine tool for managing data. You just need to know when to select it as the proper tool to do so and then how to use it properly to do so.

Hammers and screwdrivers are both used to fasten things but they're used in different circumstances. Just like databases and Excel can both store data, but they probably should have used a database here.

This is an argumentative dead end for both of you. You're both arguing over the specificity of an analogy, I would imagine you're both capable of seeing the other person's point, no?

Sometimes arguing over a specific fine point is the point.

Seems like a complete waste of time, but, if that's how you want to procrastinate don't let me stop you :)

Technically neither are used to fasten things but to apply the fasteners which fasten things.

I wouldn't blame Excel for having a row limit that's too low for this sort of data.

But if, as the article suggests, loading a CSV file with too many into Excel leads to it silently dropping data, I absolutely would blame it.

More like blaming a hammer for stripping a screw. I like Excel, but they should have moved this to Access if they wanted to stay with MS Office.

Thanks for the laugh!

I wonder if this is legacy software shoehorned into a current-day emergency situation, or something that vendors recently built as Covid-19 ramped up. In practice, depending on the data workflow and expected use cases, it's not terrible to use software that records in an Excel spreadsheet, especially if the software is intended for scenarios of 1 to 100K records. But today, it seems like a lot more work to create such a rickety system than to, say, design around SQLite.

The Daily Mail had a concerning detail about the current "fix":

https://www.dailymail.co.uk/news/article-8805697/Furious-bla...

> The technical issue has now been resolved by splitting the Excel files into batches.

No one in their right mind uses a spreadsheet for data analysis. It just too easy to make mistakes. As in that World Bank accountant who entered '25' under the impression he was entering a percentage (0.25). The spreadsheet duly carried the calculation which was off by a factor of a hundred. Not only that, as the size of the dataset grows, so does the time necessary to complete the calculations. Sometimes the whole weekend, as I recall reading somewhere.

Sadly, lots of people are not in their right mind about these things. Lots of people do use Excel to store and share data, leading to all sorts of problems. Strings inadvertently getting converted to dates, leading/trailing whitespace, data integrity not being enforced in any way, and now apparently missing data. And very important data at that. Some people love Excel just a bit too much.

In my opinion the biggest reason Excel is bad for data analysis is that it is just too easy to produce non-reproducible results.

I had a boss once who knew Excel and thought he was good at data analysis, but he wasn't. I did most of the data analysis, and I used R. That was something he just didn't understand although I did explain it a couple of times.

Things like version control, CI, R/Python for data analysis, all make sense to professionals but not necessarily to dabblers. Perhaps a dabbler in data analysis made this decision.

You will discover all sorts of interesting spreadsheets created by power users to help them do their jobs because IT took too long or failed to deliver them a solution.

I can see the appeal of the likes of Airtable.

More than that, Excel is basically the top competitor of every SaaS. Whatever you're doing some user has a spreadsheet full of macros that does the same thing.

https://tomtunguz.com/unbundling-of-excel/

https://foundationinc.co/lab/the-saas-opportunity-of-unbundl...

It is not just about lost data. Afaik there is no way of tracking the data if one just does cut and paste from one Excel spreadsheet to another. Even staying with csv files and probably <100 lines of python just to verify that each of the rows in csv confirms to some standard and that it was successfully loaded in a SQL database would be way better. Not to mention logging of all actions with md5sums/timestamps of all data files.

Putting aside the issue of whether or not Excel is suitable. Excel has a column limit of 16,384 columns. Could this mean they are storing each person as a column? That seems a pretty strange way of recording this data. Or have they gone 16,000 over the 1,048,576 row limit?

The article says they went over the row limit so the later.

Using Excel is strange, so using columns really isn’t a massive leap. It is rows as per other articles, there aren’t even that many tests available to go over 1m.

Modern versions of excel (after office 2007), using .xlsx files, have a 1,048,576 row limit. But older versions of excel (before office 2007) used .xls files and had a limit of 65,536 rows.

I could easily see them exceeding this limit, if they were using an older version of excel.

Alternatively, even the new versions of excel enforce the 2^16 row limit if working on a .xls file instead of a .xlsx file.

I'll be honest, I don't think this was incompetence.

Reply on news.ycombinator.com