Re: sqlload, mission impossible ?

Salman Mughal wrote:
>
> It seems like an ordinary sqlload case but me and my friend spent all night
> last night trying to figure it out and couldn't.
>
> We have been trying to load a file with lots of lines line the following :
>
> "423567","I would not touch it with a 10" stick","35",
>
> LOAD
> INFILE 'temp.txt'
> TERMINATED BY ',' ENCLOSED BY '"'
> INTO TABLE testdb
> ( Part_Number ,
> Description ,
> Available )
>
> But keep getting the following error :
>
> No terminator found after TERMINATED and ENCLOSED field
>
> Needless to say we have tried at least a dozen other methods and none worked.
> We cant use substr either as we dont know how long the "description" field
> will be.
>
> I would appreciate any helpful hints. Thanks a million.
>
> Salman Mughal
> salman_at_netdoor.com

I ran into this one and solved it by converting the quote delimited file to a tab delimited file with the following sed command (hopefully you are using unix):

sed 's/^"//;s/","/ /g;s/",/ /g;s/,"/ /g;s/""/"/g;s/"$//' filename.old > filename.new

Note that there are embedded tabs in the sed command. This also removed and replaces repeated double quotes ("") with a single double quote.

This will only work if your file doesn't already have tabs in it.

You can then change your sql*load ctl file to load with terminated tabs.

LOAD DATA
APPEND INTO TABLE tablename
FIELDS TERMINATED BY X'09'
TRAILING NULLCOLS Hope this helps.

Mike Madland
mikemad_at_asymetrix.com Received on Thu Jan 02 1997 - 00:00:00 CST