back to article 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 …

  1. werdsmith Silver badge

    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.

    1. Pascal Monett Silver badge

      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.

      1. Sgt_Oddball
        Paris Hilton

        Is my memory failing..

        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).

        1. Martin Gregorie

          Re: Is my memory failing..

          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.

          1. Robert Sneddon

            CSV RFC XML TLA

            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.

            1. Martin Gregorie

              Re: CSV RFC XML TLA

              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.

              1. ArrZarr Silver badge
                Happy

                Re: CSV RFC XML TLA

                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.

                1. J. Cook Silver badge
                  Boffin

                  Re: CSV RFC XML TLA

                  I can reliably state that Powershell does have an "Import-CSV" commandlet, and that said commandlet is only slightly picky about how the CSV file is formatted.

                  1. Jou (Mxyzptlk) Silver badge

                    Re: CSV RFC XML TLA

                    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.

                    1. cawfee

                      Re: CSV RFC XML TLA

                      -NoTypeInformation should be default, and you should have to request -TypeInformation. Drives me nuts.

                2. pklausner

                  Re: CSV RFC XML TLA

                  Actually, SQLite does have an extension module which allows you to access a CSV file just as a normal table: https://www.sqlite.org/csv.html

          2. Charlie Clark Silver badge

            Re: Is my memory failing..

            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.

          3. Rob 63

            Re: Is my memory failing..

            I like running php in sublime and using a generator to iterate over csvs and spit out another file, i'm weird like that

            1. werdsmith Silver badge

              Re: Is my memory failing..

              cvs is great until people start putting your comma, or whatever delimiter in the text fields.

              I definitely know where to find the § key.

              1. Kane
                Pint

                Re: Is my memory failing..

                "I definitely know where to find the § key."

                You bastard. I love it!

              2. Jou (Mxyzptlk) Silver badge

                Re: Is my memory failing..

                Thats what " is for. Powershell import-csv/export-csv uses "". Problem solved. But you can use -Delimiter ";" too. You can use SEP=; in the first line, which selects the delimiter manually - but not every tool likes it that way.

          4. Anonymous Coward
            Anonymous Coward

            Re: Is my memory failing..

            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.

            1. Eclectic Man Silver badge

              Re: Is my memory failing..

              Upvote for mentioning awk, don't talk about fgrep or I'll need a lie down in a darkened room (withdrawal symptoms).

            2. Anonymous Coward
              Anonymous Coward

              Re: Is my memory failing..

              Tip: the GNU version - gawk - also has the ability to extract records comprising fixed width fields without separators. This can be very useful when migrating legacy mainframe-type files.

          5. Doctor Syntax Silver badge

            Re: Is my memory failing..

            "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.

          6. SGJ

            Re: Is my memory failing..

            There is a standard describing CSV files - RFC 4180 - but of course most systems do their own thing.

            Go has a CSV package in it's standard library (https://golang.org/pkg/encoding/csv/) if you want to have a play.

          7. Anonymous Coward
            Anonymous Coward

            Re: Is my memory failing..

            > 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

          8. Anonymous Coward
            Anonymous Coward

            Re: Is my memory failing..

            Powershell provides such a function, though not configurable.

          9. Allan George Dyer
            Joke

            Re: Is my memory failing..

            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?

            Obligatory xkcd

            1. Anonymous Coward
              Anonymous Coward

              Re: Is my memory failing..

              Now if only they'd include a Parse::CV module.

              (Joke!)

          10. itzumee

            Re: Is my memory failing..

            "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

      2. Anonymous Coward
        Anonymous Coward

        Excel installed on a server? Oh hell to the no!

      3. Bruce Ordway

        beancounters were checking lists of Office licenses against users and...

        >>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.

        1. Terry 6 Silver badge

          Re: beancounters were checking lists of Office licenses against users and...

          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).

          1. Bruce Ordway

            Re: beancounters were checking lists of Office licenses against users and...

            >> 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.

            1. Down not across

              Re: beancounters were checking lists of Office licenses against users and...

              >> 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.

    2. jdiebdhidbsusbvwbsidnsoskebid Silver badge

      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?

      1. Eclectic Man Silver badge

        "time, cost, quality, which one do you want?"

        I want the RIGHT ANSWER, particularly when people's lives* depend on it.

        *(60, asthmatic, so high risk, I'm not paranoid, honestly.)

        1. Danny 2

          @Eclectic Man

          "(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.

          1. Robert Carnegie Silver badge

            Re: Scotch missed

            Is that now or is it Friday 6pm for two weeks and one lost weekend, not sure.

            They may be allowed to serve ginger or carry out.

          2. Eclectic Man Silver badge

            Re: @Eclectic Man

            @ 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.)

            1. Danny 2

              Re: @Eclectic Man

              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."

        2. Olivier2553

          But when lives are at risk, you also need the answer now. Are you ready to wait 6 month until the right solution is developed and fully tested?

          1. Eclectic Man Silver badge

            Consider this

            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.)

      2. Doctor Syntax Silver badge

        It's the "and dirty" bit that catches people out.

      3. Alan Brown Silver badge

        "Despite this recent snafu, I wonder if we're still better off than if we'd produced a fully tested system,"

        Some organisations HAD fully tested systems in place which they were ordered to shut down in favour of the £18billion Serco app

    3. Lee D Silver badge

      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.

      1. Eclectic Man Silver badge

        Sounds like you need, well the UK needs, a Royal Commission to investigate the use and abuse of MS Excel in HMG. (Probably chaired by Dido Harding, if our luck is as usual.)

        1. Eclectic Man Silver badge

          Mea Culpa

          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?

      2. Anonymous Coward
        Anonymous Coward

        It's not Civica by any chance? Sounds all too similar to the appalling software we're stuck with from them.

      3. Alan Brown Silver badge

        > 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)

        1. ovation1357

          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.

          1. The Oncoming Scorn Silver badge
            Childcatcher

            Washing Machines

            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.

        2. Down not across

          (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).

      4. Rol

        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.

        1. Robert Carnegie Silver badge

          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.

          1. Olivier2553

            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.

            1. Terry 6 Silver badge

              In reality UK addresses only need postcode and house number. Most autofill systems do precisely that.

              I guess a written street and town name is useful for error tracking and such.

              Barmy exceptions and errors aside.

              1. ovation1357

                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.

              2. Robert Carnegie Silver badge

                Do you know how long it takes for a new built property to get a post code?

              3. Anonymous Coward
                Anonymous Coward

                Not all uk houses have numbers (and some have more than one)

      5. Anonymous Coward
        Anonymous Coward

        "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.

        1. keithpeter Silver badge
          Windows

          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

    4. Anonymous Coward
      Anonymous Coward

      I’ve seen a this often, even in highly regulated systems.

      Highly regulated systems generally just mean there are some regulatory staff who don't understand software, who write bad documentation for the auditors, who also don't understand software - to read.

  2. Test 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.

    1. Anonymous Coward
      Anonymous Coward

      Thingies cat

      Did the government make the mistake or did it’s consultants tasked with building this solution make the mistake?

      1. Graham 32

        Re: Thingies cat

        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.

        1. Martin Summers Silver badge

          Re: Thingies cat

          You hire people expecting them to be able to do the job you hired them for. If they don't, how were you meant to know that in advance. Hindsight is a wonderful thing used to beat over people's heads unjustifiably.

          1. Mark #255

            Re: Thingies cat

            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.

            1. Alan Brown Silver badge

              Re: Thingies cat

              "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

              1. old_iron

                Re: Thingies cat

                Err,,,

                TalkTalk had an intranet connected PC in reception ... didn't take a Russian Hacker to have a nose around

                LoL

          2. J P

            Re: Thingies cat

            "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?

          3. phuzz Silver badge

            Re: Thingies cat

            You hire people expecting them to be able to do the job you hired them for

            Or in a totally fictitious and totally unrelated to the current government, honest, manner, you hire a company owned by one of your mates.

          4. doublelayer Silver badge

            Re: Thingies cat

            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.

            1. Tom 7

              Re: Thingies cat

              Not met many tories then?

            2. Eclectic Man Silver badge

              Re: Thingies cat

              @ 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.

              1. TheMeerkat

                Re: Thingies cat

                Have not we seen how difficult for the government to get rid of useless civil servants it did not hire in the first place?

                See the case of Philip Rutnam.

                And it is the civil service that makes all these decisions.

                1. Eclectic Man Silver badge

                  Re: Thingies cat

                  What makes you think they were useless, and took the decisions?

                  The Tories have been in power for a decade now and had ample opportunity to affect the Civil Service appointments to senior positions.

          5. Tom 7

            Re: Thingies cat

            If you are in a Conservative Government and you hire your mate you know its going to be a disaster.

            1. Alan Brown Silver badge

              Re: Thingies cat

              "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

              1. Anonymous Coward
                Anonymous Coward

                Re: Thingies cat

                "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.

                1. Terry 6 Silver badge

                  Re: Thingies cat

                  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.

                  1. Eclectic Man Silver badge

                    Re: Thingies cat

                    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.

                    1. Terry 6 Silver badge

                      Re: Thingies cat

                      Oh yes. It seems to be accepted in organisational hierarchies that at some arbitrary level you accept the credit for someone else's knowledge and experience.

          6. Pen-y-gors

            Re: Thingies cat

            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.

            1. Eclectic Man Silver badge

              Re: Thingies cat

              Yes, you advertise the vacancies to your group of party donors and choose the one who promises you the most support.

              That is the political way, after all. It works in the USA - pretty much every ambassador of the USA is a political crony / party donor.

        2. Alan Brown Silver badge

          Re: Thingies cat

          > "You'll need to contact the manufacturer"

          Is a surprisingly and depressingly common mantra

          British Trading Standards departments are so catastrophically overloaded that complaining about this might get dealt with in 3-6 months time, if at all

      2. Greybearded old scrote Silver badge
        FAIL

        Re: Thingies cat

        My money says that somebody in the chain has been denied software upgrades for at least a decade. Everyone else had to use the lowest common denominator for them.

        1. Anonymous Coward
          Anonymous Coward

          Re: Thingies cat

          "Denied software upgrades" or "refused software upgrades". Wasn't the shift to .xlsx about the time the ribbon interface kicked in? I've worked with people who clung on to old PCs for years as they had "proper Office" installed.

          1. l8gravely

            Re: Thingies cat

            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.

            1. Eclectic Man Silver badge

              Re: Thingies cat

              If it is still in full working order there are people with out of date media who would very much like it, if only for the spares.

  3. Anonymous Coward
    Anonymous Coward

    Anon because....

    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.

    1. BebopWeBop
      Terminator

      Re: Anon because....

      Did you ever think it might have been deliberate? -------------->

    2. Medieval Research Council

      Re: Anon because....

      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 ...

    3. Anonymous Coward
      Anonymous Coward

      If my manager had been as incompetent a decade ago...

      ...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...

      1. Alan Brown Silver badge

        Re: If my manager had been as incompetent a decade ago...

        " 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)

    4. Eclectic Man Silver badge

      Re: Anon because....

      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.

    5. ShadowDragon8685

      Re: Anon because....

      How incendiary was the fallout? Sacking? Lawsuits? Overvolted cattle prod and a window that mysteriously failed at the New Year party?

      1. Robert Carnegie Silver badge

        Re: Anon because....

        Agreement to not mention it? But you know. You all know.

  4. Boaty

    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).

    1. Steve Foster

      Pegasus Opera

      is built (or used to be) on FoxPro.

      Once upon a time, if Pegasus crashed while posting invoices it would leave the accounts db without both halves of the double-entry in place, so accounts would not balance.

      1. MJI Silver badge
        Go

        Re: Pegasus Opera

        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.

      2. Sgt_Oddball
        Windows

        Re: Pegasus Opera

        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.

        1. MJI Silver badge

          Re: Pegasus Opera

          And we are still using the same client server package with newer clients, still running DBFs but some are around 6GB.

  5. MiguelC Silver badge
    Coat

    65536

    How could anyone in IT(*) miss that telltale sign?

    (*) Oh, an Oracle Consultant, I see...

    1. Chewi

      Re: 65536

      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?"

      1. Hubert Cumberdale

        Re: 65536

        They used to call it "16.7 million" [colours] (eight bits each for R, G, and B). The rounding error always bugged me.

        1. Tom 7

          Re: 65536

          Pixelated shirley?

        2. doublelayer Silver badge

          Re: 65536

          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.

    2. Anonymous Coward
      Anonymous Coward

      Re: 65536

      It is strange an Oracle consultant did not prefer text files and SQL*Loader, especially if the database server was not a Windows one.

      Exporting from Oracle to Excel did need some external code, probably - my bet is on Visual Basic...

      1. Trubbs

        Re: 65536

        "It is strange an Oracle consultant did not prefer text files and SQL*Loader"

        Or even better the nightmare that was/is Discoverer

    3. Charlie Clark Silver badge

      Re: 65536

      They're usually mislabelled "consultants" and really just part of the sales force. At some point the pain becomes so great that the customer can be persuaded that the solution is "(more) Oracle".

      1. Alan Brown Silver badge

        Re: 65536

        > 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

        1. Charlie Clark Silver badge

          Re: 65536

          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!

  6. chivo243 Silver badge
    WTF?

    spreadsheets!

    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...

    1. Blane Bramble

      Re: spreadsheets!

      Because they wanted the count to be accurate ...

      1. Dave314159ggggdffsdds Silver badge

        Re: spreadsheets!

        Precise, not accurate. Which is exactly what they got.

    2. lglethal Silver badge
      Trollface

      Re: spreadsheets!

      A spreadsheet is only needed for numbers bigger than the User has fingers and toes.

      So 28 items absolutely needs a spreadsheet!

      1. Charlie van Becelaere
        Boffin

        Re: spreadsheets!

        If one counts on one's fingers base 2, one can get to 1,023 without resorting to toes.

        Learnt this trick at an SF convention during my uni days.

        1. Anonymous Coward
          Joke

          Re: spreadsheets!

          > Learnt this trick at an SF convention during my uni days.

          Yep, SF conventions can get pretty boring. ;-)

    3. stevebp

      Re: spreadsheets!

      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.

  7. Muscleguy

    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.

  8. 9Rune5

    RDBMS vs Excel

    At least the Oracle consultant did not go and do something stupid, like INSERT all those rows into a database table somewhere... Probably would have triggered something in the license agreement (all 255 pages worth).

    Oh, the irony...

    1. TimMaher Silver badge
      Happy

      Re: RDBMS vs Excel

      Yeah. There should have been 256 pages but the last one said “This page intentionally left blank.”

      1. Anonymous Custard
        Trollface

        Re: RDBMS vs Excel

        The BOFH in me has always wanted to be the one to set up those types of agreements or documents, just so I could put in "This page has accidentally been left blank" and see how many actually notice and comment...

        1. red floyd

          Re: RDBMS vs Excel

          I used to work for a defense contractor. I once wrote a document that included "This page intentionally left blank... except for this text."

        2. gnasher729 Silver badge

          Re: RDBMS vs Excel

          I remember using “This page intentionally left almost blank”.

      2. Doctor Syntax Silver badge

        Re: RDBMS vs Excel

        the last one said “This page intentionally left blank.”

        Surely that should be the zeroth one.

      3. Olivier2553

        Re: RDBMS vs Excel

        A colleague of mine, when translating some documentation into French, played the game to find every possible combination of the different meaning of each word (like "page" as a sheet of paper or a young person, "left" as a direction or the verb to leave).

        1. Anonymous Coward
          Anonymous Coward

          Re: RDBMS vs Excel

          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"!

  9. Toc-H-Lamp

    Similar issues

    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).

    1. Joe W Silver badge

      Re: Similar issues

      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

      1. Martin Gregorie

        Re: Similar issues

        Japanese generally write dates as ccyy-mm-dd.

        1. Toc-H-Lamp

          Re: Similar issues

          Last time I was there all my receipts had the year 10, they use the number of years the emperor has been in situ, which gets round any y2k issues I guess.

          1. katrinab Silver badge
            Meh

            Re: Similar issues

            I guess it is possible for an emperor to serve for more than 99 years - if they ascend to the throne when they are very young, has happened before, and live to 100+ which is fairly common.

      2. This post has been deleted by its author

      3. katrinab Silver badge
        Paris Hilton

        Re: Similar issues

        yyyy-mm-dd is my preferred date format.

        1. Anonymous Coward
          Anonymous Coward

          Re: Similar issues

          <Joke>

          I prefer mmm-yy/dd.DDD myself.

          </Joke, gets coat...>

      4. Ken Hagan Gold badge

        Re: Similar issues

        "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.

        1. katrinab Silver badge
          Happy

          Re: Similar issues

          Also, it is most significant digit to the left, least significant digit to the right, which is how pretty much everyone in the world writes numbers, even those who write their words from right to left.

        2. Corp-Rat
          Devil

          Re: Screwtape

          Did you dig that reference out of the back of an old wardrobe?

      5. Eclectic Man Silver badge

        Re: Similar issues

        2020-06-23 and 20200623 are ISO standard formats for dates (either is acceptable).

        ISO 8601:

        https://www.iso.org/iso-8601-date-and-time-format.html

      6. Alan Brown Silver badge

        Re: Similar issues

        "Who even uses as date format like yyyy-dd-mm"

        Anyone using standards - specifically ISO 8601

        Before 1988 they may have been using ISO 2014, ISO 2015, ISO 2711, ISO 3307, or ISO 4031.

        1. Mark #255
          Headmaster

          Re: Similar issues

          Read carefully, then scream.

          yyyy-mm-dd # this is ISO 8601

          . . . \ /

          . . . / \

          yyyy-dd-mm # this isn't

      7. Jou (Mxyzptlk) Silver badge

        Re: Similar issues

        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)

      8. Robert Carnegie Silver badge

        Re: Similar issues

        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 ;-)

        1. Down not across

          Re: Similar issues

          I prefer to convert explicitly (regardless of the vendor of the RDBMS) rather than rely on it making its own mind.

          Assume make and ass out of you and me.

    2. Anonymous Coward
      Anonymous Coward

      Re: Similar issues

      "[...] 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.

  10. technos

    You want how many leases?

    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.

    1. Joe W Silver badge

      Re: You want how many leases?

      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.

      1. Dave314159ggggdffsdds Silver badge

        Re: You want how many leases?

        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.

        1. katrinab Silver badge
          Meh

          Re: You want how many leases?

          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.

      2. Dave314159ggggdffsdds Silver badge

        Re: You want how many leases?

        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.

        1. Mark #255

          Re: You want how many leases?

          No, Excel was not right at all (recent versions are better).

          Also, (as a statistician told me), if you have to worry about choosing between the "population" and "sample" standard deviation, your sample size isn't big enough.

          1. Anonymous Coward
            Anonymous Coward

            Re: You want how many leases?

            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".

  11. disgruntled yank

    It wasn't just Excel

    My wife lost a week dealing with the old Cat's Pajamas accounting system when a table there hit the magic 65K limit. That would have been about 30 years ago.

  12. Anonymous Coward
    Anonymous Coward

    "Spotted something in the news that has triggered a long-forgotten bit of " PTSD

  13. phuzz Silver badge
    Devil

    Excel is ubiquitous

    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.

    1. Dave314159ggggdffsdds Silver badge

      Re: Excel is ubiquitous

      "I doubt there's a single business that doesn't have somewhere an Excel worksheet which is essential to the whole company."

      There are quite a lot of businesses that don't have a single electronic document, even today.

      1. Anonymous Custard
        Headmaster

        Re: Excel is ubiquitous

        So we should update the old saying to "When all you have is Excel, everything looks like a worksheet" rather than hammers and nails? Seems equally apt for modern times.

        1. Terry 6 Silver badge

          Re: Excel is ubiquitous

          Exactly

    2. J. Cook Silver badge

      Re: Excel is ubiquitous

      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')

    3. Alan Brown Silver badge

      Re: Excel is ubiquitous

      > I doubt there's a single business that doesn't have somewhere an Excel worksheet which is essential to the whole company.

      You're right. And that's why it's critically important to hunt them down and KILL them

  14. DJO Silver badge

    Excel as an intermediate step

    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.

    1. Doctor Syntax Silver badge

      Re: Excel as an intermediate step

      And if you really know what you're doing you'll use something different.

      1. DJO Silver badge

        Re: Excel as an intermediate step

        And if you really know what you're doing you'll use something different.

        Damn right.

        Occasionally Excel is the right tool for the job but in the majority of (serious) cases it's not.

    2. J.G.Harston Silver badge

      Re: Excel as an intermediate step

      If they'd formatted the phone numbers correctly in the first place that would stop Excel seeing them as numeric numbers.

      1. DJO Silver badge

        Re: Excel as an intermediate step

        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.

        1. J.G.Harston Silver badge

          Re: Excel as an intermediate step

          No, the correct formatting for telephone numbers *INCLUDES* spaces.

          It's 020 8811 8055 (or 020 8811-8055) not 02088821348randomstringofunparsabledigits

          1. Terry 6 Silver badge
            Flame

            Re: Excel as an intermediate step

            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.

          2. DJO Silver badge

            Re: Excel as an intermediate step

            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.

        2. Kevin Fairhurst

          Re: Excel as an intermediate step

          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.

          1. DJO Silver badge

            Re: Excel as an intermediate step

            True for XLS and XLSX files, they keep a last opened stamp in the file so as you say it changes when opened. There are ways to stop this but who bothers.

            Does not apply to plain text CSV files.

  15. ColinPa

    Scale to fit

    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.

    1. Anonymous Custard
      Trollface

      Re: Scale to fit

      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.

  16. T. F. M. Reader

    Once upon a time, in the previous millennium, in a country far, far away ...

    ... 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...

    1. Anonymous Coward
      Anonymous Coward

      Re: Once upon a time, in the previous millennium, in a country far, far away ...

      "Come to think of it, it's 2020 and the state budget is probably still managed in Excel..."

      As LibreOffice handles Excel files just fine, why would this be a problem?

      1. cookieMonster Silver badge

        Re: Once upon a time, in the previous millennium, in a country far, far away ...

        Just having a guess here....

        Macros, VBA .. and other MS oddities (COM, ActiveX)??

  17. J.G.Harston Silver badge

    Department X

    Did Jim have sideburns and moonlight as a photographer?

    1. Anonymous Coward
      Anonymous Coward

      Re: Department X

      Nope.

  18. Duncan Cummings
    FAIL

    Still Happening

    https://www.bbc.com/news/technology-54423988?fbclid=IwAR0E7lFUHdghfTKe-eKzlDynf0oCjy3EPZxb1vzfcRi4XQFHP0olNVsJNvI

    1. Jou (Mxyzptlk) Silver badge

      Here is your link without facebook tracking:

      https://www.bbc.com/news/technology-54423988

      Come on, you are posting this on an IT site and include the ?fbclid= ???? What rock do you live behind? Ah, behind the 65535th rock.

    2. Danny 2

      Ironically Duncan, you probably don't want to include your Facebook Click ID on a tech forum. Maybe delete that post and try again?

    3. Robert Carnegie Silver badge

      The incident in the BBC article is actually the reason why we're talking about Excel's problems. And the other reason... IS Excel's problems.

  19. Paul Hovnanian Silver badge

    This is something ...

    ... to certainly lose sleep over.

  20. Fursty Ferret

    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.

  21. Dixx

    Data and Management

    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.

  22. ChrisElvidge

    Try these two

    Planmaker gets this right: =-28.96+25+5-1+0.02-0.06

    None I've tried gets this right: =111111111*111111111

    bc and mysql do the maths correctly, though.

    1. They call me Mr Nick

      Re: Try these two

      Both calculators I use day to day get both right:

      SpeedCrunch on PC and Mathlab Graphing Calulator on my phone.

  23. VBF
    Happy

    Really?

    Referring to "Yes, Minister from back in the day" (although the fictionalised events of the show have long been TRUMPED by reality).......

  24. John Robinson 2

    A new "several" ?

    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??

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

Other stories you might like