RE: Load data line end problem

Thanks for the suggestions two people kindly sent on my data entry problem
Both focused on escaping quotes in the text data.

But I have tested further.  To eliminate possibilities I stripped out all
quotes in my test piece and for further simplification also removed the
timestamp columns from the table so that I only have the id number column
and the medium text.

My data is now 

"490":::"This is the test entry with no escaped double quotes"
"491":::"This is the second test entry which ends here"
"492":::"This is the third test entry with a field that ends here."

This is in a text file. (I changed the field separator to be very certain
but the same effect occurs with tabs and a pipe.) 


I use this query:

LOAD DATA LOCAL INFILE 'J:/My SQL insert experments/handtest.TXT' 
INTO TABLE `tmp`
FIELDS TERMINATED BY ':::'
OPTIONALLY ENCLOSED BY '\"';

Also did the query with the escape slash removed in the last line - same
result.

Did the query in PHPMyAdmin and repeated in MYSql Query Browser. Same
result.

I get an entry in the table which seems to vary slightly as I change the
parameters --- but is never correct.  My latest version gives this result
displayed


490                 This is the test entry with no escaped double quotes"
                     "491
492                 This is the third test entry with a field that ends
here.

 
I.e. it enters only the first and last data rows with the second one partly
absorbed into the first row and some of the field quotes and the line
renewal mysteriously left within the text entry.

I have tried changing the encoding of the text file from utf8 to win1252 to
ansi (the database uses utf8). No joy
I tried deleting the temporary table and recreating it.

There is obviously an issue to do with the line ending but I cannot grasp
what it is. If I specify a line ending the syntax gets rejected.

I have Win XP SP2 all updates Apache 1.3 and MySQL 5.0.7

This is crazy

Adrian




-- 
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe:    http://lists.mysql.com/[email protected]