mySQL Export select to CSV File Example

Hi Guys,

Just a quick tip here

I thought you could be interested in knowing something like this.

If you are using likes of SQLYog then you use its feature called Export to CSV.

You can do pretty much same thing with native select command too

lets see how you do it

Consider the following table structure

Table Name: States

Field Type
state_id int(5) unsigned NOT NULL
state_code varchar(5) NOT NULL
state_name varchar(50) NOT NULL
country_id int(2) NOT NULL
Now a simple select will be like

select * from States;

Above will print resultset on mysql console.
Now lets guide the resultset to a CSV file
Change above command to look like tihs

select * from States
INTO OUTFILE ‘/path/to/states.csv’
FIELDS TERMINATED BY ‘,’
ENCLOSED BY ‘”‘
LINES TERMINATED BY ‘\n’;

The CSV file will look like this for my above command. (first 6 rows)

mySQL outfile example

 

Your select can have any number of custom fields. It doesn’t matter at all.

That’s it now the result will be copied to your CSV file. Its pretty straight forward what’s shown above. We are instructing mySQL to product a CSV equivalent of the Selected result.

To know more about OUTFILE please look into this documentation page

http://dev.mysql.com/doc/refman/5.0/en/select-into.html

 

I hope this helps

Cheers,

Advertisement

 

 

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.