This discussion is archived
2 Replies Latest reply: Sep 29, 2013 9:57 PM by vikramrathour RSS

CSV Data with newline character

vikramrathour Newbie
Currently Being Moderated

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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points