This discussion is archived
2 Replies Latest reply: Jan 31, 2013 10:02 PM by 935684 RSS

importing table data from excel sheet into oracle table

935684 Newbie
935684 Jan 30, 2013 2:48 AM
Currently Being Moderated
Hi all,

We have an excel sheet which contains a table(with datas) along with some comments(some extra information on table columns) below the table.We need to import only table data into oracle table.We dont need those comments data to be imported into the oracle table.Can you please let us know how to do it?

We have tried using import option and external table in sql developer.But we are not getting the desired result.
  • 33 Views
  • 1. Re: importing table data from excel sheet into oracle table
    rp0428 Guru
    rp0428 Jan 30, 2013 9:35 AM (in response to 935684)
    Currently Being Moderated
    >
    We have an excel sheet which contains a table(with datas) along with some comments(some extra information on table columns) below the table.We need to import only table data into oracle table.We dont need those comments data to be imported into the oracle table.Can you please let us know how to do it?

    We have tried using import option and external table in sql developer.But we are not getting the desired result.
    >
    Well the obvious way is to delete the comments and load the data the traditional way.

    If you are using 11g you could also write a simple filter program to filter the data and then specify the filter app in the external table PREPROCESSOR parameter.

    See this Oracle article on how to use this feature
    http://www.oracle.com/technetwork/issue-archive/2011/11-mar/o21nanda-312277.html

    And this example Oracle-base article has an examplel also
    http://www.oracle-base.com/articles/9i/external-tables-9i.php
    >
    11gR2 Updates
    Oracle 11g Release 2 introduced the PREPROCESSOR clause to identify a directory object and script used to process the files before they are read by the external table. This feature was backported to 11gR1 (11.1.0.7). The PREPROCESSOR clause is especially useful for reading compressed files, since they are unzipped and piped straight into the external table process without ever having to be unzipped on the file system.
  • 2. Re: importing table data from excel sheet into oracle table
    935684 Newbie
    935684 Jan 31, 2013 10:02 PM (in response to rp0428)
    Currently Being Moderated
    Can you please tell how to write a filter preprocessor program for eliminating comments and only picking up desired table?

Actions

Legend

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