In this post we are discussing on how to use LOAD DATA INFILE statement to import CSV file into MySQL table.

The following statement imports data from c:\tmp\data.csv file into the discounts table.

LOAD DATA INFILE 'c:/tmp/data.csv' 
INTO TABLE discounts 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

The field of the file is terminated by a comma indicated by FIELD TERMINATED BY ‘,’ and enclosed by double quotation marks specified by ENCLOSED BY ‘”‘.

Each line of the CSV file is terminated by a new line character indicated by LINES TERMINATED BY ‘\n’.

Because the file has the first line that contains the column headings, which should not be imported into the table, therefore we ignore it by specifying IGNORE 1 ROWS option.

Transforming data while importing

Sometimes the format of the data does not match with 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 date column in the data.csv file is in mm/dd/yyyy format.

When importing data into the discounts table, we have to transform it into MySQL date format by using str_to_date() function as follows:

LOAD DATA INFILE 'c:/tmp/data.csv'
INTO TABLE discounts
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(title,@date,amount)
SET date= STR_TO_DATE(@date, '%m/%d/%Y');

Hope this will help to import data from csv to mysql database

2 Comments on “Import CSV File Into MySQL using LOAD DATA INFILE

  1. prathyusha
    October 9, 2015

    csv file not supporting some mathematical symbol.
    Any solutions?

    • navaneeth
      December 9, 2015

      Try to add the CHARACTER SET UTF8, this helps you to add symbols

Leave a Reply

Your email address will not be published.