How to Import Excel File to MySQL Database

MySQL allows you to import files and store their data into database tables. But it supports importing files in limited formats only. Sometimes you may need to import Excel file to MySQL database. In this article, we will learn how to do this.

How to Import Excel File to MySQL Database

There are several ways to do this. We will learn each of them one by one.

1. Using Third Party tools

MySQL does not provide a built-in command to directly import Excel file to MySQL database. You can use a third-party tool like Sqlizer.io for this purpose.

It requires you to upload your spreadsheet, enter the sheet name and cell range of data to be imported. Then it will automatically generate a CREATE TABLE statement and a set of INSERT statements to import data to MySQL database.

There are several other third party tools for this purpose.

2. Using Data Converter

You can also use Mr. Data Converter to import Excel files to MySQL database. In this case, you need to copy paste the data that you want to import into your MySQL database, into this tool and select the output as MySQL. Then it will automatically generate the SQL statements to create table and insert data into it in MySQL.

You can copy these SQL statements and run them in your MySQL tool such as Workbench, or HeidiSQL. If you don’t use any such tools, just copy paste the queries into a text file ending with .sql extension (e.g. file.sql) and run it using the following command.

mysql database_name < file.sql

3. Using MySQL LOAD Data

MySQL offers LOAD DATA command to import files to databases. But it supports importing text and CSV files only.

In this case the first step is to export the required spreadsheet into tab delimited file or CSV file (e.g. data.csv). Then use LOAD DATA statement to import the file into MySQL database.

LOAD DATA INFILE 'data.csv' INTO TABLE tbl_name
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

If you are using a tab delimited file, then use the following FIELDS TERMINATED BY clause.

FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\'

Of course there are many other ways to import Excel spreadsheet into MySQL database. We have mentioned some of the simple ones. You can use any of them depending on your requirement.

Also read:

How to View MySQL Log Files
How to Run MySQLdump Without Locking Tables
How to Check if MySQL Database Exists
How to Send Email from JavaScript
How to Check if String Starts With Another String in JS