How to output MySQL query results in CSV format

Question

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  lt  lt  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

User · Answer

The following produces tab-delimited and valid CSV output   Unlike most of the other answers  this technique correctly handles escaping of tabs  commas  quotes  and new lines without any stream filter like sed  awk  or tr   The example shows how to pipe a remote mysql table directly into a local sqlite database using streams   This works without FILE permission or SELECT INTO OUTFILE permission   I have added new lines for readability   mysql -B -C --raw -u  username  --password  password  --host  hostname   databasename  -e  SELECT     CONCAT           REPLACE  id                                    AS     id          CONCAT           REPLACE  value                                    AS     value         FROM sampledata  2 gt  dev null   sqlite3 -csv -separator    t  mydb db   import  dev stdin mycsvtable    The 2 gt  dev null is needed to suppress the warning about the password on the command line   If your data has NULLs  you can use the IFNULL   function in the query

User · Answer

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

User · Answer

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

User · Answer

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  lt   tmp demo sql query sql  gt   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       lt  file tsv  gt  file csv

User · Answer

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

User · Answer

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  gt  whatever-you-like csv   lt  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  lt  mysql num fields  rows    i           field info   mysql fetch field  rows   i        fields      field info- gt name    fputcsv  output   fields       loop over the rows  outputting them while   row   mysql fetch assoc  rows   fputcsv  output   row      gt

User · Answer

The following produces tab-delimited and valid CSV output   Unlike most of the other answers  this technique correctly handles escaping of tabs  commas  quotes  and new lines without any stream filter like sed  awk  or tr   The example shows how to pipe a remote mysql table directly into a local sqlite database using streams   This works without FILE permission or SELECT INTO OUTFILE permission   I have added new lines for readability   mysql -B -C --raw -u  username  --password  password  --host  hostname   databasename  -e  SELECT     CONCAT           REPLACE  id                                    AS     id          CONCAT           REPLACE  value                                    AS     value         FROM sampledata  2 gt  dev null   sqlite3 -csv -separator    t  mydb db   import  dev stdin mycsvtable    The 2 gt  dev null is needed to suppress the warning about the password on the command line   If your data has NULLs  you can use the IFNULL   function in the query

User · Answer

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  amp  amp   sha256sum mysql2csv   cmp  lt  echo  b109535b29733bd596ecc8608e008732e617e97906f119c66dd7cf6ab2865a65  mysql2csv       echo  ERROR comparing hash  Found    sha256sum mysql2csv     amp  amp  chmod  x mysql2csv    download content of the gist  check checksum and make it executable

User · Answer

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

User · Answer

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

User · Answer

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    lt  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           gt    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

User · Answer

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

User · Answer

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

User · Answer

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  gt  whatever-you-like csv   lt  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  lt  mysql num fields  rows    i           field info   mysql fetch field  rows   i        fields      field info- gt name    fputcsv  output   fields       loop over the rows  outputting them while   row   mysql fetch assoc  rows   fputcsv  output   row      gt

User · Answer

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

User · Answer

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  person address   The perl script  sniped from elsewhere  does a nice job of converting the tab spaced fields to CSV

User · Answer

You can use below command from your SQL editor Terminal    mysql -h hostname IP   -u username  -p password  databasename  lt  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 lt  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

User · Answer

mysql your database --password foo  lt  my requests sql  gt  out csv   Which is tab separated  Pipe it like that to get a true CSV  thanks  therefromhere          sql   sed  s  t   g   gt  out csv

User · Answer

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    Replace   with    in each field --  replace field1             Surround each result in quotation marks --  concat      result1       Place a comma between each quoted result --  concat ws      quoted1  quoted2         That s it

User · Answer

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

User · Answer

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  lt   tmp demo sql query sql  gt   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       lt  file tsv  gt  file csv

User · Answer

In my case from table name        before INTO OUTFILE        gives an error Unexpected ordering of clauses   near  quot FROM quot  at position 10    What works for me  SELECT   INTO OUTFILE   Volumes Development sql sql enabled contacts csv  FIELDS TERMINATED BY     ENCLOSED BY   quot   LINES TERMINATED BY   n   FROM table name WHERE column name    value

User · Answer

This solution places the SQL query in a heredoc and pipes the output though a filter    cat query sh     bin bash  mysql --defaults-group-suffix  DATABASE NAME  --batch  lt  lt  EOF   python query py SELECT  FIELDS  FROM  TABLE  EOF   This version of the python filter works without requiring the use of the csv module    cat query py  import sys  for line in sys stdin      print     join         str element         for element in line rstrip   n   split   t        This version of the python filter uses the csv module and involves slightly more code but is arguably a little bit more clear    cat query py  import csv  sys  csv reader   csv reader sys stdin  delimiter   t   csv writer   csv writer sys stdout  quoting csv QUOTE NONNUMERIC   for line in csv reader      csv writer writerow line    Or you could use pandas    cat query py  import csv  sys import pandas as pd  df   pd read csv sys stdin  sep   t   df to csv sys stdout  index False  quoting csv QUOTE NONNUMERIC

User · Answer

This solution places the SQL query in a heredoc and pipes the output though a filter    cat query sh     bin bash  mysql --defaults-group-suffix  DATABASE NAME  --batch  lt  lt  EOF   python query py SELECT  FIELDS  FROM  TABLE  EOF   This version of the python filter works without requiring the use of the csv module    cat query py  import sys  for line in sys stdin      print     join         str element         for element in line rstrip   n   split   t        This version of the python filter uses the csv module and involves slightly more code but is arguably a little bit more clear    cat query py  import csv  sys  csv reader   csv reader sys stdin  delimiter   t   csv writer   csv writer sys stdout  quoting csv QUOTE NONNUMERIC   for line in csv reader      csv writer writerow line    Or you could use pandas    cat query py  import csv  sys import pandas as pd  df   pd read csv sys stdin  sep   t   df to csv sys stdout  index False  quoting csv QUOTE NONNUMERIC

User · Answer

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  person address   The perl script  sniped from elsewhere  does a nice job of converting the tab spaced fields to CSV

User · Answer

Unix Cygwin only  pipe it through  tr    mysql  lt database gt  -e   lt query here gt     tr   t       gt  data csv   N B   This handles neither embedded commas  nor embedded tabs

User · Answer

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

User · Answer

If you are on production or any other server with no access to file system  you can use this simple trick and a little bit of manual effort to get what you want    Step 1  Just wrap all the columns under CONCAT and use as CSVFormat option provided by MySQL to get comma-delimited results   or use any delimiter you want   Here is an example   SELECT      CONCAT u id                               given                               family                               email                               phone                               ua street number                               ua route                               ua locality                               ua state                               ua country                               ua latitude                               ua longitude  AS CSVFormat FROM     table1 u         LEFT JOIN     table2 ua ON u address id   ua id WHERE     role policy   31 and is active   1    Step 2  Copy results from your terminal to a file and clean up all the pipe characters  that forms the layout of your results  using any text editor   Step 3  Save as  csv file and that s it

User · Answer

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  gt 32767

User · Answer

If you are on production or any other server with no access to file system  you can use this simple trick and a little bit of manual effort to get what you want    Step 1  Just wrap all the columns under CONCAT and use as CSVFormat option provided by MySQL to get comma-delimited results   or use any delimiter you want   Here is an example   SELECT      CONCAT u id                               given                               family                               email                               phone                               ua street number                               ua route                               ua locality                               ua state                               ua country                               ua latitude                               ua longitude  AS CSVFormat FROM     table1 u         LEFT JOIN     table2 ua ON u address id   ua id WHERE     role policy   31 and is active   1    Step 2  Copy results from your terminal to a file and clean up all the pipe characters  that forms the layout of your results  using any text editor   Step 3  Save as  csv file and that s it

User · Answer

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   gt   output file name csv    Credits  Exporting table from Amazon RDS into a csv file

User · Answer

Standing on the shoulders of  ChrisJohnson  I extended the answer from Feb 2016 with a custom dialect for reading   This shell pipeline tool does not need to connect to your database  handles random commas and quotes in the input  and works nicely in Python2 and Python3     usr bin env python import csv import sys   fields are separated by tabs  double-quotes may occur anywhere csv register dialect  quot mysql quot   delimiter  quot  t quot   quoting csv QUOTE NONE  tab in   csv reader sys stdin  dialect  quot mysql quot   comma out   csv writer sys stdout  dialect csv excel  for row in tab in        print  quot row     quot  format row       comma out writerow row   Use that print statement to convince yourself it s parsing your input correctly    A major caveat  treatment of carriage return characters   M aka control-M   r in linux terms  Altho batch-mode Mysql output correctly escapes embedded newline characters  so there is truly one row per line  defined by linux newline character  n   mysql puts no quotes around column data   If a data item has an embedded carriage-return character  csv reader rejects that input with this exception  new-line character seen in unquoted field -  do you need to open the file in universal-newline mode   Please don t   me saying I should use universal file mode by re-opening sys stdin fileno with mode  rU    I tried that  it causes the embedded  r characters to be treated as end-of-record markers  so a single input record is incorrectly transformed into many incomplete output records   I have not found a Python solution to this limitation of Python s csv reader module   I think the root cause is the csv reader implementation limitation noted in their documentation https   docs python org 3 library csv html csv reader  The reader is hard-coded to recognise either   r  or   n  as end-of-line  and ignores lineterminator   The weak  amp  unsatisfying solution I can offer is to change each  r character to the two-character sequence   n  before Python s csv reader sees the data  I used the sed command  Here s an example of a pipeline with a mysql select and the python script from above  mysql -u user db --execute  quot select   from table where id 12345 quot        sed -e  s  r   n g        mysqlTsvToCsv py  After fighting this for some time I think Python is not the right solution  If you can live with perl  I think the one-liner script offered by  artfulrobot may be the most-effective and simplest solution

User · Answer

I encountered the same problem and Paul s Answer wasn t an option since it was RDS  Replacing the tab with the commas did not work as the data had embedded commas  amp  tabs  I found that the mycli which is a drop-in alternative for the mysql-client supports csv output outof the box with the --csv flag  mycli db name --csv -e  select   from flowers   gt  flowers csv

User · Answer

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   gt   2 echo  Written to  2

User · Answer

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   gt   2 echo  Written to  2

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

How about     mysql your database -p  lt  my requests sql   awk   print  1    2    gt  out csv

User · Answer

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 like this   mysql OTHER OPTIONS --batch --execute  select   from whatever     tab2csv  gt  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

User · Answer

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 gt  amp 2   color msg  red   t l msg  1 gt  amp 2   usage        display usage    usage       echo  usage   0  -h --help   1 gt  amp 2   echo                 -o    --output      csvdirectory     1 gt  amp 2   echo                 -d    --database    database    1 gt  amp 2   echo                 -t    --tables      tables      1 gt  amp 2   echo                 -p    --password    password    1 gt  amp 2   echo                 -u    --user        user        1 gt  amp 2   echo                 -hs   --host        host        1 gt  amp 2   echo                 -gs   --get-schema              1 gt  amp 2   echo    1 gt  amp 2   echo       output  output csv directory to export mysql data into  1 gt  amp 2   echo    1 gt  amp 2   echo           user  mysql user  1 gt  amp 2   echo       password  mysql password  1 gt  amp 2   echo    1 gt  amp 2   echo       database  target database  1 gt  amp 2   echo         tables  tables to export  1 gt  amp 2   echo           host  host of target database  1 gt  amp 2   echo    1 gt  amp 2   echo    -h --help  show help  1 gt  amp 2   exit 1        show help    help       echo   0 Help  1 gt  amp 2   echo               1 gt  amp 2   echo   0 exports a csv file from a mysql database optionally limiting to a list of tables  1 gt  amp 2   echo     example   0 --database cms --user scott --password tiger  --tables person --output person csv  1 gt  amp 2   echo    1 gt  amp 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 gt  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 gt  amp 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    gt  gt   csvoutput database schema      getcolumns  table  gt  gt   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  lt  lt 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   gt   csvoutput table csv   rm  csvoutput table csv raw done      esac

User · Answer

If you are getting this error while you try to export your file     ERROR 1290  HY000   The MySQL server is running with the   --secure-file-priv option so it cannot execute this statement   and you are not able to solve this error  You can do one thing by simply running this python script   import mysql connector import csv  con   mysql connector connect      host  localhost       user  root       passwd  Your Password     cur   con cursor    cur execute  USE DbName   cur execute     select col1 col2 from table where  lt cond gt        with open  Filename csv  mode  w   as data      fieldnames   Field1   Field2       writer csv DictWriter data fieldnames fieldnames      writer writeheader       for i in cur          writer writerow   Field1  i 0   Field2  i 1

User · Answer

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

User · Answer

For those  who may want to download query result in CSV format but doesn t have access the server file but the database  First of all  it s not linux command  Steps are bellow    Create a view with the query  For example   Create VIEW v as  Select   from user where status   0   The view will be created under the view section of your database  Now export the view as CSV   If you need the table column as header of CSV  set Export method  to Custom - display all possible options and check Put columns names in the first row

User · Answer

Unix Cygwin only  pipe it through  tr    mysql  lt database gt  -e   lt query here gt     tr   t       gt  data csv   N B   This handles neither embedded commas  nor embedded tabs

User · Answer

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

User · Answer

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   gt   output file name csv    Credits  Exporting table from Amazon RDS into a csv file

User · Answer

You can use below command from your SQL editor Terminal    mysql -h hostname IP   -u username  -p password  databasename  lt  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 lt  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

User · Answer

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  quot SELECT   FROM Table123 quot    mysql Database456   tr  quot  t quot

User · Answer

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 like this   mysql OTHER OPTIONS --batch --execute  select   from whatever     tab2csv  gt  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

User · Answer

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

User · Answer

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

User · Answer

If you are getting an error of secure-file-priv then  also after shifting your destination file location inside the C  ProgramData MySQL MySQL Server 8 0 Uploads and also after then the query-   SELECT   FROM attendance INTO OUTFILE  C  ProgramData MySQL MySQL Server 8 0 Uploads FileName csv  FIELDS TERMINATED BY     ENCLOSED BY     LINES TERMINATED BY   n    is not working  you have to just change   backsplash  from the query to    forwardsplash    And that works     Example      SELECT   FROM attendance INTO OUTFILE  C  ProgramData MySQL MySQL Server 8 0 Uploads FileName csv  FIELDS TERMINATED BY     ENCLOSED BY     LINES TERMINATED BY   n     Each time when you run the successful query  it will generate the new csv file each time  Cool Right

User · Answer

If you are getting this error while you try to export your file     ERROR 1290  HY000   The MySQL server is running with the   --secure-file-priv option so it cannot execute this statement   and you are not able to solve this error  You can do one thing by simply running this python script   import mysql connector import csv  con   mysql connector connect      host  localhost       user  root       passwd  Your Password     cur   con cursor    cur execute  USE DbName   cur execute     select col1 col2 from table where  lt cond gt        with open  Filename csv  mode  w   as data      fieldnames   Field1   Field2       writer csv DictWriter data fieldnames fieldnames      writer writeheader       for i in cur          writer writerow   Field1  i 0   Field2  i 1

User · Answer

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    lt  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           gt    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

User · Answer

For those  who may want to download query result in CSV format but doesn t have access the server file but the database  First of all  it s not linux command  Steps are bellow    Create a view with the query  For example   Create VIEW v as  Select   from user where status   0   The view will be created under the view section of your database  Now export the view as CSV   If you need the table column as header of CSV  set Export method  to Custom - display all possible options and check Put columns names in the first row

User · Answer

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   gt   FNAME   It will create a file named  database table csv

User · Answer

mysql your database --password foo  lt  my requests sql  gt  out csv   Which is tab separated  Pipe it like that to get a true CSV  thanks  therefromhere          sql   sed  s  t   g   gt  out csv

User · Answer

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

User · Answer

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

User · Answer

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    Replace   with    in each field --  replace field1             Surround each result in quotation marks --  concat      result1       Place a comma between each quoted result --  concat ws      quoted1  quoted2         That s it

User · Answer

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

User · Answer

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   gt   FNAME   It will create a file named  database table csv

User · Answer

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  quot SELECT   FROM Table123 quot    mysql Database456   tr  quot  t quot

User · Answer

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

User · Answer

I encountered the same problem and Paul s Answer wasn t an option since it was RDS  Replacing the tab with the commas did not work as the data had embedded commas  amp  tabs  I found that the mycli which is a drop-in alternative for the mysql-client supports csv output outof the box with the --csv flag  mycli db name --csv -e  select   from flowers   gt  flowers csv

User · Answer

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

User · Answer

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  amp  amp   sha256sum mysql2csv   cmp  lt  echo  b109535b29733bd596ecc8608e008732e617e97906f119c66dd7cf6ab2865a65  mysql2csv       echo  ERROR comparing hash  Found    sha256sum mysql2csv     amp  amp  chmod  x mysql2csv    download content of the gist  check checksum and make it executable

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

If you are getting an error of secure-file-priv then  also after shifting your destination file location inside the C  ProgramData MySQL MySQL Server 8 0 Uploads and also after then the query-   SELECT   FROM attendance INTO OUTFILE  C  ProgramData MySQL MySQL Server 8 0 Uploads FileName csv  FIELDS TERMINATED BY     ENCLOSED BY     LINES TERMINATED BY   n    is not working  you have to just change   backsplash  from the query to    forwardsplash    And that works     Example      SELECT   FROM attendance INTO OUTFILE  C  ProgramData MySQL MySQL Server 8 0 Uploads FileName csv  FIELDS TERMINATED BY     ENCLOSED BY     LINES TERMINATED BY   n     Each time when you run the successful query  it will generate the new csv file each time  Cool Right

User · Answer

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  gt 32767

User · Answer

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 gt  amp 2   color msg  red   t l msg  1 gt  amp 2   usage        display usage    usage       echo  usage   0  -h --help   1 gt  amp 2   echo                 -o    --output      csvdirectory     1 gt  amp 2   echo                 -d    --database    database    1 gt  amp 2   echo                 -t    --tables      tables      1 gt  amp 2   echo                 -p    --password    password    1 gt  amp 2   echo                 -u    --user        user        1 gt  amp 2   echo                 -hs   --host        host        1 gt  amp 2   echo                 -gs   --get-schema              1 gt  amp 2   echo    1 gt  amp 2   echo       output  output csv directory to export mysql data into  1 gt  amp 2   echo    1 gt  amp 2   echo           user  mysql user  1 gt  amp 2   echo       password  mysql password  1 gt  amp 2   echo    1 gt  amp 2   echo       database  target database  1 gt  amp 2   echo         tables  tables to export  1 gt  amp 2   echo           host  host of target database  1 gt  amp 2   echo    1 gt  amp 2   echo    -h --help  show help  1 gt  amp 2   exit 1        show help    help       echo   0 Help  1 gt  amp 2   echo               1 gt  amp 2   echo   0 exports a csv file from a mysql database optionally limiting to a list of tables  1 gt  amp 2   echo     example   0 --database cms --user scott --password tiger  --tables person --output person csv  1 gt  amp 2   echo    1 gt  amp 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 gt  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 gt  amp 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    gt  gt   csvoutput database schema      getcolumns  table  gt  gt   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  lt  lt 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   gt   csvoutput table csv   rm  csvoutput table csv raw done      esac

User · Answer

In my case from table name        before INTO OUTFILE        gives an error Unexpected ordering of clauses   near  quot FROM quot  at position 10    What works for me  SELECT   INTO OUTFILE   Volumes Development sql sql enabled contacts csv  FIELDS TERMINATED BY     ENCLOSED BY   quot   LINES TERMINATED BY   n   FROM table name WHERE column name    value

User · Answer

Standing on the shoulders of  ChrisJohnson  I extended the answer from Feb 2016 with a custom dialect for reading   This shell pipeline tool does not need to connect to your database  handles random commas and quotes in the input  and works nicely in Python2 and Python3     usr bin env python import csv import sys   fields are separated by tabs  double-quotes may occur anywhere csv register dialect  quot mysql quot   delimiter  quot  t quot   quoting csv QUOTE NONE  tab in   csv reader sys stdin  dialect  quot mysql quot   comma out   csv writer sys stdout  dialect csv excel  for row in tab in        print  quot row     quot  format row       comma out writerow row   Use that print statement to convince yourself it s parsing your input correctly    A major caveat  treatment of carriage return characters   M aka control-M   r in linux terms  Altho batch-mode Mysql output correctly escapes embedded newline characters  so there is truly one row per line  defined by linux newline character  n   mysql puts no quotes around column data   If a data item has an embedded carriage-return character  csv reader rejects that input with this exception  new-line character seen in unquoted field -  do you need to open the file in universal-newline mode   Please don t   me saying I should use universal file mode by re-opening sys stdin fileno with mode  rU    I tried that  it causes the embedded  r characters to be treated as end-of-record markers  so a single input record is incorrectly transformed into many incomplete output records   I have not found a Python solution to this limitation of Python s csv reader module   I think the root cause is the csv reader implementation limitation noted in their documentation https   docs python org 3 library csv html csv reader  The reader is hard-coded to recognise either   r  or   n  as end-of-line  and ignores lineterminator   The weak  amp  unsatisfying solution I can offer is to change each  r character to the two-character sequence   n  before Python s csv reader sees the data  I used the sed command  Here s an example of a pipeline with a mysql select and the python script from above  mysql -u user db --execute  quot select   from table where id 12345 quot        sed -e  s  r   n g        mysqlTsvToCsv py  After fighting this for some time I think Python is not the right solution  If you can live with perl  I think the one-liner script offered by  artfulrobot may be the most-effective and simplest solution

User · Answer

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

User · Answer

How about     mysql your database -p  lt  my requests sql   awk   print  1    2    gt  out csv

[mysql] How to output MySQL query results in CSV format?

Examples related to mysql

Examples related to csv

Examples related to quotes