datafile problem

------=_NextPart_000_0004_01C6D697.7F9DC690
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit

I am puzzled by a repeated attempt I am making to load some data from a text
file.

It has five columns to go into a table with seven columns. I skip one and
the other is a datestamp automatically generated.

It has this kind of format

an issue reference number, some text, a date, an author number, and a type
number separated by vertical bars like so::


1282|"Practice is no answer. The xxxx xxxxx xxxxxxx etc. Wouoiu ""cccccc""
is a fraud and xxxx dddd ial to totally will end the it. "|04/12/2005|15|1
957|"Foio oiu opiu opiu oiuj oiu oiu oiu oiu oiu oiu oiu oiue. Sfdff Xcvb's
xx xxxxxx xxxxxx xxxxxxxx. saldkg ddddd ddddd. "|07/07/1998|29|1
1281|"Secret xxx,xxxxx, xxxxxx xxxxxx xxxxx 'txxxx xxxx' xxxx xxxx, xxxxxx
,xxxx xxxx, press order, xxxxx xxxx journalists and TV crews. How much does
it take 'ccccc c ced' a joke? But xxx - already xxxx - will make the point.
XXXXx is urgent. "|23/11/2005|15|1
1280|"'Better integration' or 'more facilities' etc etc 'solution'. Sorting
out is vital. "|06/11/2005|15|1
1279|"Xsss ever worse for xxxx stunts a 'ddd sss ' is in town. Xwww vme a
new rolling the last throw for pin and illusion - with the ditch this losing
version. But the real story is the rubbish- with only science missing to
xcxxx xxxx needed "|23/10/2005|15|1

(I have modifed the content of the text)
I use

LOAD DATA LOCAL INFILE 'C:\\Documents and Settings\\xxxx\\My
Documents\\xxxxxxx\\xxxxxx_10Dec05-7Jul06cleanedText.TXT' INTO TABLE chng
FIELDS TERMINATED BY '|' (
IssueNo, Head, Chardate, auth_id, PaperName
);


This is into an empty temporary table with columns labelled just as the
listed headings in the brackets. It has one other column in the middle which
I want it to skip and a column of automatically set time stamp at the end.

It almost loads correctly which is to say about 27 of 30 entries enter as
expected with all the data and the skipped column left blank.

But the first one loads with the IssueNo value 0 instead of 1282 (as in the
data above). The other columns load correctly.

The three entries with the lowest values for the IssueNo also load badly -
the issue number is correct in its column and the text in the next but the
Chardate column does not load - it is empty - nor the auth-id - it shows
null - nor PaperName - also empty.

All the other entries load correctly.

In the date file the entries with the lowest values are distributed randomly
among the other data - but the database sorts on that column which puts them
all together at the top of the newly populated table underneath the odd '0'l
value.

I am a newbie and unable to see what is wrong - is it something in the data
like a strange character which is throwing it? But why only these 3 lowest
entries and the one which comes first in the data file. And why do I get
this odd '0' entry for the first row?

All the double quotes in the test are escaped (with a second double quote
rather than a slash but I understood that it works) - and I cleaned the file
to ensure the quotes were all straight ones). Could the timestamp column
created the effect?

I was reluctant to put the exact data in as it is political in content and
might offend some people but the sample above indicates how it looks.
Please excuse such a long inquiry but it seemed sensible to explain all.

The OS is Winxp SP2 the server is a local Apache 1.3 and the MySQL is
5.0.21. I entered the SQL query via PHPMyAdmin.


Regards


Adrian

------=_NextPart_000_0004_01C6D697.7F9DC690--