Logo DWBI.org Login / Sign Up
Sign Up
Have Login?
Login
New Account?
Recovery
Go to Login
Informatica

Informatica Excel Source

Updated on Sep 30, 2020

This article is a guide on how to Unload data from EXCEL workbook 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
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
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
Define System DSN
Select the Workbook
Select the Workbook
Excel File
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
Informatica Source Analyzer

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

Connect DSN
Connect DSN
Import Table
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
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
Mapping Designer
  • Creation of Connection in Workflow Manager:

A relational ODBC type connection should be created.

Relational Connection
Relational Connection

Session level Connection should be created with below details:

- Username: PmNullUser
- Pwd: PmNullPasswd
- Connection String:

Connection Object Definition
Connection Object Definition
Session Properties
Session Properties

And create a workflow to run the mapping.

Workflow
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
Excel 2007
  • Select the data set
Excel 2007 data set
Excel 2007 data set
  • Go to Insert and then Table tab.
Excel 2007 Table
Excel 2007 Table
  • Create the table and then Name the table
Excel 2007 Table
Excel 2007 Table
  • Finally Save the sheet in 2007 format.
Excel 2007 Table as Source
Excel 2007 Table as Source