Using Microsoft Excel as a Source and a Target in Oracle Data Integrator

The posts in this series assume that you have some level of familiarity with ODI. The concepts of Models, Datastores, Logical Schema, Knowledge Modules and Interfaces are used here assuming that you understand them in the context of ODI. If you need more details on these elements, please refer to the ODI Tutorial for a quick introduction, or to the complete ODI documentation for more details.

Recently we saw how to create a create a connection to Microsoft Excel let's now take a look at how we can use Microsoft Excel as a source or a target in ODI interfaces.

Create a Model in Designer

First we need to create a new Model and a datastore for our Microsoft Excel spreadsheet.

In Designer open up the Models view and insert a new Model.

clip_image002[5]

Give a name to your model, I used EXCEL_SRC_CITY.

Pick Microsoft Excel in the Technology drop-down list and select your logical schema in the Logical Schema list.

Go to the Reverse tab and set the Context appropriately, I used Development in this example.

clip_image002

Go to the Selective Reverse tab and click on Selective Reverse and Objects to Reverse to display a list of the named ranges defined in your Microsoft Excel spreadsheet.

clip_image004

Click on the Reverse button to start the Reverse Engineering process.
Then click Yes in the Confirmation window.
Finally click OK to close the model.

The Microsoft Excel data store should now be listed under your Microsoft Excel Model.

clip_image006

Right-click on it and select View Data to display its content and make sure everything is set up properly.

If you cannot view the data then most likely something is wrong with your setup.

clip_image008

Using Microsoft Excel as a source in an Interface

Go to the Projects view and insert a new Interface.

clip_image010

Give it a name, I used Excel to Oracle in this example.

Go to the Diagram tab and drag and drop your Microsoft Excel datastore into the Source panel of the Interface's Diagram tab.

clip_image012

Similarly ,drag and drop the target datastore into the Target panel of the Interface's Diagram tab, I used a table called TRG_CITY in this example.

clip_image014

Define the column mappings and go to the Flow tab of your interface.

clip_image016

To move data from Microsoft Excel into an Oracle table I used the LKM SQL to Oracle. For other databases use the LKM SQL to <other technology> provided with ODI.

To integrate the data into Oracle I used the IKM SQL Control Append as I am simply truncating the target table and doing inserts.

Click on Apply to save your interface and click on Execute to start its execution.

Using Microsoft Excel as a Target in an Interface

Using Microsoft Excel as a target is very similar to using it as a source.

Go to the Projects view and create a new Interface.

clip_image018

Give it a name and click on Staging Area Different From Target as the ODI Staging Area cannot be Microsoft Excel it has to be an RDBMS.
Then in the Logical Schema list select your source logical schema.
In this example I am using an Oracle table as a source and picked a logical schema called ORA_DEMO_SRC.

Now go to the Diagram tab of your interface and drag and drop your source table into the Source panel of the Interface's Diagram tab.

clip_image020

Now add your Microsoft Excel datastore in the Target panel of the Interface's Diagram tab.

clip_image022

Specify the column mappings and go to the Flow tab.

clip_image024

You should see that the Staging Area is on your source, click on the Target and pick the IKM SQL to SQL Append in the Integration Knowledge Module (IKM) list.
The IKM SQL to SQL Append is used to insert data into a target datastore when the ODI Staging Area is set to be different from the target.

Click on Apply to save your interface and click on Execute to start its execution.

Once the interface has been executed, open up your Excel spreadsheet to review its content.

Comments:

I was using Microsoft Excel as a Source and a Target in Oracle Data Integrator, I was using the blog, but I can not see the data in the table of destination. help, thanks.

Posted by ADRIANA on April 22, 2010 at 03:48 AM PDT #

Hi Adriana, Are you getting an error message? Is Operator reporting some inserts? Thanks, Julien

Posted by Julien Testut on April 26, 2010 at 07:40 AM PDT #

Hi In report It shows this error: 903 : 42000 : java.sql.SQLException: ORA-00903: invalid table name java.sql.SQLException: ORA-00903: invalid table name at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:316) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:282) at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:639) at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:185) at oracle.jdbc.driver.T4CPreparedStatement.execute_for_rows(T4CPreparedStatement.java:633) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1086) at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:2984) at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3057) at com.sunopsis.sql.SnpsQuery.executeUpdate(SnpsQuery.java) at com.sunopsis.dwg.dbobj.SnpSessTaskSql.execStdOrders(SnpSessTaskSql.java) at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTaskTrt(SnpSessTaskSql.java) at com.sunopsis.dwg.dbobj.SnpSessTaskSqlC.treatTaskTrt(SnpSessTaskSqlC.java) at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(SnpSessTaskSql.java) at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java) at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java) at com.sunopsis.dwg.cmd.DwgCommandSession.treatCommand(DwgCommandSession.java) at com.sunopsis.dwg.cmd.DwgCommandBase.execute(DwgCommandBase.java) at com.sunopsis.dwg.cmd.e.i(e.java) at com.sunopsis.dwg.cmd.g.y(g.java) at com.sunopsis.dwg.cmd.e.run(e.java) at java.lang.Thread.run(Unknown Source) thanks.

Posted by Adriana on April 27, 2010 at 11:19 PM PDT #

Adriana, The error message doesn't seem to be linked to Excel, at what step is this error message happening and which KMs are you using? Thanks, Julien

Posted by Julien Testut on April 30, 2010 at 08:51 AM PDT #

Good tutorial. It would be great if you could write one about using XML as source and target. I am running into serious issues using the XML capabilities of ODI and didn't get any answers in the forums

Posted by Codrin on May 04, 2010 at 10:58 PM PDT #

Hi.. This info was greatly help full, can you let me know if we can use this approach to load the data in to excel files in multiple tabs(excel sheets). Regards, Naveen

Posted by Naveen on October 19, 2010 at 04:35 AM PDT #

Naveen, Yes you can use this approach to load multiple spreadsheets in your Excel file. Each spreadsheet will have its own interface but a different target corresponding to each spreadsheet. Thanks, Julien

Posted by Julien Testut on October 21, 2010 at 07:24 AM PDT #

Hi, nice article. Let me a question - Can I against 1 ODBC (= 1 excel dataserver) connect more than 1 excel file? Gejza

Posted by Gejza on March 27, 2011 at 07:18 PM PDT #

Hi Gejza, I don't think so, an ODBC data source can only point to one single Excel file. This is an OS limitation. Thanks, Julien

Posted by julien.testut on March 28, 2011 at 04:13 AM PDT #

Hi Julien,
Suppose I have data where number of rows > 70000. Since a single excel worksheet cann't store this many number of records. I'll need to store the records in multiple worksheets with every sheet containing 65000 records, as soon as the 65000 records (excluding header) is stored in current worksheet, my code should switch to next worksheet and start writing in that including the header, this cycle continues till all records are inserted in to excel file.
Please let me know how to complete this?

Posted by Upendra Singh Rajpurohit on November 09, 2011 at 04:52 PM PST #

Can we delete the data in the target excel sheet and reload the same file using truncate or delete option while creating the interface.??

Posted by guest on November 13, 2011 at 06:47 PM PST #

Excel is my target in ODI. Can we delete the data and reload the file using truncate or delete all options in while creating interface

Posted by SatyaSunil on November 13, 2011 at 06:53 PM PST #

Hi Satya,

The Excel ODBC driver doesn't support delete or truncate operations.
See Doc ID 424288.1 on our Support portal.

Thanks,
Julien

Posted by Julien on November 14, 2011 at 09:23 AM PST #

Hi Upendra,

Each worksheet becomes a single ODI datastore so you will need to come up with a filter and a few interfaces (one per target worksheet) to populate them all: row 1 to 65000 handled by interface A, 65001 to MAX in interface B etc.
This is one approach, there might be others that would work.

Thanks,
Julien

Posted by Julien on November 14, 2011 at 09:35 AM PST #

Julien,

Can we use odifilemove option to move the excel file from one directory path to another?

Its throwing error while moving like "unable to delete the file". Is it due to excel driver which is referring to that file.?

Posted by SatyaSunil on November 14, 2011 at 08:06 PM PST #

Satya,

Yes you should be able to use OdiFileMove to move an Excel file. I am guessing the file might have still been in use which would explain the error.

Thanks,
Julien

Posted by Julien on November 15, 2011 at 09:37 AM PST #

Hi, I followed your tutorial, but there was an error when trying to execute it from Excel to Oracle. I would really appreciate if I could get any help. The error was:

8000 : null : java.sql.SQLException: ResultSet is closed
java.sql.SQLException: ResultSet is closed
at sun.jdbc.odbc.JdbcOdbcResultSet.checkOpen(JdbcOdbcResultSet.java:6646)
at sun.jdbc.odbc.JdbcOdbcResultSet.clearWarnings(JdbcOdbcResultSet.java:1765)
at sun.jdbc.odbc.JdbcOdbcResultSet.close(JdbcOdbcResultSet.java:1468)
at oracle.odi.runtime.agent.execution.sql.concurrent.FastJDBCRecordSet.close(FastJDBCRecordSet.java:124)
at oracle.odi.runtime.agent.execution.DataMovementTaskExecutionHandler.handleTask(DataMovementTaskExecutionHandler.java:113)
at com.sunopsis.dwg.dbobj.SnpSessTaskSql.processTask(SnpSessTaskSql.java:2906)
at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(SnpSessTaskSql.java:2609)
at com.sunopsis.dwg.dbobj.SnpSessStep.treatAttachedTasks(SnpSessStep.java:537)
at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java:453)
at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java:1740)
at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$2.doAction(StartSessRequestProcessor.java:338)
at oracle.odi.core.persistence.dwgobject.DwgObjectTemplate.execute(DwgObjectTemplate.java:214)
at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.doProcessStartSessTask(StartSessRequestProcessor.java:272)
at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.access$0(StartSessRequestProcessor.java:263)
at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$StartSessTask.doExecute(StartSessRequestProcessor.java:822)
at oracle.odi.runtime.agent.processor.task.AgentTask.execute(AgentTask.java:123)
at oracle.odi.runtime.agent.support.DefaultAgentTaskExecutor$2.run(DefaultAgentTaskExecutor.java:82)
at java.lang.Thread.run(Thread.java:662)

Posted by Caio on April 16, 2012 at 07:20 AM PDT #

Hello Caio,

I would suggest to reach out to our Support team to solve this issue. They will be best equipped to help debug this.

Thanks,
Julien

Posted by guest on April 16, 2012 at 12:01 PM PDT #

I'm able to setup the Excel ODBC connection and reverse engineer the model. I'm also able to see the data in the model so I know that part is setup correctly.

The problem occurs when I create an integration to load the Excel data from the source to a target (ORacle). The "Invalid Fetch Size" is the problem that has me perplexed. I've spent several hours trying to figure this out.

ODI-1227: Task SrcSet0 (Loading) fails on the source MICROSOFT_EXCEL connection regions_excel.
Caused By: java.sql.SQLException: Invalid Fetch Size
at sun.jdbc.odbc.JdbcOdbcStatement.setFetchSize(JdbcOdbcStatement.java:826)
at oracle.odi.runtime.agent.execution.sql.SQLDataProvider.readData(SQLDataProvider.java:80)
at oracle.odi.runtime.agent.execution.sql.SQLDataProvider.readData(SQLDataProvider.java:1)
at oracle.odi.runtime.agent.execution.DataMovementTaskExecutionHandler.handleTask(DataMovementTaskExecutionHandler.java:70)
at com.sunopsis.dwg.dbobj.SnpSessTaskSql.processTask(SnpSessTaskSql.java:2913)
at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(SnpSessTaskSql.java:2625)
at com.sunopsis.dwg.dbobj.SnpSessStep.treatAttachedTasks(SnpSessStep.java:561)
at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java:464)
at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java:2093)
at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$2.doAction(StartSessRequestProcessor.java:366)
at oracle.odi.core.persistence.dwgobject.DwgObjectTemplate.execute(DwgObjectTemplate.java:216)
at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.doProcessStartSessTask(StartSessRequestProcessor.java:300)
at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.access$0(StartSessRequestProcessor.java:292)
at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$StartSessTask.doExecute(StartSessRequestProcessor.java:855)
at oracle.odi.runtime.agent.processor.task.AgentTask.execute(AgentTask.java:126)
at oracle.odi.runtime.agent.support.DefaultAgentTaskExecutor$2.run(DefaultAgentTaskExecutor.java:83)
at java.lang.Thread.run(Thread.java:662)

Posted by Nishant on August 15, 2012 at 09:41 PM PDT #

Hi Nishant,

This is a known bug which has been fixed. You can find more information about it on our Support Portal (http://support.us.oracle.com) in Doc Id 1366658.1.

Thanks,
Julien

Posted by Julien on August 16, 2012 at 09:57 AM PDT #

Hi

I have a scenario to send bulk data to an excel file(Target) but it is throwing and error.
I saw this blog and implemented for the source as an excel file it's working as excepted,But target it's not working.Can please suggest me.

and also for the target as a excel file which knowledge i need to refer.

I used in the interface is
LKM SQL TO ORACLE
IKM ORACLE INCREMENTAL UPDATE

also i tried one more use case as staging area is different from target.but it didn't help

error:

ODI-1228: Task SrcSet0 (Loading) fails on the target MICROSOFT_EXCEL connection EMP.
Caused By: java.sql.SQLException: [Microsoft][ODBC Excel Driver] Syntax error in CREATE TABLE statement.
at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6957)
at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7114)
at sun.jdbc.odbc.JdbcOdbc.SQLPrepare(JdbcOdbc.java:4830)
at sun.jdbc.odbc.JdbcOdbcConnection.prepareStatement(JdbcOdbcConnection.java:475)
at sun.jdbc.odbc.JdbcOdbcConnection.prepareStatement(JdbcOdbcConnection.java:443)
at sun.reflect.GeneratedMethodAccessor65.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at oracle.odi.core.datasource.dwgobject.support.OnConnectOnDisconnectDataSourceAdapter$OnDisconnectCommandExecutionHandler.invoke(OnConnectOnDisconnectDataSourceAdapter.java:200)
at $Proxy2.prepareStatement(Unknown Source)
at oracle.odi.runtime.agent.execution.sql.SQLCommand.doInitializeStatement(SQLCommand.java:83)
at oracle.odi.runtime.agent.execution.sql.SQLCommand.getStatement(SQLCommand.java:117)
at oracle.odi.runtime.agent.execution.sql.SQLCommand.getStatement(SQLCommand.java:111)
at oracle.odi.runtime.agent.execution.sql.SQLCommand.execute(SQLCommand.java:158)
at oracle.odi.runtime.agent.execution.sql.SQLExecutor.execute(SQLExecutor.java:102)
at oracle.odi.runtime.agent.execution.sql.SQLExecutor.execute(SQLExecutor.java:1)
at oracle.odi.runtime.agent.execution.TaskExecutionHandler.handleTask(TaskExecutionHandler.java:50)
at com.sunopsis.dwg.dbobj.SnpSessTaskSql.processTask(SnpSessTaskSql.java:2913)
at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(SnpSessTaskSql.java:2625)
at com.sunopsis.dwg.dbobj.SnpSessStep.treatAttachedTasks(SnpSessStep.java:558)
at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java:464)
at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java:2093)
at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$2.doAction(StartSessRequestProcessor.java:366)
at oracle.odi.core.persistence.dwgobject.DwgObjectTemplate.execute(DwgObjectTemplate.java:216)
at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.doProcessStartSessTask(StartSessRequestProcessor.java:300)
at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.access$0(StartSessRequestProcessor.java:292)
at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$StartSessTask.doExecute(StartSessRequestProcessor.java:855)
at oracle.odi.runtime.agent.processor.task.AgentTask.execute(AgentTask.java:126)
at oracle.odi.runtime.agent.support.DefaultAgentTaskExecutor$2.run(DefaultAgentTaskExecutor.java:82)
at java.lang.Thread.run(Thread.java:662)

Posted by guest on May 20, 2013 at 07:29 AM PDT #

Hi,

Excel can be used as a target but cannot be the Staging Area location so the IKM Oracle Incremental Update or IKM SQL Incremental Update cannot be used. You can set the Staging Area for this Interface to your Source logical schema and use the IKM SQL to SQL Append instead. Please review the Excel chapter in the ODI doc as it contains instructions on how to set up such Interfaces.

Thanks,
Julien

Posted by Julien on June 04, 2013 at 03:19 PM PDT #

Hi ,
I am working wiTH ODI 11.1.1.5.0,while loading Excel file to ODI ,I am facing with an error Result Set Closed.In this blog as i read to follow support portal ,that link is not opening.Please suggest me to solve with this issue.
Thanks in Advance

Posted by guest on March 18, 2014 at 11:03 PM PDT #

Hi,

I'd recommend to work with Support on this, it may be your Excel ODBC driver config or the KMs you're using.

Thanks,
Julien

Posted by Julien on March 19, 2014 at 02:58 PM PDT #

Post a Comment:
  • HTML Syntax: NOT allowed
About

Learn the latest trends, use cases, product updates, and customer success examples for Oracle's data integration products-- including Oracle Data Integrator, Oracle GoldenGate and Oracle Enterprise Data Quality

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
2
3
5
6
7
8
9
10
12
13
14
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today