Ariejan de Vroom

Export CSV directly from MySQL

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 Ariejan

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 repair audio equipment, play the piano and experiment with electronics.

I'd love to hear your questions and comments on this article! Mention me on twitter, shoot me an email or read more about me.