[TriLUG] mysql problem

[Glenn Hennessee]

What finally worked was editing my file to make the lines look like
:|1148-79-4|2,2',6',2"-terpyridine|
:|133463-88-4|2,2'-bis[(4S)-4-benzyl-2-oxazoline]|
:|75-83-2|2,2-dimethylbutane|
:|590-35-2|2,2-dimethylpentane|
:|366-18-7|2,2'-dipyridyl|
:|771-60-8|2,3,4,5,6-pentafluoroaniline|
:|389-40-2|2,3,4,5,6-pentafluoroanisole|

and using the command
load data infile '/home/setup/gunnoe.sql' into table chemical fields 
terminated by '|' lines starting by ':'

I now have all my data with the 's and "s intact.

Thanks to all that helped.
glenn

Matt Nash wrote:
>  From my reading of the Mysql SQL syntax reference, you want to say 
> "fields terminated by '|' " and not "delimited by".
>
> Matt Nash
>
> 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
>>   
>>     
>
>