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

291–300 of 834 posts

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

#291

Post by pbhjpbhj »

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

You could open a DB as a table view in OO.org, so presumably you can in LibreOffice. I'm surprised Excel doesn't integrate with Sqlite DBs.

My naive view would expect tables sheets; rows tuples to be easy to do (for MS) and just don't touch the relational aspects??

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

#292

Post by rvba »

NelsonMinar wrote: post #263
aphrax wrote: post #134

I've been bitten by this exact issue (albeit on non critical data), save a file as XLS it will silently drop rows beyond 65k - save it as XLSX and it's all good..

Silent? Are you positive? Not doubting you but if so; WTF?!

I just tried to reproduce it and got a big warning message that says that data will be lost.

You can disable this message (per file basis), if for some reason you dont want to see it.

As far as I remember this message existed since Excel 2007 (which introduced .xlsx format), so the guy is simply lying.

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

#293

Post by strbean »

TeMPOraL wrote: post #286
lordnacho wrote: post #247

The problem is that anyone who isn't a decent coder think excel is a sensible choice for this, because they've seen it in their school or work. So when the proposals come in, you're going to see one guy who says it's all common sense and we use familiar old excel for everything, and another lunatic who says something called "pigsqueal" is actually the standard, connected to a "frontend" which for some reason is now s…

That would imply non-tech people having some surprisingly deep visibility into technical details. What they really see is Excel vs. some contractor doing "a system". The reality is that Excel is available today and works, and scales up... well, until it doesn't. Still, you have data entry that everybody in the field understands, that's rock solid (so no need to unit test anything), and has a tried and true authentica…

Couldn't you achieve exactly what was needed here with Airtable? Or, easier yet, Google Sheets and a Google Form?

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

#294

Post by beervirus »

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…

If you're trying to figure out why the spreadsheets had different results, I'd start here:

> I started over using a different equation

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

#295

Post by llarsson »

andor wrote: post #254
secondcoming wrote: post #130

Earlier quoted context omitted.

XML would fix the max rows issue, but open you up to OOM issues instead!

Sorry, but if you run into OOM issues by parsing an XML file, you're using the wrong API. The DOM for a large XML document will of course take tons of space in memory. The key to parsing XML files quickly and with low memory consumption is to only keep in memory what's necessary, by streaming over the elements. https://en.wikipedia.org/wiki/Simple_API_for_XML

Correct. And to add to this: apparently the lost data was due to the data that exceeded the 16k rows XLS supports, so the amount of data per file was apparently not huge to begin with. So even a shitty XML parser should do just fine here.

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

#296

Post by happytoexplain »

lordnacho wrote: post #247

The problem is that anyone who isn't a decent coder think excel is a sensible choice for this, because they've seen it in their school or work. So when the proposals come in, you're going to see one guy who says it's all common sense and we use familiar old excel for everything, and another lunatic who says something called "pigsqueal" is actually the standard, connected to a "frontend" which for some reason is now s…

Frankly I think you're being optimistic/pessimistic (depending on your point of view). A person can be a "decent coder" by very reasonable definitions of the term and still not realize that Excel can't handle this particular volume of data.

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

#297

Post by bencollier49 »

isodev wrote: post #193
bencollier49 wrote: post #133

Good lord. Old XLS files. I'd love to have seen how this was concocted.

“Let’s see if StackOverflow has something” ... “Oh nice! Exactly what I need!” , ... “Woah it builds! Ship it!”

This is Excel. There is no "builds".

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

#298

Post by nym3r0s »

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…

This is precisely why I went with YNAB [0], started budgeting better and saved quite a bit of money.

Everyone who sees me paying for an app (I'm from India) ask me - "why can't you just use excel and do the same thing for free?". Excel sure is powerful, but in real life, your mileage may vary.

[0] - https://www.youneedabudget.com/

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

#299

Post by TeMPOraL »

strbean wrote: post #293
TeMPOraL wrote: post #286

Earlier quoted context omitted.

That would imply non-tech people having some surprisingly deep visibility into technical details. What they really see is Excel vs. some contractor doing "a system". The reality is that Excel is available today and works, and scales up... well, until it doesn't. Still, you have data entry that everybody in the field understands, that's rock solid (so no need to unit test anything), and has a tried and true authentica…

Couldn't you achieve exactly what was needed here with Airtable? Or, easier yet, Google Sheets and a Google Form?

Extremely low performance. Obscure "sharing" workflow instead of obvious "mail a file". Cloud dependency that needs to be vetted for security (EDIT: and data privacy, as 'sjansen pointed out), and policies regarding cloud accounts on government e-mails. And in case of Airtable, also for budget.

I'd be surprised if either of the above could handle 65k rows in a single table without becoming near-unusable.

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

#300

Post by nullsense »

pintxo wrote: post #114
callamdelaney wrote: post #105

Earlier quoted context omitted.

It's not a user issue, it's a government incompetence issue. Who was the technical genius who thought 'right, we need to store thousands of lines of critical and important data, I know! Excel!'

Have you every worked in a large enterprise? I have personally tested an Excel based credit rating tool, to be rolled-out globally by a major financial institution. The fact that one should not do this, is by no means a reason not to do it.

Fun fact: Toshiba has software that generates entire CRUD app skeletons driven 100% by excel spreadsheets. You fill out object definitions in excel spreadsheets with property names and data types etc. It's like an extremely uncool long lost cousin of rails -g

Source: Got one of their engineers to show me after I heard about it and had to know if it was real.

Anyone who's ever worked with or in Japan knows just how far they are willing to torture Excel spreadsheets to get them to do anything and everything.

Post reply on HN