How To Export and Import of csv data out of and into MySQL

This method works for the MySQL 5  and not sure about other versions.

Exporting the MySQL data into a comma delimited (.csv) file

SELECT * INTO OUTFILE ‘ /data.csv’ FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘”‘ LINES TERMINATED BY ‘\n’ FROM table1;

This will dump your table into a file with each row from the database being on it’s own line, columns separated by commas, and if there are any spaces or funny business going on with the data in a field, the data will be enclosed in double quotes. You can make changes to this if you like by, for instance, substituting ‘,’ with ‘\t’ would result in a tab delimited file.

Importing the comma delimited (.csv) file back into MySQL

First thing, you’ll need to clear out your old table (otherwise the keys would collide):

DELETE FROM table;

Then you can proceed with the import:

LOAD DATA INFILE ‘ /data.csv’ INTO TABLE table1 FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘”‘ LINES TERMINATED BY ‘\n’;

No related posts.

Tags: Export CSV file to MySQL database, Export mysql data to CSV file, Export or Import csv data out of and into MySQL, Import csv file into MySQL, LOAD DATA example, LOAD DATA INFILE, Load data query, LOAD DATA syntax, LOAD DATA usage, SELECT * INTO OUTFILE