Export MySQL data into CSV or PSV files

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'
"