Extra backslash \ when SELECT ... INTO OUTFILE ... in MySQL

Question

So, I am trying to export a MySQL table to CSV. I am 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"; 

Output something like this:

http://postimage.org/image/2ghyenh5w/full/

The problem is that there is always an additional backslash \, where there is a new line, for example, in the address field.

However, the CSV exported from phpMyAdmin does not have it:

http://postimage.org/image/2gi026tno/full/

Any way to do SELECT ... OUTFILE ... do the same?

The table I'm dealing with contains 20 million records, phpMyAdmin can process about 500,000 records for each export action - either it will disappear, or the mysql server will disappear, etc.

Answer 1

Try the following:

 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 problem is that MySQL is trying to avoid a newline ('\ n') in your text fields, because this is your string terminator.

FIELDS ESCAPED BY controls how to write special characters. If the FIELDS ESCAPED BY character is not empty, it is used as the prefix that precedes the following characters in the output:

Symbol FIELDS ESCAPED BY

Designation FIELDS [OPTIONALLY] ENCLOSED BY

First character of TESTED FIELDS AND LINES DISCONTINUED by values

ASCII NUL (a null-digit byte, what is actually written after the escape character is ASCII "0", not a null byte)

( MySQL )

I really don't understand why he is doing what he is doing in your case, but I was able to get something similar on my Mac, and the above request seems to fix the output in my case.

Hope this helps!

Answer 2

It seems that it is not possible to export both line feeds and quotation marks correctly for MySQL export.

When exporting, MySQL automatically escapes both

• Field Separators and
• Line separators

By default, the escape character is a backslash. You can override this by adding ESCAPED BY '' to your query.

Unfortunately, in a “normal” (Excel-compatible) CSV file, you probably need different encodings for line feeds and quotation marks. In particular, you want line feeds not to be escaped, and quotation marks duplicated.

For example, if the value contains a newline, for example:

This is line 1
And this is "String 2" which contains quotation marks

it should become

"This is line 1
And this is "" Line 2 "", which contains quotation marks "

The solution I found was to pre- ESCAPED BY '' quotes and add ESCAPED BY '' (empty string) to my query.

 SELECT REPLACE(field1, '"', '""'), REPLACE(field2, '"', '""'), ... FROM ... WHERE ... INTO OUTFILE '/someFile.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '' LINES TERMINATED BY '\n' 

Answer 3

I had the same problem and I found out (after importing the csv file into a spreadsheet) that there were line breaks in some varchar fields in the MySQL table. After removing line breaks, the export worked correctly.

Answer 4

Try the following:

 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 realized that using escaped by '\' removes the backslash when exporting results.

Answer 5

Got a response here https://bugs.mysql.com/bug.php?id=46434

Basic moments:
1. INTO OUTFILE is designed to produce results ready to load LOAD DATA
2. By default, ESCAPED BY is "\"
3. To disable shielding, use ESCAPED BY ''

Answer 6

I solved this by specifying \r\n as the end of the line, not \n :

 SELECT * FROM business WHERE id > 0 AND id <= 20000 INTO OUTFILE "business.csv" FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\' LINES TERMINATED BY '\r\n'; 

Each line is now split \r\n , but any newline characters inside your data will be left unshielded - provided that all line separators present in them are \n , not \r\n .

Surprisingly, this worked fine on Linux for my purposes - importing using League \ Csv (PHP). I assume that any software will import your generated CSV should be smart enough to distinguish between \n and \r\n for line breaks.

Answer 7

 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'; 

First, don’t put '"" as escape, it will change your content.

Secondly, if you use this query below, you also need to remove the extra line of adding '\n' with multiple lines.

 mysql -e "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';"