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; [crayon-5a31ac5958cab712531918/] 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)   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     … [Read more...]