Microsoft Excel as a Source and Target as Oracle in ODI 11.1.1.6

Microsoft Excel as a Source and Target as Oracle in ODI 11.1.1.6:

Today we are going to load data from excel file to oracle table. I have got so many mails from ODI developers requesting to write on this as they are facing couple of issues.

Note here, in case of Windows server 2008, 64 bit,  the 64 bit excel/access odbc driver will not be available for you. You can only see the default odbc driver location as “C:\WINDOWS\SysWOW64\odbcad32.exe” . But the problem is, it is not compatible with 64 bit ODI.

In this case you are probably going to see below error.
java.sql.SQLException: [Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application
To resolve above  issue, you have to use 64 bit excel/access driver. Download  link.

Since I am using windows 7, 32 bit and with ODI 11.1.1.6, there should not be any problem.

First open the excel file and put some data. Then select all rows and columns and go to formula tab. Click on Define name.
EXCELTAB1
EXCELTAB1_1

Now click ok and go to Control Panel\System and Security\Administrative Tools. Double click on Data Source(ODBC) and proceed as  per below screenshots.
EXCELTAB2

We have now created the data source successfully. Next open ODI Studio and go to topology. Create a new dataserver under excel technology and subsequently create physical schema and logical schema for it.
EXCELTAB4
EXCELTAB5

Create a new project.
EXCELTAB6

Go to Model and reverse the the excel file.
EXCELTAB7

Now create an interface ( Right click and new interface). Drag the source data store and target datastore. Select appropriate KMs and execute the interface. If you have not imported then right click on Knowledge Modules and click on import. Here you can select CKM Oracle, LKM Sql to Sql and IKM Sql Control Append.
EXCELTAB8
EXCELTAB9
EXCELTAB10
EXCELTAB11
EXCELTAB12

All looks good. Now execute the interface and check the status in operator.

EXCELTAB13

 

Hmm. “Invalid fetch size” error seems to be a known issue to me. Actually this is a bug in ODI 11.1.1.5.  Oracle has provided a patch to resolve this ( patch number 13528165 ). The same error also appears in ODI 11.1.1.6 as well, not sure why. As per Oracle support team this error should have been resolved in 11.1.1.6 but as per my testing its the same problem. When I talked with them, they suggested to go with below approach.

 

Go to the excel data server and change the Array fetch Size and Batch update Size to 0 (zero). The default value was 30. Save it and execute the interface again.
EXCELTAB14
EXCELTAB15

As you can see its running fine.
EXCELTAB16

Lets verify the data on target table. Yes, Its there 🙂
EXCELTAB17

That’s all for today. See you on my next post.

0

About the author

Bhabani(http://dwteam.in) - Currently Bhabani is working as Sr Development Engineer at Harman International. He has good expertise on Oracle, Oracle Data Integrator, Pervasive Data Integrator, MSBI, Talend and Java. He is also contributing in ODI-OTN forum for last 5 years. He is from India. If you want to reach him then please visit contact us page. If you have any doubts or concerns on the above article, please put your question here. Dw Team will try to respond it as soon as possible. Also dont forget to provide your comments / suggestions / feedback for further improvement. Thanks for your time.

Similar Posts

83 comments

Hi Bhabani, Its great to see your blog.

Hi Bhabani,

could you please explain the step by step xls or xlsx file generation from oracle table ASAP…(Table is source and xls file is target). we have an urgent requirement

Thanks,
Balakrishna

Hi Bhabani,
could you please explain the step by step xls or xlsx file generation from oracle table in ODI linux server not in windows ASAP…(Table is source and xls file is target). we have an urgent requirement
Thanks,
Balakrishna

this subject is a lot interesting for me in initial stage of ODI, it helped me so much.

It was a life saver bhabani. I dont have oracle support account. So there was no chance to download any patch. I decided to install latest version. But i got the same error in ODI 11.6. It made me really sad. But one of my friend redirected me to your blog. Above trick helped a lot.Great work.

Hi,

I am not able to see my Excel file in the selective reverse engineering tab,
can you tell me the reason

Thanks

Hi Yogesh,
The reason might with the datasource. Please delete the data source and recreate again. It will be better if you click on configure instead of adding a new one as given in my initial screenshot.

Also check the connection status in dataserver for successful or failure.
I am considering that you have selected all column and have given the name in formula tab.

Hi Bhabani,

I am facing one problem, at flow tab in the interface when I am trying to select IKM in the IKM selector as “IKM SQL To SQL Control Append”. It is not giving me any option in the list box.

May be you have selected staging is different than target. If this is the situation then you need multiconnection IKM. Open the IKM check the multiconnection option. Now close interface and open again. You should see it in the drop down box.

java.sql.SQLException: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
at oracle.odi.jdbc.datasource.LoginTimeoutDatasourceAdapter.doGetConnection(LoginTimeoutDatasourceAdapter.java:133)
at oracle.odi.jdbc.datasource.LoginTimeoutDatasourceAdapter.getConnection(LoginTimeoutDatasourceAdapter.java:62)
at com.sunopsis.sql.SnpsConnection.testConnection(SnpsConnection.java:1125)
at com.sunopsis.graphical.dialog.SnpsDialogTestConnet.getLocalConnect(SnpsDialogTestConnet.java:163)
at com.sunopsis.graphical.dialog.SnpsDialogTestConnet.access$4(SnpsDialogTestConnet.java:159)
at com.sunopsis.graphical.dialog.SnpsDialogTestConnet$3.doInBackground(SnpsDialogTestConnet.java:247)
at oracle.odi.ui.framework.AbsUIRunnableTask.run(AbsUIRunnableTask.java:258)
at oracle.ide.dialogs.ProgressBar.run(ProgressBar.java:655)
at java.lang.Thread.run(Thread.java:662)
Caused by: java.sql.SQLException: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
at oracle.odi.jdbc.datasource.LoginTimeoutDatasourceAdapter.doGetConnection(LoginTimeoutDatasourceAdapter.java:133)
at oracle.odi.jdbc.datasource.LoginTimeoutDatasourceAdapter.getConnection(LoginTimeoutDatasourceAdapter.java:62)
at oracle.odi.core.datasource.dwgobject.support.OnConnectOnDisconnectDataSourceAdapter.getConnection(OnConnectOnDisconnectDataSourceAdapter.java:74)
at oracle.odi.jdbc.datasource.LoginTimeoutDatasourceAdapter$ConnectionProcessor.run(LoginTimeoutDatasourceAdapter.java:217)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:441)
at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)
at java.util.concurrent.FutureTask.run(FutureTask.java:138)
at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
… 1 more
Caused by: java.sql.SQLException: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6957)
at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7114)
at sun.jdbc.odbc.JdbcOdbc.SQLDriverConnect(JdbcOdbc.java:3073)
at sun.jdbc.odbc.JdbcOdbcConnection.initialize(JdbcOdbcConnection.java:323)
at sun.jdbc.odbc.JdbcOdbcDriver.connect(JdbcOdbcDriver.java:174)
at oracle.odi.jdbc.datasource.DriverManagerDataSource.getConnectionFromDriver(DriverManagerDataSource.java:410)
at oracle.odi.jdbc.datasource.DriverManagerDataSource.getConnectionFromDriver(DriverManagerDataSource.java:386)
at oracle.odi.jdbc.datasource.DriverManagerDataSource.getConnectionFromDriver(DriverManagerDataSource.java:353)
at oracle.odi.jdbc.datasource.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:332)
… 7 more

Hi what is OS version? is it 32 or 64 bit. The error clearly says that ODI is not able to see your odbc data source. Please provide more information regarding your steps.

ah ok thanks
have you other notes regarding that i am new in ODI pls:)

Thats it which I was looking for. Nice explanation Bhabani.

Yes Bhabani has given a nice info. Looking forward for much better than this. Keep the good work going.

It was really good task for ODI Developes…It’s very nice post..

how can i read excel file using excel sheet name

hi odi excel read by sheet is it possible ?

Yes, if you will check the system table option in the reverse engineer tab of your model, you can read tables based on your sheet name.
Thanks for visiting.

i want to read file from linux ftp server (ftp://192.168.100.1/test.xlsx) how can i do this. In this case odi excel reader only use administrative tools -> datasources

It is an excellent article it helped to complete exercise with Excel. I just started learning ODI. Please keep posting as much as you can.

Thanks Narayana. Keep visiting for more tips and tricks.

This is really well written and insightful. Glad I found your blog, warm regards from Ivan!

Hi Bhabani,

I am facing some error while implementing this in my system. Could you please help me to sort out this issue?. the error says

ODI-1217: Session EMP_EXCEL_LOAD (118025) fails with return code 8000.
ODI-1226: Step EMP_EXCEL_LOAD fails after 1 attempt(s).
ODI-1240: Flow EMP_EXCEL_LOAD fails while performing a Loading operation. This flow loads target table EMP_DATA.
Caused By: 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:540)
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:83)
at java.lang.Thread.run(Thread.java:662)
Thanks in advance!

-Rakesh

Thats a bug in ODI. If you have oracle support account then take a look in document id : 1366658.1

What is your odi version ? I guess it is fixed in ODI 11.1.1.6

Hi Bhabani,

That is really useful.I had followed the same steps as per the screenshots but I face a problem during reverse engineering,
I have created a model with the microsoft Excel technology and mapped it to the right logical schema.
The file lists in the Selective Rev Engineering, but throws a warning that “You cannot reverse engineer a file type model.Use the reverse engineering function on the Column tab of the datastore”.
I tried that too but when i click on the search to add the resource name it throws an error “The Directory specified in your schema does not exist”.
Would appreciate help.
Thanks in advance!!

-Hema

You have to select the technology as Microsoft excel in the technology option of your model. I believe you have selected the technology as file. Please let me know if i am correct or not to take the futher course of action.
Thanks

Hi Bhabani,

I have selected the Microsoft Excel as the technology, and my file also lists correctly in the Selective reverse engineering tab.But i am just not able to reverse engineer as it throws an error as I mentioned in my previous post .

Hi Hema, If your excel file is not too confidential then can you please send it to bhabaniranjan@gmail.com and support@dwteam.in ?

I am considering that you have tested below points you are successful on this.
1. You have defined the correct link between DSN and your Excel File .
2. You have test the Connection in Topology .
3. Excel file has an Extension of XLS .
4. When reversing I believe you have selected the proper logical schema.

Also let me know if we can have a teamviewer on this.

Hi Bhabani,

Can we put CKM static check on Excel or XML datastore in ODI ?

Regards,
Saurabh

Hi Bhabani,

can you please tell me how to create xml dataserver. I tried but throws an error. I think the problem is in jdbc url can u please guide me

Thanks&Regards,
Arjun

Plz use below jdbc driver and jdbc url

com.sunopsis.jdbc.driver.xml.SnpsXmlDriver
jdbc:snps:xml?f=D:\Oracle\Middleware\Oracle_ODI1\oracledi\demo\GEO_DIM.xml

Thank you for replay sir,
I mention Jdbc driver and jdbc url like this
com.sunopsis.jdbc.driver.xml.SnpsXmlDriver
jdbc:snps:xml?f=C:\ODI_PROJECT_FILES\PROMO_SYSTEM\PROMOTIONS.xml
When i test the connection it is successful.
when i reverse engineer the model there is no tables in that model.
can u please guide me………..

Thanks&Regards,
Arjun

Try doing selective reverse and check if you can see any tables are there or not. If your xml file is not that much confidential you can send it to me. This will be easier for me to track your issue.

Hi Bhabani,

sir i tried selective reverse engineer but i didn’t find any tables,sir i emailed xml file to support@dwteam.in.

Thanks&Regards,
Arjun

Mohammad Hefny / Reply

Just set the fetch size in the DataServer to ZERO.
it will work…. same when u want to work on Access Database.

Thanks for your point Mohammad. Normally this solution is used whenever people face some kind of error related to array invalid fetch size etc. If it works for other issues as well then thats great 🙂

I want to thank you, but I have one more question!

How can I load thу second excel files without deleting the first one?

Hi Ruslan,

You might have to create 4 folder to achieve multiple excel file processing in one interface and in one package. In folder1 keep all excel files. Using ODI move the 1st file to folder2 with a generic name ( for example EXCEL_FILE). Based on this generic file you have to create the odbc/datasource. Once the file will be processed by odi move this file from folder2 to done directory by appending timestamp to the end of filename ( ex: EXCEL_FILE_11_03_AM) or to error directory. Next move the second file from 1st folder to second folder with same name (EXCEL_FILE) and repeat the same until you move all files from 1st folder to done or error folder.

Hi Bhabani ,

I have a requirement to load excel file into ODI in Unix,
and it should be an automated process.

How can we achive this

I have two possible ways.
1. You might to purchase some jdbc drivers to process excel files in unix.
2. Most feasible one, create an agent on the windows box which one normally sending the excel files.Thats all, execute the package using this agent on unix.

Hi Bhabani
Thanks for the post it is very helpful.
Unfortunately. Once I applied the workaround Array fetch Size and Batch update Size to 0

It worked perfectly buy the last few days I am getting the same error Invalid Fetch Size

Any advice?

Thanks

What version of ODI you are using ?

Buffetaudyann@yahoo.fr / Reply

Hello I’m under ODI 11.1.1.6 and windows server 2008 R2.
This solution is working with a local, but with an agent in the same computer I still have the problem. Do you think that configuring opmn.xml file will resolve this according to oracle doc ID 1581026.1

Hi Bhabani,

Above mentioned was good example for loading data from excel to Oracle table in local machine

But how can we do if the same excel file is in linux server

Can you please explain us the step by step procedure to read the excel file from linux and load it in oracle db through ODI

Many Thanks,
Dinesh

Excel is a Microsoft product. So its too difficult to read in Linux env unless you have specific jdbc driver which can read excel files. There are many way around but not sure which one fits to you. You can create an agent in widows server and process files present in Linux. The only thing you need to make sure that the path is available to windows agent. Pretty simple 🙂

Next approach could be the excel jdbc driver created by Dev. You can get it from odiexperts.com. I think it is now free for everyone but you have to consult his team to know more on the support terms and condition. There also some other vendors who are proving excel jdbc drivers.

Hope it helps.
Bhabani

Very nice and simple tutorial, to connect with MS excel. Thank you

thanks you , i have one question:Invalid string or buffer length in ODI: i dont know whether ODI JDK and ODBC is 64bit cause it?

Iam using ODI version 11.1.1.7. Iam facing an issue with Excel Files in Windows 7 64 Bit OS. Firstly I was not able to create new data source as no data sources were visible on Add Options.So to create new data source ,I used odbcad32.exe from following path C:\Windows\SysWOW64 .Able to create the same but getting following error during reverse engineering .
java.sql.SQLException: [Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application.

The link which you have mentioned in your article is not working. Can you please mention Step by Step how to work with windows 7(64 bit) and ODI 11.1.1.7.

Will really appreciate that .

Not sure why the link is not working for you. You can search in Google and download the driver from Microsoft website. That is the only solution I have right now. Even after Google search if you are not getting then let me know I will send to your email id.

Thanks
Bhabani

do anyone know the xxxfilterdatbase that reverse from Excel how to generate it?

Can you please clarify bit more about your requirement?

Khaja Mohammed / Reply

Hi Bhabani,

I am able to create ODBC DS for excel in my windows environment, created interface and evetying is working fine.

Could you please tell me how do I create “ODBC data source for excel on UNIX systems” ??

Thanks in advance.

You have two option.
1. You can use some 3rd party jdbc driver those providing to process excel files in unix env. Odiexperts.COM has given one free driver for processing excel and ms access files.

2. If you dont trust those 3rd party drivers then oracle suggest to create one agent (agent only installation) in windows system. That agent will process those MS files.

Hope it make sense.

Khaja Mohammed / Reply

Hi Bhabani,

Thanks for your inputs :).

Can you please tell me, how can I use group by clause in ODI ?

In my requirment, input from 2 table and I have to generate the out put in one table
My sql querie is
select store, item, sum(quantity) from store group by store, item;
I have tried giving sum()function on my target column but it doesn’t work.

How can I implement this group by clause in ODI ?

what version of odi you are using. If you use sum() in your mapping then odi will automatically generate group by clause.

Khaja Mohammed / Reply

I am using ODI_11.1.1.6.0_GENERIC_111219.1055.
I tried with sum()but my output is wrong. Its loading all the input records to output.
For example If I hava a table as mentioned below
Input Table
date store item qty
d1 store1 Item1 1
d2 store2 Item2 1
d3 store3 Item3 1
d4 store4 Item4 1
d5 store1 Item1 3
d6 store2 Item2 2
d7 store3 Item3 5
d8 store4 Item4 4



If I used sum() in ODI mapping its loading all input table records in my output table.
How can I get my output as
week store item sum(quy)
w1 store1 item1 4
w2 store2 item2 3
w3 store3 item3 6
w4 store4 item4 5

how can I implement is logic in ODI ?
Your inputs will be highly appreciated and thanks in advance

Its very simple Khaja. I dont know why ODI is not putting group by clause. I just replicated a map in ODI and its putting group by for rest of the column.
Are you sure you are not using a customized KM? I checked with IKM Oracle Incremental Update and its working fine. Can you invite me for a team viewer ?

Thanks Bhabani,

I got the expected output. In ODI, sum() function and IKM Oracle Incremental Update resolved my problem.

Khaja Mohammed / Reply

Hi Bhabani,

Can you please tell me how to use RFI(Retail financial Integration) for EBs TO RMS integration using ODI.

If you can provide any documents or any link post also highly appreciated.

Thanks
khaja

Hi Bhabani,
I need one simple and easy answer from you.
Can we load data into the target database without creating the target tables first.?
I mean direct copy of data from source to staging and then to target.
Thanks in Advance.
charan

You can do this but you need atleast a table to be reversed to get the metadata. Then you can drop this table. Inside ODI KM, there is option to create target table on the fly.

BTW why do you choose this approach?

Hi Bhabani,
I am getting below error while executing the .xls file to oracle table. Can you please suggest how to resolved this issue.
[Microsoft][ODBC Driver Manager] Invalid string or buffer length.

Which version of ODI you are using? You should not face this issue with ODI 11.1.1.7

yes i am using ODI 11.1.1.7. Now i try again it working fine .. Can you please suggest is it magic or technical issue. Please suggest

Hi Bhabani,
I’m using ODI 12.1.2.0.0
I created excel data server and working successfully.
But I’m getting following error when I executing mapping work-flow.

ODI-1227: Task LKM SQL to SQL (Built-In) (Load DEFAULT_AP) fails on the source MICROSOFT_EXCEL connection ALM_EXCEL_SRC.
Caused By: java.sql.SQLException: [Microsoft][ODBC Excel Driver] Syntax error (missing operator) in query expression ‘HHI_DATA.YEAR YEAR’.
at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6964)
at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7121)
at sun.jdbc.odbc.JdbcOdbc.SQLExecDirect(JdbcOdbc.java:3117)
at sun.jdbc.odbc.JdbcOdbcStatement.execute(JdbcOdbcStatement.java:337)
at sun.jdbc.odbc.JdbcOdbcStatement.executeQuery(JdbcOdbcStatement.java:252)
at oracle.odi.query.JDBCTemplate.executeQuery(JDBCTemplate.java:197)
at oracle.odi.query.JDBCTemplate.executeQuery(JDBCTemplate.java:205)
at oracle.odi.runtime.agent.execution.sql.SQLDataProvider.readData(SQLDataProvider.java:100)
at oracle.odi.runtime.agent.execution.sql.SQLDataProvider.readData(SQLDataProvider.java:38)
at oracle.odi.runtime.agent.execution.DataMovementTaskExecutionHandler.handleTask(DataMovementTaskExecutionHandler.java:73)
at oracle.odi.runtime.agent.execution.SessionTask.processTask(SessionTask.java:214)
at oracle.odi.runtime.agent.execution.SessionTask.doExecuteTask(SessionTask.java:135)

Please guide,
Regards
Kusal

Hello, I have an ODI interface using Excel as source. It works if I execute with “Local (No Agent)”. But if I execute with a logical agent I get the error “Invalid string or buffer length”. What would cause this? I am using ODI 11.1.1.6.5
Thanks

Hello, Thanks for the quick response. In the other article you say — ” it shouldn’t be an issue if you use 32bit jdk for the agent. Just do one sanity check whether you are executing by using the agent you configured with 32 bit driver or not. ”
This may be my error because when I execute with no agent it works, but when I use an agent it fails. How do I check to see if the agent is configured with 32 bit driver?
Thanks,
Michael

You can verify using below command.
C:\>java -version
java version “1.6.0_25”
Java(TM) SE Runtime Environment (build 1.6.0_25-b06)
Java HotSpot(TM) 64-Bit Server VM (build 20.0-b11, mixed mode)

while in case of 32 bit JVM it will print
C:\> java -version
java version “1.6.0_26”
Java(TM) SE Runtime Environment (build 1.6.0_26-b03)
Java HotSpot(TM) Client VM (build 20.1-b02, mixed mode, sharing)

Once you figured it out, check the installation location. Copy that path up to JDK home and put this in ODIPARAMS.bat/.sh file.
Also create the DNS as I have mentioned in the article. Let me know if you face any issues.

thanks. I will try this.

Hi Bhabani,

We are trying to load data from MS Excel file to oracle.We are using standalone linux 11.1.1.7.0 agent.When i am trying test MS excel data store connection agent goes down.
Can you please help on this.
Want to konw why this is happening?
But when i am trying to same task with local(No agent)agent it is working fine.

What do you mean by agent goes down? Do you mean though the agent is up, while testing it is saying test connection failed?
Can you give the error stack of your agent while testing?

Hell Bhabani,

When i am testing MS Excel data store connection, then agent goes down.Before checking the data store agent is up,but if you testing the connection then it will goes down.
Below is the error stack:

oracle.odi.runtime.agent.invocation.InvocationException: http://LDBVDTX001.erpcc.lafarge.com:20940/oraclediagent:org.apache.commons.httpclient.NoHttpResponseException: The server LDBVDTX001.erpcc.lafarge.com failed to respond
at oracle.odi.runtime.agent.invocation.RemoteRuntimeAgentInvoker.invoke(RemoteRuntimeAgentInvoker.java:295)
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)
Caused by: org.apache.commons.httpclient.NoHttpResponseException: The server LDBVDTX001.erpcc.lafarge.com failed to respond
at org.apache.commons.httpclient.HttpMethodBase.readStatusLine(HttpMethodBase.java:1976)
at org.apache.commons.httpclient.HttpMethodBase.readResponse(HttpMethodBase.java:1735)
at org.apache.commons.httpclient.HttpMethodBase.execute(HttpMethodBase.java:1098)
at org.apache.commons.httpclient.HttpMethodDirector.executeWithRetry(HttpMethodDirector.java:398)
at org.apache.commons.httpclient.HttpMethodDirector.executeMethod(HttpMethodDirector.java:171)
at org.apache.commons.httpclient.HttpClient.executeMethod(HttpClient.java:397)
at org.apache.commons.httpclient.HttpClient.executeMethod(HttpClient.java:323)
at oracle.odi.runtime.agent.invocation.RemoteRuntimeAgentInvoker.invoke(RemoteRuntimeAgentInvoker.java:234)
… 8 more

Very strange issue. Agent should not get down just by testing datastore. What it seems to me you might have some datastores that is pointing to samba share (points to windows shared folder) to see the excel file. Somehow ODI is not able to connect to agent. But if you are testing excel file then make sure agent is running on windows machine not linux. Ping me to discuss on this.Thanks

Update as per discussion: Architecture issue. This is now fixed with jdbc excel

Hi Bhabani,

Thanks for the post.

If it is non-windows environment(linux) – then can you please let us know,how to load excel spread sheet in ODI 12.3 ?

Either you need third party excel jdbc driver or you have to create one agent on windows server.

Hi Bhabani,
Based on your document I did intial load for excel to table.while trying to do for incremental load am facing issue.Please help in this regards

Hi ,
Is it possible to convert xml to excel in oracle odi…if it is possible pls share me the steps…Thanks

Yes it is. Select Staging to a RDBMS/Sunopsis Memory Engine and go with normal mapping.
Loading:LKM SQL to SQL
Integration:IKM SQL Control Append
You need one data server for xml and other one for excel

Is it possible to load data in multiple tabs in the same excel ? If yes , how ?

Yes. It is possible. I will publish an article on this soon.

I’m getting below error and need a solution to it.

any help is appreciated

ODI-1227: Task SrcSet0 (Loading) fails on the source MICROSOFT_EXCEL connection MyEAIESBexcel.
Caused By: java.sql.SQLException: [Microsoft][ODBC Excel Driver] External table is not in the expected format.
at oracle.odi.jdbc.datasource.LoginTimeoutDatasourceAdapter.doGetConnection(LoginTimeoutDatasourceAdapter.java:133)
at oracle.odi.jdbc.datasource.LoginTimeoutDatasourceAdapter.getConnection(LoginTimeoutDatasourceAdapter.java:62)
at oracle.odi.core.datasource.dwgobject.support.OnConnectOnDisconnectDataSourceAdapter.getConnection(OnConnectOnDisconnectDataSourceAdapter.java:74)
at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:113)
at com.sunopsis.sql.SnpsConnection.createConnection(SnpsConnection.java:390)
at com.sunopsis.sql.SnpsConnection.connect(SnpsConnection.java:348)
at com.sunopsis.dwg.dbobj.SnpSessTaskSql.processTask(SnpSessTaskSql.java:2882)
at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(SnpSessTaskSql.java:2625)
at com.sunopsis.dwg.dbobj.SnpSessStep.treatAttachedTasks(SnpSessStep.java:577)
at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java:468)
at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java:2128)
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:722)
Caused by: java.sql.SQLException: [Microsoft][ODBC Excel Driver] External table is not in the expected format.
at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6956)
at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7113)
at sun.jdbc.odbc.JdbcOdbc.SQLDriverConnect(JdbcOdbc.java:3072)
at sun.jdbc.odbc.JdbcOdbcConnection.initialize(JdbcOdbcConnection.java:323)
at sun.jdbc.odbc.JdbcOdbcDriver.connect(JdbcOdbcDriver.java:174)
at oracle.odi.jdbc.datasource.DriverManagerDataSource.getConnectionFromDriver(DriverManagerDataSource.java:410)
at oracle.odi.jdbc.datasource.DriverManagerDataSource.getConnectionFromDriver(DriverManagerDataSource.java:386)
at oracle.odi.jdbc.datasource.DriverManagerDataSource.getConnectionFromDriver(DriverManagerDataSource.java:353)
at oracle.odi.jdbc.datasource.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:332)
at oracle.odi.jdbc.datasource.LoginTimeoutDatasourceAdapter$ConnectionProcessor.run(LoginTimeoutDatasourceAdapter.java:217)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471)
at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:334)
at java.util.concurrent.FutureTask.run(FutureTask.java:166)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
… 1 more

hi Bhabani,

I’m trying to load data from Oracle to Excel in ODI 12c. Can you show me the details? I have separated staging area and target with source and staging being the same logical schema. But still I’m getting error..If I use LKM SQL to SQL I’m getting error like C$_0EMP is not a valid name. If I omit LKM and use IKM SQL TO SQL Append, then I’m getting as “General Error” Kindly help.

Leave a replay to Manavalan A Cancel reply

required*

Are you a human? *