News for hackers. Stuff that matters.
Re:Missing Covid-19 test data was caused by the ill-thought-... by Mvandenbergh on Monday October 05, 2020 @05:31AM (#24685998)
It's unlikely this will have had much impact since most reported cases were only a few days late but come on guys, really?

Using an Excel sheet as a database? In week 1, that would be "not great" but could be accepted as being a fast solution that everyone could work with. This far into a pandemic I think we can expect a little more professionalism in data handling.

Re:Missing Covid-19 test data was caused by the ill-thought-... by londons_explore on Monday October 05, 2020 @05:39AM (#24686031)
People use excel sheets as databases all the time because it doesn't require any specialist knowledge to build or use.

Sure, we techy types prefer a proper database with a web frontend and an API, but that requires significantly more skill to build than an excel file.

No database has the flexibility and flat learning curve that excel has.

Until a database manages to meet these goals, it remains a good tool for certain usecases.

We as engineers should be working towards making a real database thats just as easy to use as excel, and only then can we complain that people are using excel sheets for this kind of stuff.

Re:Missing Covid-19 test data was caused by the ill-thought-... by DanBC on Monday October 05, 2020 @05:49AM (#24686094)
> People use excel sheets as databases all the time because it doesn't require any specialist knowledge to build or use.

Test and Trace is a multi billion pound programme of work, involving huge companies (eg, Serco) interfacing with national governmental bodies (Public Health England).

We've had months to get something in place. One of the reasons organisations like Serco is used is their apparent expertise with IT.

Re:Missing Covid-19 test data was caused by the ill-thought-... by pjc50 on Monday October 05, 2020 @06:03AM (#24686199)
The main reason Serco is used is their political connections; they really ought to have been banned from consideration after the fraud scandal: https://www.bbc.co.uk/news/uk-50806919

(my one-step programme for improving UK public contracting would be that after a significant failed contract the contracting company and its directors and any other companies they are directors of would be banned from tendering for a period; maybe a couple of years?)

Re:Missing Covid-19 test data was caused by the ill-thought-... by jasiek on Monday October 05, 2020 @06:34AM (#24686355)
Unfortunately the procurement rules currently in place prevent procurement personnel evaluating a tender from taking into account any prior performance. I'm unsure whether this is a result of EU legislation and common market rules, or whether it is just the UK.
Re:Missing Covid-19 test data was caused by the ill-thought-... by riffraff on Monday October 05, 2020 @07:18AM (#24686577)
italy has legislation to forbid people who engaged in corruption/embezzlement from working with public administrations.

It also has a required certification for companies that work with the public that they weren't condemned for certain crimes (trafficking in illegal waste, extorsion, organized crime etc).

So, it doesn't seem an EU/Single Market rule that you can't check this.

Re:Missing Covid-19 test data was caused by the ill-thought-... by anonymousDan on Monday October 05, 2020 @02:42PM (#24690648)
Please tell me that is not true. That is just...nuts! What is the rationale behind that beyond corruption?
Re:Missing Covid-19 test data was caused by the ill-thought-... by thu2111 on Monday October 05, 2020 @11:24AM (#24688557)
One issue they have is that very few companies are willing to have the government as a customer. That's one reason why they keep going back to the same failed firms time and time again.

Reasons vary but the ones I've seen are generally:

1. Complex procurement regulations that take a lot of effort to comply with by contractors.

2. Low willingness to pay (both price-wise and time-wise).

3. Civil servants are nightmare customers who don't know what they want and are under no pressure to figure it out, so there's a lot of timewasting involved.

4. PR nightmare if/when things go wrong even if it's not your fault, as government is relatively open compared to other types of customers so easier to find out about problems, and lots of people automatically blame the private sector in preference to blaming government employees due to (misguided) assumptions of moral superiority of the public sector.

Re:Missing Covid-19 test data was caused by the ill-thought-... by adam-a on Monday October 05, 2020 @06:40AM (#24686387)
This is a very good point and a shame to see it being downvoted. I have been wishing there was an accessible database-ish software for doing my accounts, as the current mega-spreadsheet I use is pretty hairy and none of the specialised apps quite fit the bill. The closest thing seems to be Airtable, which while nice, is quite expensive.

Recently I was visiting hospital quite a bit (ante-natal) and witnessing the horror of the massive NHS form filling software the nurses and midwives have to use. It's clunky and sprawling but one of the main sins I think is it's lack of adaptability. In the old days of pen an paper, a new form could be written, or adapted, and photocopied easily, and on-site. Now if a new field needs to be added or a process is changed it needs to go (I imagine) to some centralised IT development office and fed into a ticketing system where it might get changed in a few weeks or months. There's no room to quickly adapt at a ground-level, so you end up with things like this excel sheet problem.

Re:Missing Covid-19 test data was caused by the ill-thought-... by chii on Monday October 05, 2020 @05:43AM (#24686059)
I'm surprised that MS Access didn't take this kind of niche.

I know it's a pretty shitty program to use - excel is much more user friendly. If MS had made excel, but have all the features of access in it...

Re:Missing Covid-19 test data was caused by the ill-thought-... by Closi on Monday October 05, 2020 @07:09AM (#24686528)
It depends which bits of functionality you want, but I think you are close to describing PowerQuery (Get & Transform) which is in Excel, which can ingest multiple data sources into effectively a relational database with hard datatypes and data-cleaning.

In PowerQuery & PowerPivot there are also no practical limits on the number of rows of data you can have (other than the impact on processing time, clearly hundreds of millions of records might start to be a problem). It's not quite access - it doesn't actually persist/store the data, just aggregates it from other sources which is probably what is required here.

Access is a little legacy and would have it's own issues. I think in reality it depends on how you are ingesting data (which is probably hospitals submitting excel templates or similar?). Maybe these could be automatically aggregated and put onto an analytics platform? (e.g. PowerBi). Hospitals could maybe report it on a portal, but I'm not convinced that the leadership would have wanted to change the established process (because of operational focus, change reduction & risk).

Re:Missing Covid-19 test data was caused by the ill-thought-... by thu2111 on Monday October 05, 2020 @11:27AM (#24688587)
For a very long time, it did.

Weaknesses of Access in no particular order: very poor multi-user/multi-device-access features. No real concept of servers. Buggy, had a nasty habit of corrupting its custom file format causing data loss (Excel never does this). Does expect you to understand SQL, data normalisation, etc. VBA just about serviceable for a spreadsheet but not good enough for a 'real' database-backed app.

Probably someone could try and make a better Access. There are surely lots of startups doing that already, along with hosting. All the "no code" tools that are out there, etc. Access had the advantage of coming with Office so lots of people had it already, whereas today's subscription based services that host your data have incremental cost.

Re:Missing Covid-19 test data was caused by the ill-thought-... by bleepblorp on Monday October 05, 2020 @02:55PM (#24690778)
LibreOffice Base is something of an attempt at a newer Access, but unfortunately it's far from ready for production use.

Base has very solid data storage foundations (including the ability to connect to real database servers) but the UI is very buggy and is painful to fight with. Note I said 'fight with' rather than 'use.'

Base seems to have a fair bit of potential, however, if someone where to pour some money and/or time into developing it further.

Re:Missing Covid-19 test data was caused by the ill-thought-... by rvba on Monday October 05, 2020 @02:10PM (#24690328)
IMHO the biggest problem is the 2GB database size limit
Re:Missing Covid-19 test data was caused by the ill-thought-... by TeMPOraL on Monday October 05, 2020 @03:10PM (#24690940)
Don't forget the formulas! Excel is essentially a functional reactive programming REPL. No RDBMS I know lets you easily define a table in which cells in a row depend on values from previous cells in a row, and keep this as an invariant.

People working on Excel replacements need to remember about this aspect of spreadsheets too.

Re:Missing Covid-19 test data was caused by the ill-thought-... by Mvandenbergh on Monday October 05, 2020 @06:38AM (#24686376)
Sure. That's why I said it wouldn't bother me if this was in the first few weeks of the pandemic or for one-off ad-hoc analysis. I actually use Excel for that all the time, including for tasks where technically a db is the correct solution. Working is better than perfect and everyone knows (sort of) how to use Excel. To still be using it in production this many months in is not acceptable. Someone should have taken that Excel based workflow and set it up to run through a database, they had the time to do it.
Re:Missing Covid-19 test data was caused by the ill-thought-... by LorenPechtel on Monday October 05, 2020 @02:15PM (#24690393)
There is also the fact that Excel has some rich editing tools. I have actually seen a system where most editing was done by exporting the relevant items to a spreadsheet, making your changes and reimporting.
Re:Missing Covid-19 test data was caused by the ill-thought-... by chimprich on Monday October 05, 2020 @06:10AM (#24686225)
> It's unlikely this will have had much impact since most reported cases were only a few days late

I'm not sure if you're being sarcastic or not, but in case you're not, this has meant c.16k people missed from having their contacts traced. That's time-critical. Delays of any number of days are very bad. People will likely die because of this error.

This is safety-critical software. It should be stress tested and have sanity checks. It should be impossible for this to happen.

For something like this system, part of a track and trace system costing billions, to fail because of a file size error is just unbelievable.

Re:Missing Covid-19 test data was caused by the ill-thought-... by Mvandenbergh on Monday October 05, 2020 @06:36AM (#24686362)
I was being serious. Distributionally, most of the cases were 1-2 days before the error was spotted. To be clear though, when I said "not much impact", I didn't mean that people might not die because of it, only that it wouldn't affect the large scale functioning (such as it is) of the tracing operation.
Re:Missing Covid-19 test data was caused by the ill-thought-... by fuzzy2 on Monday October 05, 2020 @06:40AM (#24686388)
> Distributionally, most of the cases were 1-2 days before the error was spotted.

That’s always true though if the virus is spreading exponentially (dunno whether it currently does in the UK).

Re:Missing Covid-19 test data was caused by the ill-thought-... by makomk on Monday October 05, 2020 @03:14PM (#24690982)
This is more true than it would be from pure exponential growth alone, especially the relatively slow exponential growth the UK as a whole currently seems to be seeing (doubling time maybe about a fortnight?) The issue was effectively dropping all results after a certain number from a particular location, so at the start relatively few results were dropped and it was spotted around the time that number started to become substantial. It's that hard cutoff where results suddenly start to be dropped that means most of them are in the last few days.
Re:Missing Covid-19 test data was caused by the ill-thought-... by hdjdbtbgwjsn on Monday October 05, 2020 @07:30AM (#24686649)
Sadly it takes about 24 to get tested and 24 hours for the lab result. That gives very little time to trace contacts before they start entering the asymptomatic-but-infectious-to-others stage. A one day delay is a problem.
Re:Missing Covid-19 test data was caused by the ill-thought-... by throwmeaway_pls on Monday October 05, 2020 @06:20AM (#24686274)
The WHO estimated 750 million cumulative infections on Friday. This error is not unbelievable because the system is just a bit of political theater. There’s nothing more we can do against an endemic human coronavirus. It’s time to move on.
Re:Missing Covid-19 test data was caused by the ill-thought-... by hdjdbtbgwjsn on Monday October 05, 2020 @07:32AM (#24686668)
Yeah we should give up on controlling polio, measles etc as well. After all polio only kills 0.5% of cases as well. </sarcasm>
Re:Missing Covid-19 test data was caused by the ill-thought-... by makomk on Monday October 05, 2020 @06:55AM (#24686464)
Hey, at least the UK has automated handling of data! Parts of Germany managed to not tell people they'd tested positive for a week or two because their manual, Excel-based process for handling test results couldn't cope with the increased load from testing people returning to the country - and they're meant to be the competent ones we look up to, according to our press. (I get the impression that German experts have a rather different view.)
Re:Missing Covid-19 test data was caused by the ill-thought-... by ajsnigrutin on Monday October 05, 2020 @06:49AM (#24686434)
Once you have a couple of thousands of rows in excell files, one file per day, possibly one per testing center.... possibly with inconsistent structure.... non standard date formats (even different in each row),... add to this the inconsistent namings ("Tested at" column with data: "Testing center 1, London", "TC1, London", "TC1 London", "TC1 LONDON",....), etc...

Yes, anything-sql or whatever would be better, but deciding between putting all the data into sql by hand, or "uhm... i'll think of something and do it tomorrow", me, being lazy, I'd pick the second option,...

...until i'd get shamed on here. Maybe even a few days after.

Re:Missing Covid-19 test data was caused by the ill-thought-... by hdjdbtbgwjsn on Monday October 05, 2020 @07:34AM (#24686676)
Cleaning data is a major part of data engineering there are plenty of professionals capable of helping with this task.
Re:Missing Covid-19 test data was caused by the ill-thought-... by avian on Monday October 05, 2020 @06:00AM (#24686176)
> Using an Excel sheet as a database

How do you know it was Excel? I see no technical details in the posted article except "some files containing positive test results exceeded the maximum file size".

Re:Missing Covid-19 test data was caused by the ill-thought-... by benaadams on Monday October 05, 2020 @06:11AM (#24686233)
https://twitter.com/MaxCRoser/status/1313046638915706880

> The reason was apparently that the database is managed in Excel and the number of columns had reached the maximum.

Re:Missing Covid-19 test data was caused by the ill-thought-... by jiggawatts on Monday October 05, 2020 @06:19AM (#24686270)
Columns!?

You're meant to add additional records as rows! (Excel supports only 16,384 columns, but 1,048,576 rows.)

Actually, this was a pet peeve of mine: Especially in the early days of COVID, just about every official or unofficial data source was an absolute shitshow:

- Transposed data (new entries in columns)

- Pretty printed dates (instead of ISO 8601 or Excel format, or... anything even vaguely parseable by computer).

- US date formats mixed in with non-US date formats.

- Each day in a separate file, often with as little as 100 bytes per file. Thousands upon thousands of files.

- Random comments or annotations in numeric fields (preventing graphing the data).

We don't all need to be data scientists, or machine learning wizards, or quantum computing gods.

But come on. This is the most basic, data entry clerk level Excel 101 stuff. Trivial, basic stuff.

Put rows in rows.

Don't mix random notes into columns you might want to sum or graph.

Don't mix units.

Have headers.

Use a sane date format.

That is all.

</rant>

Re:Missing Covid-19 test data was caused by the ill-thought-... by hef19898 on Monday October 05, 2020 @06:40AM (#24686393)
Oh, I saw excel files that contained screen shots of other excel files over cells containing data. Comments where in text fields, if you were lucky, or again screen shots, from word.

And then the same people wondered why things based on this file didn't work.

Re:Missing Covid-19 test data was caused by the ill-thought-... by RobertKerans on Monday October 05, 2020 @12:29PM (#24689237)
They seem to have hit the row limit, not the column limit. They were using Excel to collate the data coming in from different test centres: when one of the CSVs was loaded in, the row limit was exceeded and Excel cut off the extra rows without the operator realising (I'm going off reporting by Alex Hearn at The Guardian here, and he's normally pretty good)
Re:Missing Covid-19 test data was caused by the ill-thought-... by cblconfederate on Monday October 05, 2020 @06:28AM (#24686322)
lets assume that local centers people do save them in rows, which is the most logical thing to do. But probably the software where they copy/paste them requires the data in columns, so maybe they transposed the data without realizing that it was truncated to max size.

Or else it wouldn't explain why it took so many days to realize that they couldn't add more columns.

Re:Missing Covid-19 test data was caused by the ill-thought-... by sails on Monday October 05, 2020 @06:50AM (#24686441)
A question, was it possibly a csv?

ie a csv is equivalent to Excel at the Daily Mail, so they printed Excel as the issue due to it being more familiar to their readership.

(Can't think of what the technical issue would be in the case of a csv, also doubt the credibility of the source!)

Re:Missing Covid-19 test data was caused by the ill-thought-... by gridlockd on Monday October 05, 2020 @02:06PM (#24690288)
The story I heard was that it was indeed a CSV and that Excel (silently) drops anything beyond 1 million rows.
Re:Missing Covid-19 test data was caused by the ill-thought-... by shadowgovt on Monday October 05, 2020 @02:52PM (#24690757)
More specifically: they were detouring through the old XLS file format, which has a 65k row limit.
Re:Missing Covid-19 test data was caused by the ill-thought-... by mellosouls on Monday October 05, 2020 @06:39AM (#24686381)
Actually from The Daily Mail, and not clear where they are getting it from.
Re:Missing Covid-19 test data was caused by the ill-thought-... by yodelshady on Monday October 05, 2020 @06:41AM (#24686399)
Worth noting the primary source for that claim is the Daily Mail.
Re:Missing Covid-19 test data was caused by the ill-thought-... by mellosouls on Monday October 05, 2020 @06:38AM (#24686375)
I noticed this as well, and started worrying the various news items that were citing Excel since had been reading HN!

I still haven't seen an authoritative source, or one that predates that comment.

But it definitely sounds possible.

Re:Missing Covid-19 test data was caused by the ill-thought-... by learnstats2 on Monday October 05, 2020 @06:54AM (#24686460)
It is absolutely standard that everyone doing office work outside of high-tech jobs uses Excel for data handling.

It's not surprising that this reaches into government.

Even if your canonical database is done properly, there will be a clash here as soon as you share (or give anyone the ability to generate) CSV files that are too large for Excel.

Re:Missing Covid-19 test data was caused by the ill-thought-... by DanBC on Monday October 05, 2020 @05:39AM (#24686035)
I agree, but I'm genuinely glad they're not[1] using fax machines.

[1] they almost certainly are using fax somewhere.

Re:Missing Covid-19 test data was caused by the ill-thought-... by brigandish on Monday October 05, 2020 @05:44AM (#24686067)
It's probably Japan but that would be the least of the egregiousness.

[1] Japan's COVID-19 Reports - 140KBs of Unadulterated Incompetence https://stdio.sangwhan.com/wtf-japan-covid-19-report/

[2] The previous HN discussion https://news.ycombinator.com/item?id=22728674

Re:Missing Covid-19 test data was caused by the ill-thought-... by poooogles on Monday October 05, 2020 @06:41AM (#24686396)
I get a cert warning on https://stdio.sangwhan.com/wtf-japan-covid-19-report/ which really is a good start.
Re:Missing Covid-19 test data was caused by the ill-thought-... by FerretFred on Monday October 05, 2020 @06:55AM (#24686468)
> professionalism in data handling

Nope. They appointed Dido Harding to cough manage it, and Matt Hancock oversees it. That should tell you all you need to know. I wonder if they were running Excel 2007?

Re:Missing Covid-19 test data was caused by the ill-thought-... by 231435359236 on Monday October 05, 2020 @09:04AM (#24687376)
I think it's more interesting on what happens to any analysis done with this incorrect data. There was a news article last week suggesting that the growth in COVID-19 cases was "levelling off" and I wonder in retrospect would this incorrect data account for such a position.

The article in question: https://www.bbc.co.uk/news/health-54387057

Re:Missing Covid-19 test data was caused by the ill-thought-... by azalemeth on Monday October 05, 2020 @09:54AM (#24687768)
Excel always feels like a bit of a curse of the world for me, and one that makes me feel like I'm going mad. I work partly in partnership with the NHS within my university, and get to see it being applied, completely inappropriately, with no ability to comment.

For me, I always feel like I'm living in some sort of parallel universe. Imagine living in a world where _you_, and a select group of others, know that there are better methods to drive a screw into a wall than punching it with your fist. However, 99% of people in the world punch screws into walls by hand, because they know how to use the only tool they have to hand (i.e. their hand). Their friends and neighbours do too. Their colleagues show them neat little tricks about how to fold your hand better to get the screw in with less pain, or more efficiently. The "proper" option -- drilling a pilot hole, using a self-tapping screw, understanding the difference between nails, bolts and screws, etc, or the concept of wall-plugs, is "too technical", "too complex", or requires "a large amount of infrastructure to fix a simple problem" [buying a drill & screwdriver, etc]. Sometimes people buy new walls with screws pre-installed because the thought of driving in those screws seems like an overwhelming technical burden.

You, and a group of similarly-minded nerds, understand just how stupid this is at times. You personally then spend the rest of your life watching people occasionally have horrific hand injuries from punching screws into walls and refusing the offer of screwdrivers. Others profit from selling vertical hard surfaces with a whole array of convenient screws pre-installed; or get employed teaching others the _very_ basics of screw-driving, or sell products containing all sorts of klunky hacks to cope with the fact that screws manually installed are at odd angles and tend to fall over randomly. The supplier of a popular brand of screwdrivers happens to be the world's largest screw manufacturer, but never seems to promote screwdrivers to people who currently punch them in with their fists. Instead, they sell them a subscription to screws, with the option for a very expensive pre-screwed wall-delivery service, built to your specification (that they call Dynamics for some reason…).

You, and a subset of your nerdy and educated friends recognise just how batshit insane the widespread use of punching-as-a-method-to-install-screws is, and occasionally laugh about it, mostly to distract yourself from the grim reality of buildings badly held together by punched-in-screws. Every so often a skyscraper-sized building collapses, [1] and it's realised in retrospect that the manually-driven-in-screws were not the right tool for the job. Still, people carry on banging in screws with their fists, and you increasingly realise that you have to -- somehow -- accept that in order to sleep at night without going insane.

[1] https://www.businessinsider.com/excel-partly-to-blame-for-tr...

Re:Missing Covid-19 test data was caused by the ill-thought-... by duderific on Monday October 05, 2020 @02:23PM (#24690458)
My dad (who is 79 but still working) proudly mentioned that he spent 11 hours putting together some charts in Excel for his client. Probably something that could be done in an hour or so with an online tool.
Re:Missing Covid-19 test data was caused by the ill-thought-... by mytailorisrich on Monday October 05, 2020 @05:34AM (#24686012)
To be fair, there hasn't been any massive irretrievable data loss yet, so not too bad.

https://www.youtube.com/watch?v=K_FrQnQv0Vw (probably an accurate depiction of what's going on in Whitehall right now, though)

Re:Missing Covid-19 test data was caused by the ill-thought-... by rgallagher27 on Monday October 05, 2020 @05:55AM (#24686143)
At this point I think I'd rather have Peter Capaldi acting as Malcolm Tucker running Whitehall over the shower of incompetent people we have now. At least he might get something done in between the flaying
Re:Missing Covid-19 test data was caused by the ill-thought-... by iso1631 on Monday October 05, 2020 @06:01AM (#24686185)
I'd rather have David Brent, but we are where we are
Re:Missing Covid-19 test data was caused by the ill-thought-... by gridlockd on Monday October 05, 2020 @02:12PM (#24690356)
> Using an Excel sheet as a database?

You wouldn't (want to) believe how incredibly common that is.

Re:Missing Covid-19 test data was caused by the ill-thought-... by lini on Monday October 05, 2020 @06:46AM (#24686416)
Excel has a limit of ~1M rows per sheet. Total cases in the UK until now are ~500K. So it seems they use 2 rows per case in the sheet.

Edit: oops, it seems they used columns for the cases and the ~16K column limit was hit when daily cases exceeded that.