Prepare your Excel spreadsheet
First open up a Microsoft Excel spreadsheet, we will need to define a named range.
Step: 1) Open spreadsheet
Step: 2) Select Formulas-->Define Name
Step: 3) Select the range for the data
Define an ODBC Data Source
Step: 1) ODI will use an ODBC connection to natively access Microsoft Excel. Select start-->Administrative Tools-->data Source (ODBC)
Step: 2) click on Add select the Excel Driver press ‘Finish’. Specify the data source Name and description. Click on ‘Select Workbook’
Step: 3) Specify the Excel file path Press ok. You should now see your new ODBC Data Source listed in the ODBC Data Source Administrator.
Create a Data Server in Topology Manager
Step: 1) Open up Topology Manager and go to Physical Architecture. Right click on the Microsoft Excel technology and select New Data Server. In the Data Server window enter a name in the Name field.
Step: 2) Go to the JDBC tab. Select the Sun JDBC-ODBC Bridge in the JDBC Driver List.
Step: 3) In the JDBC URL template replace <odbc_dsn_alias> with the name of the ODBC Data Source you specified earlier. I used Excel data Server in this example.
Step: 4) Click on Test and make sure you get a successful connection.
Click OK.
Step: 5) Go to Newly created data server and Right click select New Physical Schema
Create New Logical Schema:
Create Model for Excel
Step: 2) Specify the Name, Technology and Logical schema.
Step: 3) Select Reverse Engineering Tab. Select system table check box and go to Selective reverse Engineering tab.
Step: 4) Here select Selective reverse Engineering and Objects to reverse Engineering check boxes.
Step: 5) Save it and perform reverse Engineer of this model.
Step: 6) Right click on data store select View data.
Done !
