RE: Data with carriage return and SQL loader

Hi,

This approach would work. The problem I have to load 70 tables with different column names :)

And I just wonder is there more elegant solution then UPDATE.

Cheers,
Dimitri

-----Original Message-----
From: Dale Underwood [mailto:dale_at_fox.nstn.ca] Sent: Monday, October 16, 2000 7:15 AM
To: Multiple recipients of list ORACLE-L Subject: Re: Data with carriage return and SQL loader

I still don't see why Jared's approach would work for you. Simply modify the perl script to replace CR LF with a character that will not be in the data and after use oracle translate function.

We use to do something similar with a data dump and reload across systems on Oracle 6.

1) replace CR LF with '~' in flat file
2) use sqlldr to load
3) update table using translate function, eg

update mytable

   set col1 = translate (col1,'~', chr(13) || chr(10) ),
        col2 = translate (col2, '~', chr(13) || chr(10) ),
        col3 = translate (col3, '~', chr(13) || chr(10) );

• Babette
  • Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Thursday, October 12, 2000 6:10 AM

> Jared,
>
> Yes, no problem to replace - the problem I want carriage return in the
table
> field AFTER loading!
>
> Thanks anyway!
>
> Cheers,
> Dimitri
>
>
> -----Original Message-----
> Sent: Thursday, October 12, 2000 9:06 AM
> To: Multiple recipients of list ORACLE-L
>
>
>
> Try this:
>
> --------------------
> #!/usr/bin/perl
>
> # cleandata
>
> my $CR=chr(13);
> my $LF=chr(10);
> my $QUOTE="'";
>
> open(X,"x.txt");
>
> while(<X>) {
> s/([^$QUOTE])$CR$LF/$1/;
> print;
> }
>
> --------------------
>
> cleandata < baddata.txt > gooddata.txt
>
>
>
> On Wed, 11 Oct 2000, Dmitriy M. Labutin wrote:
>
> > Dear All
> >
> > Could you please help me with loading comma delimited file into Oracle
> > database on Windows NT.
> >
> > The problem is some data fields contain carriage return pair, for
example
> >
> > 1,'abc
> > def'
> > 2,'data with carriage
> > return'
> > 3,'Data w/o carriage return'
> >
> > I can replace this pair with perl script, but I need carriage return
> present
> > in the table - no replacement.
> >
> > Cheers,
> > Dimitri
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > --
> > Author: Dmitriy M. Labutin
> > INET: Dmitriy_Labutin_at_ep.minsk.by
> >
> > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> > San Diego, California -- Public Internet access / Mailing Lists
> > --------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like subscribing).
> >
>
>
> Jared Still
> Certified Oracle DBA and Part Time Perl Evangelist ;-)
> Regence BlueCross BlueShield of Oregon
> jkstill_at_bcbso.com - Work - preferred address
> jkstill_at_teleport.com - private
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Jared Still
> INET: jkstill_at_bcbso.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Dmitriy M. Labutin
> INET: Dmitriy_Labutin_at_ep.minsk.by
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Dale Underwood
  INET: dale_at_fox.nstn.ca

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L