[TriLUG] mysql problem

[Glenn Starling]

You might check out
http://dev.mysql.com/doc/refman/5.0/en/load-data.html

I think the problem might be using delimited by instead of enclosed by.

Also, you might not need the fields terminated by ',' as the comma (I
think) is assumed as a field separator rather than the termination
character for every field.

-- Glenn


On Fri, 2008-02-08 at 16:54 -0500, Glenn Hennessee wrote:
> I'm having a problem with mysql. I need to upload data that contains ' 
> and " characters from an excel spreadsheet. What I have done is create 
> text strings that look like
> 
> :|76189-56-5|,|(S)-(-)-2,2'-bis(diphenylphosphino)-1,1'-binaphthyl|
> 
> and then I'm using this command
> 
> load data infile '/home/setup/gunnoe.sql' into table chemical fields 
> delimited by '|' terminated by ',' lines starting by ':'
> 
> but I get an error of
> |#1064 - You have an error in your SQL syntax; check the manual that 
> corresponds to your MySQL server version for the right syntax to use 
> near 'delimited by '|' terminated by ',' lines starting by ':'' at line 1
> 
> I get the error whenever I have any of the delimited by, terminated by, 
> etc. phrases. I would also like to change the table to be table 
> (cas,name) since there are other fields that aren't being uploaded here. 
> When I add the (cas,name) and leave off the rest of the line it puts as 
> much of the whole line as fits into the cas element but does no parsing. 
> I picked this method up from 
> http://russell.dyerhouse.com/cgi-bin/article.cgi?article_id=31 and have 
> consulted the mysql documentation at 
> http://dev.mysql.com/doc/refman/5.0/en/load-data.html which confirms the 
> article at russell.dyerhouse.com. Mysql gives
> Server version: 5.0.45-Debian_1ubuntu3.1-log Debian etch distribution
> on startup. I'm out of ideas on this, does anyone have any?
> 
> Thanks for any ideas.
> glenn