Monthly Archives: October 2012

MySQL CSV Output With Headers

Mysql provides this SELECT INTO syntax for formatting the query output as csv:

SELECT 'Fiscal Year','Location','Sales'
 UNION
 SELECT FY, Loc, Sales INTO OUTFILE 'salesreport.csv'
 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
 FROM SalesTable;

This works really well for the most part, but there’s no way to put the column headers¬† in the csv.¬†¬† There are some workarounds, by manually unioning the results with hand-created list of the table columns, or selecting the headers from the table metadata. But these approaches all have problems. I don’t want to have to type in the name of the table columns, and table metadata isn’t available for calculated or aggregated columns.

I decided an out-of-band solution worked best for me, and hacked up a quick perl script for it.

#!/usr/bin/perl
#
# mysql2csv.pl
# Converts formatted mysql output to CSV
#
# To feed: 
#
# Get log from mysql with "tee filename.out"
# Run your query
# use "notee" to stop logging
#
# OR
#
# Cut and paste output from mysql client into a file
#
# OR 
#
# Feed with mysql client output using "-t" option to get 
# formatted output:
# mysql -D mydatabase -u myuser -t \
#    -e "select count(*) from mytable" -p | mysql2csv.pl
#

sub trim($);

if ($#ARGV > 0)
{
   print "Usage: mysql2csv.pl [ log file ]\n";
   exit(1);
}


if($#ARGV == 0)
{
  $logfile = $ARGV[0];
  open(LOGFILE, "$logfile") || die "Couldn't open file $logfile\n";
}


while (<>)
{
  $line = $_;

  #drop any lines that don't start with pipe
  if($line =~ /^\|/ )
  {
      @columns = split(/\|/, $line);

      #remove first and last columns which are just empty
      shift(@columns);
      pop(@columns);

      #no comma in front of first string
      $delimiter = "";
      foreach(@columns) 
      {
         print($delimiter);
         $column = trim($_);

         #escape any quotes
         $column =~ s/"/""/g;

         #replace any NULL column with empty string
         if($column eq "NULL")
         {
            $column = "";
         }
         
         print("\"$column\"");
         $delimiter=",";
      }

      print("\n");
  }

}

close(LOGFILE);

# Perl trim function to remove whitespace from the start and end of the string
# From http://www.somacon.com/p114.php
sub trim($)
{
   my $string = shift;
   $string =~ s/^\s+//;
   $string =~ s/\s+$//;
   return $string;
}

It takes a file or reads from STDIN. To use it, generate output using the mysql command-line client. You can just cut and paste your output into a file, or log output to a file using the “tee” command, or use the -e command-line option to execute a command directly from the command line. If you use -e, make sure to also use the -t option to get formatted output:

mysql -D mydatabase -u myuser -t -e "select cost from receipts" -p | mysql2csv.pl