spiffytech spiffytech - 14 days ago 7
MySQL Question

Change output format for MySQL command line results to CSV

I want to get headerless CSV data from the output of a query to MySQL on the command line. I'm running this query on a different machine from the MySQL server, so all those Google answers with "INTO OUTFILE" are no good.

So I run

mysql -e "select people, places from things"
. That outputs stuff that looks kinda like this:

+--------+-------------+
| people | places |
+--------+-------------+
| Bill | Raleigh, NC |
+--------+-------------+


Well, that's no good. But hey, look! If I just pipe it to anything, it turns it into a tab-separated list:

people places
Bill Raleigh, NC


That's better- at least it's programmatically parseable. But I don't want TSV, I want CSV, and I don't want that header. I can get rid of the header with
mysql <stuff> | tail -n +2
, but that's a bother I'd like to avoid if MySQL just has a flag to omit it. And I can't just replace all tabs with commas, because that doesn't handle content with commas in it.

So, how can I get MySQL to omit the header and give me data in CSV format?

Answer

I wound up writing my own command-line tool to take care of this. It's similar to cut, except it knows what to do with quoted fields, etc. This tool, paired with @Jimothy's answer, allows me to get a headerless CSV from a remote MySQL server I have no filesystem access to onto my local machine with this command:

$ mysql -N -e "select people, places from things" | csvm -i '\t' -o ','
Bill,"Raleigh, NC"

csvmaster on github

Comments