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