10 Microsoft Excel

It is important to understand how to work with Microsoft Excel in Oracle Data Integrator.

This chapter includes the following sections:

• Introduction

• Installation and Configuration

• Setting up the Topology

• Setting Up an Integration Project

• Creating and Reverse-Engineering a Microsoft Excel Model

• Designing a Mapping

Introduction

Oracle Data Integrator (ODI) integrates data stored into Microsoft Excel workbooks. It allows reverse-engineering as well as read and write operations on spreadsheets.

Concepts

A Microsoft Excel data server corresponds to one Microsoft Excel workbook (.xls file) that is accessible through your local network. A single physical schema is created under this data server.

Within this schema, a spreadsheet or a given named zone of the workbook appears as a datastore in Oracle Data Integrator.

Knowledge Modules

Oracle Data Integrator provides no Knowledge Module (KM) specific to the Microsoft Excel technology. You can use the generic SQL KMs to perform the data integration and transformation operations of Microsoft Excel data. See Generic SQL for more information.

Note:

Excel technology cannot be used as the staging area, does not support incremental update or flow/static check. As a consequence, the following KMs will not work with the Excel technology:

• RKM SQL (JYTHON)

• LKM File to SQL

• CKM SQL

• IKM SQL Incremental Update

• IKM SQL Control Append

• LKM SQL to SQL (JYTHON)

Installation and Configuration

Make sure you have read the information in this section before you start using the Microsoft Excel Knowledge Module:

• System Requirements and Certifications

• Specific Requirements

System Requirements and Certifications

Before performing any installation you should read the system requirements and certification documentation to ensure that your environment meets the minimum installation requirements for the products you are installing.

The list of supported platforms and versions is available on Oracle Technical Network (OTN):

http://www.oracle.com/technetwork/middleware/data-integrator/documentation/index.html.

Specific Requirements

There are no specific requirements for using Microsoft Excel files in Oracle Data Integrator.

Note:

ODI does not come with a specific driver to access Microsoft Excel and you have to acquire one from third-party vendors.

To install drivers from third-party vendors, refer to:

• For ODI Studio — Adding Libraries to ODI Studio

• For Standalone Agent or Collocated Agent — Adding Libraries to a Standalone or Standalone Collocated Agent

• JEE Agent — Adding Libraries to a Java EE Agent

Setting up the Topology

Setting up the Topology consists in:

1. Creating a Microsoft Excel Data Server

2. Creating a Microsoft Excel Physical Schema

Creating a Microsoft Excel Data Server

Create a data server for the Microsoft Excel technology using the standard procedure, as described in Creating a Data Server of Administering Oracle Data Integrator.

In the JDBC tab, for JDBC Driver and JDBC URL parameters , enter the details provided by your driver provider.

Creating a Microsoft Excel Physical Schema

Create a Microsoft Excel Physical Schema using the standard procedure, as described in Creating a Physical Schema in Administering Oracle Data Integrator .Note that Oracle Data Integrator needs only one physical schema for each Microsoft Excel data server.

Create for this physical schema a logical schema using the standard procedure, as described in Creating a Logical Schema in Administering Oracle Data Integrator and associate it in a given context.

Note:

An Excel physical schema only has a data schema, and no work schema. Microsoft Excel cannot be used as the staging area of a mapping.

Setting Up an Integration Project

Setting up a Project using the Microsoft Excel follows the standard procedure. See Creating an Integration Project of Developing Integration Projects with Oracle Data Integrator.

Import the following generic SQL KMs into your project for getting started with Microsoft Excel:

• LKM SQL to SQL

• IKM SQL to SQL Append

See Generic SQL for more information about these KMs.

Creating and Reverse-Engineering a Microsoft Excel Model

This section contains the following topics:

• Create a Microsoft Excel Model

• Reverse-engineer a Microsoft Excel Model

Create a Microsoft Excel Model

A Microsoft Excel Model is a set of datastores that correspond to the tables contained in a Microsoft Excel workbook.

Create a Microsoft Excel Model using the standard procedure, as described in Creating a Model of Developing Integration Projects with Oracle Data Integrator

Reverse-engineer a Microsoft Excel Model

Microsoft Excel supports only the Standard reverse-engineering and its capabilities entirely depend on the driver being used.

To perform a Standard Reverse-Engineering on Microsoft Excel use the usual procedure, as described in Reverse-engineering a Model of Developing Integration Projects with Oracle Data Integrator.

Designing a Mapping

You can use a Microsoft Excel file as a source or a target of a mapping, but NOT as the staging area

The KM choice for a mapping or a check determines the abilities and performances of this mapping or check. The recommendations below help in the selection of the KM for different situations concerning a Microsoft Excel server.

Loading Data From and to Microsoft Excel

Microsoft Excel can be used as a source or a target of a mapping. The LKM choice in the Mapping Flow tab to load data between Microsoft Excel and another type of data server is essential for the performance of a mapping.

Loading Data from Microsoft Excel

Oracle Data Integrator does not provide specific knowledge modules for Microsoft Excel. Use the Generic SQL KMs or the KMs specific to the technology used as the staging area. The following table lists some generic SQL KMs that can be used for loading data from Microsoft Excel to any staging area.

Table 10-1 KMs to Load from Microsoft Excel

Target or Staging Area	KM	Notes
Oracle	LKM SQL to Oracle	Loads data from any ISO-92 database to an Oracle target database. Uses statistics.
SQL	LKM SQL to SQL	Loads data from any ISO-92 database to any ISO-92 compliant target database.

Loading Data to Microsoft Excel

Because Microsoft Excel cannot be used as staging area you cannot use a LKM to load data into Microsoft Excel. See Integrating Data in Microsoft Excel for more information on how to integrate data into Microsoft Excel.

Integrating Data in Microsoft Excel

Oracle Data Integrator does not provide specific knowledge modules for Microsoft Excel. Use the Generic SQL KMs or the KMs specific to the technology used as the staging area. For integrating data from a staging area to Microsoft Excel, you can use, for example the IKM SQL to SQL Append.