As much as Excel is compatible with many platforms, storing data in MySQL offers more advantages. For example, it’s easier to build an application or a website that interacts with the data on the database. Besides, it’s one of the most popular solutions that integrates easily with other applications.
If you have an Excel sheet and want to store that data in a MySQL database, then this tutorial is for you.
What is MySQL?
MySQL is an open-source relational database management system (RDBMS) that organizes and stores data. It uses Structured Query Language (SQL) to manage databases, enabling users to efficiently store, retrieve, and modify data. MySQL is widely used for various applications, from web content management systems to business applications, due to its reliability, scalability, and ease of use.
Using CSV and SQL code
1. Type your data into an Excel file
2. Clean the data making sure it is arranged into columns
3. Convert the Excel to CSV (Comma delimited)
4. Open PHPMyAdmin and create a new database then a table
5. Make sure to have the number of columns equal to the number of columns in your CSV file
6. Now it’s time to name the columns and define what data type it holds. If the column contains text then select TEXT, if it contains numbers only select INT, if it is a combination of numbers and text you can choose VARCHAR, and so on. You can learn more about data types on this blog.
7. Click on save to create the table automatically.
8. Click on the SQL tab and paste the following code
LOAD DATA INFILE 'C:\\Users\\user\\Desktop\\WEEzy\\sql.csv' INTO TABLE employeedata FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 ROWS;
Important: If your Excel file contains a header row then IGNORE 1 ROWS if it doesn’t have any header then the code should read ignore 0 rows.
9. Click on the Go button located in the bottom right corner
10. Finally your data will be loaded into the MySQL Table you have just created
If you want to convert Excel to a database, this is a simple, reliable way to do it.
Download the practice sheet.
Using an online tool
Online makes it easier for users to convert Excel files to MySQL code. All you need to do is upload the file and click the convert button. Here are some popular and most reliable tools that can convert Excel to MySQL databases.
1. Excelweez Excel to MySQL converter
Excelweez offers different Excel tools to make work easier. Excel to MySQL tool, has been developed using the latest technology ensuring a fast conversion process. Here is how it works:
- Upload File
- Click the convert to SQL button
- Copy the SQL code or download the SQL file
2. Sqlizer
Sqlizer is a website-based tool that focuses on converting files such as XML, CSV, XLSX, JSON, XLS, JSON, and TXT into SQL databases.
1. To convert Excel into SQL using Sqlizer, drag and drop the Excel file to the upload section. Alternatively, you can convert it to CSV first and then upload it.
2. On the next page select the type of database that you want. For Example MySQL, PostgreSQL, SQLServer, or SQLite
3. Tick the appropriate options on the checkboxes.to choose the worksheet, range, and whether your data has a header
3. Click the convert button
4. Finally, copy the SQL code and run it into your database, or download the SQL file and import it to the database.
3. ASPOSE
Aspose is another Popular online tool that you can use to convert an Excel file to MySQL. Here are the steps to follow.
1. Upload your Excel file by drag and drop or by clicking the browse option
2. Tick the appropriate options
3. Click on the convert button
4. Copy the SQL code then copy and run it into the database.
Final Thoughts
Converting an Excel file to a database is very easy. Doing it manually can consume many hours especially if you have a vast amount of data. Luckily, you can use SQL to import data from CSV. Alternatively, you can use online tools to convert and then import the SQL file.
