How to Import Excel File Into a MySQL Database

Mehvish Ashiq Feb 02, 2024
  1. Use the LOAD DATA Statement to Import Data
  2. Method 2: Use Sqlizer.io to Import Data
  3. Method 3: Import Data Using phpMyAdmin
  4. Conclusions
How to Import Excel File Into a MySQL Database

This article will teach how to import an excel file into a MySQL database using Workbench, Command-Line Prompt, and phpMyAdmin. This tutorial will take you through a step-by-step guide of the top 3 easiest ways to import excel files into the MySQL databases.

Use the LOAD DATA Statement to Import Data

Using the LOAD DATA statement in MySQL Workbench, you must have an empty table in your MySQL database.

We have already created a table named tb_students. This table has id, firstname, lastname, gender, and city as column names.

We will convert our file type from .xlsx to .csv to import data into the MySQL database. To do this, open your excel file that has the data.

Click on File->Save As. Make sure that you have selected CSV (Comma delimited)(*.csv) and then press the SAVE.

See the following screenshot.

top 3 easiest ways to import excel file into mysql database - save as dot csv

You now have your data in the *.csv file. We will use the command below to import data from the .csv file into the MySQL database (using Workbench).

# MySQL Version 8.0.27
LOAD DATA INFILE
'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/top 5 easiest ways to import excel file into mysql database.csv'
INTO TABLE tb_students
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

In the above LOAD DATA statement, the data from the given file will be collected and inserted into the mentioned table. The first row will be ignored (the column names) because we already have them while creating a table in MySQL Workbench.

In this example, the .csv file’s fields are terminated by a comma. Yours may be terminated by a tab or single space.

If so, replace the line FIELDS TERMINATED BY ',' with FIELDS TERMINATED BY '\t' or FIELDS TERMINATED BY ' ' respectively. Let’s look at the *.csv file’s data and then the MySQL data that are given below.

Data in .CSV File:

top 3 easiest ways to import excel file into mysql database - csv file data

Data in MySQL Table:

top 3 easiest ways to import excel file into mysql database - table data in mysql

You can also use the LOAD DATA statement from Command Line (CMD). We have created a new table named tb_students_cmd to practice the command line’s LOAD DATA statement.

top 3 easiest ways to import excel file into mysql database - new table for cmd

Use the LOAD DATA statement to import data via a command-line prompt.

top 3 easiest ways to import excel file into mysql database - load data command in cmd

Let’s read the id from tb_students_cmd to confirm that the data is imported. See the following screenshot.

top 3 easiest ways to import excel file into mysql database - load data output in cmd

If you see any error about security considerations while using the LOAD DATA statement, then you may visit this website to see the possible solutions.

Method 2: Use Sqlizer.io to Import Data

This method is useful if you don’t want to convert your file type from .xlsx to .csv. Go to sqlizer.io, follow the instructions as follows (or as per your needs).

Select your excel file and MySQL database type. Tell the sqlizer.io by checking or unchecking My File has a Header Row.

Check Use CHECK IF TABLE EXISTS if you want to. If you want to use the active worksheet, check Use the active worksheet.

If not, then tell the worksheet name. We are not using it for this tutorial and prefer to tell the worksheet name, students.

Make sure to tick Convert the whole worksheet to import the whole data. Otherwise, give it the cell range.

You can write your table name (give it a unique name) in which you want the data to be imported. Then, click on the Convert My File button.

top 3 easiest ways to import excel file into mysql database - sqlizerio

You will see the following screen. You can either download the queries or copy them to execute wherever you want.

top 3 easiest ways to import excel file into mysql database - sqlizerio queries

We can see the following screenshot to confirm that the data is imported, and we can read that.

top 3 easiest ways to import excel file into mysql database - sqlizerio queries data

Method 3: Import Data Using phpMyAdmin

If you are using phpMyAdmin, then you can import data using the following steps (see the given screenshots).

To practice, we have created a new table named tb_students_phpmyadmin. It has five columns named id, firstname, lastname, gender, and city.

Click on the Import tab and select your *.csv file. The Format would be CSV, but you can select according to requirements.

Provide Format-specific Options and click on Go at the bottom-right corner.

top 3 easiest ways to import excel file into mysql database - import data using phpmyadmin part 1

top 3 easiest ways to import excel file into mysql database - import data using phpmyadmin part 2

You can see that all the records are being imported inserted in the table named tb_students_phpmyadmin as follows.

top 3 easiest ways to import excel file into mysql database - import data using phpmyadmin part 3

Let’s see whether the data is imported or not. And here it is! We have imported data.

top 3 easiest ways to import excel file into mysql database - import data using phpmyadmin part 4

Conclusions

Finally, we have concluded that you have to use different methods to import data into MySQL.

We saw two methods for importing data from the .csv file to MySQL. It includes the LOAD DATA statement (in workbench and command line) and using phpMyAdmin.

We used sqlizer.io to import data from excel files to MySQL, which is very easy to use also.

Mehvish Ashiq avatar Mehvish Ashiq avatar

Mehvish Ashiq is a former Java Programmer and a Data Science enthusiast who leverages her expertise to help others to learn and grow by creating interesting, useful, and reader-friendly content in Computer Programming, Data Science, and Technology.

LinkedIn GitHub Facebook

Related Article - MySQL Workbench

Related Article - MySQL Import