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:[email protected]] 
Sent: 29 May 2007 19:15
To: Adrian Greeman
Cc: [email protected]; '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
[email protected]



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/[email protected]