Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: How to make SQLLoader to understand \n as part of string

Re: How to make SQLLoader to understand \n as part of string

From: Mindaugas Navickas <mnavickas_at_yahoo.com>
Date: Fri, 25 Aug 2006 09:27:32 -0400 (EDT)
Message-ID: <20060825132732.68184.qmail@web30101.mail.mud.yahoo.com>


Hi Kurt,

Not sure if I understand your suggestion correctly. Is your suggestion to change all line breaks (\n) to |\n in data file? But if we do a global replase using sed, \n, that apears inside character fields also will be changed...

Also we can not make data file in different format at the source - data extracted using DB2 (z/OS) unload and it seams very inflexible (at least it looks so from Oracle DBA point of view :-) )

Thank you
Mindaugas Navickas

> Hi,
>
> You could use the stream record format, specifying a record seperator.
> see
> http://download-uk.oracle.com/docs/cd/B10501_01/server.920/a96652/ch03.htm
> for an example.
> load data
> infile 'example.dat' "str '|\n'"
> into table example
> fields terminated by ',' optionally enclosed by '"'
> (col1 char(5),
> col2 char(7))
>
> example.dat:
> hello,world,|
> james,bond,|
>
>
> You'll have to adjust your flatfiles to include a specific record seperator
> tho
> ...
> Works from 8.1.6 and above (if I'm not mistaken)
>
> cheers,
> Kurt
>
> At 17:23 24/08/2006 -0400, Mindaugas Navickas wrote:
> >Hi,
> >
> >May be somebody faced this issue before and knows how to solve that.
> >
> >We have a CSV file where text fields enclosed witrh ("). The issue is that
> text
> >fields might contain \n (new line - 0x10) character which is treated by
> >SQLLoader as record terminator. Is it any way to tell loader that \n when
> met
> >after opening apostropha to be treated as part of field and not as record
> >terminator. Here is sample data (note the first line - bbb\nbbbshould be
> part
> >of the same field:
> >
> >111,"aaaa","bbb
> >bbb","ccc"
> >222,"xxxx","yyy",
> >333,"ffff","ggg","hhhh"
> >
> >Thank you
> >
> >Mindaugas Navickas
> >OCP DBA
> >
> >
> >__________________________________________________
> >Do You Yahoo!?
> >Tired of spam? Yahoo! Mail has the best spam protection around
> >http://mail.yahoo.com
> >--
> >http://www.freelists.org/webpage/oracle-l
> >
> >
>
> --
> Kurt Van Meerbeeck
> kurtvm_at_pandora.be
> kurt_van_meerbeeck_at_axi.be
> dude_at_ora600.org
> http://www.ora600.org
>
> Imagination is more important than knowledge...
>
>
>
>



Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
--
http://www.freelists.org/webpage/oracle-l
Received on Fri Aug 25 2006 - 08:27:32 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US