Load data line break problem

I am trying to use LOAD DATA INFILE and have worked through various
problems,

e.g. that with version 5 you have to specifically enable the "local" variant
in the security settings to use LOAD DATA LOCAL INFILE and that it helps to
have slanted quotes around the database name etc (perhaps someone could
remind me also of when these must be used and when it is not needed? Is it
just around the database name or table names etc?)

Anyway I loaded some data which includes long-ish strings. But---

Whenever these strings contain a line break, i.e. a \r\n paragraph sign,
MYSQL is treating that as the end of a line in the data. It goes onto a new
entry and of course the fields are all out of order and various nulls and
such like get entered until that data item is exhausted.
It then starts entering the data properly again from the next entry until it
again encounters a line break within one of the text strings.

But these breaks are within the string and within the quotes surrounding the
string. I had assumed they would just be treated as part of the string and
only line breaks outside quotes would be used to move onto new entries? But
perhaps they have to be escaped in some way??

I cannot work this out from manual section on LOAD DATA - where else should
I look or does anyone know the answer?


Incidentally though I do not think this is the issue I should add that I am
using Latin1 encoding for the data in the input file and the database is set
up with UTF8 encoding. (But this seems to produce the correct result when I
display the data - no odd characters displayed etc) so I assume the MySQL
default is to read in as Latin1?



Win XP SP2 Apache 1.3 MySQL5.0.21

Thanks

Regards

Adrian G




--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

RE: Load data line break problem

Thanks
I thought it might be so but am puzzled how I do it

If I look at the date for entry in the text editor I have the line breaks
show up simply as - well - line breaks i.e. as simply a new paragraph. I.e.
I can't actually SEE the \r\n in the text, just where it goes onto a new
line.

So how do I escape them? Can you make them visible?

And I am fairly sure that in the past when I used MySQL 4 and so forth, that
as long as I was entering text strings WITHIN quotes then I did not need
escapes if there was a new paragraph.

Regards
Adrian g

-----Original Message-----
From: John Theroux [mailto:john@northerninnovators.com]
Sent: 30 October 2006 16:00
To: adrian GREEMAN
Subject: RE: Load data line break problem


MySQL requires several characters to be escaped as so:

a\\r\\n for a\r\n

hope this helps

john





Whenever these strings contain a line break, i.e. a \r\n paragraph sign,
MYSQL is treating that as the end of a line in the data. It goes onto a new
entry and of course the fields are all out of order and various nulls and
such like get entered until that data item is exhausted.
It then starts entering the data properly again from the next entry until it
again encounters a line break within one of the text strings.

But these breaks are within the string and within the quotes surrounding the
string. I had assumed they would just be treated as part of the string and
only line breaks outside quotes would be used to move onto new entries? But
perhaps they have to be escaped in some way??





--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

RE: Load data line break problem

{revised - should read "data" below not "date")

Thanks
I thought it might be so but am puzzled how I do it

If I look at the date for entry in the text editor I have the line breaks
show up simply as - well - line breaks i.e. as simply a new paragraph. I.e.
I can't actually SEE the \r\n in the text, just where it goes onto a new
line.

So how do I escape them? Can you make them visible?

And I am fairly sure that in the past when I used MySQL 4 and so forth, that
as long as I was entering text strings WITHIN quotes then I did not need
escapes if there was a new paragraph.

Regards
Adrian g

-----Original Message-----
From: John Theroux [mailto:john@northerninnovators.com]
Sent: 30 October 2006 16:00
To: adrian GREEMAN
Subject: RE: Load data line break problem


MySQL requires several characters to be escaped as so:

a\\r\\n for a\r\n

hope this helps

john





Whenever these strings contain a line break, i.e. a \r\n paragraph sign,
MYSQL is treating that as the end of a line in the data. It goes onto a new
entry and of course the fields are all out of order and various nulls and
such like get entered until that data item is exhausted.
It then starts entering the data properly again from the next entry until it
again encounters a line break within one of the text strings.

But these breaks are within the string and within the quotes surrounding the
string. I had assumed they would just be treated as part of the string and
only line breaks outside quotes would be used to move onto new entries? But
perhaps they have to be escaped in some way??





--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org