Archive

Posts Tagged ‘query’

Selecting a result set into a Comma Seperated Value (.csv) file

April 14th, 2009 Comments off

Getting a result set from MySQL into a comma seperated value (.csv) file does not have to be a difficult adventure.  In the past, I might have written my query in SQLyog, select the query, hit F9 to run the query, and click on the button to Copy All Rows to Clipboard, paste the copied results into an Excel spreadsheet and save the Excel spreadsheet at a .csv file. Trust me though, there is a better way!

To save a result set into a .csv file from your query, check out MySQL’s SELECT…INTO OUTFILE syntax. In the following example, I want to create a comma seperated value file that contains a list of all cities in the state of Michigan from my cities table. The syntax to do so, on my Windows machine, would look like:

SELECT     city, state_abbrev, country_code
INTO OUTFILE 'C:\temp\cities.csv'
    FIELDS TERMINATED BY ','
    OPTIONALLY ENCLOSED BY '"'
    LINES TERMINATED BY '\n'
FROM     cities
WHERE     state_name = 'Michigan'
ORDER BY city ASC

To get more information on this nifty feature of MySQL, check out the SELECT…INTO OUTFILE manual page (http://dev.mysql.com/doc/refman/5.0/en/select.html) at mysql.org