Bug #5707 CSV format output in mysql comman-client
Submitted: 22 Sep 2004 21:55 Modified: 16 May 2009 9:41
Reporter: Witold Baryluk Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: Command-line Clients Severity:S4 (Feature request)
Version:4.0.21-1 OS:Linux (Debian GNU/Linux)
Assigned to: CPU Architecture:Any
Tags: Contribution

[22 Sep 2004 21:55] Witold Baryluk
Description:
Here you have my patch for csv (comma separeted value) format, it's very usefull in perl, bash, awk scripts, i use it from about two years in dozen of scripts.
Default separator is ';', I think it will be good if this will be configurable via comand line arguments. There can be problem if field have ; in his body. I use ',' (comma) for separation of data in one field. In some case other separator will be better.

Regreads, Witold Baryluk

--- ./mysql.cc.org      2004-09-07 00:29:37.000000000 +0200
+++ ./mysql.cc  2004-09-22 21:57:39.000000000 +0200
@@ -127,7 +127,7 @@
               rehash=1,skip_updates=0,safe_updates=0,one_database=0,
               opt_compress=0, using_opt_local_infile=0,
               vertical=0, line_numbers=1, column_names=1,opt_html=0,
-               opt_xml=0,opt_nopager=1, opt_outfile=0, named_cmds= 0,
+               opt_xml=0,opt_csv=0,opt_nopager=1, opt_outfile=0, named_cmds= 0,
                tty_password= 0, opt_nobeep=0;
 static ulong opt_max_allowed_packet, opt_net_buffer_length;
 static uint verbose=0,opt_silent=0,opt_mysql_port=0, opt_local_infile=0;
@@ -148,6 +148,8 @@
   "<", "&lt;",
   0, 0
 };
+static char *csv_separator=";";
+static my_bool csv_show_lines=0;
 static const char *day_names[]={"Sun","Mon","Tue","Wed","Thu","Fri","Sat"};
 static const char *month_names[]={"Jan","Feb","Mar","Apr","May","Jun","Jul",
                            "Aug","Sep","Oct","Nov","Dec"};
@@ -285,6 +287,7 @@
 static void print_table_data(MYSQL_RES *result);
 static void print_table_data_html(MYSQL_RES *result);
 static void print_table_data_xml(MYSQL_RES *result);
+static void print_table_data_csv(MYSQL_RES *result);
 static void print_tab_data(MYSQL_RES *result);
 static void print_table_data_vertically(MYSQL_RES *result);
 static ulong start_timer(void);
@@ -492,6 +495,8 @@
    0, GET_BOOL, NO_ARG, 0, 0, 0, 0, 0, 0},
   {"xml", 'X', "Produce XML output", (gptr*) &opt_xml, (gptr*) &opt_xml, 0,
    GET_BOOL, NO_ARG, 0, 0, 0, 0, 0, 0},
+   {"csv", 'c', "Produce CSV output", (gptr*) &opt_csv, (gptr*) &opt_csv, 0,
+   GET_BOOL, NO_ARG, 0, 0, 0, 0, 0, 0},
   {"line-numbers", OPT_LINE_NUMBERS, "Write line numbers for errors.",
    (gptr*) &line_numbers, (gptr*) &line_numbers, 0, GET_BOOL,
    NO_ARG, 1, 0, 0, 0, 0, 0},  
@@ -1504,6 +1509,8 @@
        print_table_data_html(result);
       else if (opt_xml)
        print_table_data_xml(result);
+      else if (opt_csv)
+       print_table_data_csv(result);
       else if (vertical)
        print_table_data_vertically(result);
       else if (opt_silent && verbose <= 2 && !output_tables)
@@ -1693,9 +1700,10 @@
   MYSQL_FIELD  *field;
 
   mysql_field_seek(result,0);
-  (void) tee_fputs("<TABLE BORDER=1><TR>", PAGER);
+  (void) tee_fputs("<TABLE BORDER=1>", PAGER);
   if (column_names)
   {
+    (void) tee_fputs("<TR>", PAGER);
     while((field = mysql_fetch_field(result)))
     {
       tee_fprintf(PAGER, "<TH>%s</TH>", (field->name ? 
@@ -1752,6 +1760,55 @@
   (void) tee_fputs("</resultset>\n", PAGER);
 }
 
+static void
+print_table_data_csv(MYSQL_RES *result)
+{
+  MYSQL_ROW   cur;
+  MYSQL_FIELD *field;
+  ulong                *lengths;
+  int first=0;
+
+  mysql_field_seek(result,0);
+
+  /* Columns */
+  if (column_names)
+  {
+       first = 0;
+       if (csv_show_lines) {
+         (void) tee_fputs("line_number", PAGER);
+         (void) tee_fputs(csv_separator, PAGER);
+         first++;
+       }
+        while((field = mysql_fetch_field(result)))
+        {
+         if(first++) {
+                 (void) tee_fputs(csv_separator, PAGER);
+         }
+         tee_fprintf(PAGER, "%s", (field->name ? (field->name[0] ? field->name : " ") : "NULL"));
+       }
+       (void) tee_fputs("\n",PAGER);
+  }
+
+  /* Fields */
+  while ((cur = mysql_fetch_row(result)))
+  {
+       first = 0;
+        lengths = mysql_fetch_lengths(result);
+       if (csv_show_lines) {
+         (void) tee_fputs("line_number", PAGER);
+         (void) tee_fputs(csv_separator, PAGER);
+         first++;
+       }
+        for (uint i=0; i < mysql_num_fields(result); i++)
+        {
+         if(first++) {
+                 (void) tee_fputs(csv_separator, PAGER);
+         }
+         safe_put_field(cur[i],lengths[i]);
+        }
+        (void) tee_fputs("\n", PAGER);
+  }
+}
 
 static void
 print_table_data_vertically(MYSQL_RES *result)

How to repeat:
Apply my patch

Suggested fix:
Main branch
[22 Sep 2004 22:12] Witold Baryluk
Patch

Attachment: mysql.cc-4.0.21-csv.patch (application/octet-stream, text), 3.62 KiB.

[22 Sep 2004 22:32] Witold Baryluk
In patch there is also some small bugfix in html format output.

Witold Baryluk <movax@mpi.int.pl>
[5 Mar 2009 20:17] Ben Small
Any chance we can get this implemented in the CLI for linux?
[30 Mar 2009 7:25] Sergei Golubchik
Could you use SELECT ... INTO OUTFILE instead ?
The server already has all the logic to generate CSV files.
Duplicating it in the client seems like a waste. The patch above doesn't work in a general case, it doesn't escape all the data correctly , SELECT ... INTO OUTFILE does.
[16 May 2009 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".