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.
I created a simple database table to demonstrate the results.
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.
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.
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.
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.