2 Replies Latest reply: Sep 29, 2013 11:57 PM by vikramrathour RSS

    CSV Data with newline character

    vikramrathour

      Hi,

       

      Data File - Sampledata.csv
      ID|TEXT1|CommentText
      5039587|FILREF:N01|XXXXX LTD    |
      5126306|FILREF:N02|YYYYYY INC|
      5636215|FILREF:N03|YYYYYY INC|
      5636314|Test (AA) 3B65 - Gilt Repo - legalweb  160674
      |ZZZZZZ LDN|
      
      
      Control File - Sampledata.ctl
      OPTIONS ( SKIP=1)
      load data
      infile * "str '\n'"
      TRUNCATE
      INTO TABLE DATALOAD_TEST
      FIELDS TERMINATED BY "|"
      TRAILING NULLCOLS
      (
      ID "NVL(REPLACE(REPLACE(:FACILITYID, CHR(9),' '), CHR(10),' '), ' ')",
      TEXT1 "NVL(REPLACE(REPLACE(:TEXT1, CHR(9),' '), CHR(10),' '), ' ')",
      COMMENTTEXT "NVL(REPLACE(REPLACE(:COMMENTTEXT, CHR(9),' '), CHR(10),' '), ' ')",
      )
      
      
      
      

      Command Line -

      sqlldr userid/pwd control=Sampledata.ctl,data=Sampledata.csv

       

       

      SQL*Loader: Release 11.2.0.2.0 - Production on Fri Sep 27 11:13:14 2013

       

       

      Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

       

       

      Commit point reached - logical record count 5

       

      What I need it that only 4 records should be there. I have looked at MOS Doc ID 453912.1

       

      Please help.

        • 1. Re: CSV Data with newline character
          DK2010

          Hi,

           

          You can use something like that

           

          SQL> !cat aa.csv

          ID|TEXT1|CommentText 

          5039587|FILREF:N01|XXXXX LTD    | 

          5126306|FILREF:N02|YYYYYY INC| 

          5636215|FILREF:N03|YYYYYY INC| 

          5636314|Test (AA) 3B65 - Gilt Repo - legalweb  160674

          |ZZZZZZ LDN|

           

           

          SQL> !cat aa.ctl

          OPTIONS ( SKIP=1) 

          load data 

          infile * 

          TRUNCATE 

          CONTINUEIF NEXT PRESERVE (1) = "|"

          INTO TABLE DATALOAD_TEST 

          FIELDS TERMINATED BY "|"  TRAILING NULLCOLS 

          ID "NVL(REPLACE(REPLACE(:ID, CHR(9),' '), CHR(10),' '), ' ')", 

          TEXT1 "NVL(REPLACE(REPLACE(:TEXT1, CHR(9),' '), CHR(10),' '), ' ')" , 

          COMMENTTEXT "NVL(REPLACE(REPLACE(:COMMENTTEXT, CHR(9),' '), CHR(10),' '), ' ')"

          )

           

           

          SQL>  host sqlldr me/me control=aa.ctl data=aa.csv

           

           

          SQL*Loader: Release 11.2.0.2.0 - Production on Fri Sep 13 18:06:22 2013

           

           

          Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

           

           

          Commit point reached - logical record count 3

          Commit point reached - logical record count 4

           

           

          SQL> select * from DATALOAD_TEST;

           

           

          ID                   TEXT1                                                        COMMENTTEXT

          -------------------- ------------------------------------------------------------ ----------------------------------------

          5039587              FILREF:N01                                                   XXXXX LTD

          5126306              FILREF:N02                                                   YYYYYY INC

          5636215              FILREF:N03                                                   YYYYYY INC

          5636314              Test (AA) 3B65 - Gilt Repo - legalweb  160674                ZZZZZZ LDN

          HTH

          • 2. Re: CSV Data with newline character
            vikramrathour

            Thanks,

             

            I used CONTINUEIF LAST <> "|", because there were records with multiple CRLF.

             

            However, the HEADER record in the data file does not end with a "|" and is causing an issue. My first data record is skipped as well.

             

            Regards,

            Vikram R