Welcome to the next 5 weeks of your life.  Over the course of this time, we’ll be putting together some very basic data management tips and tricks.  Some of these will focus on Microsoft products, while others will be more focused on data management practices, regardless of product.

This string of communication will cover;

• MS Excel, Database Friend or Foe?
• MS Access, corrupted data’s best friend!
• Why haven’t you tried MS SQL Express yet?
• Comparing data sets with fuzzy logic, the benefits and pitfalls.
• How to minimise the impact of data de-duping within a relational database.

I do hope you find the articles informative, and as always, we relish any and all feedback.  Also, given that we’re all more than busy enough, each article will take you no longer then 90 seconds to read…promise!

Article One: Microsoft Excel, Database Friend or Foe?

Depending upon who you speak to, an Excel document is often considered a “database” in its own right.  And this is true, it’s a “Flat File” structured database.  This means, that all information is contained within the spreadsheet, and no other spreadsheets connect to or relate to this information in any way.  Excel is a very powerful spreadsheet tool, and something that I’ve used for a great many years to help manipulate data.  It is however, not without its quirks.

Recently I was provided a data set, from a large multinational company, in excel format.  Upon delivery I started to go through the data, and noticed that the street numbers (which were in their own column), were corrupted.  Essentially they looked like, “9-Nov”, “2-Apr” etc.  Now it’s pretty clear that they should have been, “9-11” and “2-4”.  It’s easy to say this, and even better that it was caught prior to any further import, but after another extraction with this resolved, we still had corrupted data.  So, we went back to the source, and found that the source its self was corrupted.  Not good!

So, Excel Friend of Foe….well, it’s a little of both.  It’s an application that tries it’s best to be your friend, but in doing so it can actually corrupt your data.  Remember that kid who was too smart for his own good in class?  Excel is a little bit like this.

Excel.  A great application that makes data manipulation easy and accessible to the masses.  But be warned, Excel used without caution will create a world of pain!