Import & Create Microsoft Excel Source Definition

Importing a Microsoft Excel Source Definition

PowerCenter treats a Microsoft Excel source as a relational database, not a flat file. Like relational sources, the Designer uses ODBC to import a Microsoft Excel source. You do not need database permissions to import Microsoft Excel sources.

Complete the following tasks before you import an Excel source definition:

Install the Microsoft Excel ODBC driver on the system.
Create a Microsoft Excel ODBC data source for each source file in  the ODBC Data Source Administrator. For more information, see "Connecting to Databases from Windows" in the Installation and Configuration Guide.
Prepare Microsoft Excel spreadsheets by defining ranges and formatting columns of numeric data.

Defining Ranges

The Designer creates source definitions based on ranges you define in Microsoft Excel. You can define one or more ranges in a Microsoft Excel sheet. If you have multiple sheets, define at least one range for each sheet. When you import sources in the Designer, each range displays as a relational source.

You must define a range in the Designer to import the Excel source.

To define a range:

1. Open the Microsoft Excel file.
2. Highlight the column or group of columns of data to import.
3. Click Insert > Name > Define.            
4. Enter a name for the selected range and click OK.
5. If you have multiple sheets, select each sheet and repeat steps 1 to 4 to define ranges for each set of data.
6. Click File > Save.

Formatting Columns of Numeric Data

In Microsoft Excel, you can assign datatypes to columns of data. The Microsoft Excel datatypes are ODBC datatypes. PowerCenter supports  ODBC datatypes and converts them to transformation datatypes as described in Oracle and Transformation Datatypes. If you do not assign datatypes in Microsoft Excel, the Designer imports each column as VARCHAR. If you want to perform numeric or aggregate calculations in a mapping, be sure to assign numeric datatypes in Microsoft Excel before importing the spreadsheet.

To format columns in Microsoft Excel:

Open the Microsoft Excel file.
Select the columns of data that consist of numeric data.
Click Format > Cells.
In the Number tab, select Number.
Specify the number of decimal places, if necessary.

        

    6. Click OK.
    7. Click File > Save.

Steps to Import a Microsoft Excel Source Definition

After you define ranges and format cells, you can import the ranges in the Designer. Ranges display as source definitions when you import the source.

To import a Microsoft Excel source definition:

• In the Designer, connect to the repository and open the folder for the source definition.
• Open the Source Analyzer and click Sources > Import from Database.

        

• Select Excel Files (Microsoft Excel Driver (*.xls)) for the data source.
• Click the Browse button to open the ODBC Administrator.
• In the User or System DSN tabs, depending on where you created the data source, double-click the Microsoft Excel driver.
• Click Select Workbook and browse for the Microsoft Excel file, which is considered a relational database.

        

• Click OK three times to return to the Import Tables dialog box.
• Click Connect in the Import Tables dialog box.

You do not need to enter a database user name and password. The ranges you defined in the Microsoft Excel file appear as table  names. The database owner is No Owner because you are not required to enter a database user name.

• Select the table you want to import. To select more than one table, hold down the Ctrl or Shift keys to highlight multiple tables.
• Click OK.

The newly created source definition appears in the Source Analyzer.
In the Navigator, the source definition appears in the Sources node, under the database name.

• Click Repository > Save.

Manually Creating a Source Definition

You can manually create a new source definition.

To create a source definition:

In the Source Analyzer, click Sources > Create.
Enter the name for the source, the database name, and database type.
Click Create.
An empty table structure appears in the workspace. (It  may be covered by the dialog box.) The new source table also appears within the Navigator window.
Click Done when you are finished creating source definitions.
Configure the source definition.
Click Repository > Save.

The new source definition is saved to the repository. You can now use the source definition in a mapping. You can also create a source table based on this definition in the source database.