Monthly Archives: October 2012

MySQL CSV Output With Headers

1 Reply

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