Load data from Oracle table to MS Excel

Hello everyone!

This tutorial is the exact opposite of one of my previous ODI tutorials. Here I will explain how to transfer the data from your Oracle table to a MicrosoftExcel spreadsheet. The process is quite simple and involves a change inknowledge modules and staging area.

Pre-requisites: Oracle 10g Express Edition with *SQLPlus, Oracle Data Integrator 11g (build version 11.1.1.7.0)

Over here I am assuming you have created your own source table in the Oracle database. In this case my table name is karan_employee. I have also created my target Excel sheet(.xlsx) named sample_excel.

Source table

create table karan_employee(EmpId int primary key, EmpName varchar(20), EmpCity varchar(20));

Target Excel sheet

Note: Make sure you have added the ODBC data source and the DSN name for the Excel sheet.

Open ODI Studio and follow the below steps!

Step 1: Create new data server, physical and logical schemas for both Oracle table and excel sheet

Step 2: Create models by reverse engineering table and sheet respectively

creating models

Step 3: Create an interface

Finally, run the interface. Check the status of the operation under theOperator tab. If the operation was successfully completed, you should now see the Excel sheet populated with data from your Oracle table.