Thursday, December 15, 2011

Using mysqldump to export CSV file

By default, mysqldump outputs SQL table dumps.  If you ever need to export a table (or even a database) in CSV format using only mysqldump, here's the quick and easy way without using any additional clients:
mysqldump -u root -p --fields-terminated-by="," --fields-enclosed-by="" --fields-escaped-by="" --no-create-db --no-create-info --tab="." information_schema CHARACTER_SETS
mysqldump will generate 2 files (generated file name is based on the table name):
  • CHARACTER_SETS.txt
  • CHARACTER_SETS.sql
The actual output is in the .txt file.  Output of the command below (output trimmed for brevity):
big5,big5_chinese_ci,Big5 Traditional Chinese,2dec8,dec8_swedish_ci,DEC West European,1cp850,cp850_general_ci,DOS West European,1hp8,hp8_english_ci,HP West European,1koi8r,koi8r_general_ci,KOI8-R Relcom Russian,1latin1,latin1_swedish_ci,cp1252 West European,1latin2,latin2_general_ci,ISO 8859-2 Central European,1[..]
If you get the following error when running the command, specify a location where the "mysql" user (or the owner of the MySQL process) is running can write to (e.g. /tmp).

mysqldump: Got error: 1: Can't create/write to file '/home/mike/CHARACTER_SETS.txt' (Errcode: 13) when executing 'SELECT INTO OUTFILE'
Now for some description on the options:
  • --fields-terminated-by: String to use to terminate fields/columns.
  • --fields-enclosed-by: String to use to enclose the field values.  Single quote by default.  I set to nothing as it suits my needs.
  • --fields-escape-by: Set of string used to escape special characters e.g. tabs, nulls and backspace.  Look here for more info on escape sequences.
  • --no-create-db: Do not print DB creation SQL.
  • --no-create-info: Do not print table creation SQL.
For more comprehensive info on the mysqldump command, visit the reference manual page here.

No comments: