Re: Escape character while loading data using sqlloader

From: <sybrandb_at_hccnet.nl>
Date: Wed, 30 Sep 2009 20:38:14 +0200
Message-ID: <m697c5ldgqa1v5mv0ka543udof7137f0af_at_4ax.com>

On Wed, 30 Sep 2009 05:43:28 -0700 (PDT), rita <rita.sinkar_at_gmail.com> wrote:

>Hi all,
>
>I am trying to load the data from a csv file to database.
>
>my data is:
>
>"aaaa","dd\"d"
>"hhh","jbbb"
>
>I want it in databse as
>aaaa dd"d
>hhh jbbb
>
>My control file is:
>LOAD DATA INFILE 'SHEET1.csv' TRUNCATE INTO TABLE temp
>fields terminated by "," optionally enclosed by '"'
>TRAILING NULLCOLS
>(aa,bb)
>
>I am using \ as escape character.
>but if try to load the data, the data having \" does not get loaded
>
>any pointers will be appreciated.
>
>
>thanks.
>

Sql*loader has likely has never known about escape characters and Oracle has never used the \ as escape characters. You could try to use the " to escape a ". If that doesn't work, you need to make sure you are not using a character you plan to load as delimiter, or you need fixed length data so you don't need any delimiters.

'Does not get loaded' is ambiguous. Make sure you set up a bad file and a discard file to have the rejected records end up there.

-- 
-- 
Sybrand Bakker
Senior Oracle DBA

Received on Wed Sep 30 2009 - 13:38:14 CDT