Change output format for MySQL command line results to CSV

Question

I want to get headerless CSV data from the output of a query to MySQL on the command line  I m running this query on a different machine from the MySQL server  so all those Google answers with  INTO OUTFILE  are no good    So I run mysql -e  select people  places from things   That outputs stuff that looks kinda like this    -------- -------------    people   places         -------- -------------      Bill   Raleigh  NC    -------- -------------    Well  that s no good  But hey  look  If I just pipe it to anything  it turns it into a tab-separated list    people  places Bill    Raleigh  NC   That s better- at least it s programmatically parseable  But I don t want TSV  I want CSV  and I don t want that header  I can get rid of the header with mysql  lt stuff gt    tail -n  2  but that s a bother I d like to avoid if MySQL just has a flag to omit it  And I can t just replace all tabs with commas  because that doesn t handle content with commas in it   So  how can I get MySQL to omit the header and give me data in CSV format

User · Accepted Answer

I wound up writing my own command-line tool to take care of this. It's similar to cut, except it knows what to do with quoted fields, etc. This tool, paired with @Jimothy's answer, allows me to get a headerless CSV from a remote MySQL server I have no filesystem access to onto my local machine with this command:

$ mysql -N -e "select people, places from things" | csvm -i '\t' -o ','
Bill,"Raleigh, NC"

csvmaster on github

User · Answer

If you are using mysql client you can set up the resultFormat per session e g  mysql -h localhost -u root --resutl-format json  or mysql -h localhost -u root --vertical  Check out the full list of arguments here

User · Answer

It is how to save results to CSV on the client-side without additional non-standard tools  This example uses only mysql client and awk   One-line    mysql --skip-column-names --batch -e  select   from dump3  t   awk -F  t     sep     for i   1  i  lt   NF  i      gsub    t    t   i   gsub    n    n   i   gsub              i   gsub             i   printf sep     i      sep      if i  NF  printf  n        Logical explanation of what is needed to do   First  let see how data looks like in RAW mode  with --raw option   the database and table are respectively t and dump3  You can see the field starting from   new line   in the first row  is splitted into three lines due to new lines placed in the value     mysql --skip-column-names --batch --raw -e  select   from dump3  t  one line        2       new line quotation marks   backslash   two quotation marks    two backslashes    two tabs                new line the end of field  another line    1       another line description without any special chars    OUTPUT data in batch mode  without --raw option  - each record changed to the one-line texts by escaping characters like    lt tab gt  and new-lines    mysql --skip-column-names --batch -e  select   from dump3  t  one line      2  new line nquotation marks   backslash    two quotation marks    two backslashes      two tabs t tnew line nthe end of field another line  1  another line description without any special chars    And data output in CSV format   The clue is to save data in CSV format with escaped characters   The way to do that is to convert special entities which mysql --batch produces   t as tabs    as backshlash and  n as newline  into equivalent bytes for each value  field   Then whole value is escaped by   and enclosed also by    Btw - using the same characters for escaping and enclosing gently simplifies output and processing  because you don t have two special characters  For this reason all you have to do with values  from csv format perspective  is to change   to    whithin values  In more common way  with escaping and enclosing respectively   and    you would have to first change   to    and then change   into       And the commands  explanation step by step      we produce one-line output as showed in step 2  mysql --skip-column-names --batch -e  select   from dump3  t    set fields separator to  because mysql produces in that way   awk -F  t      this start iterating every line record from the mysql data - standard behaviour of awk        field separator is empty because we don t print a separator before the first output field sep       -- iterating by every field and converting the field to csv proper value for i   1  i  lt   NF  i       -- note     two shlashes below mean   for awk because they re escaped  -- changing  t into byte corresponding to  lt tab gt       gsub    t     t   i     -- changing  n into byte corresponding to new line     gsub    n     n   i     -- changing two    into one         gsub              i    -- changing value into CSV proper one literally - change   into        gsub                i     -- print output field enclosed by   and adding separator before     printf sep     i         -- separator is set after first field is processed - because earlier we don t need it     sep        -- adding new line after the last field processed - so this indicates csv record separator     if i  NF   printf  n

User · Answer

How about using sed  It comes standard with most  all   Linux OS   sed  s  t  lt your field delimiter gt  g     This example uses GNU sed  Linux   For POSIX sed  AIX Solaris I believe you would type a literal TAB instead of  t  Example  for CSV output     mysql mysql -B -e  select   from user    while read  do sed  s  t   g   done  localhost root  Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y     0 0 0 0   localhost bill  2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N     0 0 0 0   127 0 0 1 root  Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y     0 0 0 0     1 root  Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y     0 0 0 0     jim  2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N     0 0 0 0

User · Answer

The above solutions only work in special cases  You ll get yourself into all kinds of trouble with embedded commas  embedded quotes  other things that make CSV hard in the general case   Do yourself a favor and use a general solution - do it right and you ll never have to think about it again  One very strong solution is the csvkit command line utilities - available for all operating systems via Python  Install via pip install csvkit   This will give you correct CSV data       mysql -e  select people  places from things    csvcut -t   That produces comma-separated data with the header still in place   To drop the header row       mysql -e  select people  places from things    csvcut -t   tail -n  2   That produces what the OP requested

User · Answer

As a partial answer  mysql -N -B -e  select people  places from things   -N tells it not to print column headers  -B is  batch mode   and uses tabs to separate fields   If tab separated values won t suffice  see this Stackoverflow Q amp A

User · Answer

mysqldump utility can help you  basically with --tab option it s a wrapped for SELECT INTO OUTFILE statement    Example   mysqldump -u root -p --tab  tmp world Country --fields-enclosed-by     --fields-terminated-by     --lines-terminated-by   n  --no-create-info   This will create csv formatted file  tmp Country txt

User · Answer

mysql client can detect the output fd  if the fd is S IFIFO pipe  then don t output ASCII TABLES  if the fd is character device S IFCHR  then output  ASCII TABLES  you can use --table to force output the ASCII TABLES like   mysql -t -N -h127 0 0 1 -e  quot select id from sbtest1 limit 1 quot    cat  --------    100024    --------   -t  --table         Output in table format

[mysql] Change output format for MySQL command line results to CSV

Examples related to mysql

Examples related to csv

Examples related to command-line