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!");'

view rawgistfile1.sh hosted with  by GitHub

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! |
	+------+--------------------+----------------------------------------------------------------------------------------------+

view rawgistfile1.txt hosted with  by GitHub

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

view rawgistfile1.sh hosted with  by GitHub

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

view rawgistfile1.txt hosted with  by GitHub

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

view rawgistfile1.sh hosted with  by GitHub

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!

view rawgistfile1.txt hosted with  by GitHub

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

view rawgistfile1.sh hosted with  by GitHub

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

view rawgistfile1.txt hosted with  by GitHub

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 mysqlinvocations 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.