3 Replies Latest reply on Aug 12, 2013 5:47 PM by Barbara Boehmer Branched from an earlier discussion.

    Re: Can SQL Loader read newline chars - multiline column?

    Deep2910

      I have a scenario where the data file has values separated by ^.While loading this data into the table using sql loader I want to convert it into multiple lines.eg:

      data file:

      1|1013 park ridge~12345~irving|

      2|2013 park ridge~12345~irving|

      3|1013 park ridge|

       

      while loading it into table i want the data like

       

          ID ADDRESS

      1 "1013 hidden ridge

      12345

      irving"

      2 "2013 hidden ridge

      12345

      irving"

      22 "22013 hidden ridge

      12345

      irving"

      1 1013 hidden ridge

       

      My control file says:

       

      load data
      infile "/usr2/home2/adistest/h91ftp/temp/owb_test/owb_test1.csv"
      preserve blanks INTO TABLE owbrep.owb_test1

      TRUNCATE
      fields terminated by '|' TRAILING NULLCOLS
      (
         id,
         address
      )

       

       

      Please suggest what should I do?

        • 1. Re: Can SQL Loader read newline chars - multiline column?
          Barbara Boehmer

          You can use the REPLACE function in your control file to replace whatever character, like ~ in your sample data, is where the newline should be with whatever your newline is on your system, like chr(10) as in the example below.

           

          SCOTT@orcl12c_11gR2> host type owb_test1.csv

          1|1013 park ridge~12345~irving|

          2|2013 park ridge~12345~irving|

          3|1013 park ridge|

           

          SCOTT@orcl12c_11gR2> host type test.ctl

          load data

          infile "owb_test1.csv"

          preserve blanks INTO TABLE owb_test1

          TRUNCATE

          fields terminated by '|' TRAILING NULLCOLS

          (

          id,

          address "REPLACE (:address, '~', CHR(10))"

          )

           

          SCOTT@orcl12c_11gR2> create table owb_test1

            2    (id       number,

            3     address  varchar2(60))

            4  /

           

          Table created.

           

          SCOTT@orcl12c_11gR2> host sqlldr scott/tiger control=test.ctl log=test.log

           

          SQL*Loader: Release 12.1.0.1.0 - Production on Mon Aug 12 10:17:45 2013

           

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

           

          Path used:      Conventional

          Commit point reached - logical record count 3

           

          Table OWB_TEST1:

            3 Rows successfully loaded.

           

          Check the log file:

            test.log

          for more information about the load.

           

          SCOTT@orcl12c_11gR2> select * from owb_test1

            2  /

           

                  ID ADDRESS

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

                   1 1013 park ridge

                     12345

                     irving

           

                   2 2013 park ridge

                     12345

                     irving

           

                   3 1013 park ridge

           

          3 rows selected.

          1 person found this helpful
          • 2. Re: Can SQL Loader read newline chars - multiline column?
            Deep2910

            Thanks for the answer.It works.But what if I have 1000 control files and so it will take a lot of time to identify such columns from the csv files.Can I apply this replace function as a generic one for all the columns in all the control files?

            • 3. Re: Can SQL Loader read newline chars - multiline column?
              Barbara Boehmer

              As far as I know, you will need to use replace for every column for which the ~ should be converted to newline.  If there are no columns for which ~ is contained in the data and should not be converted to newline, then you can probably just use replace for every column quicker than you can figure out which ones to use it for and which ones not to use it for.  As far as I know, there is nothing that you can put in one line of code that will do it for all of the columns.