----- Original Message -----
From: "John Doe" <[EMAIL PROTECTED]>
To: "Rhino" <[EMAIL PROTECTED]>
Sent: Sunday, January 15, 2006 6:15 PM
Subject: Re: Weird behaviour of Load Data
Rhino am Sonntag, 15. Januar 2006 23.53:
I'm getting some odd behaviour from the 'load data' command which I can't
resolve. For some reason, 'load data' is putting a quotation at the end
of
the first column of the table
Here is my table definition:
create table if not exists Ref
(ref_name varchar(30) not null,
ref_org varchar(30) not null,
ref_title varchar(30) not null,
ref_email varchar(30) not null,
ref_phone varchar(30) not null,
ref_calltime varchar(30) not null,
primary key(ref_name),
index r_pk (ref_name)
) Type=InnoDB;
Here is my 'load data' statement:
load data infile '/home/rhino/MySQL/Ref.asc'
replace into table Ref
fields terminated by ';'
optionally enclosed by '"'
escaped by '\\'
lines starting by '"'
Hi Rhino
My posts are not accepted on the list.
Without test, the "lines starting by" could be the reason, since the char
is
not included in the value as the line end is not.
hth, joe
Your guess about the problem was not correct but it helped me find a
reasonable workaround for the problem.
I dropped the
lines starting by '"'
clause from the load data statement and got an even worse result where _all_
of the fields had quotes before and after the data values - except that the
first column had no end quotes. But that gave me the idea to put an extra
character, a greater than sign, in the first position of each row in the
data file. Then, I changed the 'load data' statement so that it included
lines starting by '>'
The data loaded correctly via the 'load data' statement.
I'm not sure why this should have been necessary - I had no problems before
with the configuration I showed in my original note - but the problem is
solved to my satisfaction.
Thank you for getting me onto the right track!
I'm copying the list with this note so that others can learn from it.
Rhino
terminated by '\n';
Here is an excerpt of my data file:
"Joe Blow";"ABC Inc.";"CEO";"[EMAIL PROTECTED]";"(212) 555-1212
(office)";"call anytime"
"Fred Smith";"DEF Corp.";"CFO";"[EMAIL PROTECTED]";"(213) 555-9999
(home)";"call evenings"
Here is what I get when I display the contents of the table via a Select
*:
Joe Blow" ABC Inc. CEO [EMAIL PROTECTED] (212) 555-1212
(office) call anytime
Fred Smith" DEF Corp. CFO [EMAIL PROTECTED] (213) 555-9999
(home)
call evenings
For some reason, 'load data' is putting a quotation, i.e. a ", at the end
of each value in the first column of the table, e.g. Joe Smith" instead
of
Joe Smith. If I load the same data with Insert statements rather than
'load
data', the result is identical except that the trailing quotation mark at
the end of each name in the first column is absent, which is precisely as
it should be.
I'm trying to populate this table with 'load data' if possible - it's
more
convenient than having to write the Insert statements - but I keep
getting
the unwanted quotations when I do it that way despite trying umpteen
variations. Does anyone know why 'load data' is insisting on putting this
quotation in each row?
I don't have that same behaviour in other very similar situations in
which
the table definition, the 'load data' statement and the organization of
the
data file is comparable.
I can't find anything on this problem in the manual or the archive of all
the mailing lists....
I'm running MySQL 4.0.15'-Max'.
---
Rhino
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.371 / Virus Database: 267.14.18/230 - Release Date:
14/01/2006
--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.371 / Virus Database: 267.14.18/230 - Release Date:
14/01/2006
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.371 / Virus Database: 267.14.18/230 - Release Date: 14/01/2006
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
