2 Replies Latest reply: Jan 29, 2013 10:33 PM by Karthick_Arp RSS

    New Line in data file for sql loader

    977650
      Hi all,

      I have a requirement to load a data file in which some of the rows are in new line as given in following example. If i put a '\n' character in control file, then it will load only those records which have newline in them ignoring non new line ones. please have a look at control fileand provide a solution to it :-


      32 grand street ~NY~NY

      42 riverdrive,
      apt 1B ~PL~TX

      Richardson road
      apt 32~ SF ~CA


      As you see there are newline characters in record 2 and 3, right now my control file looks like this -

      LOAD DATA
      INFILE 'example.txt' "STR '\r\n'"
      INTO TABLE "temp_table"
      INSERT
      FIELDS TERMINATED BY '~'
      TRAILING NULLCOLS
      (ADDRESS1,
      CITY,
      STATE)

      If i remove "STR '\r\n'" , then the data gets loaded into different rows like the third record looks like this -

      ADDRESS                         CITY                     STATE
      Richardson road null null

      apt 32 SF CA


      Please help.
        • 1. Re: New Line in data file for sql loader
          stefan nebesnak
          Your requirement is very unclear.

          Is this your INPUT ?
          --record 1
          32 grand street ~NY~NY
          
          --record 2
          42 riverdrive, --"NL"
          apt 1B ~PL~TX
          
          --record 3
          Richardson road --"NL"
          apt 32~ SF ~CA
          Is this your OUTPUT?
          ADDRESS      CITY STATE
          32 grand street NY   NY
          42 riverdrive,     null null
          apt 1B          PL   TX
          Richardson road null null
          apt 32           SF   CA
          Are you using Linux or Windows platform?

          You should have only one record terminator character.
          *\r\n* is Windows The End of Line character (The Carriage Return + The Line Feed).

          "STR '\r\n'" = "str X'0D0A'"

          If there is no data after \r\n, columns will be null.

          http://docs.oracle.com/cd/B28359_01/server.111/b28319/ldr_concepts.htm#i1005800
          • 2. Re: New Line in data file for sql loader
            Karthick_Arp
            There is a seperate forum to address SQL Loader related questons {forum:id=732}. Please post your question there.