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]