This discussion is archived
1 Reply Latest reply: Aug 27, 2013 1:55 PM by Barbara Boehmer RSS

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

user288393 Newbie
Currently Being Moderated

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 Oracle ACE
    Currently Being Moderated

    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.

Legend

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