3 Ways to Make Tab Delimited Files from Your MySQL Table

When it comes to exporting a tab-delimited file from a mysql database table, you have a couple different options without having to resort to heavier programming in Ruby, PHP, Java, etc. Instead, you can use MySQL’s SELECT INTO OUTFILE, run a SELECT statement and redirect the output to a file, or use mysqldump.

I started looking into this because I needed to dump a table with 140,000 rows with over 100 columns into a tab-delimited file, without the assistance of an admin tool with a convenient GUI. So, with a little help from my children’s favorite cartoon, Phineas and Ferb, here are examples of the three methods I came across during my search.

Setup

I created a simple database table to demonstrate the results.

mysql --user=root --password='' -e 'DROP DATABASE phineas_and_ferb;'

mysql --user=root --password='' -e 'CREATE DATABASE phineas_and_ferb;'
mysql --user=root --password='' phineas_and_ferb -e 'CREATE TABLE characters (id INT, name VARCHAR(100), bio VARCHAR(100));' 

mysql --user=root --password='' phineas_and_ferb -e 'INSERT INTO characters VALUES (1, "Phineas", "Thinking up ways to squeeze more fun out of every summer day helps Phineas stay entertained.");'
mysql --user=root --password='' phineas_and_ferb -e 'INSERT INTO characters VALUES (2, "Ferb", "Ferb is the brains behind the gadgets and contraptions.");'
mysql --user=root --password='' phineas_and_ferb -e 'INSERT INTO characters VALUES (3, "Perry the Platypus", "This pet platypus hides a secret life as crime-fighting agent P!");'

Database Table

+------+--------------------+----------------------------------------------------------------------------------------------+
| id   | name               | bio                                                                                          |
+------+--------------------+----------------------------------------------------------------------------------------------+
|    1 | Phineas            | Thinking up ways to squeeze more fun out of every summer day helps Phineas stay entertained. |
|    2 | Ferb               | Ferb is the brains behind the gadgets and contraptions.                                      |
|    3 | Perry the Platypus | This pet platypus hides a secret life as crime-fighting agent P!                             |
+------+--------------------+----------------------------------------------------------------------------------------------+

Method One: SELECT INTO OUTFILE

Using MySQL’s SELECT INTO OUTFILE feature, you can direct your query’s results to a file using some additional parameters to format the content. I needed to do this in two steps in order to get the column headers at the top of the file.

Command

mysql --user=root --password='' -e "SELECT GROUP_CONCAT(COLUMN_NAME SEPARATOR '\t') FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema='phineas_and_ferb' and table_name='characters' INTO OUTFILE '~/tmp/output.txt' FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '' ESCAPED BY '' LINES TERMINATED BY '\n';"

mysql --user=root --password='' phineas_and_ferb -e "SELECT * FROM characters INTO OUTFILE '~/tmp/data.txt' FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\n';"

cat ~/tmp/data.txt >> ~/tmp/output.txt

Results

id	name	bio
1	"Phineas"	"Thinking up ways to squeeze more fun out of every summer day helps Phineas stay entertained."
2	"Ferb"	"Ferb is the brains behind the gadgets and contraptions."
3	"Perry the Platypus"	"This pet platypus hides a secret life as crime-fighting agent P!"

Advantage: Optional quoting of output fields allows integers to be interpreted correctly by applications importing the data.
Disadvantages: Adding column headers requires an extra command and the use of a temp file. The queries are more complicated than other methods.

Method Two: Redirect query results to file

Execute a simple query against the database table and redirect it to an output file.

Command

mysql --user=root --password='' --column-names=TRUE phineas_and_ferb -e "SELECT * from characters;" > ~/tmp/output.txt

Results

id	name	bio
1	Phineas	Thinking up ways to squeeze more fun out of every summer day helps Phineas stay entertained.
2	Ferb	Ferb is the brains behind the gadgets and contraptions.
3	Perry the Platypus	This pet platypus hides a secret life as crime-fighting agent P!

Advantages: Column headers are automatically included in the output. Results are automatically tab-delimited.
Disadvantage: None of the output fields are quoted.

Method Three: mysqldump

Run mysqldump to directly write the data to a file. Again, I included an additional command to get the column headers at that top of the output file.

Command

mysql --user=root --password='' -e "SELECT GROUP_CONCAT(COLUMN_NAME SEPARATOR '\t') FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema='phineas_and_ferb' and table_name='characters' INTO OUTFILE '~/tmp/output.txt' FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '' ESCAPED BY '' LINES TERMINATED BY '\n';"

mysqldump --single-transaction --user=root --password='' -T ~/tmp/ phineas_and_ferb --fields-enclosed-by=\"

cat ~/tmp/characters.txt >> ~/tmp/output.txt

Results

id	name	bio
"1"	"Phineas"	"Thinking up ways to squeeze more fun out of every summer day helps Phineas stay entertained."
"2"	"Ferb"	"Ferb is the brains behind the gadgets and contraptions."
"3"	"Perry the Platypus"	"This pet platypus hides a secret life as crime-fighting agent P!"

Advantage: Simplified method to get quoting around output fields.
Disadvantages: All output fields are quoted. Adding column headers requires an extra command and the use of a temp file.

My Chosen Method

I ended up going with Method One, wrapping up the multiple mysql invocations in a Bash script. Since the file was destined to be opened in a Spreadsheet, and the optional quoting makes the import process nice and easy.

Additional Information

Using INTO OUTFILE to create CSV files
Using mysqldump to create CSV files
Using SELECT’s INTO OUTFILE
GROUP BY aggregate function GROUP_CONCAT
Demo database information supplied by Disney’s Phineas and Ferb