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?
As a partial answer: mysql -N -B -e "select people, places from things"
-N tells it not to print column headers. -B is "batch mode", and uses tabs to separate fields.
If tab separated values won't suffice, see this Stackoverflow Q&A.
The above solutions only work in special cases. You'll get yourself into all kinds of trouble with embedded commas, embedded quotes, other things that make CSV hard in the general case.
Do yourself a favor and use a general solution - do it right and you'll never have to think about it again. One very strong solution is the csvkit command line utilities - available for all operating systems via Python. Install via pip install csvkit. This will give you correct CSV data:
mysql -e "select people, places from things" | csvcut -t
That produces comma-separated data with the header still in place. To drop the header row:
mysql -e "select people, places from things" | csvcut -t | tail -n +2
That produces what the OP requested.