Connecting to Microsoft Excel using Oracle Data Integrator

The posts in this series assume that you have some level of familiarity with ODI. The concepts of Topology, Data Server, Physical and Logical Architecture 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.

In this post I will describe how a Microsoft Excel spreadsheet can be used in Oracle Data Integrator.
Microsoft Excel is one of the many different technologies you can leverage in ODI as a source or as a target.

Prepare your Excel spreadsheet

Prior to using a Microsoft Excel spreadsheet in ODI we need to specify a name for the different cell tables we want to use. You can have multiple names in the same spreadsheet.

First open up a Microsoft Excel spreadsheet, we will need to define a named range.

clip_image002

To do so, start by selecting the data you are interested in.

clip_image004

Then go to the Formulas tab and click on Define Name.

clip_image006

Specify a name in Name field, I used CITY_DATA in this example.

clip_image008

Click OK.

Note: This is slightly different in older versions of Microsoft Excel, please refer to its documentation to find out more information about how to name cells.

Define an ODBC Data Source

ODI will use an ODBC connection to natively access Microsoft Excel.

clip_image010

Open up the Data Sources menu in Microsoft Windows: click on the Start menu, go to Administrative Tools then click on Data Sources (ODBC).

The ODBC Data Source Administrator window will open up.

clip_image012

Click on Add...

clip_image014

In the Create New Data Source window, pick Microsoft Excel Driver and click on Finish.

clip_image016

In the ODBC Microsoft Excel Setup, enter any name in the Data Source Name field.
I used XLS_SRC_CITY in this example. This name will be used in Topology Manager to refer to this ODBC Data Source.

Click on Select Workbook...

clip_image018

Using the File Explorer select your Excel file and click on OK.
Make sure that the Read Only checkbox is unchecked otherwise you will get an error message while trying to insert data into the Excel spreadsheet (Error: '[Microsoft][ODBC Excel Driver] Operation must use an updateable query.').

clip_image020

You should now see your new ODBC Data Source listed in the ODBC Data Source Administrator.

Create a Data Server in Topology Manager

Open up Topology Manager and go to Physical Architecture.

Right on the Microsoft Excel technology and select Insert Data Server.

clip_image022

In the Data Server window enter a name in the Name field, I picked XLS_SRC_CITY.

clip_image024

Go to the JDBC tab.

clip_image026

Select the Sun JDBC-ODBC bridge in the JDBC Driver List.

In the JDBC URL template replace <odbc_dsn_alias> with the name of the ODBC Data Source you specified earlier. I used XLS_SRC_CITY in this example.

clip_image028

Here are the JDBC settings I used in this example:

· JDBC Driver: sun.jdbc.odbc.JdbcOdbcDriver

· JDBC URL: jdbc:odbc:XLS_SRC_CITY

Click on Test and make sure you get a successful connection.

Click OK.

In the Physical Schema window go to the Context tab to define a Logical Schema.

clip_image032

Click on clip_image030to insert a new row, select your context and enter a Logical Schema name in the Logical Schema column. I used XLS_SRC_CITY in this example.

Click OK to save and close the Physical Schema window.

We now have successfully created the infrastructure we need to use Microsoft Excel in our ODI processes.

In another post I will describe how to use Microsoft Excel as a source and a target in ODI.

Comments:

Is it just me or there simply no flexibility if you need to define data area (named range) before hand? Most use cases you'd want to automate ETL, thus data source must support varying number of entries (rows) at the very least. Is there any work around for this?

Posted by guest on October 20, 2010 at 08:29 PM PDT #

Is it possibel to have work on multiple sheets under one excel workbook ?

Posted by Anshuman Jain on February 17, 2011 at 05:03 AM PST #

Hi Anshuman, Yes if you have multiple spreadsheets they will appear as different tables when you reverse engineer the XLS file. Thanks, Julien

Posted by julien.testut on February 17, 2011 at 08:04 AM PST #

Is there any way in Oracle Data Integrator to fetch the path of the MS-Excel file or file name which we use as a parameter to run in the interface without hard coding the file name or its path?

Posted by shivesh on May 11, 2011 at 12:38 AM PDT #

Hi Shivesh, You can use an ODI variable as a startup parameter for your scenario and populate it with the path or the filename. You can find more information about this in the following blog post: http://blogs.oracle.com/dataintegration/entry/using_parameters_in_odi_the_dy_1. You could store the file names and their paths in a DB table and refresh your variable using it to dynamically pass that information to the ODI scenario. Thanks, Julien

Posted by Julien on May 12, 2011 at 07:06 AM PDT #

Hi Julien,

Same question here as the first one: Is there a way to process an excel file without having to define a range before hand? Because for example, we'll be working with a lot of auto-generated excel files that have no range definitions in them. Would we need to setup a 'define ranges' step (either done manually or if automatically if possible) first in the entire process before we could proceed with the data integration? Hope you could enlighten me.

Thanks and Regards,
Marco

Posted by Marco on July 18, 2011 at 11:20 AM PDT #

Hi Marco,

Yes this is possible, please have a look at Doc ID 424442.1 on My Oracle Support. This article walks you through how to set this up.

Thanks,
Julien

Posted by Julien on July 19, 2011 at 03:36 AM PDT #

is it possible to import/export data from oracle 10g database server onto excel file. and how?

Posted by guest on October 05, 2011 at 02:52 AM PDT #

Hi,

Yes, it is possible to load data from Oracle 10g into Excel using ODI. The Excel chapter in the ODI Doc will help: http://download.oracle.com/docs/cd/E21764_01/integrate.1111/e12644/excel.htm#insertedID0. Look at section 8.6.2.

Thanks,
Julien

Posted by Julien on October 07, 2011 at 06:14 AM PDT #

Hi Julien,

i was learning ODI while follwing your guide, and i got the follwing error while importing the data source into Topology Manager:

"microsoft obdc driver manager data source name not found and no default driver specified"

Any idea why it happnened? I triple checked the names, and i defined the data source within "User DNS" from odbcad32.exe

Thanks for help

Posted by Alex on October 17, 2011 at 03:55 AM PDT #

Hi Alex

You can check the following posts for clues;
http://forums.devarticles.com/microsoft-sql-server-5/data-source-name-not-found-and-no-default-driver-specified-8346.html

Sounds like some misconfiguration of the DSN for the context in which you are running ODI.

Cheers
David

Posted by David on October 18, 2011 at 04:32 AM PDT #

Hey I found the information in this post really interesting. I would like to confirm something.
I am trying to upload some info from an excel file to ODI, and after that to make ODI check if there is any update on that file and upload it.
Can I do this with ODI?
I do rally appreciate your help and guidance as I am new to this tech.
Best regards.
Jaison

Posted by Spanish_Guest on January 09, 2012 at 07:03 PM PST #

Hi Jaison,

There is no mechanism available to check if changes have been made to an Excel spreadsheet. You could check at the OS level (in an ODI procedure using some Groovy or Jython code) if the file has been modified and then upload it again but there is no way to detect which changes have been made in the file itself.

Thanks,
Julien

Posted by Julien on January 10, 2012 at 05:32 AM PST #

Thanx so much for your answer, I have already manged to upload from an excel file!

Now, I have gotten another question, to upload info now from an Oracle_DB_A to another Oracle_DB_B should i do the same procedure but now with the Oracle technology, no?

Once again thanx so much for your help, guidance and time.

Best regards.

Jaison

Posted by Spanish_Guest on January 11, 2012 at 09:59 PM PST #

Hi Jaison,

Yes, you will now need to define connectivity to your Oracle source and target and then create the models and interfaces needed. Use the Oracle specific Knowledge Modules: Doc -> http://docs.oracle.com/cd/E21764_01/integrate.1111/e12644/oracle_db.htm#Toc153688078.
For Oracle you have various data movement options: JDBC, DBLINK, datapump.

Thanks,
Julien

Posted by Julien on January 12, 2012 at 05:29 AM PST #

Hi Julien,

Fisrt of all thank you so much Julien!

Have done it and working now, by any chance have you gotteng any info or can redirect me to a doc where I can find ways to control with ODI:
+ How to check that in two tables the PKs are the same? For example: Column A and B in both table have gotten the same values. Can I make ODI to check this?

Best regards.

Jaison

Posted by Spanish_Guest on January 12, 2012 at 11:42 PM PST #

Jaison,

The Incremental Update KMs will do that for you: they will match records based on the PK (Update Key to be more precise) and then do an update when necessary. The KM doc I sent out previously lists the KMs available for each technology.
If you're more looking into checking referential integrity while moving the data then refer to the following Doc chapters:
- Models -> http://docs.oracle.com/cd/E21764_01/integrate.1111/e12643/create_rev_model.htm#CHDCDCHD
- Interfaces -> http://docs.oracle.com/cd/E21764_01/integrate.1111/e12643/interfaces.htm#CHDEHIDE (section '11.3.7 Set up Flow Control and Post-Integration Control')

Thanks,
Julien

Posted by Julien on January 13, 2012 at 08:41 AM PST #

Hi Julien,

I have multiple excel files with multiple spreadsheets in them. The format of all the workbooks is same only the data differ. I have defined named ranges in each and every spreadsheet. When I first reverse engineered the models in ODI for one of the workbooks all the named ranges appeared as system tables and I could load the data into a target table. But when I wanted to load data of the second workbook (by renaming it as the first file), the ODI job is failing because ODI is unable to see the named ranges in the second file even though they are there. My job is programmed to do the following:

1) Take an excel file from folder1 and rename it to the model filename (with which I reverse engineered the model).
2) read data from it.
3) Rename the file back to the original name,archive in a different folder (folder2) and delete it from the folder1.
4) Repeat the steps until there is no file left to process in folder1.

But my problem is ODI is not recognizing the named ranges in the second file even though they are exactly same as the first file.

Kindly help me with this.

Thanks in advance.
L

Posted by L on January 23, 2012 at 02:47 PM PST #

Hi L,

I'd check first if ODI can see the named ranges for those files without renaming them to see if this works fine. I suspect there might be an issue there. I'd also double-check that the structure for your datastores does look the same for all the files, if there is a difference it could explain the issue.

If everything works fine but it keeps failing when automating your process, I'd suggest to open up an SR and work with our Support team to fix your issue.

Thanks,
Julien

Posted by Julien on January 23, 2012 at 05:41 PM PST #

Hi Julien,

First of all, really thank you so much, for your help and guidance as they had been really useful to me.

Once again I will have to bring some data from some excel file, which I have already done and "know" how to do and I would like to confirm with you something.

What I shall do is the following:
From one excel workbook file which has a lot of info just in one spread sheet feed some tables with the data coming from it.

So what I have though is create mane name ranges (Define names on excel) as I need and work with them, but I will be facing with headers on the rows and columns. There is where I doubt if it will be possible. So far, I will not be able to modify the Excel’s file structure.

Something more or less like this:

Country1 Country 2
Sales1 9999999 9999999
Sales2 9999999 9999999
Sales3 9999999 9999999
Total123 9999999 9999999
Sales4 9999999 9999999
Sales5 9999999 9999999
Sales6 9999999 9999999
Total456 9999999 9999999

Best regards.

Jaison

Posted by Jaison on February 13, 2012 at 02:33 AM PST #

Hi Jaison,

You could define a name range which includes the Country1, Country2 headers as well as the empty header for the Sales1, Sales2, etc column. I didn't test this but ODI should give an arbitrary name to that column header and then when you integrate the data you can use only the column data you care about. I am assuming you don't care about the row header (Sales1, Sales2 etc.).
I hope this helps.

Thanks,
Julien

Posted by Julien on February 13, 2012 at 02:27 PM PST #

Hi Julien

I really thank you so much for all your help as it has been really useful to me.

Right now I am facing an issue when uploading data from an excel file which I have already uploaded and modified the data type of the cells.
By default when it comes from excel it comes in another format varchar and number, and I had manually changed it to varchar 250, as in some columns there will be numbers and letters.
That is why I have changed the type and length of them to varchar 250.

Is there a way to tell/order ODI to be always Varchar 250 when the reverse engineering is being done for the excel tables? Any recommendations or suggestions?

Please do not hesitate to contact me if any further info is required.

Best regards.

Jaison

Posted by Jaison on March 08, 2012 at 05:40 AM PST #

Hi Jaison,

Unfortunately you cannot force ODI to always use varchar 250 for the standard reverse engineering, this will need to be done manually.

Thanks,
Julien

Posted by Julien on March 12, 2012 at 04:53 PM PDT #

Hi Julien,

Thanx so much, for all your help, as it has been really usefull to me!

I have been uploading data from Excel files successfully ‘till now.

The files that I used to upload, were having the same “FileName.xls”. Now, what I do have is an excel file with different name, so I assume that as it was not created like that on the ODB it will not be uploaded, part of the name of the file is the same. I will let myself explain better.

MYBEAUTIFULXLS.xls ---Before
MYBEAUTIFULXLS_MORETEXT.xls ---Now

Could I use something like MYBEAUTIFULXLS% somewhere on ODI, to make him upload data from files that start with those characters?

I do really appreciate your ideas!

Best regards.

Jaison

Posted by Jaison on March 23, 2012 at 05:41 AM PDT #

Hi Jaison,

You could use an ODI variable for this, you will find more information about this in this post: https://blogs.oracle.com/dataintegration/entry/using_parameters_in_odi_the_dy_1.

Thanks,
Julien

Posted by Julien on March 26, 2012 at 12:52 AM PDT #

Hi Julien,

Thank you so much for your help,
In fact I had already reviewed those forums, but even though is not working for me. :-(

So far what I have doen is the following: -by the way, I have posted this on the forums https://forums.oracle.com/forums/thread.jspa?threadID=2364172&tstart=0 -
1. I have created a command procedure to list all the files into a *.lst file
cmd /c dir C:\<...path...>\ <...FileName...> * .xls /b /a:-d > C:\<...path...>\FileNamelist_HR1.lst
It is working, and it does create a *.lst file with all the files that start with the <...FileName...> * .xls

2. I have created an interface to put all the name files on a temporaly table. The table is being populated correctly.

3. I have created an ODI Variable that retrieves the name of the files, as the loop passes by.

4. HERE is the issue:
I have created an Interface, which is suppoused to be taking the variable but it aint!
It is just taking one of the files

Any ideas, I have checked and everything is suppoused to be ok, but it is not as its not bringing the correct data or better said being able to look on the other excel files.
So, what I have done is the following: I t have removed the excel file that is passing ok and tried to excute another, it goes well until the interface as i get a window error message:
+Cannot find data: See com.borland.dx.dataset.DataSetException error code: Base+62+

View the logs at the bottom of the page

5. I have created another Command Procedure to delete the CurrentFileName (Variable), and it is doing all right.

Posted by Jaison on April 09, 2012 at 11:38 PM PDT #

Hi Jaison,

This must be due to the fact that the Excel connectivity relies on the ODBC entry which refers to one of your xls files. Here is what I would do:
- set up the ODBC connection to use a specific filename like file.xls
- then continue using your table and variable but rename the files you get dynamically to file.xls one after the other d
- run your interface
- archive that file if you need to keep track of it

Thanks,
Julien

Posted by Julien on April 12, 2012 at 08:55 AM PDT #

Very useful.

Posted by guest on June 23, 2012 at 05:37 AM PDT #

Hi Julien,

It has been a while since I last written! :-)

I am trying to upload an Excel file and it is doing quite all right when I am protecting the sheets inside!

I have faced some issues, as the users are not following our instructions (adding extra sheets, as they cannot modify the ones we have given them). So we have decided to secure the whole workbook. Here goes the BUT, but when I am trying to upload it, it does not work.

I do get a “Load Data” Error on the ODBC –Description error message below-

-5005 : S1000 : java.sql.SQLException: [Microsoft][ODBC Excel Driver] Could not decrypt file.
java.sql.SQLException: [Microsoft][ODBC Excel Driver] Could not decrypt file.
at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6958)
at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7115)
at sun.jdbc.odbc.JdbcOdbc.SQLDriverConnect(JdbcOdbc.java:3074)
at sun.jdbc.odbc.JdbcOdbcConnection.initialize(JdbcOdbcConnection.java:323)
at sun.jdbc.odbc.JdbcOdbcDriver.connect(JdbcOdbcDriver.java:174)
at com.sunopsis.sql.SnpsConnection.v(SnpsConnection.java)
at com.sunopsis.sql.SnpsConnection.c(SnpsConnection.java)
at com.sunopsis.sql.i.run(i.java)
Could I upload protected workbook to ODI? I do really appreciate your help and guidance.

Please do not hesitate to contact me if any further information is required.

Best regards.

Jaison

Posted by Jaison Gonzalez on September 07, 2012 at 03:03 AM PDT #

Hi Jaison,

This seems to be a limitation with the Excel ODBC driver and password protected files. You can find more information about it here: http://support.microsoft.com/kb/211378. I am not sure if the workarounds are acceptable in your case, please review.

Thanks,
Julien

Posted by Julien on September 07, 2012 at 12:41 PM PDT #

Hi Julien,

Thank you so much for your help!

Best regards.

Jaison

Posted by Jaison on September 10, 2012 at 12:27 AM PDT #

Dear all,

at the moment, we'll try to determine whether we can use ODI to load data into our data warehouse (Oracle 11g) or not.

Some of our source systems are Microsoft Access databases. ODI would be installed on a Red Hat Linux system. Does ODI bring its own ODBC drivers for MS Access or do we need to get/buy ODBC drivers to connect to Access from a Linux OS?

Thank you very much.

Best Regards
Sven

Posted by guest on October 15, 2012 at 06:02 AM PDT #

Hi Sven,

ODI does not provide an ODBC driver for Access, we typically rely on the vendor's drivers. For MS Access on Linux, there is a driver available from easysoft (http://www.easysoft.com/products/data_access/odbc-access-driver/index.html).

Thanks,
Julien

Posted by Julien on October 15, 2012 at 10:40 AM PDT #

Hi Julien,

thank you so much. That helped me a lot.

Best Regards
Sven

Posted by Sven on October 15, 2012 at 11:34 PM PDT #

Hi Julien,

First of all, your posts are tremendously helpful. Thank you so much for your advice!

We are currently experiencing a problem with Excel, ODBC, and ODI. We are trying to use an Excel data source to update an Oracle EPM application. This is with 32-bit Office 2007 on Windows Server 2008 R2 Datacenter 64-bit. We are able to create the physical and logical schemas and the test connection works fine. However, when trying to do a selective reverse in the model, we receive the error message: java.sql.SQLException: [Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application

Is there a recommendation for how to get around this?

Thank you!

Posted by guest on April 08, 2013 at 03:39 PM PDT #

Hi,

The error message "The specified DSN contains an architecture mismatch between the Driver and Application" is not related to ODI itself but seems to be linked to an environment issue. I'd Google it, I found this link which might be useful: http://msdn.microsoft.com/en-us/library/windows/desktop/ms712362%28v=vs.85%29.aspx

Thanks,
Julien

Posted by Julien on April 09, 2013 at 10:23 AM PDT #

After setting the server , When I am clicking Test Connection , I am getting this error . Could you please help me on this?

"java.sql.SQLException: [Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application
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$4.doInBackground(SnpsDialogTestConnet.java:519)
at com.sunopsis.graphical.dialog.SnpsDialogTestConnet$4.doInBackground(SnpsDialogTestConnet.java:1)
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] The specified DSN contains an architecture mismatch between the Driver and Application
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] The specified DSN contains an architecture mismatch between the Driver and Application
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
"

Posted by guest on July 31, 2013 at 11:32 PM PDT #

Hello,

This appears to be an ODBC setup issue, you can find more information on Microsoft's website: http://msdn.microsoft.com/en-us/library/windows/desktop/ms712362%28v=vs.85%29.aspx

Thanks,
Julien

Posted by Julien on August 01, 2013 at 11:41 AM PDT #

Hi,

I have done above mentioned steps, after adding data server when 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?

Posted by guest on December 04, 2013 at 05:11 AM PST #

Hi,

This is a generic error message, I'd recommend to contact Support and provide more information about your architecture so they can help troubleshoot the problem.

Thanks,
Julien

Posted by Julien on December 04, 2013 at 10:47 AM PST #

Hi,

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

Posted by Dinesh on December 22, 2013 at 11:50 PM PST #

Hi Dinesh,

On Linux you have a couple of options worth exploring with ODI:
- Use an ODBC driver for Unix: http://www.unixodbc.org/
- Use a JDBC driver for Excel: http://jexcelapi.sourceforge.net/ I've used JExcel a while back. It does convert the XLS content into CSV files behind the scenes which can be loaded into any target. There are other Excel JDBC driver options such as http://www.hxtt.com/excel.html.

Thanks,
Julien

Posted by Julien on December 23, 2013 at 11:52 AM PST #

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
23
24
25
26
27
28
29
30
   
       
Today