Oracle Data Integrator (ODI)

In this blog, we describe how to make some Excel data available to Oracle Data Integrator (ODI).

Oracle Data Integrator supports JDBC, enabling it to access data from databases for which a JDBC driver is available. The Easysoft JDBC-ODBC Bridge extends the number of databases that are available to Oracle Data Integrator by bridging between JDBC and ODBC. You can then also work with data from backends for which an ODBC driver is available. We use the Easysoft JDBC-ODBC Bridge and the ODBC Driver for Microsoft Excel to connect Oracle Data Integrator to Microsoft Excel.

Note Currently, the Easysoft JDBC-ODBC Bridge supports 32-bit ODBC drivers only. Windows includes a 32-bit version of Microsoft's Excel ODBC driver.

These steps show how to access some Excel data from Oracle Data Integrator on Windows:

  1. Create a new Excel spreadsheet.
  2. Add this data to Sheet1: 
    Id	MfgPartNumber	Manufacturer	ProductCategory	CategoryCode	Description	CreationDate	UOM	Price
1	UL743E	Belkin	Writing Instruments	WI101	.5mm burgundy Barrel Mechanical Pencil	2013-Jan-07 00:00:00	CASE	1.39
3	B5W71AW#ABA	UniBall	Writing Instruments	WI101	Ballpoint Pen Medium Point Black Barrel Black Ink	2013-Jan-21 00:00:00	ea	1.38
7	1752266	Belkin	Writing Instruments	WI101	Ballpoint Pen Retractable Fine Point Black Ink	2013-Mar-10 00:00:00	dozen	2.95
4	XV87878	Belkin	Writing Instruments	WI101	Chisel Point Highlighter Fluorescent Green	2013-Mar-10 00:00:01	each	1.4
3	C6Z47UT#ABA	Stabilio	Writing Instruments	WI101	Chisel Point Highlighter Turquoise Green	2013-Jan-22 00:00:00	each	2.26
  3. Create a named range for this data. To do this, select the data, and then in the box underneath the file menu, type a name for the range. For example, "Excel_Data_for_Oracle".
  4. Save the file as an Excel97-2003 format workbook. This is the format that the Excel ODBC driver bundled with Windows supports.
  5. Create a System ODBC data source for the new Excel spreadsheet.

    To do this, use the 32-bit version of ODBC Data Source Administrator on your Oracle Data Integrator machine. On some versions of Windows, this is located in Control Panel > Administrative Tools. On some version of Windows, you need to search for ODBC in the taskbar search box. The 32-bit version of ODBC Data Source Administrator should be clearly labelled. If in doubt, in the Windows Run dialog box, type:

    %windir%\syswow64\odbcad32.exe

    In the System tab of ODBC Data Source Administrator, select Microsoft Excel Driver (.xls) and then choose Add. In the ODBC Microsoft Excel Setup dialog box, you need to specify the spreadsheet path. Leave the other settings unchanged.

  6. Download the Easysoft JDBC-ODBC Bridge. (Registration required.)
  7. Install and license the Easysoft JDBC-ODBC Bridge on the machine where the local Excel ODBC data source is located.

    For installation instructions, see the Easysoft JDBC-ODBC Bridge documentation.

  8. Copy the Easysoft JDBC-ODBC Bridge JAR file, EJOB.jar, to the ODI userlib directory, for example, C:\Users\MyUser\AppData\Roaming\odi\oracledi\userlib.

    EJOB.jar is installed in the following location on the machine where you installed the Easysoft JDBC-ODBC Bridge:

    <easysoft_install>\Jars

    On 64-bit Windows, the default location for <easysoft_install> is <drive>:\Program Files (x86)\Easysoft Limited\Easysoft JDBC-ODBC Bridge.

  9. In Oracle Data Integrator Studio, choose the Topology tab.
  10. Under Physical Architecture, right-click Microsoft Excel, and then choose new Data Server.
  11. In the Data Server configuration dialog box, enter a Name for the Data Server.
  12. Click JDBC and then enter these details:
    Property Value
    JDBC Driver easysoft.sql.jobDriver
    JDBC URL jdbc:easysoft://localhost/MyExcelDataSourceName:logonuser=MyWindowsUserOnExcelMachine:logonpassword=MyPassword
  13. Save the new Data Server.
  14. Right-click the new Data Server, and then choose New Physical Schema.
  15. Name the Physical Schema. Choose Context.
  16. Click the + button. Choose a context and then enter a Logical Schema name. Save your changes.
  17. Choose the Designer tab.
  18. In the Designer tab, create a new Project.
  19. Create a new Excel Data Model. We will be copying the data from Excel into this Data Model.
  20. Name the new model and choose Microsoft Excel as the technology. Choose the Logical Schema you created earlier. Save your changes.
  21. Choose the Reverse Engineer tab.

    ODI imports the data to the data model. The data is named the same name as the range you created in Excel.

  22. Create a new Oracle Data Model. We will be copying the Excel data into this Data Model.
  23. Name the new model and choose Oracle as the technology. Choose your new project's folder as the default folder for the model. Save your changes.
  24. Create a new diagram under the Oracle Data Model. Name and describe the diagram when prompted.
  25. Drag the Excel data store from the Excel Data Model to the diagram pane. Choose OK when prompted to confirm that the data is from another model.

    If you don't see the Excel data structure in the diagram pane. Choose the Save All Button.

  26. Right-click the Oracle Data Model, and then choose Generate DDL.
  27. Choose No when prompted whether to process only tables in the ODI model.

    The Generate DDL dialog box is displayed.

  28. In the Hierarchical View tab, click the Synchronization check box, next to the Excel data. Choose OK.

    Oracle Data Integrator creates a new procedure in the folder for the project you created earlier. The procedure creates a new Oracle table that will hold the Excel data.

  29. Choose the Play button to execute the procedure and create the empty table.
  30. Right-click the Oracle Data Model, and then choose Generate Mappings IN.

    The Generate Mappings IN dialox box is displayed. Choose OK.

    A mapping between the Excel Data and the Oracle table is created.

  31. Run the mapping to populate the Oracle table with the Excel data.