MySQL: Export Table to CSV Text Files for Excel

MySQL tables can be exported to SQL dump file which is basically a text file of SQL queries that can be used to import the table back into database. To export MySQL tables into other formats such as CSV, phpMyAdmin proves to be very handy if you have changed the execution timeout in seconds to zero (so it never times out) – or it won’t work with large tables. However, there’s another way to do this in native SQL query and it works until the end of a very large table:

SELECT * FROM mytable INTO OUTFILE "c:/mytable.csv"
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"'
LINES TERMINATED BY "\n";

The INTO OUTFILE command in this MySQL query will store all rows selected from the table mytable into the text file c:/mytable.csv in the form:

"1","Anthony","24"
"2","Rachel","27"

Now you can open and use the CSV file in another application such as Excel and manipulate it any way you like.

If you need another format, just change the query accordingly.

Add column / field headers at the beginning of the CSV file

To add column names at the beginning of the CSV file so each field is identified, use this SQL snippet:

SELECT 'id', 'name', 'age' UNION
SELECT * FROM mytable INTO OUTFILE "c:/mytable.csv"
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"'
LINES TERMINATED BY "\n";

That is, adding “SELECT ‘id’, ‘title’, ‘slug’ UNION”. And you will have:

"id","name","age"
"1","Anthony","24"
"2","Rachel","27"

The awkward thing is you have to manually add the field names one by one. For now, I know no option other than this that can add the column names before all the records when you are dumping CSV files from MySQL, except with phpMyAdmin.