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'
 SELECT FY, Loc, Sales INTO OUTFILE 'salesreport.csv'
 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.

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

sub trim($);

if ($#ARGV > 0)
   print "Usage: [ log file ]\n";

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

      #no comma in front of first string
      $delimiter = "";
         $column = trim($_);

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

         #replace any NULL column with empty string
         if($column eq "NULL")
            $column = "";




# Perl trim function to remove whitespace from the start and end of the string
# From
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 |