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

  1. I have multiple excel file like eg: Sales Jan.xls, Sales Feb.xls, Sales Mar.xls. How to cofigure multiple .xls file in ODI 11g please suggest.
    Thanks in Advance.

  2. For some reason I could not post the command. Basically it is getting the HEADER_ROWS option value using odi reference function getOption

    HdrRows=”

  3. I did the following modification to fix the issue I received while using ODI JDBC EXCEL driver.

    (1) Added a new option “HEADER_ROWS” (with default value of 1)

    (2) Modified the code in the “Load data from Excel” step as
    HdrRows=”

    (3) Used the header rows value in the Flow Control

    Thanks
    Kiran

  4. Hi,

    I have done above mentioned steps, but after adding data server by select technology ‘Microsoft Excel’ and then I am trying to do ‘test connection’, it is giving below error :

    oracle.odi.runtime.agent.invocation.InvocationException
    at oracle.odi.runtime.agent.invocation.RemoteRuntimeAgentInvoker.invoke(RemoteRuntimeAgentInvoker.java:265)
    at oracle.odi.runtime.agent.invocation.RemoteRuntimeAgentInvoker.invokeTestDataServer(RemoteRuntimeAgentInvoker.java:781)
    at com.sunopsis.graphical.dialog.SnpsDialogTestConnet.remoteTestConn(SnpsDialogTestConnet.java:585)
    at com.sunopsis.graphical.dialog.SnpsDialogTestConnet.access$10(SnpsDialogTestConnet.java:581)
    at com.sunopsis.graphical.dialog.SnpsDialogTestConnet$5.doInBackground(SnpsDialogTestConnet.java:558)
    at com.sunopsis.graphical.dialog.SnpsDialogTestConnet$5.doInBackground(SnpsDialogTestConnet.java:1)
    at oracle.odi.ui.framework.AbsUIRunnableTask.run(AbsUIRunnableTask.java:258)
    at oracle.ide.dialogs.ProgressBar.run(ProgressBar.java:656)
    at java.lang.Thread.run(Thread.java:662)

    can you please help me, what i have to check?

  5. I find your site extremely helpful, thank you.

    I just want to add a comment for ODI_JDBC_EXCEL that you made for loading from Excel files from a Unix server (I can’t add comments there).

    When I was using LKM EXCEL TO SQL_V1 to load in an interface I’ve got the error below at the load step.

    To solve this, I had to duplicate the LKM, add an option HEADER_ROWS for that LKM and change the code (in Jython)
    from
    HdrRows=”
    to
    HdrRows=”

    After that it worked just fine.

    Eroare: org.apache.bsf.BSFException: exception from Jython:
    Traceback (innermost last):
    File “”, line 16, in ?
    java.lang.NumberFormatException: For input string: “”
    at java.lang.NumberFormatException.forInputString(NumberFormatException.java:48)
    at java.lang.Integer.parseInt(Integer.java:470)
    at java.lang.Integer.parseInt(Integer.java:499)
    at excel.ReadExcel.ProcessExcel(ReadExcel.java:46)
    at excel.LKM.LoadExcel(LKM.java:21)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:597)

  6. How can i do to Oracle data to be populated in Excel file?

    Tks´s

    Rena

  7. would you know how to do this on Excel files placed on a unix server ?

  8. HI,
    I follow your tutorial but i can’t reverse my cel file
    i have the folowing error

    java.sql.SQLException: [Microsoft][ODBC Driver Excelodbc excel optional feature not implemented

    do you have any ideaas for me please?

  9. GR8 Article:

    Would appreciate , if you could explain the following:

    1) Loading of files from excel with no header,
    2) Skipping certain lines of code as header,
    3) Picking of multiple excel files from a single source folder using a single driver

    Regards
    Tej

    • Hi tej ,

      Loading of files from excel with no header,
      If in case the header comes into the data rows , you can use a filter so that you can block the record . say for the example if 103 is the header then you can use SHEET1_.”action” !=’103′

      Skipping certain lines of code as header,
      Use the same logic as defined above to remove the extra lines if incase you have spaces or Empty rows , use the column containing primary key IS NOT NULL ,
      SHEET1_.”action” IS NOT NULL where SHEET1_.”action” is the column name , so you can block empty spaces.

      Well for your third question , since at a time you relate DSN name to single Excel sheet, to the best of my knowledge i think you can do only one file , but if you can make the multiple excel files into multiple sheets and you can define each sheet as table and load all the sheets data at once.

      Hope i have answered your question . Please feel free if you have any other specific question.

  10. Hi Kshitiz,

    I was successfully able to read excel through ODI from the post. But it was on Windows machine though. I would like to know how to configure ODBC data saurce for excel on unix systems?

    Thanks!

  11. Hi Ankit,

    In the Reverse tab you have to select the option “system table” to reverse engineer the excel data model.

  12. Hi Ankit,

    In the Reverse tab you have to select the option “system table” to reverse engineer the excel data model.

    Regards.

  13. Hi Kshitiz,

    Great article.
    However, When I try to reverse engineer the data model, nothing gets reversed. I see no data stores created under this data model.
    I was able to Test the ODBC connection.

    Secondly, would you know how to do this on Excel files placed on a unix server ?
    Hi Kshitiz,

    Great article.
    However, When I try to reverse engineer the data model, nothing gets reversed. I see no data stores created under this data model.
    I was able to Test the ODBC connection.

    Secondly, would you know how to do this on Excel files placed on a unix server ?
    Hi Kshitiz,

    Great article.
    However, When I try to reverse engineer the data model, nothing gets reversed. I see no data stores created under this data model.
    I was able to Test the ODBC connection.

    Secondly, would you know how to do this on Excel files placed on a unix server ?

    Thanks

  14. Hi Kshitiz,

    Great article.
    However, When I try to reverse engineer the data model, nothing gets reversed. I see no data stores created under this data model.
    I was able to Test the ODBC connection.

    Secondly, would you know how to do this on Excel files placed on a unix server ?

    Thanks

Leave a Reply

Required fields are marked *.