Escape character while loading data using sqlloader

[rita]

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.

[sybr...@hccnet.nl]

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

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

[joel garry]

On Sep 30, 11:38 am, sybra...@hccnet.nl wrote:
> On Wed, 30 Sep 2009 05:43:28 -0700 (PDT), rita <rita.sin...@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.

Perhaps he was looking at
http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/ldr_control_file.htm#sthref671
Now, that's talking about DDL, but the so"far example seems more
generally to be talking about strings.

>
> '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.
>

I know there is an answer, I just can't remember what it is. Perhaps
a badfile will help. Also, setting the the options parameter so it
isn't so silent about errors may help. Maybe specifying options
(silent=(feedback)) on the command line will override the defaults and
give error messages. Boy, I'm rusty with this.

jg
--
@home.com is bogus.
http://www.guardian.co.uk/education/2009/sep/25/robots-to-mark-english-essays

[Ed Prochak]

On Sep 30, 8:43 am, rita <rita.sin...@gmail.com> wrote:
> Hi all,
>
> I am trying to load the data from a csv file to database.
>

[]

>
> 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.

What errors do you get? (my crystal ball has been broken since 1999)

Are you generating the data file with the backslash? Could you
generate it with different enclosing characters instead (and without
the backslash)?

A solution that works is to go to fixed length format.

Ed