By MCS


2008-12-10 15:59:51 8 Comments

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

Here's what I'm doing now:

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.

29 comments

@hrvoj3e 2016-01-29 13:52:47

This saved me a couple of times. Fast and it works!

--batch Print results using tab as the column separator, with each row on a new line.

--raw disables character escaping (\n, \t, \0, and \)

Example:

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 (but be careful because tabs could be inside field values - e.g. text fields)

tr '\t' ',' < file.tsv > file.csv

@PressingOnAlways 2016-07-12 00:45:53

Unless I'm missing something, this creates a TSV file, not a CSV file...

@hrvoj3e 2016-07-12 06:50:49

@PressingOnAlways Yes. MySQL doc quote: "Print results using tab as the column separator, with each row on a new line." But parsing should not be a problem with any delimiter. I used it to make excel files for my client.

@mc0e 2013-10-11 05:04:34

All of the solutions here to date, except the MySQL workbench one, are incorrect and quite possibly unsafe (ie security issues) for at least some possible content in the mysql db.

MYSQL Workbench (and similarly PHPMyAdmin) provide a formally correct solution, but are designed for downloading the output to a user's location. They're not so useful for things like automating data export.

It is not possible to generate reliably correct csv from the output of mysql -B -e 'SELECT ...' because that cannot encode carriage returns and white space in fields. The '-s' flag to mysql does do backslash escaping, and might lead to a correct solution. However, using a scripting language (one with decent internal data structures that is, not bash), and libraries where the encoding issues have already been carefully worked out is far safer.

I thought about writing a script for this, but as soon as I thought about what I'd call it, it occurred to me to search for pre-existing work by the same name. While I haven't gone over it thoroughly, the solution at https://github.com/robmiller/mysql2csv looks promising. Depending on your application, the yaml approach to specifying the SQL commands might or might not appeal though. I'm also not thrilled with the requirement for a more recent version of ruby than comes as standard with my Ubuntu 12.04 laptop or Debian Squeeze servers. Yes I know I could use RVM, but I'd rather not maintain that for such a simple purpose.

Hopefully someone will point out a suitable tool, that's had a bit of testing. Otherwise I'll probably update this when I find or write one.

@yeya 2016-07-28 15:28:51

You are right, for complex strings in the table you have to use some decent library, not just bash. I think nodejs has a better solutions for this kind of actions. like this example

@reallynice 2017-06-02 12:44:22

Hi, good answer, I would add a link to the python solution proposed below stackoverflow.com/a/35123787/1504300, which works well and is very simple. I tried to edit your post but the edit's been rejected

@Ben 2017-01-24 23:02:33

This answer uses Python and a popular third party library, PyMySQL. I'm adding it because Python's csv 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.

import contextlib
import csv
import datetime
import os

# https://github.com/PyMySQL/PyMySQL
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
# https://stackoverflow.com/questions/12461484
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:
    # http://stackoverflow.com/a/17725590/2958070 about lineterminator
    csv_writer = csv.writer(csvfile, lineterminator='\n')
    csv_writer.writerows(results)

@Alexander Baltasar 2017-01-25 09:39:07

An answer using python was already provided. stackoverflow.com/a/35123787/5470883

@Ben 2017-01-26 02:29:46

@AlexanderBaltasar, right, and it looks useful, but it's not using Python code to interact with the database. See the comment on that on that question.

@Ripudaman Singh 2018-09-24 12:43:18

You can use below command from your SQL editor/Terminal:

"mysql -h(hostname/IP>) -u(username) -p(password) databasename <(query.sql) > outputFILE(.txt/.xls)"

e.g hostname -x.x.x.x

uname - username

password - password

DBName - employeeDB

queryFile - employee.sql

outputFile - outputFile.xls

mysql -hx.x.x.x -uusername -ppassword employeeDB< employee.sql> outputFile.xls

Make sure you are executing the command from the directory where SQL query is located or mention the full path of the sql query location in the above command.

@Rohit Chemburkar 2018-11-07 06:15:26

What worked for me:

SELECT *
FROM students
WHERE foo = 'bar'
LIMIT 0,1200000
INTO OUTFILE './students-1200000.csv'
FIELDS TERMINATED BY ',' ESCAPED BY '"'
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n';

None of the solutions on this thread worked for my particular case, I had pretty json data inside one of the columns, which would get messed up in my csv output. For those with a similar problem, try lines terminated by \r\n instead.

Also another problem for those trying to open the csv with Microsoft Excel, keep in mind there is a limit of 32,767 characters that a single cell can hold, above that it overflows to the rows below. To identify which records in a column have the issue, use the query below. You can then truncate those records or handle them as you'd like.

SELECT id,name,CHAR_LENGTH(json_student_description) AS 'character length'
FROM students
WHERE CHAR_LENGTH(json_student_description)>32767;

@David Oliver 2012-06-26 17:15:54

MySQL Workbench can export recordsets to CSV, and it seems to handle commas in fields very well. The CSV opens up in OpenOffice fine.

@mr-euro 2012-07-11 17:02:49

Thanks a million David. After spending 3 hours getting the newlines to output properly for HTML content in the data, I used the MySQL Workbench and in 2 minutes I had my CSV file ready.

@David Oliver 2012-08-05 21:27:56

I've just found it can save as XML, too, which is great. I'm hoping to migrate from one application to another by using XSLT to transform this XML into a CSV file suitable for importing into the target application.

@cijagani 2015-08-05 03:20:03

mysql workbench is best option for import export feature.

@LancelotHolmes 2016-10-29 06:42:31

Well,but each time the workbench limit the select records up to 1000 and when it comes to much more records it does not work that well,the same condition for the import it often blocked if I try to import a relatively large csv file into the mysql database by workbench.

@Vincent 2018-04-22 15:47:17

I just successfully exported over half a million rows using mysql workbench so large files don't seem to be a problem. You just have to make sure you remove the select limit before running your query and you may also have to increase the following values in your my.ini file: max_allowed_packet = 500M, net_read_timeout = 600, net_write_timeout = 600

@Brian 2018-01-13 15:17:10

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" ,

@Chris Johnson 2018-10-21 15:52:31

That's a weak solution -- doesn't deal with embedded quotes, embedded newlines, embedded commas, many other variations. If you shortcut the CSV format, you're sure to get burned eventually. For a tiny bit more effort you can have a solution that works for all possible data. See my answer stackoverflow.com/a/35123787/763269.

@Hirnhamster 2018-05-16 10:19:23

If you have PHP set up on the server, you can use mysql2csv to export an (actually valid) CSV file for an abitrary mysql query. See my answer at MySQL - SELECT * INTO OUTFILE LOCAL ? for a little more context/info.

I tried to maintain the option names from mysql so it should be sufficient to provide the --file and --query options:

./mysql2csv --file="/tmp/result.csv" --query='SELECT 1 as foo, 2 as bar;' --user="username" --password="password"

"Install" mysql2csv via

wget https://gist.githubusercontent.com/paslandau/37bf787eab1b84fc7ae679d1823cf401/raw/29a48bb0a43f6750858e1ddec054d3552f3cbc45/mysql2csv -O mysql2csv -q && (sha256sum mysql2csv | cmp <(echo "b109535b29733bd596ecc8608e008732e617e97906f119c66dd7cf6ab2865a65  mysql2csv") || (echo "ERROR comparing hash, Found:" ;sha256sum mysql2csv) ) && chmod +x mysql2csv

(download content of the gist, check checksum and make it executable).

@Wolfgang Fahl 2018-05-14 15:19:27

The following bash script works for me. It optionally also gets the schema for the requested tables.

#!/bin/bash
#
# export mysql data to CSV
#https://stackoverflow.com/questions/356578/how-to-output-mysql-query-results-in-csv-format
#

#ansi colors
#http://www.csc.uvic.ca/~sae/seng265/fall04/tips/s265s047-tips/bash-using-colors.html
blue='\033[0;34m'
red='\033[0;31m'
green='\033[0;32m' # '\e[1;32m' is too bright for white bg.
endColor='\033[0m'

#
# a colored message 
#   params:
#     1: l_color - the color of the message
#     2: l_msg - the message to display
#
color_msg() {
  local l_color="$1"
  local l_msg="$2"
  echo -e "${l_color}$l_msg${endColor}"
}


#
# error
#
# show the given error message on stderr and exit
#
#   params:
#     1: l_msg - the error message to display
#
error() {
  local l_msg="$1"
  # use ansi red for error
  color_msg $red "Error:" 1>&2
  color_msg $red "\t$l_msg" 1>&2
  usage
}

#
# display usage 
#
usage() {
  echo "usage: $0 [-h|--help]" 1>&2
  echo "               -o  | --output      csvdirectory"    1>&2
  echo "               -d  | --database    database"   1>&2
  echo "               -t  | --tables      tables"     1>&2
  echo "               -p  | --password    password"   1>&2
  echo "               -u  | --user        user"       1>&2
  echo "               -hs | --host        host"       1>&2
  echo "               -gs | --get-schema"             1>&2
  echo "" 1>&2
  echo "     output: output csv directory to export mysql data into" 1>&2
  echo "" 1>&2
  echo "         user: mysql user" 1>&2
  echo "     password: mysql password" 1>&2
  echo "" 1>&2
  echo "     database: target database" 1>&2
  echo "       tables: tables to export" 1>&2
  echo "         host: host of target database" 1>&2
  echo "" 1>&2
  echo "  -h|--help: show help" 1>&2
  exit 1
}

#
# show help 
#
help() {
  echo "$0 Help" 1>&2
  echo "===========" 1>&2
  echo "$0 exports a csv file from a mysql database optionally limiting to a list of tables" 1>&2
  echo "   example: $0 --database=cms --user=scott --password=tiger  --tables=person --output person.csv" 1>&2
  echo "" 1>&2
  usage
}

domysql() {
  mysql --host $host -u$user --password=$password $database
}

getcolumns() {
  local l_table="$1"
  echo "describe $l_table" | domysql | cut -f1 | grep -v "Field" | grep -v "Warning" | paste -sd "," - 2>/dev/null
}

host="localhost"
mysqlfiles="/var/lib/mysql-files/"

# parse command line options
while true; do
  #echo "option $1"
  case "$1" in
    # options without arguments
    -h|--help) usage;;
    -d|--database)     database="$2" ; shift ;;
    -t|--tables)       tables="$2" ; shift ;;
    -o|--output)       csvoutput="$2" ; shift ;;
    -u|--user)         user="$2" ; shift ;;
    -hs|--host)        host="$2" ; shift ;;
    -p|--password)     password="$2" ; shift ;;
    -gs|--get-schema)  option="getschema";; 
    (--) shift; break;;
    (-*) echo "$0: error - unrecognized option $1" 1>&2; usage;;
    (*) break;;
  esac
  shift
done

# checks
if [ "$csvoutput" == "" ]
then
  error "ouput csv directory not set"
fi
if [ "$database" == "" ]
then
  error "mysql database not set"
fi
if [ "$user" == "" ]
then
  error "mysql user not set"
fi
if [ "$password" == "" ]
then
  error "mysql password not set"
fi

color_msg $blue "exporting tables of database $database"
if [ "$tables" = "" ]
then
tables=$(echo "show tables" | domysql)
fi

case $option in
  getschema) 
   rm $csvoutput$database.schema
   for table in $tables
   do
     color_msg $blue "getting schema for $table"
     echo -n "$table:" >> $csvoutput$database.schema
     getcolumns $table >> $csvoutput$database.schema
   done  
   ;;
  *)
for table in $tables
do
  color_msg $blue "exporting table $table"
  cols=$(grep "$table:" $csvoutput$database.schema | cut -f2 -d:)
  if [  "$cols" = "" ]
  then
    cols=$(getcolumns $table)
  fi
  ssh $host rm $mysqlfiles/$table.csv
cat <<EOF | mysql --host $host -u$user --password=$password $database 
SELECT $cols FROM $table INTO OUTFILE '$mysqlfiles$table.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';
EOF
  scp $host:$mysqlfiles/$table.csv $csvoutput$table.csv.raw
  (echo "$cols"; cat $csvoutput$table.csv.raw) > $csvoutput$table.csv
  rm $csvoutput$table.csv.raw
done
  ;;
esac

@Indrajeet Singh 2014-09-08 12:06:50

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: http://dev.mysql.com/doc/refman/5.1/en/select-into.html

@Chris Johnson 2018-10-21 15:48:55

Yes, but this only works when you have access to the MySQL server's file system, which isn't available when you're using a cloud service like AWS RDS. See my answer below stackoverflow.com/a/35123787/1504300 for a general approach that works from the client side too.

@minitauros 2018-03-01 12:23:23

Tiny bash script for doing simple query to CSV dumps, inspired by https://stackoverflow.com/a/5395421/2841607.

#!/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"

@Chris Johnson 2018-10-21 15:54:17

That's a weak solution -- doesn't deal with embedded quotes, and probably other variations. If you shortcut the CSV format, you're sure to get burned eventually. For a tiny bit more effort you can have a solution that works for all possible data. See my answer stackoverflow.com/a/35123787/763269.

@Paul Tomblin 2008-12-10 16:07:55

From http://www.tech-recipes.com/rx/1475/save-mysql-query-results-into-a-text-or-csv-file/

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';

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 or Amazon RDS), this solution is not suitable.

@Paul Tomblin 2011-10-22 12:32:26

@TomasT. there are a bunch of solutions below that get you tab delimited instead of CSV. Excel, for instance, can take both CSV and tab delimited, and convert one to the other.

@Michael Butler 2012-03-09 15:40:10

@Tomas if you have access to a remote filesystem and MySQL, you must be able to write somewhere. instead of /tmp, try /home/yourusername/file.csv -- if that fails, and the result set is not that large, you could copy the output from your SSH client and paste to your local machine.

@user694971 2012-07-11 13:00:23

Unfortunately this is not standards-compliant in any way

@Paul Tomblin 2012-07-11 13:28:05

The question specified MySQL, not "standards compliant".

@Bogdan Gusiev 2013-01-25 10:01:04

How to include header as well?

@styfle 2013-02-18 19:43:30

On a Windows machine, the OUTFILE string should escape the backslashes. For example, I used the string 'C:\\wamp\\bin\\mysql\\mysql5.5.24\\bin\\output\\orders.csv'

@petrkotek 2013-06-14 04:35:50

@BogdanGusiev, you can include header by prepending "SELECT 'order_id','product_name','qty' UNION" before the real query. First select query returns header, second query returns real data; UNION joins it together.

@Paul Tomblin 2013-06-14 13:35:54

@BogdanGusiev have a look at lifeboysays.wordpress.com/2012/06/23/…

@Ken Kinder 2013-06-26 15:12:59

@Michael Butler: Well, no, actually. If you're using Amazon RDS, Heroku, or any other hosted solution, you're unlikely to be able to just write to someone else's server.

@danielad 2013-07-17 13:00:03

what if you want to use it after joins ? SELECT order_id,product_name,qty FROM orders INTO OUTFILE '/tmp/orders.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' join x as x on xx.id = test.id

@spiffytech 2013-07-28 16:19:44

@Tomas: I wrote a tool specifically to handle that. See this StackOverflow answer.

@mc0e 2013-10-10 08:19:49

DB administrators should think carefully before allowing the File_priv permission that's required for this approach to work. See docs.oracle.com/cd/E17952_01/refman-5.0-en/…

@Klaas van Schelven 2014-03-25 13:24:01

@TMS, You still need the "grant file" privilege for this, see also stackoverflow.com/questions/6091427/…

@Dan Sandland 2014-12-29 18:22:56

If you have a lot of columns and you need them all: SELECT column_name FROM information_schema.columns WHERE table_name = 'orders' INTO OUTFILE '/tmp/orders_columns.csv' FIELDS TERMINATED BY '' ENCLOSED BY '"' LINES TERMINATED BY ',';

@cijagani 2015-08-05 03:15:38

you can use myadmin export option for csv file

@Muk 2015-08-18 11:24:05

On windows xampp file location is C:\xampp\mysql\data\<databasename>_<month>\orders.csv ( INTO OUTFILE 'urls1.csv' )

@octopusgrabbus 2015-08-25 14:08:47

Don't forget the permissions thing. I had to put my outfile in /tmp. mysql has no permissions to write into my working directory. Here is my query > select * from daily_reads where DeviceID=80632679 and LoadDateLocal > '2015-06-01 00:00:00' into outfile '/tmp/80632679_reads_since_060115.csv' fields terminated by ',' enclosed by '"' lines terminated by '\r\n';

@Chris Johnson 2016-01-31 19:39:19

This solution only works when you have access to the MYSQL server file system, i.e. you have direct administrative access to that server (and don't mind filling up its drive with data exports). This is not a solution when the user doesn't have access to the server, e.g. the user isn't an admin, or the user is an admin but the database is running remotely e.g. at AWS.

@runamok 2016-08-17 06:09:23

I don't think this works well if you have line breaks or double quotes in your records... There does not seem to be a method to replace " with "" (which I believe is the custom in CSV) in the output without also escaping line breaks as well.

@LancelotHolmes 2016-10-29 06:48:22

@styfle ,well, on windows you can try / as delimiter like 'C:/wamp/bin/mysql/mysql5.5.24/bin/output/orders.csv',it work for me.

@Kemin Zhou 2016-12-02 21:50:37

Be aware, if you have strict settings, you may get this: ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

@Luke Moore 2016-12-20 21:32:41

If mysql is running with --secure-file-priv you likely can't write to /tmp. Run SHOW VARIABLES LIKE "secure_file_priv"; to see which directory you can write to.

@user7161651 2017-02-27 14:55:05

I have been using [SQLyog] (webyog.com/product/sqlyog) to export to csv. It is pretty easy that way.

@Pranav MS 2017-08-21 06:26:28

@PaulTomblin how can i export it with the column name ?

@Kolob Canyon 2017-09-04 21:39:39

What use is a csv without column headers?

@mc0e 2017-10-07 15:59:24

Given that the OP specifically raises the issues that arises with quoting, how is this answer correct? What if there is a " in one of the values?

@Paul Tomblin 2017-10-07 18:09:12

@mc0e I'm pretty sure there is an "ESCAPED BY" clause you can use if you have fields that might have quotes. stackoverflow.com/a/2859026/3333

@user2426679 2018-09-24 19:04:49

@petrkotek UNION does not guarantee order; your header may end up as footer

@Chloe 2018-12-19 18:50:53

I had to use this to import into Postgres because MySQL uses '\' to escape characters like \" and \\r. \copy table (field1, field2, created_at, updated_at) from 'files/table.csv' with (format csv, header true, escape e'\\'); (I manually inserted a header with Notepad++.) stackoverflow.com/questions/7484413/ddg#7495514

@Chris Johnson 2016-02-01 05:52:26

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:

#!/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 list this:

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.

@Josh Rumbut 2016-02-04 15:52:50

An even more dependable solution would be to actually connect to the database with Python, then you should have an easier time doing what you need to do to deal with larger datasets (chunking results, streaming, etc).

@strickli 2012-10-11 15:19:41

Unix/Cygwin only, pipe it through 'tr':

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

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

@mc0e 2013-10-10 08:35:56

nor embedded backslashes or carriage returns

@Chris Johnson 2018-10-21 15:15:54

If you shortcut the CSV format, you're sure to get burned eventually by embedded commas, quotes etc. For a tiny bit more effort you can have a solution that works for all possible data. See my answer below stackoverflow.com/a/35123787/763269.

@user7610 2012-03-17 08:46:57

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:

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:

<?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 or so.

@mc0e 2013-10-10 08:57:33

Far from being unnecessarily complicated, this is the only solution here that goes in a direction likely to be correct - albeit with a bit more work. CSV is more complex than it first appears, and you've made a variety of mistakes. eg backslashes in the original. Better to use a library which has worked out all the issues for writing to CSV.

@user7610 2015-11-02 12:51:41

@mc0e Usually you either double the quote or escape the quote. I decided to double the quote, therefore I do not need an escape character. Different software has different ideas about CSV details. For example, LOAD DATA in MySQL indeed treats \ as escape character, whereas Open Office Calc does not. When I wrote the answer I was exporting data into a spreadsheet.

@Chris Johnson 2018-10-21 15:14:59

No reason to do all this work and still not handle special cases. If you shortcut the CSV format, you're sure to get burned eventually by embedded commas, quotes etc. For a tiny bit more effort you can have a solution that works for all possible data. See my answer below stackoverflow.com/a/35123787/763269.

@user7610 2018-10-22 09:04:25

My code handles everything that was needed to export my dataset back in the day ;) Your answer is also a step in the correct direction, but as the first comment there says, it should 1) connect to the sql database from the script directly as well as 2) use a proper csv library.

@Marty Hirsch 2011-12-06 18:50:13

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!

@Sri Murthy Upadhyayula 2015-06-25 06:50:02

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: Exporting table from Amazon RDS into a csv file

@Elias Escalante 2017-11-07 02:47:56

thats one crazy oneliner . hatsoff

@Michael Cole 2015-05-29 18:36:18

Building on user7610, here is the best way to do it. With mysql outfile there were 60 mins of file ownership and overwriting problems.

It's not cool, but it worked in 5 mins.

php csvdump.php localhost root password database tablename > whatever-you-like.csv

<?php

$server = $argv[1];
$user = $argv[2];
$password = $argv[3];
$db = $argv[4];
$table = $argv[5];

mysql_connect($server, $user, $password) or die(mysql_error());
mysql_select_db($db) or die(mysql_error());

// fetch the data
$rows = mysql_query('SELECT * FROM ' . $table);
$rows || die(mysql_error());


// create a file pointer connected to the output stream
$output = fopen('php://output', 'w');

// output the column headings

$fields = [];
for($i = 0; $i < mysql_num_fields($rows); $i++) {
    $field_info = mysql_fetch_field($rows, $i);
    $fields[] = $field_info->name;
}
fputcsv($output, $fields);

// loop over the rows, outputting them
while ($row = mysql_fetch_assoc($rows)) fputcsv($output, $row);

?>

@John Fiala 2018-04-05 22:01:55

Nice, clean, and works quickly - this is a great solution if you can run PHP in that environment!

@johntellsall 2014-10-23 16:09:02

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    

@DSimon 2015-01-12 21:15:04

To convert to CSV: mysql -B -D mydatabase -e 'select * from mytable' | sed -e 's/\t/,/g'

@awatts 2015-10-20 16:23:18

Using sed -e 's/\t/,/g' is only safe if you are sure that your data doesn't contain any commas or tabs.

@Denilson Sá Maia 2014-05-29 18:14:01

Not exactly as a CSV format, but tee command from 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.

@myidealab 2018-05-08 21:53:56

If .csv extension is used instead of .txt, are there any formatting issues to be aware of?

@zloctb 2014-05-07 08:41:04

  1. logic :

CREATE TABLE () (SELECT data FROM other_table ) ENGINE=CSV ;

When you create a CSV table, the server creates a table format file in the database directory. The file begins with the table name and has an .frm extension. The storage engine also creates a data file. Its name begins with the table name and has a .CSV extension. The data file is a plain text file. When you store data into the table, the storage engine saves it into the data file in comma-separated values format.

@Andrew Schulman 2018-01-03 21:53:29

Nice since this is correct without any other adjustments needed.

@Chris Johnson 2018-10-21 15:48:15

Yes, but this only works when you have access to the MySQL server's file system, which isn't available when you're using a cloud service like AWS RDS. See my answer below stackoverflow.com/a/35123787/1504300 for a general approach that works from the client side too.

@lepe 2013-05-16 08:04:06

Using the solution posted by Tim, 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):

#!/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

@Steve 2011-11-10 18:41:05

How about:

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

@Josh 2012-04-11 00:00:04

I really like this one. It is much cleaner, and I like the use of awk. However, I would have probably gone with this: mysql -uUser -pPassword your_database < my_requests.sql | awk 'BEGIN{OFS="=";} {print $1,$2}' > out.csv

@chawkinsuf 2013-02-20 22:50:36

Pure command line solution and works perfectly

@Barun Sharma 2015-04-10 06:17:39

This fails for field values with spaces.

@Chris Johnson 2018-10-21 15:13:25

This fails for field values with embedded newlines. If you shortcut the CSV format, you're sure to get burned eventually by embedded commas, quotes etc. For a tiny bit more effort you can have a solution that works for all possible data. See my answer below stackoverflow.com/a/35123787/763269.

@Stan 2010-04-08 16:53:24

$ mysql your_database --password=foo < my_requests.sql > out.csv

Which is tab separated. Pipe it like that to get a true CSV (thanks @therefromhere):

... .sql | sed 's/\t/,/g' > out.csv

@tmarthal 2011-07-08 02:38:07

not only that, but it allows for content to be created from a remote database host, unlike the other of writing to the local filesystem.

@Flimm 2011-08-30 15:13:01

It's tab-separated, not comma-separated.

@John Carter 2011-11-10 04:42:28

@Flimm, assuming you don't have embedded commas/tabs in the fields you can convert it by piping the result into | sed 's/\t/,/g'

@hibbelig 2012-02-16 06:08:24

I added -B for good measure, but tab-separated is as good as comma-separated for me.

@Fran Marzoa 2012-08-12 17:11:57

Pretty interesting. Why the output of this command to > out.csv is different than to stdout if no file redirection is specified?

@Joey T 2012-12-11 01:17:35

the sed 'fix' does not compensate for commas that may appear in any of the selected data and will skew your columns outputted accordingly

@Nathan Waters 2013-05-01 08:14:55

Is there a solution for @JoeyT's point? I have commas in some of the fields which is obviously pushing the output to the next column

@Joey T 2013-05-01 16:44:44

@NathanWaters Yes, use the -B (batch) switch mentioned in the previous solution instead of munging the results with sed.

@mc0e 2013-10-10 08:34:40

This is OK for some data, but if you have arbitrary text fields or binary data in there it's wrong. eg your fields may include tabs, carriage returns, backslashes or quotes.

@John Hunt 2014-04-30 12:44:40

The negativity is valid.. it might work for you now but it could well bite you in the future when your data includes a tab or a comma etc..

@Leith 2016-08-18 05:14:07

One trick if you need to open a resultant CSV file in Excel and you potentially have separator characters in your data is to rename the output file with a txt extension - the resulting text import into Excel for some reason is way more inclined to separate the data correctly.

@Dr. Tyrell 2016-10-20 04:08:09

sed 's/^/\"/g;s/\t/\",\"/g;s/$/\"/g'

@mc0e 2017-10-07 16:06:06

The OP specifically raised concerns with quoting the data, so this answer is substantially incorrect.

@Kirk 2018-03-09 18:18:32

Mac OSX does not understand \t. You can insert a tab literal using ctrl + v then tab

@Chris Johnson 2018-10-20 00:34:50

The simple sed substitution isn't a good approach. If you shortcut the CSV format, you're sure to get burned eventually by embedded commas, quotes etc. For a tiny bit more effort you can have a solution that works for all possible data. See my answer below stackoverflow.com/a/35123787/763269.

@Tim Harding 2011-03-22 17:31:58

Here's a fairly gnarly way of doing it. Found it somewhere, can't take any credit

mysql --user=wibble --password wobble -B -e "select * from vehicle_categories;" | sed "s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g" > vehicle_categories.csv

Works pretty well. Once again though a regex proves write only.


Regex Explanation:

  • s/// means substitute what's between the first // with what's between the second //
  • the "g" at the end is a modifier that means "all instance, not just first"
  • ^ (in this context) means beginning of line
  • $ (in this context) means end of line

So, putting it all together:

s/'/\'/          replace ' with \'
s/\t/\",\"/g     replace all \t (tab) with ","
s/^/\"/          at the beginning of the line place a "
s/$/\"/          at the end of the line place a "
s/\n//g          replace all \n (newline) with nothing

@Gaurav Gupta 2012-01-03 08:46:19

The -e flag was exactly what I was looking for! Thanks!

@Mei 2012-03-09 00:58:33

This regex is quite simple really; like a lot of other answers on this page, its just cleaning up the output from mysql -B. If you separated the regex into individual statements on separate lines, it would be quite simple (for someone who knows regex) to understand.

@lepe 2013-05-16 08:00:04

It works great! It's important to say that headers are already included (no extra steps are required).

@mc0e 2013-10-10 08:26:24

AT first glance, this looks pretty broken. Tabs and carriage returns in content will be mishandled. "s/'/\'/;" does nothing at all, because the double quotes in the shell command consume the backslash. Many other similar bugs with the backslash being lost. No handling for backslash in the db field.

@Hemerson Varela 2014-04-03 19:36:44

It looks like this command works well on Linux but not in Mac

@aidan 2014-04-14 03:59:09

This will break if you have a text field that contains tabs, backslashes, ",", and a number of other things. A regex is not the way to solve this problem

@snowindy 2015-09-26 10:51:24

I propose this ... | sed 's/"/\"/g' | sed "s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g" ...

@reflog 2017-10-04 10:42:40

this is actually much better and faster in some cases than the internal mysql's CSV export. nicely done!

@Chris Johnson 2018-10-20 00:35:32

The regex isn't a good approach. If you shortcut the CSV format, you're sure to get burned eventually by embedded commas, quotes etc. For a tiny bit more effort you can have a solution that works for all possible data. See my answer below stackoverflow.com/a/35123787/763269.

@extraplanetary 2011-07-07 17:34:02

Here's what I do:

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

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

@artfulrobot 2017-03-15 16:00:40

This is great. Slight improvement might be to quote everything except numbers perl -F"\t" -lane 'print join ",", map {s/"/""/g; /^\d+(?:\.\d+)?$/ ? $_ : qq("$_")} @F ' -- yours would not quote 1.2.3

@Leland Woodbury 2010-06-22 13:48:00

The OUTFILE solution given by Paul Tomblin causes a file to be written on the MySQL server itself, so this will work only if you have FILE 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 (using mysql --batch and optionally --raw) is the way to go.

@serbaut 2009-09-30 10:51:27

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.

@Joey T 2012-12-11 01:18:48

this is a preferred solution: 1) tab-separated-value lists are common in UNIX 2) TSV imports are natively supported by most import systems, including Excel, OpenOffice Spreadsheets, etc. 3) no need to escape quote characters for text fields 4) makes command-line exports a breeze

@Muayyad Alsadi 2013-02-13 10:31:17

this is the best solution because unlike first one need not have permissions on servers like RDS

@serbaut 2013-04-11 21:05:01

A neat trick: if you save the tab separated file as .xls instead of .csv, it will open in excel without any need for "text to data" conversion and without any regional settings issues.

@mc0e 2013-10-10 08:22:48

@Joey T - you still need to escape tabs and carriage returns. Also if the content of a field looks like a quoted or escaped field, the imported content may not look like the original.

@dennis 2014-02-20 08:22:22

Thanks, that will help me a lot in the future! The CLI --help only says: -B, --batch: Don't use history file. Disable interactive behavior. (Enables --silent.) So I always overlooked this option when I was looking for a "plain" export format.

@James 2018-06-14 05:29:31

I use this in combination with the excellent xsv[github.com/BurntSushi/xsv] utility to convert to csv

@Jonathan 2018-10-10 10:18:26

you will have a problem with NULLs.. they will come out as string nulls..

@Jonathan 2008-12-10 23:34:58

Alternatively to the answer above, 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.

@Zach Smith 2018-10-10 08:21:28

What are the limits of this in terms of file size / writes / reads / etc?

Related Questions

Sponsored Content

21 Answered Questions

[SOLVED] How do I connect to a MySQL Database in Python?

  • 2008-12-16 21:49:09
  • Marc Lincoln
  • 1098019 View
  • 1052 Score
  • 21 Answer
  • Tags:   python mysql

41 Answered Questions

[SOLVED] How to import an SQL file using the command line in MySQL?

17 Answered Questions

[SOLVED] Save PL/pgSQL output from PostgreSQL to a CSV file

22 Answered Questions

[SOLVED] How to reset AUTO_INCREMENT in MySQL?

18 Answered Questions

[SOLVED] How to import CSV file to MySQL table

15 Answered Questions

[SOLVED] How to get a list of MySQL user accounts

  • 2009-07-16 03:23:53
  • burntsugar
  • 1397695 View
  • 1285 Score
  • 15 Answer
  • Tags:   mysql mysql5

6 Answered Questions

[SOLVED] Convert mysql query results to CSV (with copy/paste)

1 Answered Questions

[SOLVED] How to output MySQL query results in csv format in Windows environment?

1 Answered Questions

Mysql / export query result in csv file

  • 2017-07-25 14:42:50
  • John
  • 276 View
  • 0 Score
  • 1 Answer
  • Tags:   php mysql

1 Answered Questions

[SOLVED] MySQL Query to CSV

  • 2014-01-14 06:28:04
  • vinay
  • 34205 View
  • 9 Score
  • 1 Answer
  • Tags:   mysql sql csv

Sponsored Content