Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2009
    Posts
    3

    SQLLDR Carriage Return

    Hi Gurus,

    Thanks for all your help in resolving my issues.

    I have a requirment to load the data in a temp table. But in the data file, for perticular column data having carriage return. Can you please help me to resolve this issue. Below is the table structure, ctl and Data file.

    CREATE TABLE REMOVE_CR_TEMP
    (
    ROW_ID VARCHAR2(15),
    COMMENTS VARCHAR2(2000),
    COMMENTS1 VARCHAR2(100)
    )

    --------------------------------------
    LOAD DATA
    APPEND
    INTO TABLE remove_cr_temp
    FIELDS TERMINATED BY "|"
    TRAILING NULLCOLS
    (
    ROW_ID,
    COMMENTS "replace(replace(:COMMENTS,chr(13),' '),chr(10),' ')",
    COMMENTS1
    )

    -----------------------------------------------------
    1|testrecord1|TEST
    2|testrecord2|TES2
    3|testrecord3|TEST3
    4|testrecord with
    carriage return|TEST4
    5|test record with
    carriage return2|TEST5
    6|test record with carriage
    return3|TEST6

    When I run this ctl file data is not loading properly.

    Please treat this as urgent and help me to resolve this issue.

    Thanks,
    Ravi

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,006
    CONTINUEIF is the keyword, I believe.

    Here's an example; I have created a table as per your example. Input data is contained in the control file (after the BEGINDATA keyword). I have replaced your "with carriage return" text with, well <Carriage return>.

    This is the control file:
    Code:
    LOAD DATA
    INFILE * 
    REPLACE
    
    CONTINUEIF NEXT PRESERVE (1) = "|"
    
    INTO TABLE remove_cr_temp
    FIELDS TERMINATED BY "|"
    TRAILING NULLCOLS
    (ROW_ID,
     COMMENTS,
     COMMENTS1
    )
    
    BEGINDATA
    1|testrecord1|TEST1
    2|testrecord2|TEST2
    3|testrecord3|TEST3
    4|testrecord4
    |TEST4
    5|testrecord5
    |TEST5
    6|testrecord6
    |TEST6
    Loading session:
    Code:
    SQL> $sqlldr scott/tiger@ora10 control=tctl.ctl log=tctl.log
    
    SQL*Loader: Release 10.2.0.1.0 - Production on Sri Srp 29 14:45:51 2009
    
    Copyright (c) 1982, 2005, Oracle.  All rights reserved.
    
    Commit point reached - logical record count 5
    Commit point reached - logical record count 6
    Result (I have formatted columns so that they would fit into a single record):
    Code:
    SQL> select * from remove_cr_temp;
    
    ROW_ID          COMMENTS             COMMENTS1
    --------------- -------------------- --------------------
    1               testrecord1          TEST1
    2               testrecord2          TEST2
    3               testrecord3          TEST3
    4               testrecord4          TEST4
    5               testrecord5          TEST5
    6               testrecord6          TEST6
    
    6 rows selected.
    
    SQL>

  3. #3
    Join Date
    Jul 2009
    Posts
    3
    Hi Littlefoot,

    Thanks for quick reply. Carriage return is a value in the data file, Let me change the data file.

    1|testrecord1|TEST
    2|testrecord2|TES2
    3|testrecord3|TEST3
    4|testrecord with
    issue 1|TEST4
    5|test record with
    issue2|TEST5
    6|test record with
    issue3|TEST6

    Could you please give me a solution for this.

    Thanks,
    Ravi

  4. #4
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    You may want to "cure" your datafile first. For example, make it so every line in your datafile starts specificly with a |. Then you can use CONTINUEIF as Littlefoot specified.

    If using unix/linux, you can use sed to add a specific char to every "record" in your datafile, or UltraEdit/gVim if on Windows (assuming they always start with a number)
    Code:
    sed 's/^\([0-9]\{1,\}\)/\|\1/g' yourdatafile
    Your sample data, as given here, would then look like below

    |1|testrecord1|TEST
    |2|testrecord2|TEST2
    |3|testrecord3|TEST3
    |4|testrecord with
    issue 1|TEST4
    |5|test record with
    issue2|TEST5
    |6|test record with
    issue3|TEST6

    Then, you can use a controlfile like this to load this data.
    Code:
    LOAD DATA
    INFILE yourdatfile
    REPLACE
    CONTINUEIF NEXT PRESERVE (1:1) != "|"
    INTO TABLE remove_cr_temp
    FIELDS TERMINATED BY "|"
    TRAILING NULLCOLS
    (
      DUMMY		FILLER,
      ROW_ID,
      COMMENTS,
      COMMENTS1
    )

  5. #5
    Join Date
    Jul 2009
    Posts
    3
    Thanks for your help... This solve my issue.

    I have a small doubt on REPLACE. When we can remove carriage return by using REPLACE(COL_NAME, CHR(13)||CHR(10),' ') in SQL, why this is not working in SQLLDR.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •