How can I output MySQL query results in CSV format?

Is there an easy way to run a MySQL query from the Linux command line and output the results in [CSV][1] format?

Here's what I'm doing now:

```lang-none
mysql -u uid -ppwd -D dbname << EOQ | sed -e 's/ /,/g' | tee list.csv
select id, concat("\"",name,"\"") as name
from students
EOQ
```

It gets messy when there are a lot of columns that need to be surrounded by quotes, or if there are quotes in the results that need to be escaped.

[1]:

[To see links please register here]

To expand on previous answers, the following one-liner exports a single table as a tab-separated file. It's suitable for automation, exporting the database every day or so.

mysql -B -D mydatabase -e 'select * from mytable'

Conveniently, we can use the same technique to list out MySQL's tables, and to describe the fields on a single table:


mysql -B -D mydatabase -e 'show tables'

mysql -B -D mydatabase -e 'desc users'

Field Type Null Key Default Extra
id int(11) NO PRI NULL auto_increment
email varchar(128) NO UNI NULL
lastName varchar(100) YES NULL
title varchar(128) YES UNI NULL
userName varchar(128) YES UNI NULL
firstName varchar(100) YES NULL

Try this code:

SELECT 'Column1', 'Column2', 'Column3', 'Column4', 'Column5'
UNION ALL
SELECT column1, column2,
column3 , column4, column5 FROM demo
INTO OUTFILE '/tmp/demo.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

For more information:

[To see links please register here]

This is simple, and it works on anything without needing batch mode or output files:

select concat_ws(',',
concat('"', replace(field1, '"', '""'), '"'),
concat('"', replace(field2, '"', '""'), '"'),
concat('"', replace(field3, '"', '""'), '"'))

from your_table where etc;

Explanation:

1. Replace `"` with `""` in each field --> `replace(field1, '"', '""')`
2. Surround each result in quotation marks --> `concat('"', result1, '"')`
3. Place a comma between each quoted result --> `concat_ws(',', quoted1, quoted2, ...)`

That's it!

This answer uses Python and a popular third party library, [PyMySQL](

[To see links please register here]

). I'm adding it because Python's [csv](

[To see links please register here]

) library is powerful enough to *correctly* handle many different flavors of `.csv` and no other answers are using Python code to interact with the database.

<!-- language: lang-py -->

import contextlib
import csv
import datetime
import os

#

[To see links please register here]

import pymysql

SQL_QUERY = """
SELECT * FROM my_table WHERE my_attribute = 'my_attribute';
"""

# embedding passwords in code gets nasty when you use version control
# the environment is not much better, but this is an example
#

[To see links please register here]

SQL_USER = os.environ['SQL_USER']
SQL_PASS = os.environ['SQL_PASS']

connection = pymysql.connect(host='localhost',
user=SQL_USER,
password=SQL_PASS,
db='dbname')

with contextlib.closing(connection):
with connection.cursor() as cursor:
cursor.execute(SQL_QUERY)
# Hope you have enough memory :)
results = cursor.fetchall()

output_file = 'my_query-{}.csv'.format(datetime.datetime.today().strftime('%Y-%m-%d'))
with open(output_file, 'w', newline='') as csvfile:
#

[To see links please register here]

about lineterminator
csv_writer = csv.writer(csvfile, lineterminator='\n')
csv_writer.writerows(results)

Also, if you're performing the query on the Bash command line, I believe the `tr` command can be used to substitute the default tabs to arbitrary delimiters.

```
$ echo "SELECT * FROM Table123" | mysql Database456 | tr "\t" ,
```

From *[Save MySQL query results into a text or CSV file][1]*:

SELECT order_id,product_name,qty
FROM orders
WHERE foo = 'bar'
INTO OUTFILE '/var/lib/mysql-files/orders.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

Note: That syntax may need to be reordered to

SELECT order_id,product_name,qty
INTO OUTFILE '/var/lib/mysql-files/orders.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM orders
WHERE foo = 'bar';

in more recent versions of MySQL.

Using this command, columns names will not be exported.

Also note that `/var/lib/mysql-files/orders.csv` will be on the *server* that is running MySQL. The user that the MySQL process is running under must have permissions to write to the directory chosen, or the command will fail.

If you want to write output to your local machine from a remote server (especially a hosted or virtualize machine such as [Heroku][2] or [Amazon RDS][3]), this solution is not suitable.

[1]:

[To see links please register here]

[2]:

[To see links please register here]

[3]:

[To see links please register here]

You can have a MySQL table that uses the CSV engine.

Then you will have a file on your hard disk that will always be in a CSV format which you could just copy without processing it.

> mysql --batch, -B
>
> Print results using tab as the column separator, with each row on a
> new line. With this option, mysql does not use the history file.
> Batch mode results in non-tabular output format and escaping of
> special characters. Escaping may be disabled by using raw mode; see
> the description for the --raw option.

This will give you a tab-separated file. Since commas (or strings containing comma) are not escaped, it is not straightforward to change the delimiter to comma.

The OUTFILE solution [given by Paul Tomblin][1] causes a file to be written on the MySQL server itself, so this will work only if you have [FILE][2] access, as well as login access or other means for retrieving the file from that box.

If you don't have such access, and tab-delimited output is a reasonable substitute for CSV (e.g., if your end goal is to import to Excel), then [serbaut's solution][3] (using `mysql --batch` and optionally `--raw`) is the way to go.

[1]:

[To see links please register here]

[2]:

[To see links please register here]

[3]:

[To see links please register here]