Change output format for MySQL command line results to CSV

Question

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?

Voltaire

Answer 1

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

Voltaire

Writing a custom utility doesn't really help those folks who can't (or don't want) to download and build it. I think the other answer has more utility. – Tacy

True, a built-in solution is usually preferable. However, the question specifically asked for the separator to be a comma, not tabs, and there is no built-in way to do that. Jimothy's answer only covers half of the question. – Voltaire

Answer 2

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.

Selection

Answer 3

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.

Utta

Exactly what I was looking for! And (based on #357078) you could generate a local CSV file at the same time, e.g.: mysql -e "select people, places from things" | csvcut -t > output.csv – Uticas

Answer 4

It is how to save results to CSV on the client-side without additional non-standard tools. This example uses only mysql client and awk.

One-line:

mysql --skip-column-names --batch -e 'select * from dump3' t | awk -F'\t' '{ sep=""; for(i = 1; i <= NF; i++) { gsub(/\\t/,"\t",$i); gsub(/\\n/,"\n",$i); gsub(/\\\\/,"\\",$i); gsub(/"/,"\"\"",$i); printf sep"\""$i"\""; sep=","; if(i==NF){printf"\n"}}}'

Logical explanation of what is needed to do

1. First, let see how data looks like in RAW mode (with --raw option). the database and table are respectively t and dump3

   You can see the field starting from "new line" (in the first row) is splitted into three lines due to new lines placed in the value.

mysql --skip-column-names --batch --raw -e 'select * from dump3' t

one line        2       new line
quotation marks " backslash \ two quotation marks "" two backslashes \\ two tabs                new line
the end of field

another line    1       another line description without any special chars

2. OUTPUT data in batch mode (without --raw option) - each record changed to the one-line texts by escaping characters like \ <tab> and new-lines

mysql --skip-column-names --batch -e 'select * from dump3' t

one line      2  new line\nquotation marks " backslash \\ two quotation marks "" two backslashes \\\\ two tabs\t\tnew line\nthe end of field
another line  1  another line description without any special chars

3. And data output in CSV format

The clue is to save data in CSV format with escaped characters.

The way to do that is to convert special entities which mysql --batch produces (\t as tabs \\ as backshlash and \n as newline) into equivalent bytes for each value (field). Then whole value is escaped by " and enclosed also by ". Btw - using the same characters for escaping and enclosing gently simplifies output and processing, because you don't have two special characters. For this reason all you have to do with values (from csv format perspective) is to change " to "" whithin values. In more common way (with escaping and enclosing respectively \ and ") you would have to first change \ to \\ and then change " into \".

And the commands' explanation step by step:

# we produce one-line output as showed in step 2.
mysql --skip-column-names --batch -e 'select * from dump3' t

# set fields separator to  because mysql produces in that way
| awk -F'\t' 

# this start iterating every line/record from the mysql data - standard behaviour of awk
'{ 

# field separator is empty because we don't print a separator before the first output field
sep=""; 

-- iterating by every field and converting the field to csv proper value
for(i = 1; i <= NF; i++) { 
-- note: \\ two shlashes below mean \ for awk because they're escaped

-- changing \t into byte corresponding to <tab> 
    gsub(/\\t/, "\t",$i); 

-- changing \n into byte corresponding to new line
    gsub(/\\n/, "\n",$i); 

-- changing two \\ into one \  
    gsub(/\\\\/,"\\",$i);

-- changing value into CSV proper one literally - change " into ""
    gsub(/"/,   "\"\"",$i); 

-- print output field enclosed by " and adding separator before
    printf sep"\""$i"\"";  

-- separator is set after first field is processed - because earlier we don't need it
    sep=","; 

-- adding new line after the last field processed - so this indicates csv record separator
    if(i==NF) {printf"\n"} 
    }
}'

Dude

"--raw" don't scape \t, \n and ". So, pay attention to this option. This option may break your result if any column have this chars. – Surge

Exactly, and that's why the solution is without "--raw" option. It was used only in first step to show exact data. – Dude

Ran into a situation where this is failing with 'ran out for this one' - it looks like percent signs in a URL are what is choking it? The mySQL dump I'm using is public (the most recent one from January 2017): ifarchive.org/indexes/if-archiveXinfoXifdb.html - and the query that causes trouble: "SELECT * FROM games WHERE id='ju8qpl62enpiutnm';" -- does awk need escpaing of percent signs added? – Shrader

I do not have any issues with % sign. Could you provide the output of the only "mysql" command, without | awk ... ? – Dude

This should be marked as the correct answer. Saved my life, thanks! If someone encountering problems with percent sign just add gsub(/%/, "%%", $i); – Justly

Answer 5

How about using sed? It comes standard with most (all?) Linux OS.

sed 's/\t/<your_field_delimiter>/g'.

This example uses GNU sed (Linux). For POSIX sed (AIX/Solaris)I believe you would type a literal TAB instead of \t

Example (for CSV output):

#mysql mysql -B -e "select * from user" | while read; do sed 's/\t/,/g'; done

localhost,root,,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,,,,,0,0,0,0,,
localhost,bill,*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,,,,,0,0,0,0,,
127.0.0.1,root,,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,,,,,0,0,0,0,,
::1,root,,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,,,,,0,0,0,0,,
%,jim,*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,,,,,0,0,0,0,,

Woodland

This may require caution if your cell values can contain tabstops. Additionally, if your cell values contain commas, this approach will generate a CSV with variable column counts, without a way to identify what's a column-separating comma, and what's part of the content. Traditional *nix tools like sed and awk aren't suitable for this, since proper escaping of content gets ugly fast. – Voltaire

It's something to build on. If your data contains commas you can use a different field delimiter. definitely not a full blown one size fits all solution – Woodland

Answer 6

mysql client can detect the output fd, if the fd is S_IFIFO(pipe) then don't output ASCII TABLES, if the fd is character device（S_IFCHR） then output ASCII TABLES.

you can use --table to force output the ASCII TABLES like:

$mysql -t -N -h127.0.0.1 -e "select id from sbtest1 limit 1" | cat
+--------+
| 100024 |
+--------+

-t, --table Output in table format.

Fluidextract

Answer 7

mysqldump utility can help you, basically with --tab option it's a wrapped for SELECT INTO OUTFILE statement.

Example:

mysqldump -u root -p --tab=/tmp world Country --fields-enclosed-by='"' --fields-terminated-by="," --lines-terminated-by="\n" --no-create-info

This will create csv formatted file /tmp/Country.txt

Condense

According to the 'mysqldump' manpage, the --tab feature drops the file onto the MySQL server, and not your client machine. – Voltaire

Answer 8

If you are using mysql client you can set up the resultFormat per session e.g.

mysql -h localhost -u root --result-format=json

or

mysql -h localhost -u root --vertical

Check out the full list of arguments here.

Superclass

Answer 9

You can use spyql to read the tab-delimited output of mysql and generate a comma-delimited CSV and turn off header writing:

$ mysql -e "SELECT 'Bill' AS people, 'Raleigh, NC' AS places" | spyql -Oheader=False "SELECT * FROM csv TO csv"
Bill,"Raleigh, NC"

spyql detects if the input has a header and what is the delimiter. The output delimiter is the comma by default. You can specify all these options manually if you wish:

$ mysql -e "SELECT 'Bill' AS people, 'Raleigh, NC' AS places" | spyql -Idelimiter="'\t'" -Iheader=True -Odelimiter="," -Oheader=False "SELECT * FROM csv TO csv"
Bill,"Raleigh, NC"

I would not turn off header writing on mysql because spyql can take advantage of it, for example, if you choose to generate JSONs instead of CSVs:

$ mysql -e "SELECT 'Bill' AS people, 'Raleigh, NC' AS places" | spyql "SELECT * FROM csv TO json" 
{"people": "Bill", "places": "Raleigh, NC"}

or if you need to reference your columns:

$ mysql -e "SELECT 'Bill' AS people, 'Raleigh, NC' AS places" | spyql -Oindent=2 "SELECT *, 'I am {} and I live in {}.'.format(people, places) AS message FROM csv TO json"
{
  "people": "Bill",
  "places": "Raleigh, NC",
  "message": "I am Bill and I live in Raleigh, NC."
}

Disclaimer: I am the author of spyql

Jiles