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.