Today lets talk a little about converting a MySQL table to CSV (Excel). My friend was looking to export MySQL to Excel, I saw couple of questions for export MySQL tables to CSV on forums. Since I saw the question often, I thought of writing out all the ways I can think of for exporting Delimited (CSV / TSV / …) data from MySQL table. Pretty chewed & basic but frequent topic.
Following are the ways to export CSV data from MySQL database / table(s).
1. Using SELECT INTO … OUTFILE statement to export from MySQL to CSV
SELECT ... INTO OUTFILE writes the selected rows to a file. Column and line terminators can be specified to produce a specific output format. Just to mention, SELECT ... INTO OUTFILE is the complement of LOAD DATA INFILE, which you may use to load CSV (generally speaking delimited) files to MySQL.
Here’s a sample command to export “tablename” table of “db” database as a CSV:
SELECT * INTO OUTFILE '/csv_files/db.tablename.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM db.tablename;