Using Excel as a Relational Database

A not so long ago i had a course of Business Intelligence and for the course project i had to build a data-mart with the dimensions and fact tables. I admit that i don’t like the Excel, although it has many advantages I …. hate him :-).
Because I was a little stressed, I didn’t stop to think how to do the work and start creating a sheet to the dimensions and the fact tables and an extra sheet to the “inner join of all dimensions and fact table”. A real pain in the … hand.

Recently I had to do another “project” using only the Excel and it was a requirement that i only made a single call to the database and do all the work in the Excel. After starting the job, i remember thinking “This is extremely stupid” so I stopped to think and with some help a Mr. Google I got a meanful breaktrought.

This is a recipe to use Excel as it was a relational database and hope it will help you and also prevent you from doing the same mistakes.

The Recipe:

  • Open Excel and create a data table.
    • Insert manually
    • Get it from the database
    • Whatever
  • Save the Excel in a easy to remember location
  •  Create a “Data Connection”.
    • Data Tab -> “From Other Sources” -> “From Microsoft Query”
    • “Databases” -> “Excel Files”
    • Ok
    • Find the saved excel file
    • Add all columns you need
    • Don’t provide any filter or order
    • Click “Save Query” and save it in the save folder than the excel file
    • Click “Finish”
  • Choose the cell location for the result.

Now we should have the data in the sheet.

Going forward.
If we copy the excel to other location we will have some missing files errors, so to prevent we will change some configurations.

  • Go to “Data” tab again and click “Connections”
  • Double-Click in the active connection
  • Click “Definition” tab
  • in the connection string change the absolute path for the excel for an relative.
    • ex: “DBQ=c:\temp\excel.xls” -> “DBQ=.\excel.xls”
  • If you need to change the query just change it in the “Command text”.
    • Verify that there is no reference to the absolute path of the excel book.

If you need some “where” clauses that depend cell value, just edit the query and in the query clause put “?”. ex: “…. where id=? ….”. When you refresh the data it will ask which cell should be read to assume the value. Niiiiiice. You can also click in the parameter button and can costumize the text should appear and the cell location.

Hope it help.