How can I output MySQL query results in CSV format?

Use:

mysql your_database -p < my_requests.sql | awk '{print $1","$2}' > out.csv

If there is PHP installed on the machine you are using, you can write a PHP script to do that. It requires the PHP installation has the MySQL extension installed.

You can call the PHP interpreter from the command line like so:

```lang-none
php --php-ini path/to/php.ini your-script.php
```

I am including the `--php-ini` switch, because you may need to use your own PHP configuration that enables the MySQL extension. On PHP 5.3.0+ that extension is enabled by default, so that is no longer necessary to use the configuration to enable it.

Then you can write your export script like any normal PHP script:

```lang-php
<?php
#mysql_connect("localhost", "username", "password") or die(mysql_error());
mysql_select_db("mydb") or die(mysql_error());

$result = mysql_query("SELECT * FROM table_with_the_data p WHERE p.type = $typeiwant");

$result || die(mysql_error());

while($row = mysql_fetch_row($result)) {
$comma = false;
foreach ($row as $item) {

# Make it comma separated
if ($comma) {
echo ',';
} else {
$comma = true;
}

# Quote the quotes
$quoted = str_replace("\"", "\"\"", $item);

# Quote the string
echo "\"$quoted\"";
}
echo "\n";
}
?>
```

The advantage of this method is, that it has no problems with varchar and text fields, that have text containing newlines. Those fields are correctly quoted and those newlines in them will be interpreted by the CSV reader as a part of the text, not record separators. That is something that is hard to correct afterwards with [sed][1] or so.

[1]:

[To see links please register here]

Pipe it through 'tr' (Unix/[Cygwin][1] only):

mysql <database> -e "<query here>" | tr '\t' ',' > data.csv

N.B.: This handles neither embedded commas, nor embedded tabs.

[1]:

[To see links please register here]

Here's what I do:

```lang-none
echo $QUERY | \
mysql -B $MYSQL_OPTS | \
perl -F"\t" -lane 'print join ",", map {s/"/""/g; /^[\d.]+$/ ? $_ : qq("$_")} @F ' | \
mail -s 'report' person@address
```

The Perl script (snipped from elsewhere) does a nice job of converting the tab spaced fields to CSV.

Using [the solution posted by Tim Harding][1], I created this Bash script to facilitate the process (root password is requested, but you can modify the script easily to ask for any other user):

```lang-bash
#!/bin/bash

if [ "$1" == "" ];then
echo "Usage: $0 DATABASE TABLE [MYSQL EXTRA COMMANDS]"
exit
fi

DBNAME=$1
TABLE=$2
FNAME=$1.$2.csv
MCOMM=$3

echo "MySQL password: "
stty -echo
read PASS
stty echo

mysql -uroot -p$PASS $MCOMM $DBNAME -B -e "SELECT * FROM $TABLE;" | sed "s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g" > $FNAME
```

It will create a file named: **database.table.csv**

[1]:

[To see links please register here]

Not exactly as a CSV format, but the [`tee` command][1] from the MySQL **client** can be used to save the output into a **local** file:

tee foobar.txt
SELECT foo FROM bar;

You can disable it using `notee`.

The problem with `SELECT … INTO OUTFILE …;` is that it requires permission to write files at the server.

[1]:

[To see links please register here]

From your command line, you can do this:

mysql -h *hostname* -P *port number* --database=*database_name* -u *username* -p -e *your SQL query* | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > *output_file_name.csv*

**Credits:** *https://stackoverflow.com/questions/9536224/exporting-table-from-amazon-rds-into-a-csv-file*

This saved me a couple of times. It is fast and it works!

> [--batch][1]
> *Print results using **tab** as the column separator, with each row on a
> new line.*
>
> [--raw][2] *disables character escaping (\n, \t, \0, and \\)*

Example:

<!-- language: lang-bash -->

mysql -udemo_user -p -h127.0.0.1 --port=3306 \
--default-character-set=utf8mb4 --database=demo_database \
--batch --raw < /tmp/demo_sql_query.sql > /tmp/demo_csv_export.tsv

For completeness you could [convert to CSV][3] (but **be careful** because tabs could be inside field values - e.g., text fields)
> `tr '\t' ',' < file.tsv > file.csv`

[1]:

[To see links please register here]

[2]:

[To see links please register here]

[3]:

[To see links please register here]

Many of the answers on this page are weak, because they don't handle the general case of what can occur in CSV format. E.g., commas and quotes embedded in fields and other conditions that always come up eventually. We need a general solution that works for all valid CSV input data.

Here's a simple and strong solution in Python:

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

#!/usr/bin/env python

import csv
import sys

tab_in = csv.reader(sys.stdin, dialect=csv.excel_tab)
comma_out = csv.writer(sys.stdout, dialect=csv.excel)

for row in tab_in:
comma_out.writerow(row)

Name that file `tab2csv`, put it on your path, give it execute permissions, then use it like this:

<!-- language: lang-sh -->

mysql OTHER_OPTIONS --batch --execute='select * from whatever;' | tab2csv > outfile.csv

The Python CSV-handling functions cover corner cases for CSV input format(s).

This could be improved to handle very large files via a streaming approach.

Tiny Bash script for doing simple query to CSV dumps, inspired by *[Tim Harding's answer][1]*.

```lang-bash
#!/bin/bash

# $1 = query to execute
# $2 = outfile
# $3 = mysql database name
# $4 = mysql username

if [ -z "$1" ]; then
echo "Query not given"
exit 1
fi

if [ -z "$2" ]; then
echo "Outfile not given"
exit 1
fi

MYSQL_DB=""
MYSQL_USER="root"

if [ ! -z "$3" ]; then
MYSQL_DB=$3
fi

if [ ! -z "$4" ]; then
MYSQL_USER=$4
fi

if [ -z "$MYSQL_DB" ]; then
echo "Database name not given"
exit 1
fi

if [ -z "$MYSQL_USER" ]; then
echo "Database user not given"
exit 1
fi

mysql -u $MYSQL_USER -p -D $MYSQL_DB -B -s -e "$1" | sed "s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g" > $2
echo "Written to $2"
```

[1]:

[To see links please register here]