Oracle Data Integrator – Part 2 – Working with Excel Data

What have I learned today …

Today I took a closer look at declaring or using my Excel file as a source model for the ODI for which I’ve used the ODI User Guide and the help-documentation in ODI itself.


What do I need to do to get ODI to talk to my Excel file?

  • Create a new ODBC datasource for my Excel files using the Microsoft ODBC Data Source Administrator (run from the Configuration Panel)
  • Make sure you’ve defined a name for the data in the Excel spreadsheet. You can accomplish this by defining a name for the data shown in every worksheet. You have to mark the data in a specific worksheet (select all the rows and columns with data) and choose to insert a name for this data using the insert-menu.

For the configuration of ODI I’ve used different kinds of guidance, help then only the User Guide. To define my ODBC Datasource in the ODI I’ve followed the topic ‘Create a Microsoft Excel Data Server’ (written in the help-documentation of ODI) . This topic has a more detailed approach then the topic ‘Creating a Data Server’ explained in the User Guide.

The steps I’ve performed:

  • Connect to the Topology Manager (first open up the Designer from your Oracle Data Integrator menu)

  • Select Topology > Physical Architecture > Technologies > Microsoft Excel in the tree

  • Right click and select Insert Data Server
  • Fill in the necessary fields in the Definition and JDBC tab, such as the name and the JDBC Driver (sun.jdbc.odbc.JdbcOdbcDriver) and JDBC URL (jdbc:odbc: => the name you defined for your ODBC data source)

  • You can test your connection, test if the connection was succesfull by clicking the test-button in your Data Server properties screen.

The next step we need to perform is to create the physical schema for our Excel file to be able to map this ‘source’ schema to the ‘target’ schema.

  • Right-click on your newly created Data Server and choose to insert a new physical schema:
  • Leave the defaults as is for the Physical schema and choose the wright context in the context-tab which means you need to specify the logical schema you want to use. Specify a name and click apply and ok.

  • Let’s reverse engineer the Excel file into a new model. Go to the designer and choose to insert a new model. In the technology-dropdown choose ‘Microsoft Excel’ and choose your Logical Schema.
  • In the reverse-tab make sure to select the context you want to define the model in.
  • In the ‘Selective Reverse’ tab choose all checkboxes and the data you’ve defined names for in the spreadsheet will be listed. Click OK to reverse-engineering the defined data into your model.
  • In the model-tree you can see the different tables that where created inside the model depending on the data you’ve defined in the excel sheet.

  • In the different DataStores that were created (depending on the data you’ve defined in the spreadsheet) you can check the colums that were defined in your spreadsheet.

The next step is to start creating Interfaces to define the transformation that’s needed from our source-model to our target-model.

Stay Tuned

About these ads

9 thoughts on “Oracle Data Integrator – Part 2 – Working with Excel Data

  1. Hi Nathalie …Nice to read your posts.;)I am trying to create a Data Server, for Oracle, files, whatever….I keep receiving the same error: “This Object was modified by supervisor… your changes cannot be saved”Do you have to guarantee any kind of permissions?? The ones I gave were the ones described to snpm!!!Thanks and keep on with the good working.regards,Alvaro (alvaro.jsilva@wipro.com)

  2. I haven’t ran into this issue yet. You are just trying to define your technology stack in designer, is that correct?Are you getting the error when you’re trying to physical and logical model?

  3. hey,I’d to know if it s possible de export a table from oracle to an Excel file using Oracle Data Integrator ???THx in advance

  4. Hi ‘anonymous’,If you want to export table data from Oracle to an Excel file, you can just use SQL Developer for that purpose. You don’t need ODI to provide you that functionality.ODI can help you delivering data integration services, such as transforming or loading data as a service inside a business process, …

  5. Hi Z-Series,Jeff Pollock has commented on a thread regarding integrating SAP and ODI, these are your options:Regarding SAP, you have five choices:(1) Use ODI support for SAP iDOChttp://www.oracle.com/technology/products/oracle-data-integrator/10.1.3/htdocs/documentation/oracledi_tools_reference.pdf(2) Use ODI support for reverse engineering SAP tables via JDBC(this uses the SAP dictionaries/metadata to make them understandable)(3) Use custom BAPI/RDC APIs from SAP to generate/consume data files(then use the file handler capabilities from ODI to interact with the files)(4) Use iWay adapters to consume and post XML content to SAP R3 APIs(ODI’s XML handlers will interact with the iWay XML interfaces)(5) Use SAP XML Gateway or Xi to publish a standards-based (WSDL, Java, XML, etc) API that ODI can consume and/or invokeThe tradeoffs you should consider are whether you need to batch large volumes of data, work with lots of smaller messages, how SAP exposes the data you need (eg; not everything is available via iDOC etc), what latencies are acceptable etc. — these should influence your API entry-points to SAP and integration technology choices.

  6. Hi Nathalie …I wonder if you can help me. I want to extract an Excel File but the names of the columns are in the left. I can say that my table is horizontal so when I extract the data the names of the columns are not what I want. Example:month: jan feb mar year : 09 09 09Cost: 222 22 22 I appreciate if you can told me if there is a way to do this. thanksSorry for my english :P RegardsKaren (karenisole@hotmail.com)

  7. Hi Karen,Sorry for the late reply, but I haven't been able to work on your example yet.Can't you define the named range on the columns in the left, such as you would do for header-information.Kind regards,Nathalie

  8. I had tried this. but I didn’t get anything and I read one article that they said that ODI could not transform from excel. the problem haven’t solved yet

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s