MySQL Bugs: #11203: "LOAD DATA INFILE" does not accept same characters for "ESCAPED" and "ENCLOSED"

Bug #11203	"LOAD DATA INFILE" does not accept same characters for "ESCAPED" and "ENCLOSED"
Submitted:	9 Jun 2005 12:32	Modified:	27 Oct 2005 7:10
Reporter:	Oliver Baltz	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	ALL	OS:	UNIX
Assigned to:	Jim Winstead	CPU Architecture:	Any

Description:
Some programs may only export CSV-data like "field1","field2 "escape_test"","field3".

MySQL can`t handle with files where I need to say: 
LOAD DATA INFILE [...] ENCLOSED BY=' " ' [...] ESCAPED BY ' " ' [...]

See details on:
http://hashmysql.org/paste/viewentry.php?id=372

How to repeat:

See details on:
http://hashmysql.org/paste/viewentry.php?id=372

I tested this in 5.0.7 from bk:

mysql> create table csvimport (item_id  varchar(20), item_id2 varchar(20));
Query OK, 0 rows affected (0.00 sec)

mysql> LOAD DATA LOCAL INFILE 'test.csv' INTO TABLE csvimport FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' (item_id,item_id2);
Query OK, 1 row affected (0.00 sec)
Records: 1  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from csvimport;
+---------+-----------+
| item_id | item_id2  |
+---------+-----------+
| field1  | "field2"
 |
+---------+-----------+
1 row in set (0.00 sec)

mysql> truncate table csvimport;
Query OK, 0 rows affected (0.00 sec)

mysql> LOAD DATA LOCAL INFILE 'test.csv' INTO TABLE csvimport FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '"' LINES TERMINATED BY '\r\n' (item_id,item_id2);     
Query OK, 1 row affected, 1 warning (0.00 sec)
Records: 1  Deleted: 0  Skipped: 0  Warnings: 1

mysql> select * from csvimport
+-----------------+----------+
| item_id         | item_id2 |
+-----------------+----------+
| "field1,field2
 | NULL     |
+-----------------+----------+
1 row in set (0.00 sec)

mysql>

Tested with versions 3.23 - 5.0.7

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/26346

Thanks for fixing this bug so fast! I love it!

Oliver, could you please attach (see [Files] tab) a short example CSV file created by such a program, to show how it does escaping ?

Your example that you pasted into a form was reformatted (read: garbled :) by a web interface.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Note that this the CSV format used by Excel, as documented here: http://www.creativyst.com/Doc/Articles/CSV/CSV01.htm

Fixed in 4.1.16 and 5.0.16.

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Documented fix for 4.1.16 and 5.1.16; closed.