Export MySQL data into CSV or PSV files

There are several ways to export data from MySQL tables. Could use mysqldump, then remove SQL statements from the dump file. An easier way though is to pipe SQL SELECT output to a file.

Export by piping output of SELECT to a file

For CSV (comma delimited, enclosing content in quotes):

foo@bar:~$ mysql my_db -e "SELECT * FROM my_table" | sed 's/\t/","/g;s/^/"/;s/$/"/;' > my_db.my_table.csv

Example output:

"column1","column2"
"value1","value2"

Pipe is probably a better delimiter (unless your content includes pipes):

foo@bar:~$ mysql my_db -e "SELECT * FROM my_table" | sed 's/\t/|/g' > my_db.my_table.psv

Example output:

column1|column2
value1|value2

Tab delimited output is even easier:

foo@bar:~$ mysql my_db -e "SELECT * FROM my_table" > my_db.my_table.tsv

Example output:

column1	column2
value1	value2

Shell script to export all tables:

#!/bin/bash
 
db=YOUR_DB
user=YOUR_USER
pass=YOUR_PASS
 
for table in $(mysql -u$user -p$pass $db -Be "SHOW tables" | sed 1d); do
  echo "exporting $table.."
  mysql -u$user -p$pass $db -e "SELECT * FROM $table" | sed 's/\t/|/g' > $db.$table.psv
done

Export using SELECT INTO OUTFILE method

mysql my_db -e "
  SELECT * 
  INTO OUTFILE 'mytable.psv'
  FIELDS TERMINATED BY '|'
  LINES TERMINATED BY '\n'
  FROM mytable
"

The cool thing about this method, is that the reverse is also possible, i.e. load (import) data into MySQL DB from a csv/tsv/psv file (without having to insert SQL statements around the data):

mysql my_db -e "
  LOAD DATA INFILE 'mytable.psv'
  INTO TABLE mytable 
  FIELDS TERMINATED BY '|' 
  LINES TERMINATED BY '\n'
"

UPDATE: Here's a script to export every table using the SELECT INTO OUTFILE method.

4 Comments

  • 1. Dave replies at 22nd July 2011, 5:15 am :

    Really usefull, thanks
    just a correction in the shell script:

    for table in $(mysql -u$user -p$pass $db -Be "SHOW tables" | sed 1d); do
     echo "exporting $table.."
     mysql -u$user -p$pass $db -e "SELECT * FROM $table" | sed 's/\t/|/g' > $db.$table.psv
    done
  • 2. Alain Kelder replies at 22nd July 2011, 11:03 am :

    Thanks for catching that, Dave! Must have worked for me because I had credentials in “~/.my.cnf”..

  • 3. Florian replies at 10th July 2012, 11:54 pm :

    Excellent!!! Thanks!

  • 4. Jose Tapia replies at 5th November 2012, 8:45 pm :

    Great article, very simple, very kind, excelent, thanks for share my friend

Leave a comment

NOTE: Enclose quotes in <blockquote></blockquote>. Enclose code in <pre lang="LANG"></pre> (where LANG is one of these).