This discussion is archived
12 Replies Latest reply: Dec 6, 2012 10:42 AM by 978459 Branched to a new discussion. RSS

Can SQL Loader read newline chars - multiline column?

730474 Newbie
Currently Being Moderated
Hello All,

I am using SQL Ldr (Release 10.2.0.1.0) to load from a .csv file to a single table.

The .csv file however has a column which spans across multiple lines (with a newline chars).

Example below:

-----------------------
Record1:
A, B, "ABCD", "123"

Record2:
X, Y, "XY
Z", "456"

Record 3:
P, Q, "P
QR", "789"

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

Notice that record 2 & 3 has newline (it is not a word wrap)


My SQL loader treats each line as a new record!


I tried using the following in my control file but no avail
INFILE 'C:\xyz.csv' --"STR '\r\n'"

I also tried using the following for that particular column but with no use.
"REPLACE(:col_name,CHR(13) || CHR(10))",



Is there a way for this or is there a restriction on the SQLldr version or the Loader itself.


Someone please,

Thanks!
  • 1. Re: Can SQL Loader read newline chars - multiline column?
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    Pl see if MOS Doc 74719.1 (How to specify alternate end-of-record terminators in SQL*Loader) can help.

    Also see the doc at http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/ldr_concepts.htm#i1004677

    HTH
    Srini
  • 2. Re: Can SQL Loader read newline chars - multiline column?
    730474 Newbie
    Currently Being Moderated
    I cannot use
    INFILE "datafile_name" "var n"
    as each record in my input file varies ... upto 7 lines with a newline char

    Example:

    Record1:
    X, Y, "XY
    Z****
    *****
    **", "456"

    Record 2:
    P, Q, "P
    QR****
    ******", "789"
  • 3. Re: Can SQL Loader read newline chars - multiline column?
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    I believe you will need to use the "Stream Record Format"

    HTH
    Srini
  • 4. Re: Can SQL Loader read newline chars - multiline column?
    446825 Newbie
    Currently Being Moderated
    Hi,

    I have already tried using STF, but it doesn't work. Please provide some sample control file/e.g.

    Samples tried:
    load data
    infile *
    into table T
    TRUNCATE
    fields terminated by ',' optionally enclosed by '"'
    TRAILING NULLCOLS
    (
    BEXT,
    TEXT "replace(:text,'\\n',chr(10))"
    )
    begindata
    ***

    load data
    infile * "str X'2A2A2A0D0A'"
    into table T
    TRUNCATE
    (
    BEXT terminated by ',',
    TEXT CHAR(4000) terminated by ',' enclosed by '"'
    )
    begindata
    ***


    Thanks,
    Nitin jain
  • 5. Re: Can SQL Loader read newline chars - multiline column?
    446825 Newbie
    Currently Being Moderated
    Hi,

    I tried with a higher version of SQL Loader (10.2) and it worked with the STF format.

    //Nitin
  • 6. Re: Can SQL Loader read newline chars - multiline column?
    xkb Newbie
    Currently Being Moderated
    Hi Yuvi Jain,

    I'm stuck with the same problem here but I quite don't get how you got out of it (I use SQL Loader 11.2 on Linux).

    My control file test_sqlldr.csv.ctl :

    lOAD DATA
    INFILE "test_sqlldr.csv" "STR '\n'"
    INTO TABLE ODI_TEST.TEST_SQLLDR
    TRUNCATE
    FIELDS TERMINATED BY ','
    OPTIONALLY ENCLOSED BY '"'
    TRAILING NULLCOLS
    (
    COL1 ,
    COL2 terminated by ',' enclosed by '"'
    )

    When I launch :

    sqlldr ODI_TEST/ODI_TEST control=test_sqlldr.csv.ctl log=test_sqlldr.csv.log

    On file test_sqlldr.csv as following (EOL = \n) :

    l1c1, "l1c2",
    l2c1, "l2c2",

    ...both lines and columns get loaded fine.

    If test_sqlldr.csv looks like that :

    l1c1, "l1
    c2",
    l2c1, "l2c2",

    I get the following error message in the log :

    Record 1: Rejected - Error on table ODI_TEST.TEST_SQLLDR, column COL2.
    second enclosure string not present

    Basically, newline seems to have absolute precedence over opened enclosure " whatever my insistence (enclosed by '"').

    How do I load second column of first line as a multi-line text ?

    Edited by: xkb on 3 févr. 2011 16:50
  • 7. Re: Can SQL Loader read newline chars - multiline column?
    xkb Newbie
    Currently Being Moderated
    Hi,

    Little bump here, I'm still searching and still can't find a solution.

    I've found other concerned topics like :

    Re: SQL LOADER problem with line breaks in text field

    ...CONTINUEIF could not help in this case since next char sequence is not pre-determined.

    Nonetheless, syntactically/grammatically, enclosing fields with double-quote (") with a supplementary escape double-quote for the double-quote char itself doesn't leave any ambiguity and the parser should be OK with multiline text fields.

    If someone knows it just is *not* possible, please mention, it will help anyway.

    I could imagine a preparser (in whatever scripting language) to convert every actual line feed into char sequence "<LF>" (or "\n") to be reconverted into control chars once in database but I'd feel sorry to introduce a supplementary component upstream.

    Any help ?
  • 8. Re: Can SQL Loader read newline chars - multiline column?
    OrionNet Guru
    Currently Being Moderated
    Hello,

    Besides your fields delimited by ",". Insert a record delimiter (pipe, any special character doesn't appear in your day ) at the end of the record
    l1c1, "l1
    c2",
    l2c1, "l2c2"|
    
    1,2 ,3,4|
    And then change your control file e.g. *"STR '|\n'"*

    lOAD DATA
    INFILE "test_sqlldr.csv" "STR '|\n'"
    INTO TABLE ODI_TEST.TEST_SQLLDR
    TRUNCATE
    FIELDS TERMINATED BY ','
    OPTIONALLY ENCLOSED BY '"'
    TRAILING NULLCOLS
    
    ....
    
    {code}
    
    Then try
    {code}
    sqlldr ODI_TEST/ODI_TEST control=test_sqlldr.csv.ctl log=test_sqlldr.csv.log
    {code}
    
    If still failing, please post 3 different sample records with table structure.
    
    Regards
    OrionNet
    
    Edited by: OrionNet on Mar 2, 2011 5:40 PM                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
  • 9. Re: Can SQL Loader read newline chars - multiline column?
    xkb Newbie
    Currently Being Moderated
    Hi,

    Many thanks for your interest in my problem.

    Nonetheless, you are shifting it to another : if I had an actual unambiguous delimiter to my logical end of records, there would be no problem at all, dos/unix end of line markers would simply be ignored as delimiters (be it <LF> or <CR><LF>).

    Problem is : some columns in database contain multiline text, hence contain newlines marker \n and file exporter program marks end of records with the very same \n (I have no control over that, it's not my process).

    Still, an "intelligent" loader could parse back the export file into database because grammar is consistent : fields are enclosed by double-quote " and double-quote from text itself is escaped (with another ")

    I can write the parser in Perl in a couple of minutes ; I don't want to do that (for the moment) ; I would like to stick to sqlldr options.

    Here is a speaking example from a Gvery Gfamous on-line contact manager (but a MSvery MSfamous spreadsheet would do the same) : I export my contacts to .csv file without much control on structure and it yields the following :
    "Full Name", "Full Address"\n
    "Benedict XII+IV", "Vatican"\n
    "Nicolas BRUNI", "55 rue du faubourg Saint-Honoré\n
    75008 Paris\n
    France"\n
    The second guy has a bit longer an address that takes 3 lines.
    I have three logical records (first one is the header with column names)

    My "intelligent" parser would quite rapidly figure out that second field from second record on line 3 continues on line 4 and 5 because double-quote " has not been encountered until then.

    I would just (infinitely) like to find the right configuration of sql loader in order to load Nicolas' three lines address back into a single line and column of a table.

    (I've thought of bruteforce loading line by line into a single varchar2(4000) column table and then parse in base with pl/sql but that's even hell of a worst solution than pre-parsing with another script language).

    I *do* appreciate your suggestions.
    (don't tell me I should change my acquaintances :-)
  • 10. Re: Can SQL Loader read newline chars - multiline column?
    853090 Newbie
    Currently Being Moderated
    Hi..I am having the same issue. Did your issue get resolved?
  • 11. Re: Can SQL Loader read newline chars - multiline column?
    912314 Newbie
    Currently Being Moderated
    Oracle should really state somewhere that SQL Loader doesn't support the CSV [RFC 4180|http://tools.ietf.org/html/rfc4180] standard... There is no shame on that, I think that the aim of SQL Loader is to load data, not to be compliant with every plain text format standards. Also the RFC 4180 standard is not well recognized, CSV is known as "having no standard".

    Still, I lost half a day searching for a solution for this problem. A lots of forums, web sites give bad solutions that don't work. I would have been happy if on my first search result, I could have this answer...

    (Correct me if I'm wrong so I won't need to write a pre-processor to handle the line-feeds on my CSVs.)

    Edited by: 909311 on 20 janv. 2012 04:56
  • 12. Re: Can SQL Loader read newline chars - multiline column?
    978459 Newbie
    Currently Being Moderated
    I just ran into the same problem.

    Did you ever write the pre-processor to handle the line-feeds on the CSV? I guess it is not simple, because you first have to identify the right EOLs to replace. Any ideas or code to share?

    Thank you.

Legend

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