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/win32?unsub=gcdmw-win32@m.gmane.org

Re: Load data line end problem

At 07:08 AM 5/28/2007, Adrian Greeman wrote:
>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/win32?unsub=mos99@fastmail.fm

--
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 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/win32?unsub=gcdmw-win32@m.gmane.org

Re: Load data line end problem

Adrian,

I know this sounds strange, but you need the field delimiter at the end
of the line too. Your first line of data should look like this:

"490":::"This is the test entry with no escaped double quotes":::

Randy Clamons

Systems Programming
randy@novaspace.com



Adrian Greeman wrote:
> 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/win32?unsub=gcdmw-win32@m.gmane.org

RE: Load data line end problem

More on this.

I have the solution and it is very simple. I had to remove the quotes from
around the numbers in the data. Double quotes at least are no good.

With those gone the query worked very well. And it works without escaping
the quotes within the text file or with those quotes escaped. (This this may
because I used PHPMyAdmin to enter the query - on command line the result
may be different and the escape may be needed).

Thanks again for responses which helped explore various possibilities.

I have a follow up question - I want to add a timestamp field to show
when the data is entered and would like it to set to NOW() for each data row
as it is entered. How do I write that in the import data file so that the
timestamp sets? I tried writing NOW() in the appropriate field space in the
file but it was ignored.

Adrian




--
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 end problem

Thanks. I tried that one too. It did not help and I got the same problem.

Then I used the PHPMyAdmin "import" page for a table which lets you set all
the parameters for load data query in little boxes - I chose 'CSV Load Data'
option with 'local' checked and set the separator as '|'. It worked. I could
not understand it because with the same sql entered by hand, as below, it
did not work.

It works without or without escaping the quotes inside text and with or
without an end delimiter in PHPMyAdmin.

I thought about it and the only thing I had not tried was the quote marks. I
decided to try the handwritten SQL without quotes on the number field
entries in the data file.

It worked. So that was the problem. I guess PHPMyAdmin must strip them off
automatically somehow before or during running the query. I should try this
in the command line too I guess, to be sure, but it also works in MySQL
Query browser and I am lazy.

(I have not experimented with single quotes instead - does that make a
difference?)

Finally I discovered that in the Query Browser it DID make a difference if I
put an end delimiter --- but ONLY if I was putting NULL there (to force a
'now' entry in the column as MOS suggested - which works nicely). If I put a
fixed value number, it was entered perfectly with or without a delimiter and
just the line end. I guess that is something to do with using functions but
do not know what. A NULL worked without a delimiter in PHPMyAdmin.

I hope this might help someone, having put everyone else to such trouble.

Regards

Adrian







-----Original Message-----
From: Randy Clamons [mailto:randy@novaspace.com]
Sent: 29 May 2007 19:15
To: Adrian Greeman
Cc: win32@lists.mysql.com; 'mos'
Subject: Re: Load data line end problem

Adrian,

I know this sounds strange, but you need the field delimiter at the end of
the line too. Your first line of data should look like this:

"490":::"This is the test entry with no escaped double quotes":::

Randy Clamons

Systems Programming
randy@novaspace.com



Adrian Greeman wrote:
> 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/win32?unsub=gcdmw-win32@m.gmane.org