12 Loading Data Stored in a Microsoft Excel File

Scenario

A company stores its employee data in an Excel file called employees.xls. This file contains two worksheets: employee_details and job_history. You need to load the data from the employee_details worksheet into a target table in Warehouse Builder.

Solution

To load data stored in an Excel file into a target table, you must first use the Excel file as a source. Warehouse Builder enables you to source data stored in a non-Oracle source, such as Microsoft Excel, using the Heterogeneous Services component of the Oracle Database.

Figure 12-1 describes how the Oracle Database uses Heterogeneous services to access a remote non-Oracle source.

Figure 12-1 Heterogeneous Services Architecture

Description of "Figure 12-1 Heterogeneous Services Architecture"

The Heterogeneous Services component in the database communicates with the Heterogeneous Services agent process. The agent process, in turn, communicates with the remote database.

The agent process consists of agent-generic code and a system-specific driver. All agents contain the same agent-generic code. But each agent has a different driver depending on the type of data being sourced.

Case Study

This case study shows you how to use an Excel file called employees.xls as a source in Warehouse Builder.

Step 1: Install ODBC Driver for Excel

To read data from Microsoft Excel, you must have the ODBC driver for Excel installed.

Step 2: Delimit the Data in the Excel File (Optional)

To source data from an Excel file, define a name for the range of data being sourced:

In the employee_details worksheet, highlight the range that you want to query from Oracle.

The range should include the column names and the data. Ensure that the column names confirm to the rules for naming columns in the Oracle Database.
From the Insert menu, select Name and then Define. The Define Name dialog box is displayed. Specify a name for the range.

Step 3: Create a System DSN

Set up a System Data Source Name (DSN) using the Microsoft ODBC Administrator.

Select Start, Settings, Control Panel, Administrative Tools, Data Sources (ODBC).

This opens the ODBC Data Source Administrator dialog box.
Navigate to the System DSN tab and click Add to open the Create New Data Source dialog box.
Select Microsoft Excel Driver as the driver for which you want to set up the data source.

Click Finish to open the ODBC Microsoft Excel Setup dialog box as shown in Figure 12-2.

Figure 12-2 ODBC Microsoft Excel Setup Dialog Box

Description of "Figure 12-2 ODBC Microsoft Excel Setup Dialog Box"
Specify a name for the data source. For example, odbc_excel.
Click Select Workbook to select the Excel file from which you want to import data.
Verify that the Version field lists the version of the source Excel file accurately.

Step 4: Create the Heterogeneous Services Initialization File

To configure the agent, you must set the initialization parameters in the heterogeneous services initialization file. Each agent has its own heterogeneous services initialization file. The name of the Heterogeneous Services initialization file is initSID.ora, where SID is the Oracle system identifier used for the agent. This file is located in the ORACLE_HOME/hs/admin directory.

Create the initexcelsid.ora file in the ORACLE_HOME/hs/admin directory as follows:

HS_FDS_CONNECT_INFO = odbc_excel
HS_AUTOREGISTER = TRUEHS_DB_NAME = hsodbc

Here, odbc_excel is the name of the system DSN you created in Step 3. excelsid is the name of the Oracle system identifier used for the agent.

Step 5: Modify the listener.ora file

Set up the listener on the agent to listen for incoming requests from the Oracle Database. When a request is received, the agent spawns a Heterogeneous Services agent. To set up the listener, modify the entries in the listener.ora file located in the DATABASE_ORACLE_HOME/network/admin directory as follows:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = excelsid)
      (ORACLE_HOME = c:\oracle\db92)
      (PROGRAM = hsodbc)
    )
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = c:\oracle\db92)
      (PROGRAM = extproc)
      )
  )

For the SID_NAME parameter, use the SID that you specified when creating the initialization parameter file for the Heterogeneous Services, which, in this case, is excelsid.
Ensure that the ORACLE_HOME parameter value is the path to your Oracle Database home directory.
The value associated with the PROGRAM keyword defines the name of the agent executable.

Remember to restart the listener after making these modifications.

Note:

Ensure that the initialization parameter GLOBAL_NAMES is set to FALSE in the database's initialization parameter file. FALSE is the default setting for this parameter.

Step 6: Create an ODBC Source Module

Use the following steps to create an ODBC source module:

From the Project Explorer, create an ODBC source module. On the navigation tree, ODBC is listed within the Non-Oracle node under the Databases node.
You can provide the connection information for the source location either at the time of creating the module, or while importing data into this module.
To provide connection information while creating the module, on the Connection Information page, click Edit and provide the following details:

Ensure that the service name you provide is the same as the SID_NAME you specified in the listener.ora file.

Provide the host name and the port number using the Host Name and Port number fields respectively.

Because you are not connecting to an Oracle database, you can provide dummy values for user name and password. The fields cannot be empty.

The Schema field can be left empty because you will not be importing data from a schema.

Step 7: Import Metadata from Excel Using the Metadata Import Wizard

Use the Metadata Import Wizard to import metadata from the Excel file into Warehouse Builder. Select Tables as the Filter condition. The wizard displays all the worksheets in the source Excel file under the Tables node in the list of available objects.

Select employee_details and use the right arrow to move it to the list of selected objects.
Click Finish to import the data.

The data from the employee_details worksheet is now stored in a table called employee_details in the ODBC source module.

Step 8: Create a Mapping to Load Data Into the Target Table

In the Warehouse Builder console, expand the module that contains the target table. Use the table called employee_details in the ODBC source module as a source to load data into the target table. Figure 12-3 displays the mapping used to load data into the target table.

Figure 12-3 Mapping to Load Data Into the Target Table

Description of "Figure 12-3 Mapping to Load Data Into the Target Table"

Step 9: Deploy the Mapping

Use the Control Center Manager or Design Center to deploy the mapping you created in step 8. Ensure that you first deploy the source module before you deploy the mapping.

Troubleshooting

This section lists some of the errors that you may encounter while providing the connection information.

Error

ORA-28546: connection initialization failed, porbable Net8 admin error

ORA-28511: lost RPC connection to heterogeneous remote agent using
SID=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(Host=localhost)(PORT=1521)))(CONNECT_DATA=(SID=oracledb)))

ORA-02063: preceeding 2 lines from OWB###

Probable Cause

Providing the same SID name as that of your database.

Action

Provide an SID name different from the SID name of your database.

Error

ORA-28500: connection from ORACLE to a non-Oracle system returned this message: 
[Generic Connectivity Using ODBC][H006] The init parameter <HS_FDS_CONNECT_INFO> 
is not set. Please set it in init<orasid>.ora file.

Probable Cause

Name mismatch between SID name provided in the listener.ora file and the name of the initSID.ora file in ORACLE_HOME/hs/admin.

Action

Ensure that the name of the initSID.ora file and the value provided for the SID_NAME parameter in listener.ora file is the same.

Tip:

Ensure that you restart the listener service whenever you make changes to the listener.ora file.