Posts Tagged ‘mysql’

Create a csv from MySQL output

Wednesday, December 10th, 2008

Today I got asked to pull some information from a mysql database. Pretty simple, but the one minor catch was that the person asking wanted it in a spreadsheet. Thankfully this is pretty easy with mysql’s built in ‘INTO OUTFILE’ functionality.

select * from users where last_name LIKE "%smith%" order by first_name
INTO OUTFILE '/tmp/result.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

This will write the file /tmp/result.csv in a format that is readable by pretty much any spreadsheet program (Excel, OpenOffice, etc). If you have any specific requirements for your file format (say you want tab separate) play around with the options until you get it just right. As you can see you can do anything you would regularly do in a sql query (joins, where clauses, ordering, etc).

And there you have it, one happy person who can access their data in their favourite program