Properly formatted CSV not possible with MySQL’s “INTO OUTFILE”

I have been attempting to export my data from a SELECT statement into CSV format without having to modify the original SELECT statement. This is apparently not possible.
 
Let’s start with outputing using the default, TAB delimited:

INTO OUTFILE "test1.csv"
RMC200BK        SYNTHETIC,2",BLACK      \N      01/01/2014
RMC200BK SYNTHETIC,2",BLACK \N 01/01/2014

 
This works fine, I’ve got my item, description with quote and commas included, then a NULL field, and finally my date field.
Now I want CSV instead of TAB delimited so let’s terminate by a comma.
 

INTO OUTFILE "test2.csv" FIELDS TERMINATED BY ','
RMC200BK,SYNTHETIC\,2"\,BLACK,\N,01/01/2014
RMC200BK SYNTHETIC\ 2"\ BLACK \N 01/01/2014

 
Well that doesn’t work. I get a backslash in front of all my commas in my description, not just in front of my “N” (NULL) field. Let’s try enclosing everything with quotes so all my text stays together in the description field.
 

INTO OUTFILE "test3.csv" 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
"RMC200BK","SYNTHETIC,2\",BLACK",\N,"01/01/2014"
RMC200BK SYNTHETIC,2\ BLACK" \N 01/01/2014

 
Well that doesn’t work either. Sure, I’ve got quotes around my text, but the backslash has changed from being in front of the commas and is now being added in front of my double quotes in my description field. That’s not proper CSV, it should be an extra double quote instead, so let’s change the escape character from backslash to double quote.
 

INTO OUTFILE "test4.csv" 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"' 
ESCAPED BY '"'
"RMC200BK","SYNTHETIC,2"",BLACK","N,"01/01/2014"
RMC200BK SYNTHETIC,2",BLACK N,01/01/2014

 
Still not right. I’ve got my description properly quoted, but now it sees the NULL and adds a double quote in front to escape it, so now that field doesn’t terminate properly. As a last resort, let’s try terminating and escaping fields, but not enclosing them.
 

INTO OUTFILE "/data/backups/abcs3/test5.csv" 
FIELDS TERMINATED BY ',' 
ESCAPED BY '"'
RMC200BK,SYNTHETIC",2""",BLACK,"N,01/01/2014
RMC200BK SYNTHETIC" 2""" BLACK N,01/01/2014

 
Not even close.
 
Conclusion: It’s not possible to export properly formatted CSV files without having to modify the original SELECT statement. The only exception would be if you knew none of your data contained NULLs or none of your data contained double quotes.

Advertisements
This entry was posted in MySQL. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s