3 Ways to Make Tab Delimited Files From Your MySQL Table.

3 Ways to Make Tab Delimited Files From Your MySQL Table

Method One:

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

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

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

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.