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

561–570 of 834 posts

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

#561

Post by gxs »

konjin wrote: post #520
gxs wrote: post #516

Earlier quoted context omitted.

The problem is you only hear about botched excel jobs - you never hear about all the successful projects, so when you hear a horror story you just jump to the conclusion excel sucks. Ok, ask a geneticist to build what they need with their “off the shelf” parts you mentioned vs 90% of the time being self sufficient with excel and allowing them to, you know, do genetics work. (The other 10 percent being where it makes…

I worked with a couple. They stop being geneticists rather quickly because the salary for someone who can use python well is about 4 times that of someone working on genes. Keeping them away from coding is now an executive decision since university departments can't pay competitive salaries. The dropbox post was right. Knowing someone who wins a lottery is no reason to conclude spending all your money on lottery tick…

>> The dropbox post was right

Enough said.

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

#562

Post by shalmanese »

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 anyway

The first CSV file was created in 1983. The first CSV standard was created in 2005[1].

The two decades of CSV surviving as an informal standard means that it takes minutes to make a 95% complete CSV parser and an infinite amount of time to make a 99.99% complete CSV parser.

[1] https://en.wikipedia.org/wiki/Comma-separated_values#History

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

#563

Post by yawaramin »

tasogare wrote: post #558
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…

Quote your strings. The problem is more with how you generate CSV (which is easy to break in the first place) than Excel’s fault. It’s like writing your identifiers as numbers in JSON instead of string then complain the leading zeros are removed.

Quoting doesn't work. Try opening this CSV with Excel:

    a,b
    "01",01
Excel interprets both as the same number–1.

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

#564

Post by shalmanese »

theptip wrote: post #238
tannhaeuser wrote: post #39

Earlier quoted context omitted.

> 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 term…

How to encode data with newlines into CSV files is always a fun morass to wander into.

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

#565

Post by yawaramin »

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…

Nobody is blaming Microsoft. From the article:

> And it appears that Public Health England (PHE) was to blame, rather than a third-party contractor.

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

#566

Post by jp0d »

kevin_nisbet wrote: post #533
jp0d wrote: post #532

You'd be surprised to hear how many big organisations still use Excel for their reporting needs. I used to worked at a company with 35000 employees. Their payroll reports were generated using a MS Access database and reported using Excel. It was a massive manual job every week as they'd weekly, fortnightly and monthly payroll cycles! It's very easy to lose files and/or make an error in the calculations. But nobody bo…

We had a telco vendor use excel to manage all their configuration. It was a weird dual source of authority system, where the excel configuration was used at install / upgrade time, but you could change the config after the install at runtime. So you had to merge the active configuration into excel, upload the excel document to some server that customers didn't have access to that turned the excel file into a config f…

I'm not surprised. I've seen all kind of shit working for enterprise clients. Only one of them adhered to very high standards and had processes for everything. They were a military contractor and most of the clients were ex-military.

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

#567

Post by sheetjs »

yawaramin wrote: post #563
tasogare wrote: post #558

Earlier quoted context omitted.

Quote your strings. The problem is more with how you generate CSV (which is easy to break in the first place) than Excel’s fault. It’s like writing your identifiers as numbers in JSON instead of string then complain the leading zeros are removed.

Quoting doesn't work. Try opening this CSV with Excel: a,b "01",01 Excel interprets both as the same number–1.

The correct way to generate a CSV cell with a leading 0 is

    ="01"
You can verify this with

    01,"01",="01"

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

#568

Post by unishark »

theptip wrote: post #238
tannhaeuser wrote: post #39

Earlier quoted context omitted.

> 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 term…

You have to navigate the same basic issues to convert each csv as it comes in to xls as PHE did in the article. They could just as well have converted them to a consistent csv format and stopped there.

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

#569

Post by andreareina »

Bukhmanizer wrote: post #546
thedudeabides5 wrote: post #519

Earlier quoted context omitted.

Well, not exactly like non-Excel systems are bulletproof either. Entire Japanese stock market went down last week, and it's not like there was a flood of people on HN bemoaning that. At least with Reinhart and Rogoff et al you have a responsible party. As opposed to 'nameless machine failed, and nameless backup machine also failed, and now it's in JIRA so don't worry about it'. https://www.nytimes.com/2020/09/30/busi…

I think similar to “code smells” in software engineering, using excel for critical data pipeline work can be considered a “data smell”. It’s not bad in and of itself, but it’s a signal that something may have gone seriously wrong with the data engineering process.

Unfortunately excel is the lingua franca of data exchange in SME

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

#570

Post by terwey »

sheetjs wrote: post #567
yawaramin wrote: post #563

Earlier quoted context omitted.

Quoting doesn't work. Try opening this CSV with Excel: a,b "01",01 Excel interprets both as the same number–1.

The correct way to generate a CSV cell with a leading 0 is ="01" You can verify this with 01,"01",="01"

“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.
Post reply on HN