Informatica

Informatica Excel Source

Saurav Mitra

This article is a guide on how to Unload data from EXCEL file system to target relational database using Informatica.

Excel as a Source

Follow the instructions below on how to extract data from excel file

  • Define Range in Excel:

    File Name: EXCEL_FILE

    Path: PowerCenter8.6.1/server/infa_shared/SrcFiles [File is on ETL Server]

    Define Excel Range

    A range should be defined containing the data in the excel workbook like below and then save the workbook.

    Define Excel Range Name

    This defined name “DEPT” will be treated as relational table by informatica.

  • System DSN creation:

    DSN Name: DSN_EXEL_FILE

    Define System DSN

    Workbook selection in DSN

    Select the Workbook Excel File
  • Importing data definition of Excel in Source Analyzer:

    Since Informatica treats MS Excel file as a database, Import from Database needs to be selected.

    Informatica Source Analyzer

    System DSN created above should be selected. No username/password is required here.

    Connect DSN Import Table

    DEPT needs to be selected and OK should be clicked to import the source definition. After import Data types and Precision can be changed accordingly.

    Table Definition

    [N.B.: range name that is defined in excel is the table name]

    Mapping needs to be created with the imported source.

    Mapping Designer
  • Creation of Connection in Workflow Manager:

    A relational ODBC type connection should be created.

    Relational Connection

    Session level Connection should be created with below details:

    Username: PmNullUser

    Pwd: PmNullPasswd

    Connection String:

    Connection Object Definition Session Properties

    And create a workflow to run the mapping.

    Workflow

Limitations:

  1. Manual intervention is required to select the range in Excel.
  2. Different DSN and Connection should be made for different excel workbook.
  3. Need to have Microsoft Excel Driver under PowerCenter8.6.1/ODBC5.2

NOTE: Excel 2007 Table as Source

Create Table/ Define Name in MS Excel 2007:-

  • Create the data set in MS Excel sheet 2007 Excel 2007
  • Select the data set Excel 2007 data set
  • Go to Insert and then Table tab. Excel 2007 Table
  • Create the table and then Name the table Excel 2007 Table
  • Finally Save the sheet in 2007 format. Excel 2007 Table as Source