-3

I'm looking for detailed answers to the question : What are the pros and cons of using an Excel file as a database ?

Community
  • 1
  • 1
otus
  • 385
  • 3
  • 10
  • 28
  • Can the down voters explain why this question is not relevant ? – otus May 12 '15 at 08:03
  • 3
    I'm one of the people that down-voted. The reason for it is because the question isn't really fit for Stack Overflow (SO). SO expects questions to be a specific programming questions. Your question is going to generate opinion based answers that aren't encouraged. – Gareth May 12 '15 at 10:22
  • @Gareth I disagree with you, this [post](http://stackoverflow.com/questions/14994391/thinking-in-angularjs-if-i-have-a-jquery-background) for example is about a clearly not specific programming question and it is one of the most upvoted of the site. Moreover my question is new and may help future developer avoid bad practices like using an Excel file as a database. Anyway, you're entitled to your opinion. – otus May 12 '15 at 10:49

3 Answers3

0

Cons: size/performance, sharing

Pro: none

P.S. If VBA is an issue, why not Access?

Matt
  • 13,674
  • 1
  • 18
  • 27
0

One of the pros seems to be that users are familiar with Excel and can work with the tables without needing to know about databases. There are however many reasons not to use Excel as a database. - Even though you can do some validation in Excel it is no match to any good database program.
- When importing data from an Excel file to, for instance, a SQL database you often run into problems because of the misinterpretation of the valuetypes
- Also when importing dates the interpretation may fail
- Strings like 000234 will most likely be read as numbers and end up as 234
- As stated before the sharing of the database is very limited
- But one of my main concerns using Excel as a database is the fact that it is a single file that can easily be copied to various locations which may cause you to end up with several versions of it with different data

Tom
  • 747
  • 5
  • 16
0

I wouldn't really suggest that Excel is or can properly act like database - as it lacks the features, data protection and security to act as such.

If the reason to use this is based upon ease of use and end user familiarity - it is quite easy to connect Excel as a front end to a database - using it as a reading and writing device, whilst taking advantage of the speed and stability issues of a 'true' database.

Pros: Very familiar VBA is easy to use to create fairly simple to use sheets Lots of functions to manipulate data

Cons: Slow and VERY clunky with large data set Hard to validate on imported data Prone to crashing with large datasets Lack ability to use intelligent queries or views Many more..

Trum
  • 620
  • 3
  • 12