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":
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.
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?
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?
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.
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.
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.
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.
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.
25 comments
[ 2.7 ms ] story [ 48.8 ms ] threadThe 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.
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.
(Don't answer; I already know: far too many people.)
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.
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.
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.
[1] https://support.office.com/client/power-pivot-powerful-data-...
[2] https://support.office.com/client/Data-Model-specification-a...
I can see the appeal of the likes of Airtable.
https://tomtunguz.com/unbundling-of-excel/
https://foundationinc.co/lab/the-saas-opportunity-of-unbundl...