+ 1

Why can't we use MS Excel as database to store data? I mean it is also columns and rows with so many inbuilt functions.

I understand there's limitations like number of rows and columns, can't we use multiple sheets in same workbook? Also, online version allows simultaneous editing. I can't understand why we can't use this and why to learn SQL. Is it due to copyright issues? Please clarify.

16th Oct 2018, 4:45 AM
Akshay
19 Antworten
+ 11
Have you ever tried to group an Excel table with 10,000 rows by the values of column B, sort the results simultaneously by column C and get the sum of the top 100 results in column A that are between value X and value Y, but only for rows that have a certain value in column D? Excel isn't made for that. If you need to combine multiple tables because one table isn't enough to hold all of your data, things are only getting worse. Compared to SQL, Excel would be terribly slow and difficult to maintain, probably can't be accessed by multiple users/instances at once etc.
16th Oct 2018, 6:59 AM
Anna
Anna - avatar
+ 7
the tougher part is going from one single table in Excel to a properly defined relational database with one to many relationships between the tables....but that effort is important because a properly defined relational database is how you minimize redundant data in your system which in turn provides high data integrity, consistency, and reliability
16th Oct 2018, 12:14 PM
Lisa F
Lisa F - avatar
+ 6
I think it's important to note that SQL is a query language to modify and select data from a relational database management system, not a relational database management system itself where the data is actually stored. SQL can select data from Access or SQLserver, Microsoft's rdbms products. SQLserver has much larger storage capabilities and excellent security capabilities. Access works for a handful of trusted users.
16th Oct 2018, 12:09 PM
Lisa F
Lisa F - avatar
+ 5
Akshay You can use Ms Access instead of MS Excel for a simple database with lesser data amount capability then SQL. But if you make a complex database with bunch of data, I suggest SQL (some people will suggest NOSQL). I agree with Anna for the arguments. Later, when you need to analyze with spreadsheet, you can export it to appropriate program, at least CSV.
16th Oct 2018, 9:19 AM
Alfa Yanuar Riansyah
Alfa Yanuar Riansyah - avatar
+ 5
other rdbms such as Oracle and Postgress and Mysql all use SQL to select and modify data. the dialects can be slightly different but only slightly
16th Oct 2018, 12:16 PM
Lisa F
Lisa F - avatar
+ 4
Also if you're not sure which product to use to store your data you can start with a single Excel table and import that data into an Access relational database as you run into performance and data integrity issues. you can import Access tables into SQLServer if Access is not enough.
16th Oct 2018, 12:12 PM
Lisa F
Lisa F - avatar
+ 3
While agreeing emphatically with all prior posts... Technically, you really can use MS Excel as a database. Many people do. I worked for a large company for a couple of years that did this. There was very little data integrity being preserved -- many costly errors, lost work, and repeated work. It was incredibly inefficient, frustrating, and had a huge impact on the bottom line. I remember one particularly annoying performance issue where I felt the task should have been relatively painless (like find a value), but because something could be anywhere in the sheet, MS Excel examines the contents of each cell individually -- which takes so long that I thought my computer froze, whereby I would get up to grab coffee and come back to see (to my surprise) that it had done something (not the whole thing, but something). In addition, MS Excel has character limits per cell, which means data and functions contained in each cell would be truncated or broken. Really, there are many reasons to use SQL over Excel.
16th Oct 2018, 10:46 AM
Janning⭐
Janning⭐ - avatar
+ 3
Akshay all rdbms systems that I have used have a mechanism for viewing table data and an SQL window where you can write SQL to select specific data.
17th Oct 2018, 1:49 PM
Lisa F
Lisa F - avatar
+ 1
you can use anything as anything you want shoes like hat, excel as db, or else in excel don't have queries which is main part of sql interface and command langue in excel don't have fine visualization for 100.000.000 rows as in db managers, in excel you don't have provider to your base for two or more users at once, what is need for write sites or office tools, by the way, web hosts working only with sql and nosql db, no one will let you use excel on host, all world use sql, and you will need solve all this problem to use excel instead of sql. But it's still really, as wear shoe instead of hat. and if you writing a small table with data and using excel to code, i think for a short time while your table not grow, you can strorage it in excel sheet for one user only
17th Oct 2018, 3:52 AM
чел челкоФ
чел челкоФ - avatar
+ 1
I could say more on DB's, but here are some dev hints at / beyond Excel... You *can* use DDE to do all sorts of crazy cool parallel things with Windows apps: https://docs.microsoft.com/en-us/windows/desktop/dataxchg/about-dynamic-data-exchange Or OLE automation via VBA... https://docs.microsoft.com/en-us/office/vba/api/overview/excel But this is Excel's file format spec (ok to skip this reference, just keep going for my point*) https://www.loc.gov/preservation/digital/formats/fdd/fdd000398.shtml Excel documents aren't databases with clustering, granular locking / transactional mechanisms integrated with the OS. They're ZIP archives. * "[documents] can be explored...typically by changing the file extension to .zip."
17th Oct 2018, 5:43 AM
Kirk Schafer
Kirk Schafer - avatar
+ 1
Akshay, I think you should try MSAccess first for understanding how SQL works for making databases and all of their relationships, I did it before I knew what is SQL. There you can also see the data of the table easier as if you saw excel or other spreadsheet apps. You can also program the GUI based on VB that is already attached in MSAccess. SQL is used for storing data which you can access it with other programming language such as PHP, Python, Java, C, CPP, etc. When you program it well, you can see data as if you are using spreadsheet apps. PHPMyAdmin will help you organize it which is already packetted by XAMP etc. Also you can export it to excell or csv format. I remember the old time when I used dot DAT "*.DAT" extension format for saving datas.
26th May 2019, 1:50 AM
Alfa Yanuar Riansyah
Alfa Yanuar Riansyah - avatar
0
Thank you everyone for taking time and answering my question. After I read through your answers I'm bit clear on why excel is a best solution for database uses.
16th Oct 2018, 10:50 AM
Akshay
0
I've another question if it's ok, What I understand is that almost all apps that store data like note taking, expenses management, contacts, file explorer etc.. has database behind it. Am I correct?
16th Oct 2018, 10:51 AM
Akshay
0
Seems right. Data can be stored locally without a database, but a DB can be layered on top of it.
16th Oct 2018, 10:58 AM
Janning⭐
Janning⭐ - avatar
0
MS ACCESS is used to store the database
16th Oct 2018, 11:26 AM
Prasanna kumar
Prasanna kumar - avatar
0
Excel is also a small DB. But there are limitations when working with complex projects
16th Oct 2018, 4:30 PM
Seniru
Seniru - avatar
0
I usually use excell (now I prefer google sheet) for pre and post dB making. 'Pre' means for designing tables I want to look like and what kind of data in it, also how the data to be displayed. 'Post' means I analyze data I gathered from the dB. And FYI, google sheet currently support SQL instruction too. But still, if I want to make a complex dB, I prefer using MS-Access because it is simple and can do inter-relationship between tables; if the datas are no more than 1000's, but if it is too big then I choose mySQL for it is can handle intrer-relationships between tables too. Lately I read about NOSQL with its advantages and disadvantages, but I still learn it deeply. Google uses both of them. You can watch it on "Google SQL vs No SQL" on YouTube.
16th Oct 2018, 10:28 PM
Alfa Yanuar Riansyah
Alfa Yanuar Riansyah - avatar
0
Alfa Riansyah Anna Lisa F so isn't there a database that's having a GUI interface so that we can see the tables like we do in Excel etc. Also, can't we make such GUI interface software to run all SQL commands using buttons etc.
17th Oct 2018, 3:05 AM
Akshay
0
what is the importance of<font color="#ff0000"> SQL<\font>?
17th Oct 2018, 1:31 PM
Nachiket Patil
Nachiket Patil - avatar