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

651–660 of 834 posts

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

#651

Post by altacc »

TacoSteemers wrote: post #621
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…

Maybe it can be solved by prepending "ID:" for exports, and removing it on import. It could be proposed as a convention for all HC software companies.

Introducing standards across many companies is incredibly difficult but your main problem is that a lot of healthcare IT systems are very old and not actively maintained, they're essentially on life support. So changing the format of a file, or requiring new import cleaning, is going to break integrations and data sharing in many places. Basically it'd create far more problems than it's attempting to solve.

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

#652

Post by dageshi »

collyw wrote: post #619
koliber wrote: post #613

Earlier quoted context omitted.

They are costing money. No doubt. At the same time, the business world runs on Excel. How much money is Excel making? I've done my share of cursing at Excel at various jobs. At the same time, I am grateful for the quick and easy way it allows me and many others to manipulate data. It's unfair to just cite the costs of using Excel without acknowledging the benefits it brings.

The real question should be would it be cheaper to replace it with a proper solution (probably something custom built application written by a software engineer). Excel's ease of use is it's downfall. It is the worlds most popular database, despite not actually being a database. I have wasted countless hours dealing with Excel where something else should have been used. I built a database for a friend recently, I thi…

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 from that prototype.

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

#653

Post by simonh »

tsjq wrote: post #518
totaldude87 wrote: post #491

All previous major spreadsheet screwups were costing money (largest being $6 billion in losses due to a excel formula mishap) , but this one is playing with lives :( 5 – London Olympics Oversells Swimming Event by 10,000 Tickets 4- Banking powerhouse Barclay’s accidentally bought 179 more contracts than they intended in their purchase of Lehman Brothers assets in 2008. Someone hid cells containing the unwanted contra…

and one more: "Scientists rename human genes to stop Microsoft Excel from misreading them as dates. Sometimes it’s easier to rewrite genetics than update Excel" https://www.theverge.com/2020/8/6/21355674/human-genes-renam...

I think that's a good way forward, Excel is a fantastically powerful, ubiquitous tool and isn't going away any time soon. If you're going to standardise on a technology, it makes sense to design your systems to work well with that technology. That includes making identifiers that you need to be treated like strings look like strings to your tools. A little bit of thought in advance and maybe some small compromises early on can save massive problems down the line.

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

#654

Post by altacc »

vertere wrote: post #625
_pmf_ wrote: post #609

Earlier quoted context omitted.

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

I sure don't want to have to deal with people putting `="01"` in CSV files.

We have multiple legacy systems where I work, communicating via batch csv files, some of which are authored & curated by staff in Excel. I can confirm that doing this would be a very bad thing indeed and make our systems grind to a halt.

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

#655

Post by ArnoVW »

swiley wrote: post #529
1_player wrote: post #352

Earlier quoted context omitted.

> Always use checked pre- and post-conditions in a data pipeline. [A] simple post-condition would have caught this issue I'm not sure they teach that in medical school.

They spend so much time memorizing random trivia maybe a course in handling data wouldn't be a bad idea.

Give them a break. They don't have the luxury of a highly-structured field, purposefuly constructed by humans (engineers & mathematiciens on top of that) with only 50 years of legacy.

They have to deal with a field that was haphasardly constructed by nature over the course of 4 billion years.

Imagine having to retro-document a code base with 4 billion years of history, created solely by junior developers fresh out of college..

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

#656

Post by benhurmarcel »

strbean wrote: post #293
TeMPOraL wrote: post #286

Earlier quoted context omitted.

That would imply non-tech people having some surprisingly deep visibility into technical details. What they really see is Excel vs. some contractor doing "a system". The reality is that Excel is available today and works, and scales up... well, until it doesn't. Still, you have data entry that everybody in the field understands, that's rock solid (so no need to unit test anything), and has a tried and true authentica…

Couldn't you achieve exactly what was needed here with Airtable? Or, easier yet, Google Sheets and a Google Form?

For most cases where Excel is used where it "shouldn't be", any alternative that stores the data in the cloud is not possible.

Just getting the approval and contracts would take longer than the entire span of the project.

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

#657

Post by altacc »

collyw wrote: post #619
koliber wrote: post #613

Earlier quoted context omitted.

They are costing money. No doubt. At the same time, the business world runs on Excel. How much money is Excel making? I've done my share of cursing at Excel at various jobs. At the same time, I am grateful for the quick and easy way it allows me and many others to manipulate data. It's unfair to just cite the costs of using Excel without acknowledging the benefits it brings.

The real question should be would it be cheaper to replace it with a proper solution (probably something custom built application written by a software engineer). Excel's ease of use is it's downfall. It is the worlds most popular database, despite not actually being a database. I have wasted countless hours dealing with Excel where something else should have been used. I built a database for a friend recently, I thi…

I find people often use Excel (or Access if they're brave) when they have a known problem and can visualise the solution in something they know, like Excel. The crucial factor is implementation time. They can open an Excel and start work immediately, or wait weeks or months going through a lengthy and complex IT procurement process that will take from their budget. So in most cases starting with Excel is a no-brainer. Same reason why a lot of stealth IT gets implemented.

It's often only after years of a business using what has become sacred & business critical Excels, that somebody suggests formalizing it into software. In a business with an IT function, or a consultancy looking for business, it should always be somebody's job to find these Excels and replace them with something more robust.

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

#658

Post by dumb1224 »

codeulike wrote: post #142
bencollier49 wrote: post #133

Good lord. Old XLS files. I'd love to have seen how this was concocted.

Perhaps some old integration code from 10+ years ago that was written to spit out XLS rather than XLSX e.g. some old versions of Crystal Reports can only do XLS. There must be loads old systems around that can only do XLS

I worked with teams integrating legacy systems from hospitals before. There seem to be a large amount of carefully customised scripts for pulling data together. There was never a straight-forward data ETL / export for something other than routinely reported. A lot of information is derived and inferred from even not related (e.g infer chemo events from drug prescription codes if parts of system is not accessible). Hence if you really need high quality data you need a lot of validation and cross-checks as well (either by script or manually). To me it sounds like the checks and validation did not happen enough as the time is very critical. I don't think Excel is the single thing to blame here. Hospital IT systems have a long history of data related issues.

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

#659

Post by Closi »

EpicEng wrote: post #548
swiley wrote: post #528

Earlier quoted context omitted.

Is it really that hard to spend 5 minutes setting up a decent database? Use sqlite if you really want something simple. I guess you still have to understand locking (especially on distributed filesystems.) I've certainly seen people mess that up with spreadsheets.

Sure, I'll just teach tell my wife to learn SQL and some devops while she's at it. After that I'll coach her on how to get buy in from management on this new system while she neglects her primary duties. Throwing up a database and integrating it into a workflow/system isn't something anyone can just get up and do. I have to imagein you know that.

Yep! And then when your wife gets that promotion she wanted, she still has to spend time maintaining and making changes to the old database system because nobody else understands it or has the skills to change it.

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

#660

Post by Closi »

bobbylarrybobby wrote: post #494
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…

The issue with is excel is that its failure mode is suboptimal. Programming languages will generally crash at runtime when an unrecoverable error occurs (index out of bounds, memory allocation failed, null pointer, etc). But excel just throws away your data?

In this case, Excel throws an error and says that not all rows have been loaded.

It seems that warning has just been ignored by the user.

Post reply on HN