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

Question

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.

Lyrism

+1 I've always generated my CSVs with PHP, so this looks a lot easier. – Buckskin

@alex, may I have a look at your snippet to do it? ;) What are the escapes? What if there are " and , in the content? How to take care of UTF8 characters? – Lyrism

Answer 1

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!

Verile

If you use single quotes to surround your query and double quotes inside, use fields terminated by "," OPTIONALLY ENCLOSED BY "\"" escaped by "\"" LINES TERMINATED BY "\n" – Gaptoothed

Answer 2

It looks like it's impossible for a MySQL export to correctly export both Newlines and Quotes.

When exporting, MySQL will automatically escape both

• Field delimiters, and
• Line delimiters

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 want different encodings for newlines and quotes. Specifically, you want newlines to be unescaped, and quotes to be doubled.

E.g. If a value contains a newline like so:

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

it should become

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

The solution I found was to pre-escape the quotes, and add ESCAPED BY '' (an 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'

Jimjimdandy

+1 I've spent ages on this problem. Quite why MySQL can't just export as a "standard" CSV without a myriad of options and hacks is a mystery to me. – Ashlan

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 deleting the line breaks, the export worked correctly.

Brandish

Answer 4

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 realised using escaped by '\' makes removes the backslash on exported results.

Crane

Answer 5

Got an answer here https://bugs.mysql.com/bug.php?id=46434

Main points are:
1. INTO OUTFILE is intended to produce results ready to load by LOAD DATA
2. By default ESCAPED BY is '\'
3. To disable escaping, use ESCAPED BY ''

Caprifoliaceous

Answer 6

I solved this by specifying \r\n as the line terminator, rather than \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 row is now separated by \r\n, but any newlines inside your data will be left unescaped – assuming that the line separators present therein are all \n, not \r\n.

Surprisingly, this worked fine on Linux for my purposes – importing using League\Csv (PHP). I'm guessing that whatever software will be importing your generated CSVs has to be smart enough to differentiate between \n and \r\n for line breaks.

Verada

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, do not put '"" as escape, this will change your content.

Second, if you are using this query on a cli as bellow you need to also remote an extra '\n' add row 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';"

Inquire

Answer 8

I experienced similar problem.

1. To prevent extra backlash \ where there are newlines in field values, I added ESCAPED BY '\'.
2. To prevent MySQL terminating fields where newlines exist within a field value, I replaced newline \n with space ' '.

SELECT
    REPLACE(field1, '\n', ' '),
    REPLACE(field2, '\n', ' '),
    REPLACE(fieldN, '\n', ' ')
FROM business
WHERE id > 0 AND id <= 20000 INTO OUTFILE "business.csv"
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\' LINES TERMINATED BY '\n';

Jewfish