ODIExperts.com

The blog for Oracle Data Integrator ( ODI )

Step by step procedure to Read Excel (*.xls)

  1. Sample of the Excel file to be populated in Oracle.

    2. Set up the odbc connection to excel file through

    Control panel -> administrative tools – >Data source (odbc) (Windows )

    3. Use the JDBC – ODBC BRIDGE DRIVER in the put the ‘odbc’ name defined above (for this example its  detail) in the URL as show below in ODI

    4. Define your Physical and Logical Schema

    5. Link it to logical schema

    6. Create a model and using a selective reverse and do the revere engineering

    7. Finally all the selected tables with be visible as the data store as shown below

    8. Right click to see the data, to check everything is fine

    [ Note: The above procedure can be used to connect to Microsoft Access. Set up the ODBC connection using the Access driver and use the above process to set up the connection to Microsoft Access ]

 

This part of the Post is update on 20 – jun – 2010 in order to cover the few issue related to reading and  avoiding unnecessary null Rows .

For some of the Excel files , the sheets don’t appear if you select ‘ Tables’ , we need to select ‘ System Tables ‘

image

image

Removing Unnecessary Null Rows

image

Due to space  between the Main Header and the data , the space rows is captured  as you can see in the below screenshot.

image

to remove this just put a Filter condition

image

and you will not have any more space being captured

[Note : please use the column which is unique and is like a primary id , since using a wrong column can lead to Filter of Correct Data ]

image

Removal of Extra Header

Some time we have header being in First few lines and they are visible in the data to remove that just use a simple Filter condition

image

Here i am trying to remove the unnecessary Header called base from the data , just use a simple query with  column_name !=’string to be removed’

image

24 Comments

Leave a Reply

Required fields are marked *.


This site uses Akismet to reduce spam. Learn how your comment data is processed.