How to import csv file in MySQL

Posted on October 17, 2016  •  1 minutes  • 88 words

If your MySQL server started with --secure-file-priv option, you will have to move your csv file to that specific folder first.

To check where that folder is, first run SHOW VARIABLES LIKE "secure_file_priv";

Move your csv file to that folder and issue the following command in MySQL console. Remember to replace /var/lib/mysql-files/ with your secure_file_priv path variable.

LOAD DATA INFILE "/var/lib/mysql-files/data.csv" \
INTO TABLE my_table \
COLUMNS TERMINATED BY ',' \
OPTIONALLY ENCLOSED BY '"' \
ESCAPED BY '"' \
LINES TERMINATED BY '\n' \
IGNORE 1 LINES;