How to Save Select Result Into CSV File on MySQL

Well .. if you ask me why we need make the select result into csv file, you will find it out when you work together with some people which is don’t know how to populate data like they wish. Sometimes it will get more easy to check the data using makro script or something else.

Here is the sample how to create the csv file.

In this sample case, I have table:

CREATE TABLE Persons
 (
 PersonID int,
 LastName varchar(255),
 FirstName varchar(255),
 Address varchar(255),
 City varchar(255)
 );

so, the script can be:

select * from Persons
into outfile '/Users/ariestania/Persons.csv'
fields terminated by ',' optionally enclosed by '"'
lines terminated by '\r\n';

if you don’t want get NULL value become “N\A” on CSV file, change to be like this:

select ID, LastName, FirstName,
CASE when Address is NULL then '' else Address end as Address,
CASE when City is NULL then '' else City end as City
from Persons
into outfile '/Users/ariestania/Persons.csv'
fields terminated by ',' optionally enclosed by '"'
lines terminated by '\r\n';

Explanation:

1. You can change the select statement depend on your requirement.
2. “/Users/ariestania/” is the folder where you want to put your csv file.
3. “Persons.csv” is the name for your csv file.

Hope this article can help .. 🙂