A decades-old lesson on not inserting Excel where it doesn't belong These are interesting times and, in light of recent events, The Register has elected to break from tradition with an out-of-band Who, Me? to show that the more things change, the more they stay the same. Our tale comes from a reader we shall call "Jim", not because of the Regomiser, but because this writer has fond memories of …
Who has been in this business for a while and not seen Excel inserted as a gaffer tape fix on a system that’s been rushed in and not quite finished, and the temporary Excel fix got forgotten and went on a bit longer than it should?
I’ve seen a this often, even in highly regulated systems. And I’ve seen far worse.
I've actually lived through something like that. I was called in to develop a data export from a production database to a system on another platform. I was all ready to go via CSV, but I was specifically told that the export should be in an Excel file.
This was when Office 2010 was already out, so I didn't have much chance of hitting the million row limit.
So I did the job as per spec, created the code that grabbed the data, opened a new Excel sheet and plonked it in, row by row, then saved and closed Excel, grabbed the file and FTP'd it to wherever it had to go.
A few years later I got a call from that same customer. They remembered that I had done the job and now it wasn't working anymore, could I come in and fix the problem ? Sure.
So I went and checked the code. Nothing had changed in my code, so I asked to be able to run a test. With permission I ran the code in debug mode and, lo and behold, when Excel was asked to open a spreadsheet the code halted, there was no spreadsheet to be had.
After explaining the problem, a server admin used his access to check the server in question and reported that Office was no longer installed on the server. Long story short, it turns out that the beancounters were checking lists of Office licenses against users and, since that license didn't have a person associated to it, they cut the license. Apparently it would be useless trying to explain that that license was a business requirement. It was internal procedure that every Office license had to correspond to an actual, breathing human being.
Solution ? Could you please modify the code to use the CSV format ? Sure. That way the recipient will just have to bung it into Excel on his license. Problem solved.
Another day at the coalface.
Or wasn't there an 'office pack' for servers so it could do some office related processing without a full install/license of office? I have vague memories of using it for importing excel docs into a database as an import driver for MsSQL (still one of Ms's better products).
CSV is so useful that I'm more than a little surprised that a decently flexible and configurable CSV module has never surfaced in either the C or Java standard libraries. Does Python have one?
After all, most relational databases can read and write CSV files: many use them for backups too.
We could also do with a CSV editor capable of handling any CSV file output - no NOT a spreadsheet because most of these are too fond of silently reformatting data input as CSV files and/or imposing limits on the number of rows and/or columns in such a file.
I think the problem is that there's no hard-and-fast definition of what a CSV file structure should look like so coding a library to process and output them would be a bit of a hairy-arsed monster and likely to introduce obscure errors upstream when out-of-cheese errors occur.
Writing a CSV i/o module isn't as tough as you might think. Some time ago I wrote one in C and a near clone of it in Java: 1333 and 1193 lines respectively including fairly complete class/module and function/method level comments. Both get reasonably steady use.
Both versions allow the programmer to control the field separator character, newline (CR,LF.CRLF) and quoting [NORMAL (only quote text), MINIMAL (only quote fields containing quotes or field separators), ALWAYS, NEVER] which covers every CSV variant I remember seeing on UNIX/Linux, DOS, Windows and WinCE systems.
No, it's not a horrible thing to write, but if there were a standard CSV i/o module for C, Java et al, then everybody could use the same one and know what the quirks and bugs of that particular implementation were rather than everybody having their own module with their own quirks and bugs.
Yes, your code has quirks and bugs. All code has quirks and bugs. It would be better if everybody had the same quirky and buggy code.
Most important options for import-csv and export-csv: -Delimiter ";" -Encoding UTF8
And on export-csv add -NoTypeInformation if needed.
No problems here that way, the most stable options. Even excel can handle it. Also supports multiple values within a cell (aka multiline cell) which you can select - if you know how.
Yes, Python has one and it even supports the Excel dialect (actually CSV written by Excel are usually fine). But because CSVs are repeatedly PITAs, most people tend to use .read_csv() from the Pandas library, which also lets them do things like column select, etc.
One of the best (and most underrated) tools for hacking CSV files is awk. awk, coupled with sed, can handle anything in csv very efficiently and without arbitrary limits on the number of lines. Versions of awk have been ported to Windows (and probably DOS) since time immemorial. MKS have supported a commercial version for Windows for decades / DOS for decades. And don't forget perl, versions of which have also been available on Windows for...a very long time.
"CSV is so useful that I'm more than a little surprised that a decently flexible and configurable CSV module has never surfaced in either the C or Java standard libraries."
TStringlist, the Swiss Army Knife text handling class in Object Pascal (Delphi and Lazarus) has options for treating data as CSV.
> After all, most relational databases can read and write CSV files: many use them for backups too.
Some even store their data in text files, saving the conversion into something proprietary.
https://www.drdobbs.com/rdb-a-unix-command-line-database/199101326
A CSV library for C, Java or Python? Why not use Perl instead, then you can choose from Text::CSV_XS, Text::CSV_PP or Parse::CSV. Choice is good, right?
"We could also do with a CSV editor capable of handling any CSV file output - no NOT a spreadsheet because most of these are too fond of silently reformatting data input as CSV files and/or imposing limits on the number of rows and/or columns in such a file."
Check out EmEditor - not only can it present CSV files as a spreadsheet-like grid but it also handles unlimited file sizes
>>beancounters...licenses
This has happened to me on several occasions, numerous license types MS Office but Dynamics, tools, etc...
At least I (usually) don't have to dig around too much before I find the point of failure.
As for Excel, a LOT of people REALLY love it. Sometimes I think that is the only application people really know how to use. I really do like it as a personal tool but anything beyond that.... not so much.
One of my pet peeves with Excel is surprises from copy/paste. I've been bitten more than once when Excel "guessed" on a data type and changed a few values along the way.
Excel can be used in very simple ways. And then more complex functionality can be mastered as and when needed. It's initial state it's just a ready made table, which immediately places the user one step ahead of creating a WORD table. It then guides users to use simple formulae, or they can readily be looked up, to, say add a column of numbers, or count the size of a list, when they want to seek an easier way to do the job. And it can be used to create a simple flat database by using the columns as fields, (something that used to be available in simple database software that seems to be pretty much extinct now) but is bloody difficult and rather excessive to implement in ACCESS. In other words, Excel has a nice smooth, gentle learning curve for ordinary office workers with a job to do ( and is understandable by managers).
>> implement in ACCESS
Now that you mention it, I also like/use Access.
Again, as a personal tool, not so much when shared with other users. ( And I prefer 2003 before MS started deprecating some of nicer features, added the ribbon, etc... ).
Where I like Excel for handling numbers & math for accounting and Access for quick and dirty data queries, manipulation.
These days when users need a database they can share in the workplace, I've been steering them towards MS SQL Express and in some cases even VS Community. So far..so good.
>> implement in ACCESS
Now that you mention it, I also like/use Access.
I hate Access. It should be burned at the stake. Perhaps it has improved over the years, but we had people using to to make front end to a real database (let's say Sybase as an example). Sure it works. For one person. The idiotic thing locked all tables.
disclaimer: This was over a decade ago so perhaps it has improved, but given Microsoft's QA, I have my doubts.
Snap, I've seen Excel used so often simply because it's quick and dirty and can produce something useable, if not perfect, very quickly. I'm sure that's what happened here.
Just goes to show how simple things like selecting a file type can have serious repercussions.
Despite this recent snafu, I wonder if we're still better off than if we'd produced a fully tested system, but one that would have taken twice as long before it did anything. It's the old system production triangle: time, cost, quality, which one do you want?
"(60, asthmatic, so high risk, I'm not paranoid, honestly.)"
You're so old you probably remember when David Attenborough documentaries were uplifting rather than clinically depressing.
I fully agree with your comment. Apparently all my local Edinburgh pubs are now closed, which was a bit of a shock as nobody told me they'd been opened.
@ Danny 2 (Hey, we've 'spoken' before, I hope you are well)
"You're so old you probably remember when David Attenborough documentaries were uplifting rather than clinically depressing."
Indeed I do. I have lots of 'Life' boxed sets, with the great St. David A gradually ageing from fit young adventurer to somewhat filled out National Treasure (as BBC 2 Controller his other claim to fame is commissioning 'Monty Python's Flying Circus').
BUT (deep breath, get ready for the multitude of down votes). after decades of his BBC Natural History Unit showing us a pristine world, untroubled by humanity or our polluting ways, with wild animals and Nature beautifully shown, I feel that he has come a trifle late to the 'lets save the world from human interference' party.
OK, crash helmet on, I've stocked up on all the necessities (gin, brandy, rum, chocolate, and a new packet of M&S Aloe Vera toilet paper), front door locked, phone off the hook, here we go.
(Clicks "Submit" button and waits for the veritable hurricane of dissent.)
Hiya @Eclectic Man,
A pal just described Attenborough as a Pollyanna (overly optimistic). I'm not sure, I think it is just he was trying to inspire our love of animals and now he's lost faith in us.
I hope you stay well too, it's always a pleasure reading you here.
I was playing five a side football at a new job, and quickly ended up bent double, purple faced and wheezing. Two guys came over to ask if I was okay, and I said, "Don't worry, I'm an asthmatic and a smoker."
"Aye, so are we."
You are in an aircraft flying across the sea. Air traffic control can be available all the time, but not reliable, or available some of the time, but highly accurate. Which would you prefer? Please bear in mind that the majority of cases of commercial aircraft loss are due to CFIT (Controlled Flight Into Terrain).
A wrong answer now, based on incomplete or false data will result in an incorrect assessment of the situation and probably actions which do little or nothing to resolve the situation, and may even exacerbate it. (Note that the ONS does not provide daily counts of deaths due to Coronavirus as a lot of them are recorded several days after the event.)
I agree, however the sad thing is that this is a common story:
In a system I manage, which is nothing more than a SQL database with a web interface, we struggled to get a "report" (PDF, in their terminology) of some very critical HR data. Literally the one document we must supply to government, regularly, run regularly, keep paper copies of in house, require in an emergency, etc. etc. etc.
The PDFs that come out the system are reliant on all kinds of nonsense on the backend - a service that runs Office 2010(!) products to then output to a fixed default "virtual printer" that creates a PDF of the Office output.
Anyway, after months of faffing, we were told that the report that comes out isn't fit for purpose, by the same government inspectors that required it. So we contacted the company. Their response, after much faffing, was "well our guy used to work for that same organisation and THEY think it's okay". Which, I'm sorry, is not an answer. Not when we are literally told outright that it's not suitable.
Cue many months of back-and-forth and no progress. In the end, they said it wasn't possible to get the information we wanted into an acceptable format except the original they supplied. We argued. They said if we sent them a template, they'd make it.
In the end, I did just that. Not just "here's a mock-up". I ODBC'd their database tables, interpreted their junky table layout and weird column names and built the SQL queries to get the base information from the same locations as their original report. Then I coded up an Excel sheet with a button to pull in that data, process it to some form of sanity (what IDIOT puts full postal addresses in a single field with no verification, so I had everything from just an address to just a postcode, to "no address" in 20 different hand-written formats, addresses that didn't exist, foreign address, some with town name, some without, etc. in a single free-form text box) present that data in a sensible layout and fit it all on a page with a single line for each person and a bunch of checkmarks in columns for each thing we needed to check about that person.
I supplied said printout, and said spreadsheet to them. 18 months later. Still nothing. We have a literally "not fit for purpose" output from the program, our own bodge in Excel that *is* fit for purpose and based solely off things they could do in their reports (and far easier than I ever could have!), and got no further with them actually making that report the way we LEGALLY need it to be.
So obviously we can't ditch the Excel. We can't make them put it in the software (even just for us, and they literally have custom reports for anything you like, so it wouldn't take them more than an hour and wouldn't affect a single other customer). And we can't pay someone to code it up properly as they'd have to integrate the systems and it would cost more than the salaries of all those involved anyway, and need to be updated to fit their software changes regularly.
Entire literal organisations are hinging their legal capability to operate under government remit on an Excel that I knocked up in a few hours. And I will not be around forever.
Similar story on just about every system we buy - access control programs that can't produce a list of who's on site or query in a decent format, visitor management programs that can't produce a list of visitors in a printed format, and so on, etc.
OK, so I had not actually read Wednesday's Financial Times letters page (page 22) when I posted that comment. In it a certain Dan Klein, Data Director of Zuhlke Engineering basically says exactly the same thing, but with this example:
"In 2017 the energy regulator Ofgem procured a data platform to resolve their issue with managing an £80bn market on a single Excel spreadsheet (around250 sheets with 1m lines per sheet), that was being emailed around the 900 staff with limited change control."
He goes on to ask when the NAO will audit the over use and reliance of government departments on Excel, and predicts future disasters.
I do hope El Reg finds out about this ginormous Excel spreadsheet. Maybe a competition for the largest spreadsheet used by an actual organisation for an actual essential purpose. Not sure what the prize would be, but maybe a course in data base management would fit the bill?
> Their response, after much faffing, was "well our guy used to work for that same organisation and THEY think it's okay".
Quality British Workmanship, Innit
There's a reason "Made in the UK" is a warning klaxon across the commonwealth (even louder if it has a union jack attached) even 47 years after the trading agreements were torn up which essentially forced companies in Commonwealth countries to buy British items
(The reaction is so visceral that General Motors found they couldn't GIVE away Vauxhalls in Australia/New Zealand in the late 1990s - having to phsysically change the badging on a shipment of ~1000 Vectras to Holden or Opel)
Oh, you'd just love my "Brexit" Washing Machine in that case! Until I did some serious research I'd never even heard of Ebac but they have a decent reputation and seem to be the only company aside from Miele that make their machines fully repairable (even the likes of Bosch uses sealed drums now so that a simple £20 bearing failure turns into a £300 drum failure)....
The down side is that the owner of Ebac is one of those typecast bitter, white northerners who seems desperate for a return to the "good ol'days" and when I bought my machine (which turned up broken - smashed in transit), it came bundled with a glossy brochure, adorned with Union flags (including a flags photoshopped to show a large flat following from the door of one of these great machines) and including a letter plus other propaganda raving about how we're apparently moving forward as one nation and how I'll securing the futures of my grandchildren buying s brief
If it wasn't for the fact that it's actually a really good machine which has a hot water inlet and a whopping 9kg capacity I'd have possibly sent it back in disgust.
Nearly one year on it's serving our family well so I can't complain too much.
I worked with a Network guy in recent years who had along with his wife produced 11 children (Icon).
The washing machine & dryer ran through its expected life fairly quickly, service calls increased (Barely into it's 5 year warranty). In the end the manufacturer withdrew the unit, refunded the cost of the machines & warranty, then as a capper banned them from ever buying one of their machines again.
They ended up with the handsome refund buying more commercial grade units that were fit for purpose.
(The reaction is so visceral that General Motors found they couldn't GIVE away Vauxhalls in Australia/New Zealand in the late 1990s - having to phsysically change the badging on a shipment of ~1000 Vectras to Holden or Opel)
Not necessarily all bad. Holden badge seems to (fairly often) come with a V8 to replace whatever puny engine was in the original).
I find it hard to believe the new db had only one address field. More likely, the old data structure was so full of incongruous elements that it was deemed the practical solution was to just add all the old address elements together and bung it in one field.
I have worked with poorly designed db, that have failed to anticipate such things as foreign names and addresses, resulting in reports requiring a lot of human intervention to make presentable.
I, like you, eventually crafted a spreadsheet to automate the process as much as possible, which allowed me to edit the fields with one hand while typing the "I keep telling you, but you won't listen" email to the boss, with the other.
A Royal Mail written address has up to 12 possible distinct lines - some of them logically don't appear with others e.g. 'PO Box Number". Actual Royal Mail data has about 50 fields, one or more of which contain multiple values at once, e.g. "Property name" and "Number of property that is not a single integer e.g. 1A, or 3 & 5". However, around 200 other countries have their own address composition rules.
It's fairly unlikely that you need to do anything with an address besides send mail to a multi line printed address, so just store that - maybe with a nationally acceptable field separator like, oh, a comma.
Well.... maybe "person name" + "job title" + "business name at property" + "physical address of property", except that these blur into each other as well.
Anyway, yes you may also need an "Address Is Shit" column Y/N.
I have always considered that the user who provides the address and fill the form knows what the address should look like in their administrative region, so address field should be as open as possible, with maybe an exception for the country as it is used to calculate the postage fee.
If addresses will be used for non mail stuff, like sending a physical person to a location, we are dealing with a more restricted area (maybe country size) and then the address can be formatted with fields that corresponds to the country>
I only once received a telegram since I live in Thailand, I was very interested to find that the address had been summarized for administrative purpose and consisted of the lot number where the house reside (with a sub-number is the lot has been divided further), the number of the district subdivision and the district post code. 3 numbers for an address is very efficient IMHO. Since, I am very careful when writing the numbers as I know it is all that matters.
Barmy exceptions indeed. Sadly as is usually the case with these things it's just never as simple as it should be.
A noteworthy and common exception is flats... If you're lucky the whole block has its own postcode but in many cases the flat is in a block that has its own number (or name).
So If numbers 1,3,5 are houses and 7 is a whole block of flats (FYI non-Brits: our roads often have odd numbered houses on one side and even numbers on the other), then number 7 in fictional postcode AA12 3BC is only going to reach the whole block. So now you nee something like "Flat 1, 7 Some Road, AA12 3BC"
It starts to get even more convoluted with business address, especially as the official databases from Experian and Royal Mail have inconsistent use of data fields and make the job even harder.
"Anyway, after months of faffing, we were told that the report that comes out isn't fit for purpose [...]"
An important large customer had been promised - by management - a monthly SLA report of their comms links' breaks to be delivered on the 1st of every month.
The official application from the network suppliers proved to be riddled with significant errors for many real-world link failures - with no chance of fixes in the near future.
We knocked up an Excel VBA spreadsheet to collect and process the raw statistics for each link from the application's web pages. Our VBA made sense of most of the previously misinterpreted cases - and flagged those that needed some human tooth-sucking decisions.
Muggins would be in the office at 06:30 on the 1st of a month to process the official network raw statistics that had been consolidated overnight. By 08:30 it was usually looking good - and a reward was a fry-up breakfast in the canteen*** while the processing slowly trundled to a conclusion. Then there was the manual tidying of the awkward cases where a line break had been like a ping-pong ball.
After doing this for a couple of years it was discovered that the customer merely filed the reports as a clerical "tick in the box".
***Unfortunately on weekends and national holidays there was no canteen service.
Not IT: most of the routine information I am asked for is not actually used for anything. Now I just wait until someone shouts loud, then provide it. Has cut my paperwork down significantly.
Exceptions: safeguarding, special access arrangements, sudden unexplained changes in student performance. Management by exception basically.
Icon: ranty old man
To be more accurate, it was an XLS document that has that 65,000odd limit. XLSX has a far higher limit. Wouldn't have helped the OP, mind (XLSX came out in 2007) but would have helped the current government who made the exact same mistake 13 years after a file format that replaced the aging XLS format came out.
It's the government's fault.
If I purchase something from a shop and it's faulty, I return it to the shop. "You'll need to contact the manufacturer" will be met with a swift "I bought it from you. My contract is with you."
I get a chance to vote for the government. I pay tax to the government. If they spend my tax money hiring incompetent people it's still the government's fault.
I know we're getting perilously close the "unknown unknowns", but if you're that far out on the Dunning-Kruger inability-to-recognise-your-own-limits scale, you should be (at best) Assistant Manager of a motorway services franchise, not leading a government's response to a global pandemic.
"if you're that far out on the Dunning-Kruger inability-to-recognise-your-own-limits scale, you should be (at best) Assistant Manager of a motorway services franchise"
and yet the person at the heart of this has presided over a long series of fusterclucks including losing personal data of hundreds of thousands of people at TalkTalk
It's presence near ANYTHING should be a warning klaxon that whatever the project was, it's now a "jobs for the boys" boondoggle
"You hire people expecting them to be able to do the job you hired them for."
Presumably, though this would (to pick an example at random) mean that you might expect a ferry hire company to maybe have some ferries? And if you 'expect them to be able to do the job' notwithstanding what might to any other observer look like a clear indication of incompetence, then perhaps there is more than one weak link in the chain here?
You hire people based on a diligent attempt to establish their abilities and responsibility. Only if you made that effort in good faith can you start deflecting blame. It's not enough to find a person, hire them without checking, and blame them for anything that goes wrong. Similarly, if you hire someone to do a task, it becomes your responsibility to get enough information to determine whether they're doing what they were hired to do. Sometimes you may hire someone else to help you manage that task, but this just increases the size of the tree which you still have to monitor. If you didn't do enough to validate that the people you hired were capable of the job and were actually doing it, you still take the blame for things when it turns out they weren't.
@ double layer: "If you didn't do enough to validate that the people you hired were capable of the job and were actually doing it, you still take the blame for things when it turns out they weren't."
Unfortunately, and I have worked for people like this, some managers are unable to distinguish between 'delegation of authority' and 'abrogation of responsibility'. How many times have we heard a senior manager or director say 'but my subordinates assured me it was ok / did not inform me of this problem' as a defence (and get away with it)? e=recent examples include the UKs' tabloid phone hacking scandal.
"If you are in a Conservative Government and you hire your mate you know its going to be a disaster."
No, WE know it.
You are off in sunlit la-la uplands prancing with lesser spotted mauve unicorns
I don't know what they put in the water at Westminster but it seems to induce some pretty powerful hallucinations of adequacy
"I don't know what they put in the water at Westminster but it seems to induce some pretty powerful hallucinations of adequacy"
The Peter Principle. In any walk of life those who are self-deluding about their (in)abilities are the ones who get promoted or elected.
How many IT companies have done an HR survey of technical skill-sets based on self-assessments? Those that shine are the ones with very limited experience who believe they know it all - or are chancers gaming the system for career advancement.
There is a bigger issue here, too.
I discovered the hard way, early on in my career, that modesty, or even plain honesty can be a disadvantage. People who crow and claim brilliance get the plaudits - and promotions. People who do well, or even brilliant, are the also rans. I saw mediocrities rise far and fast above staff who were doing a wonderful job ( and yes, I'd include myself in at least part of that).
Partly because managers (who probably got there that way themselves) seem happy to take the boasts at face value. But maybe sometimes because the PHBs assume that everyone is over-promoting themselves so downgrade everyone equally.
You are not wrong. In a previous incarnation I was responsible for assessing the levels of ability claimed by various members of my company, including the Directors. When asked about their contributions to international standards etc. two of the company (one a Director) replied independently with exactly the same phrase: "I wrote it". Note this was the same international standard we were talking about. Noether of them had more than a passing acquaintance with it, and they had probably not done more than submit a few comments. Neither was the main editor, or even a representative on the national standards panel. As I thought at the time, I would not trust either of these people to buy my shoelaces, let alone tie them.
Well, the normal process is that you advertise your requirements and people contact you. You then interview them, study their track record, evaluate their proposals and then appoint the best one.
What you don't do is look up the list of donors to your party and chose one at random to do the job.
My did still bitches about SuperCalc 4 and how he loved SC3 and still runs his business on SC spreadsheets printed one a wide tractor dot matrix printer. I think he's finally moved to ink/laster jets for print outs, but while at his office yesterday I found a ZIP drive hooked upto something and he professed surprise at it, having not a clue what it was. Sigh...
I'm going to have to spend some serious time excavating stuff in there for him some day.
On the other hand, he was *very* impressed I could still run SuperCalc3 on linux using WINE. At least I think I can... have to poke around and see if I can find the damn thing. And heh, I did. dosbox rules.
Best Excel story I've personally been involved with. About ten years ago, my manager at that time sent out the annual appraisal spreadsheet so we could fill in our self assessment for him to rubbish. He'd forgotten to remove the tabs that i) had all the team's annual salaries and ii) the company's true feelings for each and every individual*.
* Good God that man was a colossal arse.
When I was our company representative on the "local traffic" tea and bikkies forum I asked HR for a list of the postcodes of all our staff so I could work out the range of journey lengths. No names, no addresses, just the postcodes. What I was given was an xls of the staff records, names, spouse/partner, addresses, number of dependents, etc. Two columns were "blanked out", consisting of just hashmarks, one was salary and I forget the other. Of course when the column is only 4 characters wide and a number greater than 4 digits is in the cell hashes is all you get. Until you format the column to fit the data.
When there was a serious round of redundancies, me included, a few months later I was sorely tempted ...
...that would have saved me the trouble of silently lifting a similar spreadsheet from the c$ share on his machine using a local admin account (they all had the same password, which meant it could be extracted from my own machine using NTLM rainbow tables). Still, even in the relative innocence of the XP days, you'd think someone managing a software team might be more security conscious. Turned out the salaries of my peers were actually pretty fair. Nonetheless, I maintain that the only people that secret salaries benefit are the company's bean counters. I work for myself now...
" Nonetheless, I maintain that the only people that secret salaries benefit are the company's bean counters. I work for myself now..."
This is exactly who it benefits and why attempts to keep them secret ar illegal in most parts of the world
As soon as company policies are there to prevent people comparing salaries you know they have something to hide (usually either gender discriminatory policies or "owner family friendly" renumberation policies)
I had a very similar experience, instead of salaries, it included the entire company's 35-strong consultancy team's sick leave and the reasons fort being off. Adding insult to injury, we were an IT security company and did a client course on how to comply with the Data Protection Act. Did I really need to know that an idiot not called Edgar was off sick with stress for 4 weeks? I must admit though, when I pointed this out to 'the management' it was fixed that day.
A prestigious university in London, Student Union is on new version of Excel, College Administration is still on old. Financial data is forwarded on spreadsheets (because of incompatible systems - Pegasus Opera vs something written in PowerHouse). Of course, at some point, the 2^16 row limit is breached. The old version doesn't complain but merely drops the excess. Fortunately the receiving bean counter runs their own tests on the data and realises there is a problem - but not what it is. Took a while for the underlying issue to be recognised (and then for the admin departments to move forward).
And it was a bit flakey due to networking.
But I could open the files with a Clipper application no problem.
Clipper had the magic of replaceble database drivers, one was a client server engine. Went years with no data corruption.
We did once reindex a customers Pegasus Opera as otherwise it was use the backup, we used the client server engine to do it.
Thanks. Thanks for bringing that memory of Pegasus on a Foxpro DB (1gb DB limit)... All those years of therapy. WASTED!
We used to have strip out table rows in MS Access, then re-run the DB from where it fell over from being full.
Should have been replaced 5 years before I joined that company and still needed replacing 4 years later when I left.
Indeed, I would be rather disappointed if anyone I worked with failed to spot that. Things do get a bit hazy with the larger powers of 2 though. Not so long ago, a colleague pointed out something interesting about the strange figure of 16777216 we were getting. "That's 2 to the 24." We all looked at him with eyebrows raised. "What, doesn't everyone know that?"
I'm guessing they didn't want anyone to say they'd overstated what the screens or image formats could do. Then again, it didn't seem to hurt the storage industry much when they used a definition for kilobyte, megabyte, and gigabyte which was not the same as the definition memory manufacturers came up with, or the portable tech industry when they dramatically overstate battery life.
> At some point the pain becomes so great that the customer can be persuaded that the solution is "(more) Oracle".
The problem is that companies frequently "roll their own" instead of buying off the shellf software to do whatever is needed (with small amounts of customisation), not realiing that this is a recursive nightmare where you end up disappearing up your own fundament
Avoiding spending $2000 on a decent package and $1000 on customisation may result in $150,000 in endless developer tweaking over the next decade "because it's cheaper that way" (Personal experience fighting with board of directors in my company. Of the director pushing the increasingly complex solution happened to be an "accounting expert" but, running up a parallel system in quickbooks showed that his fantastic system had slightly less accuracy than a poo-flinging baboon)
With that kind of history in pain and suffering Oracle can frequently look easier/cheaper because MBAs expect a working solution will always cost more than one which doesn't
With that kind of history in pain and suffering Oracle can frequently look easier/cheaper because MBAs expect a working solution will always cost more than one which doesn't
Companies like Oracle rely on this kind of approach knowing that manager's will seek safety in buying from a big vendor. "No one ever got sacked for buying IBM" may no longer always true but it does shift the game: get a committee to approve the decision and the relevant manager is off the hook. Not the case for someone running and inhouse project, which may or may not be merited. If the project is big enough, it can be taken out of standard expenditure and given its own budget as part of "extraordinary items". More than a few managers have fallen up once their failures crossed the £ 100 million barrier. After all, they must be good if they can manage projects of that size. Even if "managing" means blustering, blaming and bullying – The 3B™ method of management!
Just make sure all of your rows are selected for your calculations, nothing like coming up short on an order... My manual count of switches needed for project X was 28 switches, my boss got 26 because his formula didn't include the last row, which had been added later! I really have to ask why a spreadsheet was needed to count to less than 30...
I was taught by an accountant (my ex-wife), that if you add up anything in Excel, make sure you put in another addition, using a different method to check that the first is correct - only if the two complement each other can you have surety over the answer and even then you would do a "reasonable check" in your brain to make sure it had the right number of digits.
This all came home to roost (not with Excel) when a colleague of mine at a large bank got fired because he failed to notice that when he got a message from the backup software saying "Backup complete", the amount of data it had actually backed up was miniscule and when the corporate email server took a dive and a corruption, there were no valid backups to recover from. All due to lack of admin permissions on the target server.
I have long had a bee in my bonnet as a scientist about those who use Excel as a database. I’m something of a FileMaker wizz having built several relational databases with it for such mission critical stuff as reporting our animal usage to the Home Office Animal Inspectorate (6 months gaol and/or £5k fine for getting it wrong) or handling patient samples for a big population based pharmacogenetic study in Type II diabetes. It read barcodes and printed them on stickers.
That sort of thing. One careless sort without proper selection of all and all the data relations will get scrambled in Excel. Cell protection is not absolute. I get the collywobbles whenever I have to use an Excel ‘database’, my protests having fallen on deaf ears. Don’t say I didn’t warn you.
I built my first database back in the ‘80s using a FileMaker type precursor called Reflex for the references from my Honours thesis. I upgraded to ProCite which was both an excellent and proper databases and would reference your paper or thesis. Not like the crap which replaced it which is not a database. I could cry sometimes. I loved ProCite, it did nested queries letting you home in on exactly the reference you wanted and it reminded you you were working on found records.
Working in France back in the late 80s I looked up something for the system we were using in the manual, which was an English transalation of the original French manuals. I was delighted to find one command listed with a note saying "in the hands of an animal this command is extremely dangerous"!
We had a new call management system installed and running in early October around 2008. By the 13th of of the month it was falling over consistently. This system was reliant on Microsoft VBA which had accepted the 1st of the October as the 10th of January but couldn’t convert the 13th of of October to the 10th of the 13th month. 2 guys flew in from the states and were struggling to make sense of it, it was working fine on their test server (based in the US), but fell over locally. I happened to be sat in the room they were working in and when I mentioned date formats they poo pooed the idea, so I kept schtum. Two days later they finally got their heads around date formats and how differently configured systems handle date conversions "seamlessly" (ie, completely invisible to the user).
More fun: specifying a date as '2020-06-23' and getting told it was impossible to convert the string to a date, exceeding the allowed range for the values. What the _____ TSQL?
Who would interprete it like this? Who even uses as date format like yyyy-dd-mm, for that was the problem?
I now start everything with SET DATEFORMAT = ymd
This post has been deleted by its author
"Who even uses as date format like yyyy-dd-mm, for that was the problem?"
Screwtape, one presumes. Having noticed that yyyy-mm-dd is correctly parsed by everyone on the planet (even if it isn't *their* preferred format in most cases), he decides that slipping the novel yyyy-dd-mm into human affairs might be worth Wormwood's time.
Run this in powershell:
foreach ($culture in $([System.Globalization.CultureInfo]::GetCultures([System.Globalization.CultureTypes]::AllCultures))) {
[System.Threading.Thread]::CurrentThread.CurrentUICulture = $culture
[System.Threading.Thread]::CurrentThread.CurrentCulture = $culture
Write-Host "$culture.Name " -NoNewline # $culture.DateTimeFormat
Get-Date -Format g
}
My preference: en-SE
(Note: I am from Germany, not Sweden, I just love iso8601 date format)
Transact-SQL apparently has a bug with converting string NNNN-NN-NN to "datetime" and which national convention is applied. Uncoincidentally? introduced at the same time as datetime2 type, which does not have the bug. You can explicitly CONVERT specifying the date convention by number (British = 103, personally I like 126), or use datetime2 if you don't understand the problem (which now you do), or if you're in a hurry 'NNNNNNNN' which consistently auto converts as YYYY MM DD, except in leap years after 2021 for all I know, let's find out together ;-)
"[...] how differently configured systems handle date conversions "seamlessly" [...]"
A while ago I had VBA processing event details screen-scraped from choirs' web pages. A revelation about how dates and times could be written.
Some pages stand out in my memory:
1) mixed formats depending on who made the entry.
2) a continuous list of events that were added sequentially as the bookings were made. No chronological order - and no year indication.
3) a mixed format of dates - with one variant being "dd mm yyyy - where "mm" was Roman numerals eg IV for April.
4) elite university colleges would often refer to a weekday name in the "nth" week of an academic term eg Monday 2nd week of Michaelmas Term.
Worked for a place that leased equipment. Big companies, big checks every month.
The AR department had a woman who's job it was to keep up on late customers, and she used a reasonably clever Excel spreadsheet to do it. With probably 2000 active leases it wasn't unmanageable, the spreadsheet floating right around 25,000 rows by the end of any given fiscal year.
The problem was a certain retail corporation. They didn't want one big lease, no. They wanted one lease per location, per equipment type, so ~1,200 small leases with ~1,200 small checks.
Six months later she called IT freaking out how Excel was broken and wouldn't let her add data! Seems that Retail Corporation sent lots of late payments, which required extra rows to document, and she'd hit the magic number of 65,536.
Retail Corporation got their own spreadsheet, she squeaked out the year at 62,000 rows, and the whole thing was replaced by a VB application with a real database not long after.
Ecxel would only use the first 63 (or maybe 255?) rows for statistical functions (at least for the standard deviation), way-back-when wooden shoes were still made of stone...
Excel is useful for balancing your accounts - and per the example above: not even that.
Excel isn't a sensible choice for actually balancing accounts. Use book keeping software.
Excel is great for keeping unbalanced - single entry - accounts that are fairly simple. I have a spreadsheet - or rather a workbook - of invoices, which is a good use - I can filter by paid or unpaid, dates, costs, clients etc very easily, and there aren't enough to cause any problems.
But then, I know how to use Excel fairly well, and use more than one sheet per workbook (!) so I can put details on other sheets and use the main sheet as a dashboard/aggregator.
Honestly, 99% of the problems moaned about here are either things which have a built in solution the complainer isn't using, or would be solved with multiple worksheets.
The problem is, the likes of Sage is fine for recording the customer balances, but if you want to calculate the split between capital repayment and interest each month, and calculate any late-payment interest, it won't do that. That's why she would have used Excel.
https://statistics.laerd.com/statistical-guides/measures-of-spread-standard-deviation.php
Excel was right(ish), and you were wrong. The calculations are different for sample and whole population SD. The behaviour is justifiable, though I have no idea if the explanation, notification, error messages etc were up to scratch. Probably not, back in the day.
I have a statistician I ask when I'm having trouble with an xl statistics function. Usually, it's my ignorance rather than an odd function.
Having to be careful about *which* standard deviation function to use was always irritating to me. Why, oh why, should the default be "sample"??? In our case, we were doing repeatability tests for balances - standard deviation of 10 results, and that's the full "population".
I doubt there's a single business that doesn't have somewhere an Excel worksheet which is essential to the whole company.
(Of course, usually, the only time the IT department finds out about it is when it's failed, but trust me, it's there, like an unexploded bomb.)
This is because the average person can use Excel, so when they come across a problem that you or I might consider a good case for a database, they figure they can probably do it in Excel. (Accounts departments are very prone to this in my experience). And of course, over the years that spreadsheet becomes more essential, and filled with well intentioned fixes and tweaks, and less and less well understood.
At my last job, the Finance Director used a spreadsheet to keep track of the cash flows for the entire company (worse still, it relied on Access importing data from a SQL database). He was the only person that understood what it was doing, and he used it to make all the big decisions about the companies finances. I made sure it was well backed up, but I lived in dread of that spreadsheet (or possibly the FD) dropping dead, and the entire company going down the pan with it.
What's that? You think your organisation definitely doesn't have something like that? Too late, that just means they're hiding it from you, possibly on a personal laptop.
We have a number of people at [RedactedCo] whose job is to audit the various revenue streams we have. Their spreadsheets were massive, requiring us to add memory to the workstations they ran on, because they would just trash the drive to death.
at least one of these spreadsheets pulls data (using ODBC) from multiple, separate SQL databases. It's a beast*.
(and by beast, meaning 'eldritch monstrosity that really should have been a stand alone app')
We do a lot of data collection and get regular CSV files from various Telcos listing all the calls we've made.
Sometimes the files are pre-processed in Excel which like all Microsoft programs goes out of its way to be helpful by stripping all those pesky leading zeros and converting long international numbers into scientific notation.
Cue sarcastic emails to supplier.
The moral of the story is: Never use fucking Excel as an intermediate processing step unless you really really know what you are doing.
This has been a public service announcement.
They do format them correctly - no spaces. Spaces are added for display purposes only, internally it's always a continuous string of numerals.
I'm getting the raw files from the telcos, they're normally OK unless someone opens one with Excel and (accidentally?) saves it over the original.
And while we're at it
For London numbers, NOT just NOT 0208 or 0207 anything. It's (020 outside London or on a mobile)followed by 8xxx xxxx etc.within London
BT created confusion with this years and years ago during the transition to this numbering system because there was a bodge overlap when you either used (within London) the old number system, 366 1234 for example, or the new used/written as 0208 366 1234. Which ended after the transition. A long long long long time ago.
But you still see even new signs and headings with this stupid illogical 0207* xxx xxxx and so on.
Rant over.
*Explanation for non-Londoners. 020 is the dialling code for London- so you only need it outside the geographic area or on a mobile. Within London you dial the exchange code which starts with either 8 (mostly outer London), 7 (inner London) or sometimes 3 (just to make things more confusing) but that 4 digit area code is a single unit and you can't separate the 7 from the following digits. It has to be (020 o/s London) 7123 1234.
The correct display formatting certainly uses spaces, sometimes even hyphens, the files the telcos use internally never contain spaces and definitely no hyphens, I should think they know their own standards.
Consumer files will be formatted for ease of reading by a consumer, within the comms industry I've never seen numbers with spaces, the files are formatted for ease of import. The files I get are never intended to be looked at by a person, no point - they can contain over a million lines, they are meant to be read into a proper database and worked on from there.
Usually with Excel just opening it is enough for the conversion to happen. The file is then marked as "modified" and autosave overwrites the original with the modified sci-no version. The file gets closed with no warnings about being modified, and the user is literally none the wiser as to what has happened.
I remember having to record the time we spent on a project. Time spent ( to the nearest 10 minutes) on design, code, defects, project meetings, other meetings, filling the time sheets etc. The theory was at the end of the project the bean counter could do analysis,count the beans, and see if it was worth doing design reviews.
At the end of the project this analysis was done and it made no sense. We went back to the data and found that each week, someone had collected the data for the whole development team into a spread sheet, then scaled it so every one's time was 37 hours, kept this scaled data, and threw the rest away. If you had spent 60 hours one week debugging problems... that got cut down to 37 hours. If you were part time your hours were scaled up!
Next release this was thrown away and we got back a lot of time.
Did you remember to record how much time you spent working out and filling in data collection about how much time you'd spent on the various items?
Anyway beancounters will always assume everyone only works the standard hours, because they almost always do so themselves. Reality and other peoples job requirements are just a rounding error.
... a group of open-source enthusiasts tried to approach the government with a proposal. The country that remain unnamed to protect the guilty. Suffice it to say that the country was quite a bit smaller than the UK and quite a bit larger than San Marino. The proposal was - drumroll, please! - to break Microsoft's stranglehold on the government's computing in favor of FOSS alternatives. I wasn't directly involved, but watched from the sidelines...
Unexpectedly, the government listened to the proposal and nodded their heads. Or at least the people who got to listen to the proposal nodded their heads. Or at least some of them did. They said they wouldn't mind to switch to FOSS software, in general. However, then they said it was quite unrealistic, because the state budget was managed as a HUMONGOUS collection of Excel spreadsheets! Just imagine: the whole bloody state budget!! In bloody EXCEL!!! That alone killed the whole idea.
Come to think of it, it's 2020 and the state budget is probably still managed in Excel...
... to certainly lose sleep over.
I cursed when trying to read a daily summary only to discover that it was in xlsx format. Google Docs managed to open it and I mentally admonished the government for using Excel to present data, but assumed that it was an export from a deeper and more complicated dataset.
I probably shouldn't have been surprised to learn that the entire thing was handled in an Excel spreadsheet.
The interesting thing about excel is that many managers see it as a tool they can pick up and run with when the job actually needs structured data with views and aplications. How much does that actually cost in efficiency when you scale it up across an organisation like the NHS who deferred a database management system for patient management for donkeys years.
In the BBC article referred to - https://www.bbc.co.uk/news/technology-54423988 - "about 65,000 rows" limited the number of cases to "about 1,400", "since each test result created several rows of data". How many rows is "several"? Do the division yourself! About 46. REALLY??
The Register 
Biting the hand that feeds IT
Copyright. All rights reserved © 1998–2025
