Using Excel as a database
A lot is written on the web about using Excel as a database. The opinions diverge about the pros and cons for this approach. You could also read that Excel is “The World’s Most Popular Database” but if I Google on that I get “MY SQL”. If you add Excel to the search – you will only get Excel-results. Nor Google or anybody one else knows the answer to the question “Which is the most popular database?” The answer of course also depends on how you define what a database is.
In my work I often come into situation where Excel is used for the storage of data for many mission critical application in business. Depending on size and complexity this approach is more or less successful, often used by “desperate” professionals, needing their job to be done, and lacking resources and time to get there needs satisfied by a professional developer.
Being brought up as a database developer I used to deliver my DB-solutions with a form based UI, in MS Access or with HTML. Many users feel awkward in Access, lacking the control Excel gives you in that you always self makes the decision when it is time to save. The HTML solutions almost always get very expensive, but has the great feature of being available for everyone.
This year I have been into three projects where a database has been a very appreciated solution for replacing a bunch of spread sheets. The governments in Sweden has done a lot of investigations of the quality of the water in many of the lakes and rivers. Some samples are collected, it could be water, mud or fishes. The samples are sent to different laboratories and the result is sent back as a spread sheet. During the years thousands of different observations are collected and many qualified professionals has spent a lot of time copying and pasting the figures forth and back trying to understand relationship among the environmental disasters. This spring I have spent time with a company having arranged all their price lists in some 100 sheets in 50 workbooks, containing the prices for about 100 000 items. This system was used for calculating new prices, interacting with the ERP-system and updating the invoice system when changes to prices occurred. The last example is from a manufacturer in the food business, where Excel is used as an intermediate step for storing data obtained from scanned invoices in paper. These projects have given me a deep insight into different aspects of moving data from Excel into a database, which I aim to share in some post on my blog.
