Home » MySQL Basics » Import CSV File into MySQL Table

Import CSV File into MySQL Table

Summary: in this tutorial, you will learn how to import a CSV file into a MySQL table using the LOAD DATA INFILE statement and MySQL Workbench.

1) Importing a CSV file on the MySQL server into a table using LOAD DATA INFILE statement

The LOAD DATA INFILE statement allows you to read data from a CSV file in a specified directory on the MySQL server and import its contents to into a table.

Before importing the file, you need to prepare the following:

  • A table that you want to import data into.
  • A CSV file with data that matches the number of columns of the table and the type of data in each column.
  • A MySQL user account that has FILE and INSERT privileges.

Suppose you have a table called discounts with the following structure:

discounts table

And the following  discounts.csv file contains the first line as column headings and the other three lines of data:

id,title,expired date,amount
1,Spring Break,2014-01-01,20
2,Back to School,2014-09-01,25
3,Summer Holiday,2014-08-25,10Code language: PHP (php)

To import the discounts.csv file into the discounts table, you follow these steps:

First, open the Command Prompt on Windows or the Terminal on Unix-like systems and connect to the MySQL server:

mysql -u root -p

Second, change the current database to classicmodels:

use classicmodels;Code language: PHP (php)

Third, create a discounts table:

CREATE TABLE discounts (
    id INT NOT NULL AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    expired_date DATE NOT NULL,
    amount DECIMAL(10 , 2 ) NULL,
    PRIMARY KEY (id)
);Code language: PHP (php)

Fourth, show the value of the @@secure_file_priv variable:

SELECT @@secure_file_priv;Code language: CSS (css)

Output:

+------------------------------------------------+
| @@secure_file_priv                             |
+------------------------------------------------+
| C:\ProgramData\MySQL\MySQL Server 8.0\Uploads\ |
+------------------------------------------------+
1 row in set (0.00 sec)Code language: JavaScript (javascript)

The secure_file_priv option indicates the directory where you are allowed to store the input file and execute it with the LOAD DATA INFILE statement.

Fifth, copy the discounts.csv file to the directory specified by the secure_file_priv option.

Sixth, import data from the discounts.csv file into the discounts table by executing the following statement:

LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/discounts.csv' 
INTO TABLE discounts 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;Code language: SQL (Structured Query Language) (sql)

Output:

Query OK, 3 rows affected (0.01 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0Code language: CSS (css)

The output indicates that the statement has loaded the file with three rows successfully.

Here’s how the LOAD DATA INFILE works:

  • LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/discounts.csv': Specifies the path to the input file (discounts.csv) that contains the data to be loaded into the table.
  • INTO TABLE discounts: Specifies the target table (discounts) where you want to load the data
  • FIELDS TERMINATED BY ',': Specifies that the fields in the input file are separated (terminated) by a comma (,). This indicates that the file is a CSV (Comma-Separated Values) file.
  • ENCLOSED BY '"': Specifies that the fields in the input file are enclosed by double quotation marks ("). This is common in CSV files to handle cases where a field may contain the delimiter ,.
  • LINES TERMINATED BY '\n': Specifies that each line in the input file is terminated by a newline character (\n). This indicates the end of a record (row) in the CSV file.
  • IGNORE 1 ROWS: Instructs the statement to ignore the first row in the input file. This is useful when the first row contains headers and should not be imported as data.

Finally, retrieve the data from the discounts table to verify the import:

SELECT * FROM discounts;

Output:

+----+----------------+--------------+--------+
| id | title          | expired_date | amount |
+----+----------------+--------------+--------+
|  1 | Spring Break   | 2014-01-01   |  20.00 |
|  2 | Back to School | 2014-09-01   |  25.00 |
|  3 | Summer Holiday | 2014-08-25   |  10.00 |
+----+----------------+--------------+--------+
3 rows in set (0.00 sec)Code language: JavaScript (javascript)

Notice that if you don’t place the file in the directory specified by the secure_file_priv variable, you’ll get the following error:

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statementCode language: JavaScript (javascript)

Transforming data while importing

Sometimes the format of the data does not match the target columns in the table. In simple cases, you can transform it by using the SET clause in the  LOAD DATA INFILE statement.

Suppose the expired date column in the  discount2.csv file is in the  mm/dd/yyyy format:

id,title,expired date,amount
1,Spring Break,01/01/2014,20
2,Back to School,09/01/2014,25
3,Summer Holiday,08/25/2014,10Code language: PHP (php)

When importing data into the discounts table, you can transform it into MySQL date format by using STR_TO_DATE() function:

First, truncate the discounts table:

TRUNCATE TABLE discounts;

Second, transform and load data from the discount2.csv file into the discounts table:

LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/discounts2.csv'
INTO TABLE discounts
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(id, title, @expired_date,amount)
SET expired_date = STR_TO_DATE(@expired_date, '%m/%d/%Y');Code language: SQL (Structured Query Language) (sql)

2) Importing a CSV file from a local computer to a table on a remote MySQL server

The LOAD DATA INFILE allows you to import a CSV file from your local computer to a table in a remote MySQL server via the LOCAL option.

Configuration

MySQL disables loading a local file to the server by default. To load a local file to the MySQL server, you need to enable the option on both the client and server sides.

The local_infile controls whether the client and server permit the use of the LOCAL option in the LOAD DATA INFILE statement.

First, open the MySQL configuration file (my.ini or my.cnf) and add the following line to enable loading the local file on the MySQL server:

[mysqld]
local_infile=1

Second, restart the MySQL server to apply the change.

Third, open the mysql client program on the local computer to connect to the MySQL server

mysql -h hostname -u root -p

You need to replace the hostname with your remote MySQL server.

Fourth, set the global variable local_infile to 1 (or ON):

SET GLOBAL local_infile = 1;Code language: PHP (php)

We have configured the local_infile on both the server and client.

Loading the local file

First, change the current database to the one that contains the discounts table:

USE classicmodels;Code language: PHP (php)

Second, execute the LOAD DATA INFILE statement to import data from a CSV file located in the C:\temp\ directory to the MySQL server:

LOAD DATA LOCAL INFILE  'c:/temp/discounts.csv'
INTO TABLE discounts
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;Code language: JavaScript (javascript)

The only addition to the LOAD DATA INFILE statement is the LOCAL option.

If you load a big CSV file, you will see that with the LOCAL option, it will take time to transfer the file to the MySQL server.

Note that if you don’t configure both client and server properly, you will get the following message:

ERROR 3948 (42000): Loading local data is disabled; this must be enabled on both the client and server sidesCode language: JavaScript (javascript)

3) Importing CSV files into a table using MySQL Workbench

MySQL workbench provides a tool to import data into a table

First, open the discounts table:

Second, click the import button:

Third, select the path to the CSV file and click the Next button:

Fourth, select the destination table, which is the classicmodels.discounts in this case. Note that you can create a new table before importing the file and/or truncate the table before import:

Fifth, map the columns in the source file with the columns in the destination table and click the Next button:

Sixth, review the steps that the Workbench will do and click the Next button:

Seventh, review the steps that the Workbench will do and click the Next button:

Eight, review the import result and click the Finish button:

Finally, show the contents of the discounts table:

Summary

  • Use the LOAD DATA INFILE statement to import a CSV file into a table.
  • Use MySQL Workbench to import a CSV file from the local computer to a table on a remote MySQL server.
Was this tutorial helpful?
MySQL Collation
Previously
MySQL Collation
Up Next
MySQL Export Table to CSV
MySQL Export Table to CSV