Hi, your test.csv file has some flaws in records 5 and 7 because the OPTIONALLY ENCLOSED BY char=" isn't matched for each column. Changing those offending CSV lines to be instead something like:
> "Record 5","\"some\"data\"","Fails" > "Record 7","some data\"","Fails" and then modifying your LOAD to be something like: LOAD DATA INFILE '/home/sql/test.csv' INTO TABLE quotetest FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' ESCAPED BY '\\' LINES TERMINATED BY '\n'; should do it. The reason you use the ENCLOSED BY clause is to take into account data like: id,Name,phone 12,Last,First,555-555-5555 Since the 2nd column actually contains a comma you need to use OPTIONALLY ENCLOSED BY to contain it like: id,Name,phone 12,"Last,First",555-555-5555 If the "s aren't matched LOAD gets confused as to what delemits a column. -Joe > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, October 31, 2001 12:48 PM > To: [EMAIL PROTECTED] > Subject: Re: Load data infile > > > Hi > > I didn't see any difference between using optionally or not. > > Any solutions to make the two that fails work? (see test case below) > > Using 3.23.41. > > Thanks > > > test.csv: > "Record 1","some data","Works" > "Record 2",""some" data","Works" > "Record 3","some 'data'","Works" > "Record 4",""some" "data","Works" > "Record 5",""some"data"","Fails" > "Record 6","some"data","Works" > "Record 7","some data"","Fails" > "Record 8","'som"e"'data","Works" > "Record 9","some"data","Works" > > CREATE TABLE quotetest ( name text, content text, description text ); > > mysql> LOAD DATA INFILE '/home/sql/test.csv' INTO TABLE > quotetest FIELDS > TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES > TERMINATED BY '\n'; > Query OK, 9 rows affected (0.00 sec) > Records: 9 Deleted: 0 Skipped: 0 Warnings: 2 > > mysql> select * from quotetest; > +----------+--------------------+-------------+ > | name | content | description | > +----------+--------------------+-------------+ > | Record 1 | some data | Works | > | Record 2 | "some" data | Works | > | Record 3 | some 'data' | Works | > | Record 4 | "some" "data | Works | > | Record 5 | "some"data","Fails | NULL | > | Record 6 | some"data | Works | > | Record 7 | some data","Fails | NULL | > | Record 8 | 'som"e"'data | Works | > | Record 9 | some"data | Works | > +----------+--------------------+-------------+ > 9 rows in set (0.00 sec) > > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
