Excel V/S MySQL
- What can SQL do that Excel cannot?
When we think of data and how we store it; most of us generally use Excel to save our data and store it on our local system or a cloud. It so happens that 1048576 and 16384 columns are more or so highly adequate for our information storage requirements. SQL files can go up to 64 terabytes at times and that’s roughly 1 trillion rows of data but what is the point when we’ve got Excel which already is excess of our requirements! What is so different in SQL that makes it better or worse than Excel? This question is what we’ll try to answer in this article.
Note: For this article, we’ll consider the basic differences of MySQL and Excel and try to analyze which one to prefer in which situation. This is not based on an in depth study of both but situations that are routinely encountered by front-end users of these applications.
Let’s go over a very basic understanding of the two:
What is Excel?
Excel is a spreadsheet software which comes as a part of the Microsoft Office tools; the latest being Office 365. The back-end language used in Excel is VBA i.e. Visual Basic for Applications. There are multiple built-in functions which help users perform a multitude of tasks using Excel. This is used by almost everyone around the world.
What is MySQL?
MySQL is a Relational Database Management System which uses SQL i.e. Structured Query Language in the back-end. This is similar to other database management systems like PostgreSQL, Oracle Database and Ms-Access. There are commands (called queries) in SQL which returns required information from multiple tables present in databases which can be related to each other. Though not used as much as Excel, it is still a very viable option to store information.
We’ll now try to differentiate between the two on the following parameters:
- Ease of Access
- Data & Storage
- Availability
- Data Constraints
- Compatibility with other tools
- Features
Disclaimer: This is not a comparison meant to prove which one’s better off the two. They’re both amazing tools to have in your arsenal which depends on the kind of data you need to store and analyze. Knowing the differences will only help us in understanding which one can be used in our daily life when a need arises.
EASE OF ACCESS:
What is easier to use if you have to enter a lot of information into a table or spreadsheet? Well, it depends. If you have random data to be entered in a table, Excel is the way to go. It’s spreadsheet functionality makes it easy to use. The rows and columns are predefined and data can be entered pretty much anywhere and as needed. It can have data apart from the fields specified in a table. Where as if you have data that has to go into fields specific to your tables and you cannot have any other data lying around with multiple constraints, MySQL is a better choice.
Technically, you can enter data into Excel’s spreadsheets the moment you open it and you can save it onto a cloud or your local system within seconds. Using SQL on the other hand you’ll first have to create a Database and then create a table and enter data into the tables one row after another without skipping any row or column easily. There is also additional time that’s required to enter data using the syntaxes as such.
Also, one will need to learn SQL in order to use MySQL while there is no such requirements to work with Excel. Hence, Excel is clearly better if you have to enter data on the go without any constraints or restrictions to make it clean data (as done using SQL and discussed further in this article).
DATA & STORAGE:
While data types in SQL uses data such as integers, variable characters, characters or decimals etc. Excel can accept almost any kind of data in its cells that contains everything that you see on a keyboard but there are some things that are to be noted here. Excel might be useful if you don’t have any specific type of data to be entered in fields; for example, name would use text or contact numbers would be integers. But in case you do have such constraints on your data types then SQL is the way to go.
Another thing to note is, if you have images to be entered into your table then SQL would not be a viable option because Excel has an easier way to accept such kind of data too.
The storage of data is done in terms of the data types (varchar has a greater size as compared to char) in SQL and each entry doesn’t exceed certain bytes and the same thing in excel happens over the number of entries and the size of the files can vary between the two. A large amount of data would be significantly larger in excel as compared to SQL (since it uses specific data types).
It is also easier to handle data when it is in a database like SQL because you can have a database for a certain number of tables while you have to maintain sheets for each one of them in order to view them in Excel and you cannot connect them or relate them to each other as we’ll understand ahead in the article.
AVAILABILITY:
When you think about the availability of these software then only one thing comes to mind that pretty much everyone accesses Excel and if you have your files in Excel then sharing them is easy whereas if you have a SQL database then it is important to know that when you share the data with someone they will have to have a database management system in order to access your database.
It may also happen at your work that everyone uses SQL and thus file sharing in this scenario would be easier. There is not much apart from this in the availability criteria and it comes down to who you are sharing your data with in order to choose Excel or SQL Databases. Generally Excel files are used in order to share data owing to the lack of knowledge of individuals on SQL.
DATA CONSTRAINTS:
While it might be possible to enter only a particular type of data in an Excel field using VBA or conditional formatting, SQL performs comparatively better in this scenario owing to its queries which need users to specify the kind of data that can be entered in a particular field. For example if a query specifies a field to be CHAR then only alphabetical characters can be entered into this field.
You can also set a default value in case a value is missing in a field and it is also easy to define a primary key and foreign key which can in turn build relationships between tables. Your primary key in a table defines a unique identifier while the foreign key defines a relationship of the current table with a unique identifier based out of another table. These relationships can make it very easy to develop a database schema where one table is connected with another table and it also allows cascade deletion of data which is not possible in Excel. This is one of the major reasons why some companies and individuals prefer using SQL in place of Excel.
These data constraints are difficult to perform in Excel and will have to be updated every time a new entry is added unlike SQL where the rules apply dynamically to any new entry in the table (irrespective of the number of entries).
COMPATIBILITY WITH OTHER TOOLS:
Both these tools are highly compatible with a lot of other tools such as Tableau, power BI, Python etc. where files can be uploaded without any restrictions. To upload files to other tools using SQL it is important to have the tool connect with the SQL server where the files are hosted. Excel files, on the contrary, are the most commonly used sources of data in many tools with files using an .xlsx or .csv extensions (for a comma separated variables file). SQL servers can also be used to upload tables with their relationships unlike Excel where the relationships were have to be defined again.
However this comes down to the kind of data sources that are used in your work as both these tools can host a lot of data and can export them equally well. In terms of time consumption in uploading these files from the respective sources, Excel consumes lesser time as it provides a direct file upload when compared to SQL databases that require information about the SQL servers to be mentioned.
FEATURES:
The major difference finally comes out of this factor i.e. the features offered by these tools. Excel built-in functions and features are vast and can perform a multitude of tasks such as statistical functions, financial functions, data visualization and also custom functions where as in MySQL which is database management system there are a lot of functions that are available but minimized to handling the data and manipulating it easily.
Excel also allows users to quickly create pivot tables and charts to visualize the data which is not possible in case of SQL. Sometimes data can be sourced into excel using SQL giving it a greater flexibility. Another major difference comes with the use of macros which is possible in excel using VBA. If you don’t have a very large data set then these features can be afforded compromising the file size in Excel.
While it might be difficult to extract data out of Excel it is significantly easier to perform operations on the data using Excel and the opposite goes for MySQL. If your work needs you to only work on data extraction and manipulation only, then MySQL is a better choice to use. But what if your work asks you to perform calculations and derive analytics out of a dataset? Then, Excel clearly is the winner.
Overall, SQL is better in terms of only storing data and manipulating it as and when required using MySQL and Excel fares better in terms of data analytics and calculations. SQL is a strong tool and can help you in your data science career definitely! You can also connect SQL to multiple languages and can prepare applications and forms to connect and store data directly into your database (for starters!).
Go ahead and check out the official documentation of MySQL for more details on how it works:
For more such articles, stay tuned with us as we chart out paths on understanding data and coding and demystify other concepts related to Data Science and Coding. Please leave a review down in the comments. It was a long article, thank you very much for reading it all the way here! Great going!
