This is clearly a case of someone not knowing the limits of a technology / how to select or use a technology. Yes, more modern versions of Excel support many more rows than 65k. As a "power" excel user in the early 2000s, it's tiring to see this same mistake made. And I might have blamed MSFT in 2008. But the "modern" world has taken an absurdly boorish response to this virus. This unfortunately looks like another example.
Missing Covid-19 test data was caused by the ill-thought-out use of Excel
581–590 of 834 posts
Re: Missing Covid-19 test data was caused by the ill-thought-out use of Excel
#582I have done a couple of gigs in life sciences and Excel is one of the big responsible for VB.NET to be still relevant.
I have met several researchers using Excel, not R, Python, Julia, nope plain old Excel, eventually with some VBA macros.
The more savvy ones, eventually ask IT for VB installation when they outgrown the VBA capabilities and carry on from there with either small Windows Forms based utilities or Office AddIns.
Any attempt to replace those sheets with proper applications has gotten plenty of push back until we basically offered enough Excel like features on the new applications.
Re: Missing Covid-19 test data was caused by the ill-thought-out use of Excel
#583I 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…
I just upvoted you.
You are fully correct, I have seen plenty of stuff like that in life sciences projects.
Re: Missing Covid-19 test data was caused by the ill-thought-out use of Excel
#584Re: Missing Covid-19 test data was caused by the ill-thought-out use of Excel
#585I 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…
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.
Re: Missing Covid-19 test data was caused by the ill-thought-out use of Excel
#586This is clearly a case of someone not knowing the limits of a technology / how to select or use a technology. Yes, more modern versions of Excel support many more rows than 65k. As a "power" excel user in the early 2000s, it's tiring to see this same mistake made. And I might have blamed MSFT in 2008. But the "modern" world has taken an absurdly boorish response to this virus. This unfortunately looks like another ex…
Forgot to mention: there's nothing inherently wrong with storing data in CSV files. One does have to think about consumption and processing. A Postgres database seems pretty reasonable to track information like this, but I wouldn't blame a CSV format for shortsightedness. We're not talking about actual big data here...
Re: Missing Covid-19 test data was caused by the ill-thought-out use of Excel
#587Earlier quoted context omitted.
> Is there anything in Excel itself that behaves like that? I'd imagine the default error-handling behaviour of 9/10 Excel macros is to throw away data
Yes and "on error resume next" is what I usually see in VBA code
Curious, in what context are you all still using VBA?
Re: Missing Covid-19 test data was caused by the ill-thought-out use of Excel
#588I have done a couple of gigs in life sciences and Excel is one of the big responsible for VB.NET to be still relevant. I have met several researchers using Excel, not R, Python, Julia, nope plain old Excel, eventually with some VBA macros. The more savvy ones, eventually ask IT for VB installation when they outgrown the VBA capabilities and carry on from there with either small Windows Forms based utilities or Office…
This was me when our online meetings exploded and I had to record a ton of metrics manually without any prep or software. Now there is this digusting, VBA-augmented excel sheet with a million hacks being read by the c-levels to make very important changes by. I can't vouch for a single cell being accurate at this point. Even the few automatic metrics from the meeting software are misleading from the get go, but then they meet my black-box of doom.
Re: Missing Covid-19 test data was caused by the ill-thought-out use of Excel
#589As a data engineer, I think blaming Excel for this is the wrong framing. The real problem here is with the entire process, and specifically, the lack of testing.
Excel is great for many use cases, especially if you need people to enter data somewhere. Its UI is unparalleled in terms of quickly giving something to users that they can understand, mess around with, and verify. It's a very common use case to then need to pull in data from a bunch of Excel files, into one main repository of data (a data warehouse). That can be stored in an Excel file, although more commonly would be stored in a database.
But there are always problems with this process! There can be missing data, there can be weird data conversions because the program/language you're using to parse the data and get it into the database reads things differently than how Excel intended, there can be weird database issues that causes data loss, etc.
Complex systems always, always have bugs.
It is the job of a data engineering team to, among other things, test the systems thoroughly, and put in place systems to test against data loss, etc. It is pretty common, for example, to count the data going into a pipeline, and the data that you end up with, and make sure nothing was lost on the way.
Anyone can make a mistake. Any team, no matter how good, especially when they're rushed, can use shortcuts, use the wrong technologies because it's expedient, or simply have bugs. It is the job of the team, and of project management in general, to do all the manual and automatic testing necessary to make sure that mistakes are caught.
The real lesson isn't "Excel is bad". It's not. It's an amazing tool. The real lesson is "Data Engineering is hard, requires a lot of resources", and "all systems have bugs - testing is mandatory for any critical system".
Re: Missing Covid-19 test data was caused by the ill-thought-out use of Excel
#590Earlier quoted context omitted.
“Correct” is a strong word. It isn’t defined in https://tools.ietf.org/html/rfc4180 so Excel should not try to be smart and add extensions only they support.
Excel predates RFC4180 by nearly 20 years (RFC4180 is October 2005, Excel 1.0 was September 1985) and this behavior was already cemented when the RFC was written. As for the actual RFC, it's worth taking a read. Any sort of value interpretation is left up to the implementation, to the extent that Excel's behavior in interpreting formulae is 100% in compliance with the spec.
What spec?
Anyway the RFC doesn't mandate any value interpretation IIRC.