1. How to Load Data from Excel
1.1 Main Steps
- Define a name in Excel
- Define an ODBC data source
- Create a data server in Topology Manager
- Create a data model in Designer
- Create an interface
1.1.1 Define a Name in Excel
Select the data you are interested in and go to Formulas tab to define a name.
Rename the name and add comment as below.
Define the cell format before define a name. This cell format will affect the data type when creating data model with “Reverse Engineer”.
Number->Number
Text->Varchar(255)
1.1.2 Define an ODBC Data Source
1.1.3 Create Data Server in Topology Manager
Rename jdbc:odbc:<odbc_dsn_alias> to jdbc:odbc:ODIExcel
Click “Test Connection” to test
Create physical schema
Create logical schema
1.1.4 Create a data model in Designer
Click “Reverse Engineer” to reverse the model structure
Double click “PRODUCT_INFO” view the data model
Update excel cell format to Text
Delete the model and recreate again. The data type will be reversed as VARCHAR(255)
View data to confirm.
There are some data with null value from line 6 to 10. We can filter these data when we create interface.
1.1.5 Create an interface
Define the filter condition execute on “Staging Area”.
Run the interface and check log
1.2 Reference
Using Microsoft Excel as a Source and a Target in Oracle Data Integrator
Connecting to Microsoft Excel using Oracle Data Integrator
Microsoft Excel as a Source and Target as Oracle in ODI 11.1.1.6
No comments:
Post a Comment