1 Reply Latest reply: Aug 27, 2013 3:55 PM by Barbara Boehmer RSS

    How to read specific lines from a text file using external table or any other method?

    user12249378

      Hi,

       

      I have a text file with delimited data, I have to pick only odd number rows and load into a table...

       

      Ex:

      row1:  1,2,2,3,3,34,4,4,4,5,5,5,,,5  ( have to load only this row)

      row2:   8,9,878,78,657,575,7,5,,,7,7

       

      Hope this is enough..

      I am using Oracle 11.2.0 version...

       

      Thanks

        • 1. Re: How to read specific lines from a text file using external table or any other method?
          Barbara Boehmer

          There are various ways to do this.  I would be inclined to use SQL*Loader.  That way you can load it from the client or the server and you can use a SQL*Loader sequence to preserve the row order in the text file.  I would load the whole row as a varray into a staging table, then use the TABLE and MOD functions to load the individual numbers from only the odd rows.  Please see the demonstration below.

           

          SCOTT@orcl12c> HOST TYPE text_file.csv

          1,2,2,3,3,34,4,4,4,5,5,5,,,5

          8,9,878,78,657,575,7,5,,,7,7

          101,201

          102,202

           

          SCOTT@orcl12c> HOST TYPE test.ctl

          LOAD DATA

          INFILE text_file.csv

          INTO TABLE staging

          FIELDS TERMINATED BY ','

          TRAILING NULLCOLS

          (whole_row VARRAY TERMINATED BY '/n' (x INTEGER EXTERNAL),

          rn SEQUENCE)

           

          SCOTT@orcl12c> CREATE TABLE staging

            2    (rn         NUMBER,

            3     whole_row  SYS.OdciNumberList)

            4  /

           

          Table created.

           

          SCOTT@orcl12c> HOST SQLLDR scott/tiger CONTROL=test.ctl LOG=test.log

           

          SQL*Loader: Release 12.1.0.1.0 - Production on Tue Aug 27 13:48:37 2013

           

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

           

          Path used:      Conventional

          Commit point reached - logical record count 4

           

          Table STAGING:

            4 Rows successfully loaded.

           

          Check the log file:

            test.log

          for more information about the load.

           

          SCOTT@orcl12c> CREATE TABLE a_table

            2    (rn       NUMBER,

            3     data  NUMBER)

            4  /

           

          Table created.

           

          SCOTT@orcl12c> INSERT INTO a_table (rn, data)

            2  SELECT s.rn,

            3         t.COLUMN_VALUE data

            4  FROM   staging s,

            5         TABLE (s.whole_row) t

            6  WHERE  MOD (rn, 2) != 0

            7  /

           

          17 rows created.

           

          SCOTT@orcl12c> SELECT * FROM a_table

            2  /

           

                  RN       DATA

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

                   1          1

                   1          2

                   1          2

                   1          3

                   1          3

                   1         34

                   1          4

                   1          4

                   1          4

                   1          5

                   1          5

                   1          5

                   1

                   1

                   1          5

                   3        101

                   3        201

           

          17 rows selected.