How to save MySQL Query results to csv file

In this tutorial, we are going to export the MySQL query results to csv file. CSV stands for comma separated values. We can often use the CSV file format to exchange the data between applications such as Microsoft Excel, Open Office, Google Docs, etc.

Here we are going to export the table ‘film’ from the sakila database.

For following query result, we have to export to csv file.

select actor_id,first_name,last_name, last_update from actor;

+----------+-------------+--------------+---------------------+ | actor_id | first_name | last_name | last_update | +----------+-------------+--------------+---------------------+ | 1 | PENELOPE | GUINESS | 2006-02-15 04:34:33 | | 2 | NICK | WAHLBERG | 2006-02-15 04:34:33 | | 3 | ED | CHASE | 2006-02-15 04:34:33 | | 4 | JENNIFER | DAVIS | 2006-02-15 04:34:33 | | 5 | JOHNNY | LOLLOBRIGIDA | 2006-02-15 04:34:33 | | 6 | BETTE | NICHOLSON | 2006-02-15 04:34:33 | | 7 | GRACE | MOSTEL | 2006-02-15 04:34:33 | | 8 | MATTHEW | JOHANSSON | 2006-02-15 04:34:33 | | 9 | JOE | SWANK | 2006-02-15 04:34:33 | ...........

Export the above query result to csv file.

Before exporting data, we must ensure that:

  • The MySQL server’s process has the write access to the target folder that contains the target CSV file.
  • The target CSV file must not exist.
  • We have write access to /tmp directory.

select actor_id,first_name,last_name, last_update from actor INTO OUTFILE '/tmp/film.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'

Output:

Query OK, 200 rows affected (0.07 sec)

Responses are currently closed, but you can trackback from your own site.

Comments are closed.

Powered by k2schools