BY-NC-SA: shindoverse

Getting a complex MySQL resultset in CSV format on a remote client (*NIX)

closeThe world moved on since this content was published 4 years 10 months 13 days ago. Exercise diligent care when following any instructions and see opinions in the time they were written. If you must have an updated version, please ask kindly through the contact page.

Sometimes you just need a resultset in CSV (so non-geeks can read it in XLS format). MySQL has several options for that. First and foremost: SELECT (…) INTO OUTFILE. This is probably the most common one, and the easiest to use. However, this does not work if you’re on a remote client, as this will create a file on the server. You may not have access there, or the server may even be restricted to that, by not giving you FILE permissions.

Then there’s the option of mysqldump. This can take the same FIELD OPTIONS, creating a CSV as well, and works on remote clients. The restriction here is that you are restricted to single tables. You can use a WHERE constraint, but there’s no JOINing, or any other fancy MySQL stuff. And you really can’t just dump the whole db in CSV format, and expect the user to figure things out, and link the rows in their mind. So for complex resultsets, this doesn’t work either. Now what?

The MySQL manual suggests to use the ‘mysql -e “SELECT …” > /tmp/file’ format. If you try that, you will notice that this does not create a CSV file, but simply the resultset you would otherwise get in your client. So that’s not much of a help either. However, we are on the right path here. We just need to call upon the powers of some other Linux tools, sed in this case, to help us out. After lots of googling, I came accross debianadmin.com, a site with the ultimate solution. From that site:

mysql -u exampleuser -p letmein exampledb -B -eselect * from `person`;” | \
sed ’s/\t/”,”/g;s/^//;s/$//;s/\n//g’ > filename.csv

There’s 2 things that are worth noticing here. First of all, the -B parameter to the MySQL client. From the MySQL man page:

· –batch, -B
Print results using tab as the column separator, with each row on a new line. With this option, mysql does not use the history file.
Batch mode results in non-tabular output format and escaping of special characters. Escaping may be disabled by using raw mode; see the description for the
–raw option.

In other words: instead of the pipelines between columns, and underscores between rows, stuff get’s seperated by tabs. This makes the output more readable for sed.

There’s the second thing: sed. Sed is a stream editor, with the power to apply filters on a stream. This is done in a single pass, so is usable as an intermediate step in a pipe-lined shell command. It transforms the data from the MySQL client (forwarded through the | operator) and passes it’s results to the filesystem (indicated by the > operator). The lines we use are simple regex, but for those not that familiar, the explenation is as follows:

  • s/\t/”,”/g – Replace tabs with “,” throughout the whole line.
  • s/^/”/ – Replace the start of each line (prepend) with a “
  • s/$/”/ – Replace the end of each line (append) with a “
  • s/\n//g – Replace linebreaks with nothing (remove), throughout the whole line

The end result? A perfectly formatted CSV, ready to be used in Excel, or any other tool of your choosing.

All credit goes to ‘admin’ of debianadmin.com for finding this out, and posting it on the web.

~RW

Filed Under: Tutorial

Tags: , , , , ,

Released: on Feb 05, 2010 under a Creative Commons Attribution-NoDerivs (CC-BY-ND) licenseCC-BY-ND

Comments (2)

Trackback URL | Comments RSS Feed

  1. Nathan Andersen says:

    echo “SELECT * FROM artist LIMIT 1 ” | mysql -h pldnyi04 -u myusername –password=mypassword database_my_database –batch -B | sed ‘s/\t/”,”/g;s/^/”/;s/$/”/;s/\n//g’ > nate_test.csv

  2. Marianne says:

    Thanks for the careful explanation. For some reason, the mysql command only works for me if there is no space between “-u” and “username” and between “-p” and “password”, i.e. I need to use “mysql -uusername -ppassword databasename -B -e ...” Otherwise, mysql just dumps a description of all options on the screen (this is on Ubuntu).