Re: Escape character while loading data using sqlloader
From: joel garry <joel-garry_at_home.com>
Date: Wed, 30 Sep 2009 13:18:32 -0700 (PDT)
Message-ID: <2f3e64b2-f401-4390-98b1-96ec7d1fd784_at_d15g2000prc.googlegroups.com>
On Sep 30, 11:38 am, sybra..._at_hccnet.nl wrote:
> On Wed, 30 Sep 2009 05:43:28 -0700 (PDT), rita <rita.sin..._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.
Date: Wed, 30 Sep 2009 13:18:32 -0700 (PDT)
Message-ID: <2f3e64b2-f401-4390-98b1-96ec7d1fd784_at_d15g2000prc.googlegroups.com>
On Sep 30, 11:38 am, sybra..._at_hccnet.nl wrote:
> On Wed, 30 Sep 2009 05:43:28 -0700 (PDT), rita <rita.sin..._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.
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
-- _at_home.com is bogus. http://www.guardian.co.uk/education/2009/sep/25/robots-to-mark-english-essaysReceived on Wed Sep 30 2009 - 15:18:32 CDT