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

#201

Post by pbreit » Mon, Oct 05, 2020, 7:21 PM UTC

jjkaczor wrote: post #156

codeulike wrote: post #142

Earlier quoted context omitted.

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

XLSX files have been around for ... nearly 13-years... Ugh - I still deal with clients who are creating brand new XLS and DOC files and expecting to have all the latest features (co-authoring when hosted in 365/SharePoint Online/OneDrive/Teams)...

Excel has never worked well with external data.

Even the current version of Excel still inexplicably auto-converts long numerical strings to Scientific Notation.

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

#202

Post by alexott » Mon, Oct 05, 2020, 7:21 PM UTC

labster wrote: post #180

jjkaczor wrote: post #156

Earlier quoted context omitted.

XLSX files have been around for ... nearly 13-years... Ugh - I still deal with clients who are creating brand new XLS and DOC files and expecting to have all the latest features (co-authoring when hosted in 365/SharePoint Online/OneDrive/Teams)...

Ten years ago people were still complaining about OOXML not being an open standard. Don't worry guys, I'm sure they'll make a reference implementation of the standard soon! I'm sure there were a few people waiting around for a "real" standard to come out before implementing XLSX support.

It was submitted for standardization in 2006: https://en.wikipedia.org/wiki/Office_Open_XML.

At that time the was less information about 97-2003 file formats (OLE2-based), but it was fixed around 2008-2009: https://alexott.blogspot.com/search/label/file%20formats

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

#203

Post by tda » Mon, Oct 05, 2020, 7:21 PM UTC

arnon wrote: post #80

For most companies, "big data" is when they can't fit the data in a single Excel sheet anymore.

This is coincidentally also how I describe my job: I help when it doesn't fit in excel anymore.

Much easier for people to understand than shorter than civil engineer turned software developer in an engineering firm where I do data management, automation and application development for (mostly internal) clients to streamline business and engineering processes.

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

#204

Post by emerged » Mon, Oct 05, 2020, 7:22 PM UTC

usmannk wrote: post #192

SV_BubbleTime wrote: post #176

>As a consequence, each template could handle only about 65,000 rows of data Oh? Like 65,535 or so? That seemed weird a first that even as old as xls is that only 16bits were allocated to max rows. But then maybe not. Each row might get an ID, so that's 16bits * rows you have. I wonder if when XLS was designed they considered it very unlikely many people would have 500MB of db of empty rows and then would needed more…

XLS does in fact have a 16 bit row counter :/

That should be enough bits to do everything a computer will ever need to do.

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

#205

Post by Glyptodon » Mon, Oct 05, 2020, 7:23 PM UTC

jjkaczor wrote: post #156

codeulike wrote: post #142

Earlier quoted context omitted.

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

XLSX files have been around for ... nearly 13-years... Ugh - I still deal with clients who are creating brand new XLS and DOC files and expecting to have all the latest features (co-authoring when hosted in 365/SharePoint Online/OneDrive/Teams)...

Even xlsx files have caused endless headaches at my current workplace when they're large.

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

#206

Post by curiousllama » Mon, Oct 05, 2020, 7:24 PM UTC

Columns. They were using a new column for each new observation. The 16k number isn't a coincidence - it's the max # of columns in a spreadsheet.

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

#207

Post by hinkley » Mon, Oct 05, 2020, 7:27 PM UTC

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 equation for 'fairness', and a different layout because something about her tables was just confusing and messy. When I was done, we had $300 a month of extra spending money between the two of us, despite using the same targets for pay-down, savings and bills.

I spent about 90 minutes poking at her spreadsheet and mine looking for the error and never did find it. I don't know what the right solution is to this sort of problem, at least for non-developers. But if I was skeptical of Excel going in, I was doubly so after that. Especially for something that is going to be used to make decisions that will affect every day of your life.

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

#208

Post by threatripper » Mon, Oct 05, 2020, 7:29 PM UTC

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.

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

#209

Post by bigbubba » Mon, Oct 05, 2020, 7:31 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

Mimicking a spreadsheet with an RDBMS would be a neat hack, but I think the better solution is to simply use the database like a database. If an amateur carpenter is trying to hammer screws into wood, you hand him a screw driver, not try to invent some sort of hammer with a twisting mechanism that turns screws when you wack them. Use the right tool for the job, and use it like it's meant to be used. If the workers involved don't understand the right tool, then either train them or hire new workers who do.

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

#210

Post by diarrhea » Mon, Oct 05, 2020, 7:32 PM UTC

Each test result creating several rows of data seems like a problem too. In clean data, every observation is one row. It makes working with the dataset much easier. In this scenario, I would expect one observation to correspond to one test result. The multiple rows are then better off pivoted into columns.