So I'm trying to export a MySQL table into CSV. I'm using this query:
SELECT * FROM business WHERE id > 0 AND id <= 20000 INTO OUTFILE "business.csv"
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY "\n";
That output something like this:
http://postimage.org/image/2ghyenh5w/full/
The problem with this is that there's always an extra backslash \ where there's newline such as in the address field.
However, CSV exported from phpMyAdmin doesn't have it:
http://postimage.org/image/2gi026tno/full/
Any way to make the SELECT ... OUTFILE ... do the same?
The table I'm dealing with has 20 million records, phpMyAdmin can only handle about 500,000 records for every export action - or it will go blank or mysql server gone away, etc.
Try this:
SELECT * FROM business WHERE id > 0 AND id <= 20000 INTO OUTFILE "business.csv"
fields terminated by ',' OPTIONALLY ENCLOSED BY '"' escaped by '"'
LINES TERMINATED BY '\n';
I think the issue is that MySQL is trying to escape newline ('\n') in your text fields because it's your line terminator.
FIELDS ESCAPED BY controls how to write special characters. If the FIELDS ESCAPED BY character is not empty, it is used as a prefix that precedes following characters on output:
The FIELDS ESCAPED BY character
The FIELDS [OPTIONALLY] ENCLOSED BY character
The first character of the FIELDS TERMINATED BY and LINES TERMINATED BY values
ASCII NUL (the zero-valued byte; what is actually written following the escape character is ASCII “0”, not a zero-valued byte)
(MySQL)
I don't really understand why it's doing what it's doing in your case, but I was able to get something like that on my Mac and the query above seemed to fix the output in my case.
Hope that helps!