I just started working for a local company. So far work has been very good and everything is great. A few days ago they asked me to help them move some data from their current repository to a new software they just bought. 

I asked for the data and I was handed 3 or 4 (10 MB+) excel spreadsheets. At first I thought it was a joke, but come to find out they keep all their history and information about sites their maintain in excel spreadsheets! Needless to say that the data is a mess, it is all typed by hand, no standardisation at all, and of course each sheet has 10 million fields and 4-5 separate worksheets with a billion different formulas.

I have spent the past week digging through it trying to get some “clean” data out, so far I have not been very successful at scripting or getting said data out without having to physically manipulate it.

As soon as I can sort that mess I am doing away with all the spreadsheets and moving everything to a database system. I can not believe that there are people out there still using Excel as a data repository. Excel is not a database, it is a data manipulation software. Please please please NEVER USE EXCEL for “Data Storage” you will regret it. 

4 Comments on Excel is not a database people!

  1. jason says:

    They’re going to let you destroy their precious spreadsheets? You are my hero. The manufacturing company I work for keeps all of their QC data in approximately 3,500 xls files. I’m not kidding; I just checked this morning. All of them (that I’ve seen) are set up as you said in your second paragraph. I’ve begged, pleaded, proven, argued, demonstrated the benefits of constructing a database and the pitfalls of not doing so, but the powers that be operate under the mantra “if it ain’t broke, don’t fix it.” Unfortunately for them, they don’t recognize broke when they see it.

    Is there *ANY* hope?

  2. jason says:

    A humorous/pitiful example of their futility came to mind after posting:

    A coworker of mine was required to spend two entired work days (while everyone else was on Christmas break) at the office copying and pasting, field by field, 450 rows of data… get this… FROM a *REAL* database… TO Excel. The data was text comments about customers (special requirements, etc.) and not anything that would ever be manipulated by Excel (not that it would be justification for this idiocy, but at least it would explain why they chose Excel). Excel was merely the preferred “database”, as far as they were concerned.

    • Jose C Gomez says:

      WOW! I feel your pain, I was able to move them away (for the most part) from the massive Excel sheets.
      Now we run on 2 separate systems both which have Full DBMS on the back end.
      Not that I am recommending this, but perhaps 1/2 of those Excel sheets could get “corrupted” and the backups could fail for a day or two. A good scare goes a long way!
      Thanks for the comments 🙂

  3. jack says:

    At my work there are over 10,000 xlsx files that they want me to save into a MySQL database. That is sort of a first step towards migrating to a database, but they will create new data using spreadsheets instead of say, a web entry form that ties in direct to the DB>

    I have tried multiple times to persuade the boss to have me develop a web based entry form and do away with the xlsx files entirely. However, everyone at work LOVES xlsx files and despite me working here for ages still don’t quite get the difference between spreadsheet and database, even though my job has been to migrate Excel data into a database for the last year or so.

    It’s crazy.

    So, I’m just building a web entry form anyway, with or without boss’s permission. Will see what he thinks when I finish it.