MySQL: Exporting Selective Columns to CSV

Friday, November 11, 2011

There are many to export large data sets from MySQL in CSV. If you need data for the complete database or a table in the database, the 'mysqldump' command is a great solution. But sometimes you might need to just export a subset of columns in the database, filtered by where clause. This can be achieved by using the SELECT ... INTO clause. It lets you specify the columns you need for your CSV and you can easily use aggregate functions (SUM, AVG etc.), WHERE clause, GROUP BY clause, ORDER BY clause etc. to get the appropriate data to be exported. Also, custom CSV delimiters can be specified to be used for the exported data. You would need to have filesystem access to the MySQL server to run this command.

Here is an example query:

SELECT `column1`, `column2`, `column3` INTO OUTFILE 'c:\\data\\today.csv'

    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

    LINES TERMINATED BY '\n'

FROM `tablename`

For more details and options please refer to the MySQL reference for the command.

Another way to export data from MySQL is by using MySQL Workbench, which is a free tool to administer MySQL databases. Though, the above command is really helpful if you want to schedule export jobs.