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

231–240 of 834 posts

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

#231

Post by OrwellianChild »

hinkley wrote: post #207

I hate solving problems and not knowing how I did it, so this particular experience has stuck in my craw. Trying to develop a budget to pay off debts, my partner made this elaborate Excel spreadsheet and the output was that basically she had no spending money and I had very little, until one or both of us got a raise. It was far more austere than either of us were willing to go. So I started over using a different eq…

The only real way to solve issues like this is a step-by-step reconciliation between the old model and new. Each assumption and calculation must be tested to confirm that they replicate the same output. This is extremely difficult if the underlying structure/assumptions are different (e.g. monthly views vs. transactional views, etc.).

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

#232

Post by M2Ys4U »

andy_ppp wrote: post #219

Maybe everyone in school should be given real world problems like this to do in Excel so people can get a feel for doing it more correctly? Rows === records and columns === fields being the first lesson...

They've tried that already.

When I was in school (early 2000's) our GCSE computing lessons were, more or less, "Here's Microsoft Word, today we will learn how to format a letter!" or "Here's Microsoft Excel, today we will learn how to create a chart!".

The only upside to taking the class was that our school managed to identify the kids who were obviously computer literate and we got to go on a tour of Microsoft's UK headquarters...

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

#233

Post by smsm42 »

daanlo wrote: post #16

We did a bit of work for laboratories this year and csv is not an uncommon exchange format between labs. In general almost all exchange formats are text based, with labs saying they will upgrade to „modern xml formats“ at some point in the future. So seen in this context a csv or an excel file doesn‘t really surprise me and should probably also be seen in this context.

There's nothing wrong with CSV, especially if you don't have arbitrary-text data (if you do, just don't do CSV). Excel though adds an addition layer of services, which turns it into a nightmare if used as something it explicitly wasn't made to be - a database.

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

#234

Post by maaarghk »

threatripper wrote: post #208

People keep putting the blame on Excel but it supports more columns than most implementations of SQL databases. MySQL for example supports only 4096 columns. SQLite defaults to 2000. That's way less than Excel's 16384.

Amazing comment, sorry to see you're getting downvoted. ALTER TABLE upvotes ADD COLUMN threatripper_24691123_upvote DEFAULT 1 NOT NULL;

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

#236

Post by psahgal »

I would hope that any decent data engineer would know not to do this. Are there avenues for fully-employed engineers to help governments build these systems properly? I'd honestly be happy to just donate some of my free time to help these efforts.

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

#237

Post by aschatten »

daanlo wrote: post #16

We did a bit of work for laboratories this year and csv is not an uncommon exchange format between labs. In general almost all exchange formats are text based, with labs saying they will upgrade to „modern xml formats“ at some point in the future. So seen in this context a csv or an excel file doesn‘t really surprise me and should probably also be seen in this context.

There is nothing wrong with CSV or text base formats. You can even use AWS Athena to query CSV feels stored in S3. It's a good format for data import/export, that many systems can natively understand or have tools to parse, given it's known how to interpret data.

It was a data pipeline issue. Software has little to do with it. If they received data in json and tried to interpret it as CSV, the same could have happened. I believe Excel even warns when you open file that has too many rows.

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

#238

Post by theptip »

tannhaeuser wrote: post #39
daanlo wrote: post #16

We did a bit of work for laboratories this year and csv is not an uncommon exchange format between labs. In general almost all exchange formats are text based, with labs saying they will upgrade to „modern xml formats“ at some point in the future. So seen in this context a csv or an excel file doesn‘t really surprise me and should probably also be seen in this context.

> exchange formats are text based, with labs saying they will upgrade to „modern xml formats“ Worth noting that XML is also a text format. SGML even can treat CSVs as markup. There's nothing wrong with CSVs/TSVs anyway - it's a concise tabular format using only minimal special coding for a record and a field separator, as envisioned by ASCII and EDIFACT. The problem seems more like that there was no error checking in…

> There's nothing wrong with CSVs/TSVs

I can see the theoretical purity of this statement, but based on my experience working with CSV files generated by actual non-technical users I have to disagree here.

There are a number of footguns here that are really subtle and the average non-technical user has no hope of spotting them.

Problems that I've seen in the wild, off the top of my head:

* Windows vs. Linux line terminators breaks some CSV libraries.

* Encoding can change depending on what program emitted the CSV file, and auto-detecting encoding is not perfect. For example, Excel for Mac uses Linux encoding by default, IIRC.

* Excel does wacky things when you export a "CSV" in the wrong format; real users use Excel to generate their CSVs, not Python. For example if you import the string "0123456789" in an Excel sheet, it infers "number" and strips the leading "0" when you export. Now your bank account/routing numbers are invalid!

* "What's a TSV?" -- if users use CSV, how do you handle commas in the data? It's nontrivial to train users to do their CSV upload as a TSV.

Etc.

In practice we needed to build a fairly beefy helpdesk article with accumulated wisdom on how to not break your CSV exports, and most users don't read/remember these steps until they experience the trauma first-hand.

I'd say the CSV format is deceptively simple -- it's quite easy to do the right thing as a developer where the source and sink are both code you control, but in the wild it gets messy really quickly.

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

#240

Post by redwall_hp »

piannucci wrote: post #217
jsty wrote: post #197

Earlier 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

Oh, my God. "On Error Resume Next" is one of my earliest memories of learning to program for Windows. It never occurred to me that it might result in deaths.

That's an interesting ethical point in CS: sure, you didn't intend the software you hacked together in an afternoon to be used in anything with life-and-death consequences. But that doesn't mean someone isn't going to come along later and use it, without ever even looking at the source, for something very critical down the line.

I wonder how many bleeding edge master branches of GitHub repos, pulled in blindly by someone cobbling something together to meet a deadline, are running in places they probably shouldn't be.

Post reply on HN