FAQ
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,

Search Discussions

  • Oliver Artelt at Oct 11, 2000 at 10:46 pm
    Hi Dimitri,

    can you replace the newlines and recode them with update after loading?

    oli
    On Mit, 11 Okt 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).
    --
    ---

    Oliver Artelt, System- und Datenbankadministration
    ---------------------------------------------------------------
    cubeoffice GmbH & Co.KG # jordanstrasse 7 # 39112 magdeburg
    telefon: +49 (0)391 6 11 28 10 # telefax: +49 (0)391 6 11 28 19
    email: oli@cubeoffice.de # web: http://www.cubeoffice.de
    ---------------------------------------------------------------
  • Jared Still at Oct 12, 2000 at 5:01 am
    Try this:

    #!/usr/bin/perl

    # cleandata

    my $CR=chr(13);
    my $LF=chr(10);
    my $QUOTE="'";

    open(X,"x.txt");

    while() {

    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
  • Dmitriy M. Labutin at Oct 12, 2000 at 9:07 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-----
    From: Jared Still
    Sent: Thursday, October 12, 2000 9:06 AM
    To: Multiple recipients of list ORACLE-L
    Subject: Re: Data with carriage return and SQL loader

    Try this:

    #!/usr/bin/perl

    # cleandata

    my $CR=chr(13);
    my $LF=chr(10);
    my $QUOTE="'";

    open(X,"x.txt");

    while() {

    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
  • Dmitriy M. Labutin at Oct 12, 2000 at 9:09 am
    Hi, Oliver

    It could be solution but I need to load 70 tables with different column
    names!

    Cheers,
    Dimitri

    -----Original Message-----
    From: Oliver Artelt
    Sent: Thursday, October 12, 2000 2:51 AM
    To: Multiple recipients of list ORACLE-L
    Subject: Re: Data with carriage return and SQL loader

    Hi Dimitri,

    can you replace the newlines and recode them with update after loading?

    oli
    On Mit, 11 Okt 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).
    --
    ---

    Oliver Artelt, System- und Datenbankadministration
    ---------------------------------------------------------------
    cubeoffice GmbH & Co.KG # jordanstrasse 7 # 39112 magdeburg
    telefon: +49 (0)391 6 11 28 10 # telefax: +49 (0)391 6 11 28 19
    email: oli@cubeoffice.de # web: http://www.cubeoffice.de
    ---------------------------------------------------------------
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Oliver Artelt
    INET: oli_at_md.transnet.de

    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
  • Diana Duncan at Oct 13, 2000 at 3:16 pm
    It looks like you want to use the CONTINUEIF syntax.

    Diana

    -----Original Message-----
    From: Dmitriy M. Labutin
    Sent: Thursday, October 12, 2000 6:18 AM
    To: Multiple recipients of list ORACLE-L
    Subject: RE: Data with carriage return and SQL loader

    Hi, Oliver

    It could be solution but I need to load 70 tables with different column
    names!

    Cheers,
    Dimitri

    -----Original Message-----
    Sent: Thursday, October 12, 2000 2:51 AM
    To: Multiple recipients of list ORACLE-L

    Hi Dimitri,

    can you replace the newlines and recode them with update after loading?

    oli
    On Mit, 11 Okt 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).
    --
    ---

    Oliver Artelt, System- und Datenbankadministration
    ---------------------------------------------------------------
    cubeoffice GmbH & Co.KG # jordanstrasse 7 # 39112 magdeburg
    telefon: +49 (0)391 6 11 28 10 # telefax: +49 (0)391 6 11 28 19
    email: oli@cubeoffice.de # web: http://www.cubeoffice.de
    ---------------------------------------------------------------
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Oliver Artelt
    INET: oli_at_md.transnet.de

    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
  • Dale Underwood at Oct 16, 2000 at 3:19 am
    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 -----
    From: "Dmitriy M. Labutin"
    To: "Multiple recipients of list ORACLE-L"
    Sent: Thursday, October 12, 2000 6:10 AM
    Subject: RE: Data with carriage return and SQL loader
    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() {
    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
  • Dmitriy M. Labutin at Oct 16, 2000 at 7:59 am
    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
    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"
    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() {
    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedOct 11, '00 at 4:20p
activeOct 16, '00 at 7:59a
posts8
users5
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase