Re: How to make SQLLoader to understand \n as part of string
- From: Mindaugas Navickas <mnavickas@xxxxxxxxx>
- To: Kurt Van Meerbeeck <kurtvm@xxxxxxxxxx>, oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 25 Aug 2006 09:27:32 -0400 (EDT)
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
--- Kurt Van Meerbeeck <kurtvm@xxxxxxxxxx> wrote:
> 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
> >--
> >//www.freelists.org/webpage/oracle-l
> >
> >
>
> --
> Kurt Van Meerbeeck
> kurtvm@xxxxxxxxxx
> kurt_van_meerbeeck@xxxxxx
> dude@xxxxxxxxxx
> 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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
