Sample of the Excel file to be populated in Oracle.
2. Set up the odbc connection to excel file through
Control panel -> administrative tools – >Data source (odbc) (Windows )
3. Use the JDBC – ODBC BRIDGE DRIVER in the put the ‘odbc’ name defined above (for this example its ‘detail’) in the URL as show below in ODI
4. Define your Physical and Logical Schema
5. Link it to logical schema
6. Create a model and using a selective reverse and do the revere engineering
7. Finally all the selected tables with be visible as the data store as shown below
8. Right click to see the data, to check everything is fine
[ Note: The above procedure can be used to connect to Microsoft Access. Set up the ODBC connection using the Access driver and use the above process to set up the connection to Microsoft Access ]
This part of the Post is update on 20 – jun – 2010 in order to cover the few issue related to reading and avoiding unnecessary null Rows .
For some of the Excel files , the sheets don’t appear if you select ‘ Tables’ , we need to select ‘ System Tables ‘
Removing Unnecessary Null Rows
Due to space between the Main Header and the data , the space rows is captured as you can see in the below screenshot.
to remove this just put a Filter condition
and you will not have any more space being captured
[Note : please use the column which is unique and is like a primary id , since using a wrong column can lead to Filter of Correct Data ]
Removal of Extra Header
Some time we have header being in First few lines and they are visible in the data to remove that just use a simple Filter condition
Here i am trying to remove the unnecessary Header called base from the data , just use a simple query with column_name !=’string to be removed’