Best practice to use MS Excel as a database

Question

In office, it is popular to use MS Excel to store data. In most cases, the data is structured, which means it is suitable for a database. I know peole prefer MS Excel for it is easy to change the data structure and data value. So I have an idea to use MS Excel as a database IF people follow a general rule to store data. In other words, by a best practice to use MS Excel as a database. I have thought to use MS Access to store data, but it is expensive and not popular as MS Excel.

I don't mind to buy such a solution, especially when it is for .Net platform.

Any ideas or suggestions are welcome.

Answer 1

I think you would be better with SQLServer Express or for that matter any open source SQLdatabase. Most databases have ODBC Connectors and if they do you can still access them through Excel.

Answer 2

I am not sure why you claim MS Access is expensive solution. Office Professional (which includes Access) is $499.99, Office Standard (which does not include Access) is $399.99. That's $100 per license to get Access; and for that money you get not only a database engine, but a quite good turn-key solution for processing large volumes of data. Plus, you can still very easily import/export data between Access and Excel.

If that is not a good enough solution for you, you can also download SQL Server Express and use as local database engine deployed on each desktop.

There are also other free, non-Microsoft alternatives as well, like SQLLite and MySql if you need a SQL solution, or MongoDB, Cassandra and CouchDB if you are fine with a NoSQL solution. Note that the NoSQL solution has some specifics and might not be a good fit for your needs, if you need to process large volumes of structured normalized relational data.

In any case, any of these would be a better solution to store data than Excel. While Excel excels (pun intended :-)) in numerical processing of data, it is a specialized product and storage, indexing and retrieval of data is not something it's been optimized for.

Answer 3

The great thing about Excel is that you can put any data anywhere. The reason that Excel is awful as a database management system is that you can put any data anywhere. Databases help you maintain data integrity that a spreadsheet simply cannot do effectively. Data integrity include rules like requiring values, data types on values, ranges of acceptable values, integrity between related sets of data etc.

You can never design a reliable system on the premise that the user probably will not do X. It simply never works. If the user can do something, they will do it eventually unless the system prevents it.

So, it is definitely an anti-pattern to use Excel for a database. Access is a decent, relatively inexpensive, simple solution for small systems and comes with a form builder. If you thought that the system might be very large or have a large number of users, then it would not be a good solution. Even less expensive would be SQL Server Express, but that requires that you build an interface for the users to the database.

Answer 4

The key question is whether the users need to share data. This is the main reason why Excel is so dangerous (and Access, albeit to a lesser extent): every desktop has a different version of the truth, because there is no mechanism for synchronizing the data.

If you have money for the licenses the top solution would be to use SQL Server to store the data, SharePoint to handle the distribution and retain Excel as the front-end. This gives you the advantages of the database while allowing your users to maintain their current way of working. MSSQL 2008 has some neat features to support this stack.

The superficially cheapest option is put shareable Excel spreadsheet on a network drive. Two problems. The first one is this serializes user write access to the data. But the larger problem will be getting the users to agree which spreadsheet holds the correct version of the data. Of course, that applies to any exercise in consolidating disparate data sources.

Answer 5

At first, I want to thank you all for replies.

As a developer, I cannot agree with you all more. But I meat a complicated situation. The easy problem is money. The IT manager told me MS Access was expensive and he thought not necessasy to buy it for all staff. But it is not a big problem. The difficult problem is most staff in my office preper to use MS Excel for it is very flexible. Partly it is because they can control all. Partly it is because from time to time, we have to add some fields or restructure the data. And the last reason is if the data structure is controlled by IT department, it will be a heavy job for us. If not, the database will be out of control. And it is not easy for non-IT staff to control the structure(scheme).

So I am thinking to make a compromise.

If there is no such a way, I will let them use MS excel files and share by copies.

Thanks again.

Answer 6

There is no best practice for keeping database data in Excel. Excel is not suited to storing relational data.

The data engine used by Microsoft Access is installed as part of almost every Windows installation (if not every one). If you are programming in dot net you can use that engine to store data without any requirement for MS Access licenses.

Answer 7

I just searched Internet and found the following interesting pages:

1. Excel Is NOT a Database! (http://adminsecret.monster.com/benefits/articles/2599-excel-is-not-a-database) This article summarizes most above replies.

2.Excel as a database (http://www.neopoleon.com/home/blogs/neo/archive/2003/09/29/5458.aspx) This article provides a serial of funny pictures to show the IT ability of office.

3.Using Excel Sheets as a Database Backend (http://www.beansoftware.com/NET-Tutorials/Excel-ADO.NET-Database.aspx) This article provides a basic way to use excel as a database.

Thanks for you all

Answer 8

For most small businesses owners running everything themselves, an excel database is a great idea. Especially considering that a lot of businesses don't even have a database at all.