create/export and load/import csv files with mysqldump in mysql May
27
1
0

I'm often asked to create csv files to import into other applications. If you're using MySQL it's handy to be able to create a CSV on the fly with mysqldump. This is easily achieved with the following command:

mysqldump -u <user> -p --tab=<directory> --fields-enclosed-by='"' --fields-terminated-by=',' <database> <table>

This creates a .sql dump file and a .txt csv in the directory that you specified with [--tab]. The second part of this operation (the .txt csv creation) will be performed by the MySQL user, so it'll need to have write permissions in the [--tab] directory. Also, the mysql user needs to have the appropriate permissions (I believe it's the FILE privileges).

Alternatively you can import a csv into the database with the following command while logged into the mysql shell using the appropriate database:

LOAD DATA LOCAL INFILE '/path/to/my.csv' INTO TABLE <table>
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(<field1>, <field2>, <field3>);
Bookmark and Share
blog comments powered by Disqus