Missing Covid-19 test data was caused by the ill-thought-out use of Excel • Hacker News Board

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

#771

Post by carlmr » Tue, Oct 06, 2020, 2:21 PM UTC

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…

>It is the worlds most popular database, despite not actually being a database.

"A database is an organized collection of data, generally stored and accessed electronically from a computer system."[1]

Excel is an organized collection of data, stored and accessed electronically from a computer system. So I would call it a database.

[1] https://en.wikipedia.org/wiki/Database

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

#772

Post by zentiggr » Tue, Oct 06, 2020, 2:23 PM UTC

ivanhoe wrote: post #686

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…

Couldn't you validate the data on upload or if it's such a common problem just auto-fix it by left padding w/ zeros?

There are data sources that have a clear unambiguous specification of what information is in each field, and exactly how they are formatted, such that you could auto-parse the column. And even have no data entry errors, so that you can rely on that parsing and have clean error free imported data.

There are many more that don't have a clear spec, or even if they do, have every possible variation of data corruption / keying errors / user misunderstanding / total 'don't give a shit, I'll enter it how I want, that's what the computer is supposed to handle' problems in that source data, that make parsing an absolute nightmare.

I've had to manually review a list of 10k+ data readings monthly, from an automated recorder, because the guy who was supposed to copy the collected data files as is and upload them, instead opened each one and fixed what he (badly mistakenly) thought had been recorded wrong. Different changes in a dozen different ways based on no particular logic beyond "that doesn't look right". And un-fireable, of course.

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

#773

Post by thedudeabides5 » Tue, Oct 06, 2020, 2:25 PM UTC

thirdsun wrote: post #730

lima wrote: post #721

Earlier quoted context omitted.

The issue is that CSVs are untyped and software has to guess the type, sometimes unsuccessfully.

Does it? I think it should treat everything as a string since it can't be certain about the correct type. It could also suggest the correct type rather than applying it without user confirmation.

You want Excel to auto-interpret a table of numbers, that come from a CSV, as entirely text?

Looking forward to the first time anyone tries to use your excel on a table of numbers and then immediately has to multiply everything by *1 (in a separate table) just to get it back into numbers...

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

#774

Post by zentiggr » Tue, Oct 06, 2020, 2:28 PM UTC

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"

If you want to write a bespoke CSV generator for an application where you know for sure that the file is only ever going to go straight to an Excel instance, sure.

For all the other uses in the world, that's a breaking change.

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

#775

Post by IanCal » Tue, Oct 06, 2020, 2:30 PM UTC

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.

Although make sure to not use that for the first column heading, an uppercase I will make excel think it's a SYLK file and then refuse to open it.

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

#776

Post by zentiggr » Tue, Oct 06, 2020, 2:34 PM UTC

petre wrote: post #732

dragonwriter wrote: post #661

Earlier quoted context omitted.

> Just format the identifier cells as text CSV is text. If you mean in Excel, if you opened it in Excel (rather than importing and choosing non-default options), you've already lost the data so formatting doesn't help you.

Yes, I mean Excel. We have CSV to XLS import scripts/forms that format identifier cells as text. The data format is standardised. Using templates to do the imports was the dumb part. Microsoft has a Power BI tool if ones doesn't want to write or use scrips. Use that. I assume a government agency has the resporces to pay for it and for data scientists. https://powerbi.microsoft.com/en-us/ Thanks for bothering to respo…

> I assume a government agency has the resources to pay for it and for data scientists.

Bold strategy there, let's see how that plays out.

Having been in and around military / DoD usages for a long time, I can tell you it's always an uphill battle to get processes to work well, instead of defaulting to whatever the original spec happened to get included as a result of some incompetent who wasn't even aware of good practice.

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

#777

Post by jasode » Tue, Oct 06, 2020, 2:35 PM UTC

jugg1es wrote: post #768

baobabKoodaa wrote: post #735

Earlier quoted context omitted.

No, that's not correct. The parent poster specifically made this false claim: "that's Excel's default behavior and you CANNOT TURN IT OFF!" The false claim is the part that the poster wrote in all caps. He said that this behavior can not be turned off, when in fact it can be turned off.

How? Where is the setting to turn it off when initially loading a file?

>How? Where is the setting to turn it off when initially loading a file?

Confusion is happening because 2 different ideas of Excel using csv files:

- you saying "can't turn this off" : File Explorer double-clicking a "csv" or MS Excel "File->Open" csv.

- others saying "you can preserve leading zeros" : click on Excel 2019 Data tab and import via "From Text/CSV" button on the ribbon menu and a dialog pops up that provides option "Do not detect data types" (Earlier version of Excel has different verbiage to interpret numbers as text)

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

#778

Post by zentiggr » Tue, Oct 06, 2020, 2:37 PM UTC

baobabKoodaa wrote: post #735

SifJar wrote: post #724

Earlier quoted context omitted.

> you can import CSVs values as strings instead of numbers Of course, but the problem isn't that the person who posted the comment doesn't know this - it's that many users of their systems don't know it. Most people are just going to accept whatever defaults Excel suggests and not know any better, causing problems down the line.

No, that's not correct. The parent poster specifically made this false claim: "that's Excel's default behavior and you CANNOT TURN IT OFF!" The false claim is the part that the poster wrote in all caps. He said that this behavior can not be turned off, when in fact it can be turned off.

It can't be turned off, if you're opening a CSV in Excel.

It can be avoided, if you go through the Data | Import tools. The complaint is that few Excel users know that the import engine is available, or use it, instead of just opening the file and getting all the default interpolations. Which can't be avoided in the usual Open code path.

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

#779

Post by noja » Tue, Oct 06, 2020, 2:54 PM UTC

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…

Does it strip leading zeroes if you format the numbers as strings with quotes like this?

blah,blah,"00001553",blah

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

#780

Post by fetbaffe » Tue, Oct 06, 2020, 3:08 PM UTC

benhurmarcel wrote: post #669

bitlevel wrote: post #190

Earlier quoted context omitted.

There's not much to stop the devs from using open source tools to create a PHP web frontend and db backend for this data - and secure it as well. Heck, if they still need to export, they could do that from the data too. Sure - use Excel for POC, but get that DB backend up pronto.

I work in an office that relies way too much on Excel. To consider your solution, first showstopper, it needs a server. We don't have a server, nor anyone who knows how to manage one. We'd need to ask IT, that will take months and they'll require a budget transfer, so we'd need to request it to management (which will need a business case to convince) and involve the finance guys. We can't just plug a RaspberryPi into…

How do you share the spreadsheet? Shared network drive? By email? With Excel?