How to Import Excel into MySQL Database?

Migration from Excel into MySQL – The ease of access, power formulas, and the ability to make visually stunning reports has made Microsoft Excel a widely used tool.

Table of Contents

1. However, managing thousands of sheets can be a time-consuming and resource-intensive task that may also lead to some errors.
2. A good solution is to import your Excel files to a scalable, reliable, and fast relational database management system like MySQL.
3. You can import Excel into MySQL either by using simple SQL commands like LOAD DATA or opt for third-party tools.
4. By importing your files from Excel to MySQL, you can leverage the powerful MySQL engine to quickly query data and perform complex data manipulations.
5. In this article, you will learn how to effectively import Excel to MySQL using 4 different methods.

What is Excel?

Excel is a powerful spreadsheet program developed by Microsoft. It is a part of the Microsoft Office suite of productivity software. Excel allows users to create, manipulate, and analyze data in tabular form. It provides a wide range of functions and tools for tasks such as calculations, data visualization, charting, and data analysis.

What is MySQL?

MySQL is an open-source relational database management system (RDBMS) that is widely used for managing and storing data. It is one of the most popular databases in the world, known for its reliability, scalability, and ease of use. MySQL is commonly used in web applications to store and retrieve data efficiently.

Why Should You Import Excel into MySQL?

• Microsoft Excel is an easy-to-use spreadsheet application that allows you to directly access data and perform a combination of operations on this data.
• As you collect data and store it in your separate Excel files, it becomes a huge task to track each sheet with millions of records in it.
• To simplify this, you need to learn how to import Excel files into MySQL Workbench to connect both applications.
• MySQL is a scalable, fast, and reliable database management system that allows you to store and query millions of rows of data using simple SQL commands.
• Using MySQL, you can also connect your Excel tables with one another tables using primary and foreign keys.
• MySQL can easily handle large datasets and allows you to manipulate data, filter data, update data, and combine it. You can connect Excel to MySQL and enjoy better speed, accuracy, & consistency for your ever-growing data needs.
• Moreover, MySQL offers strong security measures to safeguard data confidentiality and integrity. Additionally, it provides advanced querying and reporting functionalities, enabling valuable data insights for informed decision-making, trend identification, and performance tracking in businesses.

Hevo Data is a No-code Data Pipeline solution that can help you move data from 150+ data sources (including 50+ free sources like Google Drive & Google Sheets) to your desired destination such as MySQL, Data Warehouses, or BI tools in a completely hassle-free & automated manner. Using Hevo you can easily upload your Excel files present on your Google Drive to your MySQL Database.

Hevo also supports MySQL as a source for loading data to a destination of your choice. Using Hevo, you no longer have to worry about how to import Excel file into MySQL workbench.

How to Import Excel File in MySQL: 6 Easy Methods

Method 1: Using Sqlizer.io to Import Excel into MySQL

To connect and convert Excel to MySQL, you can use Sqlizer.io to convert your useful MS Excel files into SQL commands. You can achieve this by following the simple instructions given below:

• Step 1: Click on the Browse button and select the Excel file you want to import to MySQL.
• Step 2: Select MySQL as your desired database. According to your Excel file, check or uncheck My File has a Header Row.
• Step 3: Based on your Excel file, check Use CHECK IF TABLE EXISTS. For using the active worksheet, check the Use the active worksheet box.
• Step 4: You can also choose to enter the Worksheet name. For this example, the worksheet name Students is mentioned.
• Step 5: To import and convert Excel to MySQL, you can either provide the data cell range or check the Convert the whole worksheet box to import the whole data.
• Step 6: Finally, name the table in which to import Excel into MySQL. Then, click on the Convert My File button.

• Step 7: After your file is converted, you will get the following message. Now, download the queries or you can even copy the queries to execute them on MySQL.

After the queries have been executed, you can view your data in MySQL using the SELECT command.

Method 2: Using the Load Data Statement to Import Excel into MySQL

Using the LOAD DATA Statement, you can also connect Excel to MySQL. To do that, follow the simple steps given below:

• Step 1: You have to first convert your .xlsx file to .csv file. For that, open up the Excel file that you need to import.
• Step 2: Navigate to File > Save As. Select CSV (Comma delimited)(*.csv) as your file type and click on the Save button.

• Step 3: Now, enter the LOAD DATA command shown below to import data from Excel to MySQL Workbench.

# MySQL Version 8.0.27 LOAD DATA INFILE 'C:/ProgramFiles/MySQLExcel/import excel into mysql.csv' INTO TABLE new_file FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY 'n' IGNORE 1 ROWS; 

In the above process to import an Excel file in MySQL workbench, the .csv file data will be added to the new_file table in your MySQL database without the first row as it contains the row headers.

For this example, the .csv file uses a comma as the delimiter to separate 2 fields. You can also use FIELDS TERMINATED BY ‘t’ or FIELDS TERMINATED BY ‘ ‘ if your file’s fields are separated using a tab or a single space respectively.

Alternatively, you can use the LOAD DATA command directly from the Command Line (CMD).

mysql> load data infile -> 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/import into mysql.csv' -> into table tb_students_cmd -> fields terminated by ',' -> enclosed by '"' -> lines terminated by '\n' -> ignore 1 rows; Query OK, 10 rows affected (0.27 sec) Records: 10 Deleted: 0 Skipped: Warnings: 0 mysql>

Method 3: Import Excel into MySQL using phpMyAdmin

You can also import Excel into MySQL using phpMyAdmin by first converting your .xlsx files to .csv files. For that, you have to first download and phpMyAdmin tool on your system. For this example, a new table named “tb_students_phpmyadmin” has been created in the MySQL Database. To import Excel into MySQL, you can follow the simple steps given below:

• Step 1: In phpMyAdmin, click on the Import tab and choose your desired .csv file.
• Step 2: Now, enter the format-specific options and once you are done, click on the Go button present in the bottom right corner of your screen.

After clicking on the Go button, you will observe the data import confirmation messages for all your rows.

• Step 3: Using the SELECT statement, you can check if the process to import Excel into MySQL was successful or not.