back to article Christmas Eve ERP migration derailed by silly spreadsheet sort

Welcome again to On-Call, the column on which we conduct a Friday forage through the inbox full of readers' stories of jobs gone wrong. Today, we wrap our special week of Festive On-Call and so our topic is ... drumroll please ... Christmas. Let's start with “Mike” who told us that “Back in the Christmas of 1995 I was working …

Silver badge

If you want to screw something up, use excel

Excel genetically modified bugs.

Happy Halloween (OCT31==DEC25).

26
0
Silver badge

Re: If you want to screw something up, use excel

"Someone had sorted that spreadsheet and in doing so mixed up the order..."

If I had a € for every time - no, if I just had all the time spent fixing stuff like that back I'd be the happiest man on earth.

17
0

Re: If you want to screw something up, use excel

I remember a temp being told to sort a spreadsheet of names and addresses on the surnames, in Excel 2000. She selected the Surname column, pressed the sort button, saw they were alphabetical and ran the mail-merge.

Alas, Excel 2000 didn't warn that only that column was being sorted. So pensioners all over the borough were sent letters inviting them to come for a flu jab, but addressed to the wrong person.

11
0
Silver badge
Facepalm

Re: If you want to screw something up, use excel

Why would any spreadsheet program default to sorting a single colu... Oh, Excel - carry on.

23
0
Silver badge

Re: If you want to screw something up, use excel

Its taken MS years but they finally have a product that is completely immune to all normal programming management techniques designed to make software just a bit more reliable then a Samsung battery,

20
0
Silver badge

Re: If you want to screw something up, use excel

@Tom 7 - Samsung batteries are more reliable than Slurpware; they can be counted on to catch fire.

10
1
Silver badge
Facepalm

Re: If you want to screw something up, use excel @Will G.

Why would any spreadsheet program default to sorting a single colu... Oh, Excel - carry on.

My Excel 2013 works exactly as LO - i.e. it asks whether columns with data should be included.

I just tested OO 1.1.5 (released in 2005, couldn't find earlier ver) and it didn't have such prompt. (and it also asked wheter I'd like to install Java!)

It also capitalized the first letter in a cell as I typed data for the sort test, which Excel certainly doesn't do!

5
8

Re: If you want to screw something up, use excel

Friend had a sheet that listed leased lines between international sites. Marked which were old and to be disconnected, and which were new and now live.

Between last update, and going to the next department, someone spotted the sheet didn't have the company logo on it, and inserted it, moving some coloums down.

All "to be disconnected/old" became "new/live", and all "new/live" lines became "to be disconnected".

They only noticed when the sites started to vanish..

7
0
Silver badge

PCock-up

My brother worked for a temp agency and was desperate for qualified PC tinkerers to work between Christmas Day and New Year.

The problem? His client, a German food discounter, had bought a container load of PCs from their German distributor for their UK stores... Only they sent the wrong HGV to England and the PCs sold in the stores were German specification, with German keyboards and German Windows 95 installed... Unfortunately, they only found out after they had sold the complete batch!

As these PCs were mainly going to be given as Christmas presents, the discounter was looking for tech support staff to driver around the country and swap out the keyboards and re-install Windows on those PCs.

The pay offered wasn't bad, but I decided that the wrath of hundreds of unhappy families watching over my shoulder as I reinstalled Windows wasn't worth it.

16
0
Silver badge

"...and in the morning when the parents turned it back on they would be greeted with 'Operating system not found' instead of a working Windows 95 PC."

I am actually at a loss to say which one is worse.

22
0
Silver badge

"I am actually at a loss to say which one is worse."

You don't remember the times when people used to queue to get the latest version of Windows? Actually 95 was a major step forward, at least in terms of versions built on top of DOS. They put together a lot of stuff that had been around in terms of UI over the previous few years and hit a sweet spot with it. There's a good argument that the overall trend form that time has been down. They also incorporated a lot of stuff from HP's New Wave which, if you ran it over W3.x, made a big improvement in usability. And this from someone who is considerably less than Microsoft's greatest fan.

21
1
Silver badge
Flame

Win95

Still mostly 16bit, just all the 32bit extensions in WFWG3.11 rolled up. Real 32bit NT was two years old. Properly configured Win For Workgroups had poor "Program Manager", but had 32bit disk driver, same 32bit TCP/IP, Video For Windows/ Multimedia, Win32s to run NT programs.Win95 was less stable than properly installed WFWG 3.11 with all extras. Rubbish compared to NT3.5

Plus: The new desktop instead of Program Manager.

Minuses:

* Explorer File Mangler, single window and buggy.

* Autrorun added, even though already proven bad on Amiga

* Fake Win APIs added so mostly 16 bit Office 95 wouldn't run on Win3.x, even with Win32s, which meant that that NT 3.5 couldn't run Office 95, they had to release NT3.51

* No named pipe creation (just like Win3.x)

* Barely more than WFWG 3.11 with new shell.

* No process isolation

* No Security. The Log-in only affected what LOCAL Network resources you could connect to!

* Poisoned development of NT4.0, Win2K, XP and led to Vista.

* Led to Win ME, the Win98SE was best version.

* Only later OEM Version b had USB

* Encouraged bad practices on Program Application development.

* Direct X, nothing more than a Kludge to port DOS games with direct access to Graphics HW, leading to stupidity of Graphics and Printer Drivers in Kernel in NT4.0.

* Held back deployment of NT in business for about 7 years.

* As damaging to progress of Security & decent OS adoption as DOS was in 1980/1981.

Win95 should ONLY have been on a games console. Instead something intended only for home users was mis-sold to most small bushiness and many larger ones.

In reality to run Excel, Web Browser, Word and TCP/IP, without paging, you needed as much RAM as NT 3.5x anyway.

Oh, and a Shell only version of Explorer desktop / Explorer File manager as "Tech preview" worked fine on NT3.51. Instead they broke NT4.0 by kernel changes and Direct X (and it still couldn't run most Win9x games anyway, so they broke it without achieving anything for business users).

It's been downhill since 1995 for MS, accelerating in 2002/2004 with Vista development. Too late, Aero is officially a mistake. Vista = NT 6.0, Win9x on Ecstasy.

Win7 is really Win 6.x

Win 2000, XP and Win 2003 are NT 5.x

Win 8 and Win 10 are pointless.

MS already had a decent 32bit OS for over two years when they released Win95.

Heck, they even had their own versions of UNIX(Xenix in 1987?) and MS OS/2+LanManager (1989 after ending IBM deal).

All they needed was to put the new shell on NT3.5 and have a game console with Win95. Idiots.

9
4
Silver badge
FAIL

Re: Win95 @Mage

"Rubbish compared to NT3.5"

Except that NT had much tougher hardware requirements, and had poor backwards compatibility with DOS software. On purely architectural merits NT and others beat it hands down, no question.

"Plus: The new desktop instead of Program Manager."

Compared to DOS/Win3.1 there were more than just that. Long file names and just a general ease of use for the home users not accustomed to using command line. With Windows you could just plug in hardware and have some expectations of Windows finding it and having it working instead of guessing the serial port addresses and interrupts for COM3 or finding the correct mouse driver and not worrying about having the base 640k exhausted and thus not having enough memory for programs to launch.

"Only later OEM Version b had USB"

WIndows 95, released surprisingly in 1995, predates USB. NT - released in 1996 (post USB) didn't.

"Encouraged bad practices on Program Application development."

Please explain. Microsoft has been offering coding guidelines since before Win95.

"It's been downhill since 1995 for MS"

"All they needed was to put the new shell on NT3.5 and have a game console with Win95. Idiots."

Are you from Bizarro world??

Bill Gates and his cohorts weren't idiots - still the richest man alive? The company is still a behemoth and steadily making money and ruling on PC platform. They've made lots of stupendous mistakes but Windows 95 was really the point where they left the competition behind on personal computers.

10
2
Silver badge

Re: Win95

Urban Dictionary has a rather short definition:

"Windows 95/98, (n): 32 bit extension and a graphical shell for a 16 bit patch to an 8 bit operating system originally coded for a 4 bit microprocessor, written by a 2 bit company that can't stand 1 bit of competition."

20
0
Silver badge
Mushroom

Re: Win95 @ Mage

Personally, I think you're being far too kind to Slurp.

Even if it it's Christmas.

Fortunately I missed '95. Got landed with Crashmatic 98se.

BLEAURRRGH!

At least it got me to cuddle up to the Penguin. Never looked back.

Feelin' hungry yet, ms??>>

2
2
Silver badge
Pint

consumer-focused digital candy bar phones

Best description of modern smartphones ever. Totally going to pinch that.

8
1
Silver badge

Re: consumer-focused digital candy bar phones

Sorry to disappoint you, but 'candy bar phone' is usually used to mean something like an old school nokia in order to differentiate them from the flip phones which came before. I assume because one of those Nokias is approximately the dimensions of a chocolate bar (ish).

12
0
Silver badge

Re: consumer-focused digital candy bar phones

Can we call the modern thin smartphones Diam Bar phones, please?

1
1
Trollface

Re: consumer-focused digital candy bar phones

"Can we call the modern thin smartphones Diam Bar phones, please?"

I thought we voted to rename them back to Dime bars?

6
0
Silver badge

Re: consumer-focused digital candy bar phones

I prefer Armadillos.

13
0
Silver badge

Re: consumer-focused digital candy bar phones

I think the canonical mars-bar phone was actually a Sony

http://www.tvfilmprops.co.uk/det/434/Sony-CM-H333-%5EMars-Bar%5E-Mobile-Phone/

1
0
Silver badge
Joke

Re: consumer-focused digital candy bar phones

"Can we call the modern thin smartphones Diam Bar phones, please?"

I have had many comments from SWMBO about her "Diam phone!" ... or something that sounded like that...

6
0
Silver badge

Re: consumer-focused digital candy bar phones

We might as well call them Scroll Bars...

4
0
Joke

Re: consumer-focused digital candy bar phones

Come now, SURELY you'd have to call them Crunch bars.

(https://upload.wikimedia.org/wikipedia/en/thumb/f/f3/Nestle-crunch-small.jpg/330px-Nestle-crunch-small.jpg for those who don't know of them)

3
0
Silver badge
Joke

I prefer Armadillos.

Hmmm...

Probably got fairly decent shock protection, but might be a little hard on the pocketses.

How would you butt dial on one of them?

Ouch!

1
0
Boffin

Re: consumer-focused digital candy bar phones

Sorry to disappoint you, but 'candy bar phone' is usually used to mean something like an old school nokia in order to differentiate them from the flip phones which came before.

Indeed, but it still applies. It's just that in the transition to smartphones, and due to the iPhone design taking over everything else, every single smartphone is now in candybar form.

And I hate this. I miss the clamshell form factor, as it was far more pocketable. The last manufacturer to keep some form of this was BlackBerry, and they decided to give up this year.

I've seen the future, and it's full of boring, featureless slabs with no physical keyboards.

1
0
Anonymous Coward

Ah, Excel sorting

I've been at the wrong end of a bad Excel sort. Disk migration, where the storage guys had a spreadsheet mapping the old LUN IDs to the new so I'd written up all my scripts to do the LUN remapping at the VIOS and import the VGs... and it didn't work. After much head scratching, it turns out the storage guy had sorted the spreadsheet, but not all the columns (only a section) so some of the columns weren't in sync so all my planning was out the window.

We wound up having to revert back the change as there were over 700 disk IDs and no way we could sort out the mess in the change window.

8
0
Silver badge

Re: Ah, Excel sorting

God forbid you should ever try and save something with a leading zero to excel either...

Like say a phone number or a part number... And thats before we come to having numbers longer than 10 characters.

Always helpful when you've got a CSV file that a customer helpfully edits and then saves to upload to a database before complaining everythings missing/wrong..

13
1
Silver badge

Re: Ah, Excel sorting

Most spreadsheets (including Excel) allow you to modify the format of a column (/row/cell) to allow it to keep the leading zero. It's up to the user to pick the appropriate format themselves.

4
5

Re: Ah, Excel sorting

>Most spreadsheets (including Excel) allow you to modify the format of a column (/row/cell) to allow it to keep the leading zero. It's up to the user to pick the appropriate format themselves.<

But that's only temporary formatting. Try saving it as a CSV, ready to import into something else - then decide you need to edit a little more. When you open that CSV in Excel the leading zeros are all gone

13
0

Re: Ah, Excel sorting

or importing data which has an e in it; e.g. LUN IDs like 12E3 or MAC addresses like 12345678e123. It'll "helpfully" assume they're numbers in scientific notation and import them. You can reset to text, but by that time, it's changed the underlying data as well and you have to recopy/reimport. PITA.

20
0
Silver badge
Facepalm

Re: Ah, Excel sorting

I've had to pull SKUs from excel files recently. Some SKUs are of the form 123456, others 123456.323 or 123456.1 or 123456.3232323 and still others 123456.abc. Of course 123456 is an integer, 123456.323 is floating point and usually turns into something like 123456.3229999999999 and 123456.abc is a string. Even if you do manage to convert the excel SKU into what it looks like on the excel sheet, they don't always match what's in the database, i.e. the supplier will have 654321.654, 654321.653 but the company will only buy one type and so uses just 654321 as the SKU. Gobs of fun.

5
0
Silver badge

Re: Ah, Excel sorting

One of my pet peeves with Excel is when importing a CSV file (at least how it is set at work) is it auto imports using the site settings for dates. Some of our reports have a custom, programmed date format in the CSV file which is forwarded to our customers. Queue autoimport, every so often the programming group gets a ticket to fix the date format in the Excel file. This means we have to verify the CSV file has the correct format and write up a reply saying this one is on Slurp not us and next time try sending some hate mail to bozos in Redmond.

8
1
Silver badge

Re: Ah, Excel sorting

'Most spreadsheets (including Excel) allow you to modify the format of a column (/row/cell) to allow it to keep the leading zero. It's up to the user to pick the appropriate format themselves.'

Yes, but if I type a leading zero surely it should figure out that I want a format that includes one and switch to it. Be a more useful feature than that f*****ng ribbon.

10
0

Re: Ah, Excel sorting

Of course, a lot of this wold be avoided if people didn't insist on using spreadsheets when they should be using databases.

20
0
Silver badge

Re: Ah, Excel sorting

'Of course, a lot of this wold be avoided if people didn't insist on using spreadsheets when they should be using databases.'

I would upvote this more if I could.

The problem is most people can 'use' a spreadsheet, whereas setting up a database appears more challenging. If you could solve that problem I wouldn't be in the situation where my regulatory authority requires me to keep an auditable record of operating risks in a spread sheet.

3
0
Silver badge

Re: Ah, Excel sorting

But a telephone number has spaces in it* so gets inserted into the underlying Excel data as a string.

*if you've entered it correctly

2
0

Re: Ah, Excel sorting

but... why would you edit an intermediate file not the master "source of truth"? and again, Excel CSV import can be told about formatting so it knows what columns it should treat as (say) text. I'm not an Excel fan, but most gripes with it are down to how people use it - watching the magic that experts can whip up is astounding!

1
0

Re: Ah, Excel sorting

If you have performed all your testing and everything is working exactly as designed when reading the data from the spreadsheet, why would you not:

A) Have version numbers appended to the filename every time the data is changed while doing your development work?

B) Save it as read only once you have finalised all changes to it and communicate this to all the relevant people working on the project?

C) Make at least two backup copies in safe locations (including your own personal USB pen drive, perhaps) to guard against just this scenario?

Although option C will almost certainly be seen as a potential security breach, sometimes it's the only way to ensure that you have the exact copy of the file the whole project is dependent on.

1
0
Silver badge

Re: Ah, Excel sorting

... and don't even go there with international format numbers! (+44...)

1
0
Silver badge

Re: Ah, Excel sorting

Because said file might be a list from a supplier and a customer has then decided to add a cost column to it so they can import it into a full fat database (because that how they've always had it) - happy fun times for all. As for how people using it... most aren't experts. Some would be insulting to people learning to use excel to call them amateurs... unfortunately these people generally pay IT bods like myself money to make problems go away without taking the time to figure out what they're doing wrong. And in some instances it's the software thats making arbitrary choices on behalf of the user.. choices that are usually wrong (or at least poorly judged).

1
0
Anonymous Coward

Re: Ah, Excel sorting

"But a telephone number has spaces in it* so gets inserted into the underlying Excel data as a string."

Not necessarily. Every .gov.uk site with the whiff of GDS (that I've come across) insists you enter the phone number without spaces.

The latest one I came across wouldn't allow pasting a phone number in.

All in all a complete and utter bugger for acute arthritis sufferers.

So much for Accessibility guidelines.

0
0
Silver badge

"Gary and his mates were granted their wishes, practiced the job until they could do it in two hours and showed up on Christmas Eve confident of a fast and lucrative getaway."

"...since I’m not in the mood to make some big, dramatic, sweeping statement, I’ll just tell you this: God hates doctors, He truly does. You see all these old people in here? Well, any of ’em would give just about anything to be able to sashay off this planet, but most of ’em are gonna stay and they’re gonna live forever and ever and ever. And your Mr. Milligan, well, it turns out he’s just young enough to die. I mean, think about it: It’s the holidays, there’s a sweet little kid involved. Can’t you just feel it?"

source

7
0
Silver badge

He is my mentor

3
0
Gold badge
Unhappy

Extract, Translate & Load on Xmas Eve.

Genius.*

I smells a bonus at stake or a PHB with zero home life at work.

*Smart move about the no "lessons learned / wrap up " meeting afterwards.

8
0
Silver badge

Re: Extract, Translate & Load on Xmas Eve.

Oh I dont know - I've always found Xmas the one time of the year where my home life collapses into something near hell* and yet the office is free of PHBs and much work can be done.

*The pubs are full of amateur drinkers, you cant get a decent meal out, the telly has nothing worth watching, you realise why people would rather sit in a kayak in a frozen sea and harpoon seals than be at home with the relatives. Christmas is why people want to go live on Mars ffs.

33
0
Silver badge
Facepalm

Re: Extract, Translate & Load on Xmas Eve.

Part of that is just proof that TV is the work of Satan - EVERY TIME around the year one would have some time to sit down and actually watch something, everything even mildly interesting invariably scoots off the screen, and only marathon versions of inane reality shows and 20-year old reruns remain. EVERY. SINGLE. TIME. I mean seriously, what the hell?!? It's not like it's all live, nobody actually needs to be present at the time except the broadcast techs - so why do we absolutely HAVE to watch canned shit on anything that even remotely resembles a holiday or weekend...?

6
0
Pint

Re: Extract, Translate & Load on Xmas Eve.

Click for "amateur drinkers". Here's to having a pint or 12 this Christmas.

0
0
Silver badge

How?

How did Gary and his team not have a backup of a known and working version of the excel sheet? That situation could have been easily avoided with a bit of prudent version management.

5
2
Silver badge

Re: How?

Excel (all of Office really) has no real version control so if someone sorted the data and did a save then after a few minutes there is no way to undo the change. Sometimes there is temp file created which has the changes which is automatically deleted after a few minutes. Unless the sorter saves the two versions the original order will be lost.

8
0

Page:

POST COMMENT House rules

Not a member of The Register? Create a new account here.

  • Enter your comment

  • Add an icon

Anonymous cowards cannot choose their icon

Forums

Biting the hand that feeds IT © 1998–2017