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

#801

Post by Isthatablackgsd » Tue, Oct 06, 2020, 5:25 PM UTC

carlmr wrote: post #771

collyw wrote: post #619

Earlier quoted context omitted.

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

Excel is not a database, it is a spreadsheet. They are different beasts.

This link will explain the difference between two of them. https://365datascience.com/explainer-video/database-vs-sprea...

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

#802

Post by bitlevel » Tue, Oct 06, 2020, 5:34 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…

I'm sure, at a push, a PC could be co-opted to do the work while a server (even online) could then be brought up to speed and the data migrated - but that's your use case.

What we're talking about here is a government department who do have access to servers, but choose not to use them (or so it would seem).

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

#803

Post by carlmr » Tue, Oct 06, 2020, 5:43 PM UTC

Isthatablackgsd wrote: post #801

carlmr wrote: post #771

Earlier quoted context omitted.

>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

Excel is not a database, it is a spreadsheet. They are different beasts. This link will explain the difference between two of them. https://365datascience.com/explainer-video/database-vs-sprea...

What i was getting at is that it fulfills the (very) basic definition of a database, even though it's very bad for this purpose.

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

#804

Post by mark-r » Tue, Oct 06, 2020, 5:47 PM UTC

jugg1es wrote: post #539

mark-r wrote: post #153

Earlier quoted context omitted.

Have you seen the kind of mess Excel can make with a CSV file? The names of several genes were recently changed so that Excel would stop mangling them.

Excel removes leading zeros from numeric fields no matter what you do and you cannot turn it off. This is a huge problem in healthcare where many patient identifiers have leading zeros

Zip codes are a problem for the same reason.

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

#805

Post by Isthatablackgsd » Tue, Oct 06, 2020, 5:54 PM UTC

iso1631 wrote: post #701

reportgunner wrote: post #678

Earlier quoted context omitted.

Excel actually checks that and gives you an 'Inconsistent formula' warning with a little green corner similar to when you comment a cell. All you need to do is scroll through the worksheet you have just made.

All you need to do is scroll through 20,000 lines looking at each one

Excel have an ability to highlight the cell that have invalid data with a red circle to make it pop out on the table. Just look for something red.

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

#806

Post by dragonwriter » Tue, Oct 06, 2020, 6:08 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 resporces to pay for it and for data scientists

First, the upthread commented said "healthcare" not "government agency".

Second, as someone who has worked in public sector healthcare: HA HA HA!

I mean, sure we have the resources to pay for data scientists (of which we have quite a few) and could conceivably probably afford to develop custom scripts for any CSV subformat that we decided we needed one for (though if its a regular workflow, we're probably acquiring it an importing it into a database without nontechnical staff even touching it, and providing a reporting solution and/or native Excel exports for people who need it in Excel.)

The problem is that when people who aren't technical staff or data scientists encounter and try to use CSVs (often, without realizing that's what they are) and produce problems, its usually well before the kind of analysis which would go into that. If its a regular workflow that's been analyzed and planned for, we probably have either built specialized tools or at least the relevant unit has desk procedures. But the aggregate of the stuff outside of regularized workflows is...large.

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

#807

Post by contextfree » Tue, Oct 06, 2020, 6:21 PM UTC

langitbiru wrote: post #150

So the limit for Excell is 65,000 rows for the old format, and one million-plus rows for the new format (from the article). Then I wonder, is there any tool that mimic Excell but with Sqlite as the backend? The limit of rows in Sqlite is 2 raised to the power of 64 (18446744073709551616 or about 1.8e+19). https://sqlite.org/limits.html

Something sort of like this already exists shipped with Excel in the form of Power Pivot, which stores data in an embedded database inside the Excel workbook, and is supposed to scale up to 2GB and hundreds of millions of rows.

https://support.microsoft.com/en-us/office/power-pivot-power...

As you might see from that link it's semi-integrated with the Excel interface, but the differences in the underlying model show through in some ways (like not being able to edit individual cells or use VBA)

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

#808

Post by lukeramsden » Tue, Oct 06, 2020, 6:48 PM UTC

corty wrote: post #680

lukeramsden wrote: post #608

Earlier quoted context omitted.

The extension is not at fault, all it does is rewrite HTTP links to HTTPS (which should be the default IMO, I agree with the parent commenter). The fault is with the site providing a self-signed certificate when accessed over HTTPS.

All it does is assume that a https site serves the same content and audience as the corresponding http site. That is a broken assumption. The consequences of such a broken assumption are very much the fault of the extension.

> That is a broken assumption

Why is that a broken assumption? Can you name a legitimate reason for HTTP and HTTPS sites to serve separate contents and audiences? I would rather not connect over HTTP to _anything_ nowadays.

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

#809

Post by ashtonkem » Tue, Oct 06, 2020, 6:51 PM UTC

jeltz wrote: post #513

ashtonkem wrote: post #259

Earlier quoted context omitted.

I feel like text-based is colloquial shorthand for "schemaless" or "dynamically typed".

CSV is not schema less and it does not have to be any more dynamically typed than XML either.

It doesn't have to be, but it often is.

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

#810

Post by corty » Tue, Oct 06, 2020, 7:39 PM UTC

lukeramsden wrote: post #808

corty wrote: post #680

Earlier quoted context omitted.

All it does is assume that a https site serves the same content and audience as the corresponding http site. That is a broken assumption. The consequences of such a broken assumption are very much the fault of the extension.

> That is a broken assumption Why is that a broken assumption? Can you name a legitimate reason for HTTP and HTTPS sites to serve separate contents and audiences? I would rather not connect over HTTP to _anything_ nowadays.

CMS, serve the content over http and the admin page over https.

And for sites with noncritical static content https is superfluous to dangerous. ESNI isn't implemented yet, IP addresses are still visible to the eyes. And content sizes and timing are a dead giveaway for the things you are looking at. HTTPS for everything is just a simulation of privacy at best, and misleading and dangerous at worst, because there IS NO PRIVACY in the aforementioned cases.