A rogue space in the date field caused almost 30,000 Scottish students to get their exam results a day early, as Excel versions clashed and human checking fell down. The texts telling students their Higher results (the Scottish equivalent of A-Levels) were supposed to go out Thursday morning, having been preloaded onto the …
All hail Excel!
It's clearly the right tool for the job.
But the people setting this up are probably a product of the UK education system, where teaching computing appears to be synonymous with forcing MS applications down the throats of the completely disenfranchised kids.
"teaching computing appears to be synonymous with forcing MS applications down the throats of the completely disenfranchised kids."
Prepares them for the world of work, synonymous with forcing MS applications down the throats of the completely disenfranchised call centre / bank backend lackies.
If business complain that kids don't know enough about Excel, it gets put on the curriculum.
No use teaching a kid to use VIM or even OpenOffice when they get a job (even techy stuff such as development requirements, support calls weekly report or QA test cases) and the work computer has Office 2010 installed and the business analysts / "highers up" are using .xlsx s.
(Not that I am an MS fanboi, quite the opposite, but unfortunately they do seem to have a monopoly on business desktop OS and office suites)
what has Open|LibreOffice got to do with anything?
Teach them maths, music theory and the von Neumann architecture, and of course how to read. Far better teaching a man how to fish for information than making him dependant on using one particular tool.
Not entirely true these days - I know of quite a few schools which, pressed for cash, are using Ubuntu and Open Office.
And it often is
Verity Stob hits the nail firmly on the head as usual - this sort of failure is inevitable as time passes and Excel is just a Birmingham screwdriver.
The ubiquitous Excel usage in this sort of thing is a reflection on the rest of the tools available - quite honestly it's easier to do many simple tasks in Excel than it is to use Word or Access due more to the shortcomings of those applications than any real malice on the part of the user.
re: And it often is
What do Word and Access have to do with it? The question was why was MS's spreadsheet editor used instead of a competing tool (Open/LibreOffice, etc), not why was a spreadsheet editor used.
@Brendan - Actually in my original post
what I was trying to say was why use a spreadsheet for something that would be better in a database with strong data validation rules.
And the other thing I alluded to was don't teach packages, teach techniques, especially to those students who have a real interest in computers as technical devices rather than computers as a commodity means to an end.
Because they're crap?
Seriously, they look and feel like Win 95 vintage. There's some great and usable OS software out there, but Open Office feels like driving a Trabant. At least with Microsoft, you get a Lada.
Bloated office suites are legacy-ware - why waste timing cloning them? Time for a rethink
your circular reasoning is circular and kind of offensive
If you taught the kids only Excel, then you'd drive the market towards purchasing more Excel (since that's all anyone knows how to use), creating more demand for trained Excel monkeys, and so on, and so forth.
It also kind of implies that we're living in a sort of feudal state where our value is strictly defined as our ability to do data entry or dig potatoes on our lord's estate.
Better that they look like they're of 'Win95 vintage' (which they don't) than have THAT FUCKING RIBBON!!!!
@AC 20110804:1642 RE:Actually in my original post
I offer no disagreement with your post. My disagreement was with Version 1.0's response to your original post in which the point of both the article and your post seemed to be completely lost.
You miss the point of the ribbon
It's a brain training aid. Every time you need a menu item, you have to go on a little treasure hunt
no problem with that
I agree with you - my post was more a comment on the general situation than a direct response to the article. Basically I feel we're building crap tools and than leaving the monkeys to figure out how to try and use them.
So it's not too surprising when then use them inappropriately and badly - yes, Excel is a terrible choice for this sort of work ... but that's the tool that "we" gave them ... so it's not too surprising when they do this sort of thing.
Reminds me of an XKCD...
The real failure was the Scottish Qualifications Authority in providing dates as string format. What happens next time when SQA fills in the spreadsheet with a different date format? Presumably the person doing the import has been spoken to, and perhaps their import tool changed to warn of unexpected data rather than going ahead blindly with defaults.
Excel and dates
I hate, hate, hate, hate, hate, hate, hate with the burning passion of 1000 suns the way Excel mucks up dates so I set everything to be text.
And I'm wary of even opening a csv file in Excel.
Oh, the price we pay for quick and dirty charting and the awesomeness of the auto-filter.
Re: Excel and dates.
"....so I set everything to be text."
You don't work for the Scottish Qualifications Authority by any chance.....?
Excel does not excell at holding data
Converting CSV->Excel and Excel->CSV gives different results for the various version of Excel.
Top Tip: Never use Excel in any step of your data flow.
Dates? Try phone numbers!
+4412345678901 gets turned into 4.41235E+12.
We've been here before.
Automatic conversions. Back then it was scientific data that got corrupted. Now it's something else, but also sensitive. Didn't we learn not too long ago that the USoA is running its nuclear waste administration using similar micros~1 software? And that it offered to "help" Russia with "upgrading" to their wonderful system from much less quirk-prone paper?
Of course, the standard solution is /more/ of that truly useful behind-your-back automatic software.
Why did they need a send-date? Surely all the results should be "send on Thur Aug 5 2011", and thus the date is redundant?
Why did they need a send-date?
good point , maybe there was only one date that applied to the whole list
another "feature" of excel csvs i've noticed is that it'll stick a whole load more commas in than if you made your own.
every row has the same amount of commas as the row with most entries, including blank rows
Until next year
When the headline is that all the results were sent out as soon as they were entered because the system thought it was lagging behind
"Why did they need a send-date?"
Because it's a third party system that sends more than exam results one day a year I would imagine. Would seem a bit extreme for the SQA to have their own system specifically for this purpose.
"every row has the same amount of commas as the row with most entries, including blank rows"
You mean like:
Isn't it painfully obvious why each row has the same format?
Pre Load V's Post Load checks
A Pre-load manual check would not spot a space char in an excel spreadsheet, on 99% of occasions..(unless it was looked for) a check in .txt or .csv would likely have spotted this. However I am a great believer in checking right at the last minute before the go button is pressed in the database table itself, this way you can catch default or import errors after they happen without having to pre-empt them.
Heres a tip
If you are entering a number field like date, serial numbers etc that are getting fubar'd by Excel, especially that damn Scientific number conversion for long numbers, then just insert a ' before the number, then Excel leaves it the hell alone and it goes in exactly as you typed it.
When it's much easier to diss the evil microsoft corp. than actually bothering to learn how to use the software
Re: Here's a tip
That causes Excel to store it as text. Causing this problem......
For long numbers, formatting the cells as number rather than leaving them as general and allowing Excel to swap the formatting between number and scientific to suit itself is the right approach. Likewise, yer serial numbers 'n such have no issues in a column formatted as text.
Using ' is a bodge.
What they should have done is set the column type correctly... excel would have automatically discarded the space then...
Either that or they should have stored the bloody information in a proper database, instead of glorified electronic graph paper.
... just insert a ' before the number...
Ever tried that in Open/Libre Office ?
It strips the apostrophe right out again and formats it as a date.
( I can almost hear it thinking "The things I have to do for these dumb w**nkers !)
When I submit data to a (feathered) bird-reporting website, it REQUIRES me to upload the data in an MS Excel spreadsheet, containing dates as text.
I don't do Excel: I use LO
I have to make a text string like " 'date25/12/2011 "
Then use the formula =right(A1,10)
Then copy the result, and paste "formula as text" into the target cell.
This flummoxes Libre Office into doing the right thing in spite of itself.
Then I save the file in the (pre-historic) MS Excel format for upload.
You have not been charged for this gem of a work-around.
"It strips the apostrophe right out again and formats it as a date."
I think you need to learn how to use a spreadsheet !
From LibreOffice help
"If you decide to enter a number directly as a text string, enter an apostrophe (') first. For example, for years in column headings, you can enter '1999, '2000 and '2001. The apostrophe is not visible in the cell, it only indicates that the entry is to be recognised as text and not as a number."
and it works for me !
an even better tip
here's an even better tip: never, ever use the massive pile of shite that is excel for anything.
perhaps you've been assimilated by the borg. some of us use software that does what it's fucking told to do instead of dictating what's best for us.
burned too many times to consider an alternative date format
Excel is a horrid liability
It's far, far too easy for Excel to interpret numbers in ways you didn't expect, for users not to understand that what is displayed and what is stored are two different things, and crucially the fact that some of the functions are prone to introduce errors.
I would bring especial ire to the filtering functions, which look very useful until you try to use a formula as an input. It's very easy to create results which look reasonable but are in fact entirely incorrect.
In the future I'm going to be looking at either R, or a proper programming language.
This is actually the Office 2007 Excel workbook extension.
Having said that, one of the great problems of Excel is that people look at the rows & columns and assume that is structured data.
Not entirely Excel's fault
Imagine that you're writing a CSV importer (disregard the fact it would be sensible to use one of the many robust, tested solutions to this particular problem).
Your importer is processing a field that is expected to contain a date string. It finds something surrounded by quotes. Do you:
(a) Strip off the quotes and any spaces to see if what they contain is a date string, because quoted fields are a common feature of CSV?
(b) Report an error and reject the row (or the entire file)?
(c) Use an arbitrary date value (today's date...your birthday...14 Oct 1066) and carry on?
I can't think of any situation in which (c) might be the right answer.
use for option (c)
if the date is the "loaded date" and just used in the ETL system meta data. In the case that the date cannot be read then the current date would probably suffice.
However case (a) followed by case (b) in the event of a failure would be the normal approach
Maybe like this
"Send the message immediately unless the DoNotSendUntil field contains a valid date+time, which it probably won't"
"Set AttemptSendWhen = ISNULL(DoNotSendUntil, GETDATE())"
Generally when you transmit a text message, you want it delivered without waiting.
I don't feel confident that this really is a description of what happened. The detail that intrigues me is the assertion that AQL was sending the text messages free of charge. A false economy by the SQA.
(d) using the following import:
if IsDate([Data]) Then [Data] else Null end
(Expecting Bad data to throw an isnull error.) Into a Table Where the Date Column Default is =Now() would result in the error described.
This is most likely to have happened because person A built the table in year 200X and person Y built the import routine in year 200B.. neither understanding the consequences of what appeared to be a sensible-ish bit of code.
Defaults is bad! mmkay..
Good job computers are infallible
Couldn't read the date so inserted default of todays date.
Couldn't read the name on the DNA profile so default entered the suspect
Couldn't read the coordinates of the target so default entered the current position
They aer infallible
They're only doing what they were told to do!
You can always spot an Excel-exported csv
Several clients send us data feeds as csv; you can clearly spot the ones which have come from a stored routine in an ETL tool and those which Brenda has knocked up in Excel!
"They'll be checking all their processes to find out why the last-stage human read-through didn't pick up the error" = "We haven't exported your job function yet? We'll have to rectify that situation post-haste."
Fail, fail, fail, good job it wasn’t corporate mission critical data. Oh Lord* please protect me from end user computing. Business users *LOVE* excel (and ms-access) as it puts computing power on the users desk;
why have one centrally managed application and database when you hundreds of spreadsheets and access databases all over the place. Much better for empire building.
Why have internal chargeback for IT development when you can let every luser think he/she can work as a developer and database administrator. Much easier to work if you don’t have share/justify your budget with anyone else! It’s much easier to waste 20K of budget for the day to day running of your operation rather than explain why you want to spend 10K in the IT department, after all that would mean making a decision.
It is impossible to create an fool proof application as the fools are so dammed inventive.
I can imagine what happened, whoever created the spreadsheet tried to replicate the cell with the date in it by dragging it down the column but excel kept incrementing the date by adding one day to the date. Of course by sticking a space on the end excel now sees it as text and does not increment the date when replicating the cell. <sarcasm> Wow, I’ve never seen a user do that before </sarcasm>.
And what sort of IT idiot would accept an excel/csv file as "correctly formatted data"
That is why now when the users want to upload anything to the servers, I give them a spreadsheet that is programmed to validate their input, create an upload file and kick off the FTP, and most importantly, the spreadsheet is protected to stop them fiddling with the code.
From Stan Kelly-Bootle's wonderful "Computer Contradictionary": -
downtime: the period during which the system is stable and safe from user input.
lusers can start the down voting now......
annon, for obvious reasons
* Imaginary friend of your own choice.
The bad news about spreadsheets...
... is here
The WORSE news about spreadsheets...
If you must send someone an Excel spreadsheet to fill in,
for the love of god/buddha/vishnu/fsm, lock it down with as many validation rules and cell protection as is humanly possible.
Then, when it comes back with incomplete information, try and explain to the powers that be that you need a better way of collecting your data (even though you know the answer will be that there is no budget for this).
Let's talk about the real problem...
Can someone please tell me WHY it has been considered damaging to students to release exam results early? That is the real issue.
problem? what problem?
> WHY it has been considered damaging to students to release exam results early?
perhaps it's escaped your attention that universities offer places to prospective students based on exam results.
if you get your exam results ahead of everyone else, you have a better chance of a place at the university or course that you want, all other things being equal. you'll also have a wider choice of options: eg being at the head of the queue if a place opens up on a course that's already full; or immediately going to your n+1th preference because your results weren't good enough for your n-th choice; or an earlier entry into clearing.
admissions tutors tend to favour students who want to study at their institution. so if you can start knocking on their doors ahead of the competition, you have an advantage. this can be significant if your exam results just scraped under the required number of points and the course isn't over-subscribed.
although universities will have made conditional offers based on expected exam results, things start to crystallise once the results are known. the exam boards will have already passed on those results to universities anyway. so if you've got a head start on when everyone else gets their results, you've got an advantage when assessing your options. not to mention an extra day to talk to admissions tutors, get advice about alternative courses and so on before the rush starts.
paris icon because she knows a thing or two about early entry.
- Does Apple's iOS 7 make you physically SICK? Try swallowing version 7.1
- Fee fie Firefox: Mozilla's lawyers probe Dell over browser install charge
- Pics Indestructible Death Stars blow up planets with glowing KILL RAY
- Video Snowden: You can't trust SPOOKS with your DATA
- Hands on Satisfy my scroll: El Reg gets claws on Windows 8.1 spring update