News: 1601915109

Spreadsheets are "human middleware" almost everywhere, not only in the NHS. I've only a couple of times in as many decades seen a "risk register" that wasn't an Excel spreadsheet. Indeed the limitations of a spreadsheets continues to inhibit proper risk assessment, as it has to be hierarchical from scenario to causality in order to arrive at realistic results but nobody seems to have found out how to do that in a spreadsheet.

And although it's not so disastrous, I've also seen spreadsheets widely used a layout grids for corporate tasks that have no requirement for calculation, such a organisation charts and customer enquiry forms.

Spreadsheets are "human middleware" almost everywhere, not only in the NHS.

My other half does a lot of consultancy work for various HMG departments. They usually want spreadsheets as their deliverables because a) the politicians and civil servants know Excel and don't want to learn anything else, and b) Excel runs pretty much on every departmental computer so the spreadsheets will run(*) anywhere. The downside is of course that bugs in spreadsheets are ubiquitous and a bastard to find.

(*) Some are so huge that "crawl" would be a better verb.

Good enough for politicians but real work needs something better.

This is true... a friend of mine with extensive experience with the Cabinet Office and other departments made precisely that observation earlier today...

Excel is wedged into places and made to do things it's not designed for, but because that's all people care to understand, that becomes the default format. It's truly horrific.

The solution is simples:

https://www.change.org/p/microsoft-increase-the-maximum-number-of-columns-in-excel-to-32-768

Signed!

W - H - Y - ?.....

Don't encourage this petition for idiots!!!

This must the the most stupid idea in the history of stupid ideas ("Dear Microsoft, please double the number of columns in Excel to 32768"). I trust that Microsoft will ignore this, or at least explain why this is a silly request.

However, when I looked just now, only 7 people had signed it

Have you had an irony bypass or just had a bad day?

What I don't get in all this is who decided to ignore the error message? I also don't understand how cases could rise, surely once the file hit the limit you can't add any new data. The cynic in me see this as a distraction from the number of cases and to avoid calls for a second lock down but at least we should all go to the cinema.

If you really want an interchange standard for a transaction log with "lots of rows" that Excel (other spreadsheet programs are available) can read, what is wrong with CSV? With a bit of care it could even count as EDI and bring standards right up to the late '70s.

Who knows, one could even write a 10 30 line perl python program to add the whole lot to any database of your choice.

Lack of data integrity checks, no way to encrypt contents, the usual CSV madness with fields with dates or spaces in them. To start with.

As a way of setting up a testing reporting method between Serco and PHE in record time with minimum contract penalties for the government to pay then yeah, it works. Wonderful. That doesn't mean it should be used any more than strictly necessary.

And whoever signed off on that contract should he taken out back and shot pour encourager les autres.

The only place where CSV is going to have a problem with content is if it contains a comma, surely. And we have a solution to that it's called \

Not according to RFC 4180, it says a comma must be escaped by surrounding the field which it belongs in in double quotes.

Holy shit 2005? I didn't even realize that RFC existed. I've been using it since the 90s and just \ escaping things.

I want to know, or rather to rhetorically ask, what happens if the data field contains a quote mark.

All the software tools I use want to know what an RFC is. Except for our change control system in which an RFC is a change order.

This one that defines CSV is from 2005 anyway: complying with it now would break all of our carefully managed incompatibilities.

My preferred way to deal with CSV data is to read the entire line and then apply my own decoder. Generating output, on the other hand, I take pains to remove line breaks and tabs breaks. In my field, nobody really wants these usually, so I just change them to space and to > , which hasn't yet bitten me, but may.

Another favourite is an external provider which sends us a "CSV" file in which fields are separated with | which is not a comma and also not a problem. Up to now.

That'll be the "integrity check" that was both missing and the error message that was ignored in this case then?

A spreadsheet is something user fiddles with. It is a not a reliable, appropriate, nor scalable means of data exchange. QED.

Forms of CSV with field definitions, validators and some means of batch (and message) integrity checks is a bog standard - and effective - means of data exchange which, incidentally, underlies far (faaaaaaaar) more of world's data exchange than, clearly, you would be comfortable with.

Correct. I don't trust them as there's too much ambiguity when it comes to reading and writing them. I know, I have to fix the bugs afterwards.

And someone is bound to load one into Excel to look at it then save it again for extra fun.

You encrypt the file by, well, duh, encrypting the file.

I can see that happening before it's put on a USB stick and taken by train from Serco to PHE HQ.

The problem with CSV is that when you open it in Excel, if there are too many rows, it chops off the ones at the bottom.

They were receiving the case lists from around the country and compiling them into a single file in Excel.

But the excel excuse relies on it being used as an interchange format, "oh we were just using it to move data between systems, we aren't so completely incompetent as to maintain all the test records in excel". If it's being combined into a single excel file then that is worse .

As for csv lacking integrity checks, well, so does excel. Send it with a checksum if you're worried. Yes, excel might produce a warning if a file has been corrupted, but it can't tell you data in a row hasn't been accidentally modified. There is a pretty reliable convention for csv now, and if you control both the sending and receiving end then you can make sure they're in agreement about field data types. If you don't want to do that, R, Stata, SPSS all have their own data formats which are designed for this stuff. Using a data format that is designed to be opened up and monkeyed around with rather than machine processed for data at this scale is a mistake.

In my mind the Covid-19 data was churned by mighty databases processing astoundingly accurate statistics in sparkly data centres. The reality is frightningly mundane and somehow not quite serious enough for the situation.

From my understanding it was reported right up to the top so that there would be absolutely no misunderstanding that Excel should not be used. The heads of development at NHSX told PHE exactly what would happen if they used a spreadsheet system and sure enough it happened.

From a civil service point of view every single person involved has done CYA. The sword is now passed to Baroness Dido to fall upon.

It wouldn't be the first time that Typhoid Dido has overseen an IT disaster.

I'm feeling, if not confident, hopeful that the organisation I work in will now not require me to use Excel as a database - for about four weeks.

I am willing to release data by copying it to Excel if that immediately is someone else's problem after that.

Someone was given a series of steps they are told they must perform exactly as listed without any deviation. If you ask for a robot, you get a robot.

A robot that's paid peanuts.

IIRC, macro code can be used to suppress warnings and errors. Wouldn't be too hard to imagine some bright spark thinking this solves a few problems on importing a new file.

This might do the trick, and gets rid of those pesky "are you sure", "file already exists" type messages. Perhaps also the "file got too many rows or columns" one?

Application.DisplayAlerts = False

otherwise known as "a hancock" (or "a dido")

It's a sliding scale from a Hancock, through a Dido up to a full on Grayling. Because there's failing and then there's Grayling.

I bet Jeremy Hunt is glad is he safely on the back benches.

Although the article has 'wave' in the title the link to it uses the collective noun for vultures. Make of that what you will.

Excel has been both a blessing and a curse throughout my working life. A blessing because it's given me plenty of work fixing cock-ups caused by its being (ab)used for data entry and preparation, and a curse because I've had to set about fixing the cock-ups in the first place. Lowlights along the way include Excel outputs breaking batch loads thanks to its 'smart quotes' feature, a major row at a Very Big Bank because the 'test data validation team' were oblivious to the Software from Hell's propensities for arbitrarily reformatting dates, another series of little oopsies caused by the silent conversion of large integers into exponentials and data quality reports going haywire because saved CSV files had loads of empty trailing rows.

If Serco really are using Excel as a main data store, they deserve to be hung out to dry and permanently barred from getting any future government work, considering the money that Spaffer's spaffed on them. That's absolutely unforgivable.

See icon for what I'd like to do to Excel.

So positive Cases passed 500,000 this weekend, so say two lines per test - date taken, date of result - gives a million - so max rows - from merging all the test centre files - Excel goes bang?

So close, but according to BBC they were using XLS (circa 1987) files rather than XLSX (circa 2007) so limits were 65k rows... we’re doomed!

The entire team responsible should be sent home to write out "Excel is not a database" by hand, 16,000 times. Each.

At the very least include a count of results uploaded at the top and check that that number is actually received. For preference also add a specific end of file marker. Ring alarms if one of these tests fails.

Is it really that hard?

Excel is not a database^↲

^↓

^d

But they would use a spreadsheet to do that task.

The only way I can imagine such a howler of an error going unnoticed for so long, is if this poor abused spreadsheet was wrapped in an evil VBA front end, perhaps with the four words of VBA damnation:

on error resune next which translates to "fail silently please"

but ffs. EXCEL IS NOT A DATABASE! Argh. When will MS Excess & VBA shuffle off into the dustbin of obsolescence..

I reckon the job was probably left to a biologist in a testing lab as a temporary solution for a couple of weeks, and they did a really good job of it within their IT ability, but the final product never arrived due to moving goalposts etc.

Never let a badger program your test and trace.They've got form and they've got a valid grudge!

Yeah, I worked in the NHS, I remember a quite senior doctor coded up a shitty timetabling system! My first thought on hearing about it was that a doctor might be behind it :D

That's my position, and I'm sticking to it.

How appropriate, a spreadsheet was the original 'killer app' after all...

That is all,

Tragic Dido must be losing her touch... she "lost" 10 times that many at Talk Talk.

Expect further debacles to bring up the numbers to something more respectable...

Only 16k lost records have been found ...

Development time to import data files direct to final system using very flexible and reliable but hugely hassle ridden generic import - two weeks

Development time using excel as an intermediary due to far more user friendly import routines - twenty minutes

Not to ,mention the potential use of the obscure but oh-so-useful Excel binary format, the .XLSB

Stop

USING

EXCEL