Load data line end problem

Having problems with data inserted from a file using LOAD DATA once more
loading into a four column table which has 
a smallint id number (not auto because I am bringing id values from a
previous database)
a mediumtext
timestamp and 
timestamp automatically updating on alteration


I try loading data with a textfile using what I am pretty sure is the
correct syntax according to the latest manual

If I run:-

LOAD DATA LOCAL INFILE 'J:/tests/inserttest.TXT' 
INTO TABLE `articledump`
FIELDS TERMINATED BY '|'
LINES TERMINATED BY '\r\n'
OPTIONALLY ENCLOSED BY '\"';

I get an error message which seems to be caused by the 'Lines terminated'
statement 

If I remove "LINES TERMINATED BY" it will run, but only the first entry
loads OK. The second one does not 

The test data in the file is very limited but I think sufficient. I use two
test entries to representing data I want to put in:-

"490"|"This is the test entry with \"escaped\" double quotes and also
'single quotes' and the whole including 
paragraphs within the 'quotes' which hopefully
are not read as field ends. The field in fact ends here"|"2007-05-06
13:22:44"|""
"493"|"\This\" is the second test entry with more \"escaped\" double quotes
and also 'single quotes' and the whole including 
paragraphs 
within the 'quotes' which hopefully are not read as field ends. The field in
fact ends here"|"2007-05-06 13:22:46"|""

(I also tried this with actual values instead of the null values for the
second timestamp. No difference)

After much messing it seems to be connected with the line end.

The problem occurs whether I enter the SQL via the sql box in PHPMyadmin or
using the MySQL Query Browser.
Have not used command line. (Do they automatically do something with the
line ending???)

The problem is not in the server etc I am fairly sure. Everything runs on
WinXP SP2 fully updated, with Apache 1.3 and MySQL 5.0.37. All this works
very well for creating databases and tables etc and populating them in
various ways including dumping and re-importing SQL files via PHPMyadmin,
using the insert interface in PHPMyadmin ( and more or less everything else)
and plain insert statements etc in SQL.

I had a similar issue some time ago and it seemed to be connected with
surrounding table names etc with backquotes like "`" but this is not
changing anything here - I am using backquotes around table and column
names.

I am puzzled by this and would appreciate some help.


Regards Adrian



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