Export CSV directly from MySQL

• tagged general, mysql, csv, quick and trick • 147 words • ~1 minutes

How ofter were you asked by a client for a CSV (or excel) file with data from their app? I get asked that question quite often, so I wanted make the process as easy as possible. And guess what? You can create CSV files directly from MySQL with just one query!

Let’s say you want to export the id, name and email fields from your users table to a CSV file. Here is your code:

SELECT id, name, email INTO OUTFILE '/tmp/result.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
ESCAPED BY ‘\\’
LINES TERMINATED BY '\n'
FROM users WHERE 1

Well, if you know MySQL, you’ll know how to customize this query to spit out the the right data. Your csv file can be found in /tmp/result.csv

Make sure your MySQL server has write permissions to the location where you want to store your results file.

About

Hey! I'm a software engineer building rock solid back-end systems using Ruby, Ruby on Rails and Golang. In my free time I like to play the piano and experiment with electronics.

I'd love to hear your questions and comments on this article: just mention me on twitter or dispatch an email. Cheers!