Import CSV File Into MySQL using LOAD DATA INFILE
Posted by navaneeth on Feb 27, 2015 in General, MySQL, Php | 2 Comments

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”
prathyusha
October 9, 2015csv file not supporting some mathematical symbol.
Any solutions?
navaneeth
December 9, 2015Try to add the CHARACTER SET UTF8, this helps you to add symbols