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.

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. 

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.

Method 4: Using MySQL Workbench

Follow the steps below to import Excel data into MySQL using MySQL Workbench.

• Connect to MySQL server by opening MySQL Workbench.
• Select Server -> Data Import in the menu
• Go to Data Import window -> choose Import from self-contained File -> locate the Excel file.
• Next, go to Target Schema -> choose the database to import the data.
• Go to Default Target Object Options -> select to create a new table -> type in a new table name for the imported data.
• Get the cursor to Advanced Options -> choose Use the first row as column names if your Excel file has column headers.
• Type in Start Import to start the import.
• The import process will take sometime to complete depending on the size of the Excel file and the amount of imported data.
• A confirmation message can make sure that its done properly.

You can see the new tables in the database in MySQL Workbench which can be used to view the imported data.

Method 5: Using a Programming Language: Apache or Pandas

A. Using Apache

Run the command given below to install the dependencies: 

pip install apache-airflow[mysql]

Import required libraries by opening a new Python file.

import pandas as pd
from sqlalchemy import create_engine

Use the read_excel() function to move Excel data into a pandas DataFrame :

df = pd.read_excel('filename.xlsx')

Use create_engine() function and create a connection to MySQL db.

engine = create_engine('mysql://username:password@host/database')
Use the to_sql() function to write the DataFrame to the MySQL database
df.to_sql(name='table_name', con=engine, if_exists='append', index=False)

B. Using Pandas:

Run the command to install the required dependencies.

pip install pandas mysql-connector-python

Import the required libraries by opening a new python file.

import pandas as pd
import mysql.connector

Use the read_excel() function to the Excel data into a pandas DataFrame :

df = pd.read_excel('filename.xlsx')

Use mysql.connector.connect() function to create a connection to the MySQL:

connection = mysql.connector.connect(host='hostname',
user='username',
password='password',
database='database')

To import the data into the MySQL table, Create a cursor object -> Execute the SQL query

cursor = connection.cursor()
query = 'INSERT INTO table_name (column1, column2, column3) VALUES (%s, %s, %s)'
for row in df.itertuples():
cursor.execute(query, (row.column1, row.column2, row.column3))
connection.commit()

Based on your use case, update filename, and credentials such as username, password, host, database, table_name, and column1, column2, and column3.

Limitations of Using Manual Methods to Import Excel into MySQL

Manual methods for importing Excel data into a MySQL database have several limitations:

1. File Format Issues: One of the most common issues is related to the file format. MySQL may not support certain Excel file formats, requiring the file to be converted into a compatible format like CSV or XLSX before importing.
2. Data Type Mismatches: Data type mismatches between the Excel file and the MySQL table can cause the import process to fail. For instance, if a column in the Excel file is formatted as text, but the corresponding column in the MySQL table is defined as an integer, the import process may fail.
3. Encoding Issues: If the Excel file uses a different encoding than the MySQL database, special and non-ASCII characters may not be imported correctly.
4. Syntax Errors: Syntax errors in the LOAD DATA statement or SQL queries can cause the import process to fail.
5. Data Cleaning: Manual methods require the data to be clean and correctly formatted before importing. This includes removing unnecessary formatting, ensuring data consistency and accuracy, and checking for any blank or null values.

Remember, these limitations can be mitigated by using automated tools or scripts, ensuring data cleanliness, and choosing the correct file formats and data types before importing.

Learn more about – How to import excel into Bigquery and import excel into PostgreSQL

Method 6: Using Hevo Data’s Automated Data Platform to Import Excel into MySQL

Without the need for converting your .xlsx file to .csv, you effortlessly connect Excel to MySQL using Hevo by following the simple steps given below:

• Step 1: Upload your Excel files into your Google Drive.
• Step 2: Login to Hevo and naviagte to Pipleines > + Create. Select Google Drive as your source and configure your Google Drive account by selecting the authentication method for connecting to Google Drive.

• Step 3: Finally, provide a name for your Pipeline and select all the folders whose Excel files you want to import to MySQL. Then, click on the Continue button.

• Step 4: For completing the process to import Excel into MySQL, you can start by providing your MySQL database credentials such as your authorized Username and Password, along with information about your Host IP Address and Port Number value. You will also need to provide a name for your database and a unique name for this destination.

Expert Tips for Streamlined Excel to MySQL Data Import

Importing Excel data into a MySQL database can be a smooth process with the right approach. Here are expert tips to make the process efficient and error-free:

• Data Preparation: Before importing, ensure your Excel data is well-organized and formatted. Check for errors, inconsistencies, and empty values. Clean formatting will prevent potential import issues.
• Unique Identifiers: Include a unique identifier (like an ID number) for each record in your Excel sheet. This simplifies data management and updates within MySQL.
• Batch Inserts: For large datasets, break the data into smaller groups and import them in batches. This improves import speed and efficiency.
• Automation: Consider using a script or tool to automate the import process. This reduces manual effort and the potential for errors.
• Testing: After the import, carefully test your data within MySQL. Verify that all fields are correct, and make any necessary adjustments.

Additional Resources on Import Excel into MYSQL

• Connect Excel to Postgresql

Conclusion

• In this article, you have learned how to import Excel into MySQL using 4 different methods effectively. Using Method 2, you convert your Excel file rows into SQL queries via sqlizer.io.
• You can also use the LOAD DATA statement or a database administration tool like phpMyAdmin, though these require you first to convert the Excel files into CSV files.

When you need to frequently import Excel files with millions of rows of data in real-time that require complex data transformation, then you can opt for a no-code automated data integration platform like Hevo Data!

FAQ on Import Excel into MySQL Database