MySQL export table to CSV

MySQL CASE

MySQL provides an easy way to export table result set into a CSV file.

Suppose we have a query like:

SELECT name, email, age
FROM
users
WHERE status = 'Active';

So if we need to export this data into a csv file then using below query we can do:

SELECT name, email, age
FROM
users
WHERE status = 'Active'
INTO OUTFILE 'C:/xampp/users.csv'
FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"'
LINES TERMINATED BY '\r\n';

To add a column heading you need to UNION as below:

(SELECT 'Name','Email','Age')
UNION
(
SELECT name, email, age
FROM
users
WHERE status = 'Active'
INTO OUTFILE 'C:/xampp/users.csv'
FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"'
LINES TERMINATED BY '\r\n';
);

Leave a Reply

Your email address will not be published. Required fields are marked *