Informatica Excel Source

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]
  

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

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

• System DSN creation:
  
  - DSN Name: DSN_EXEL_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.

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

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.

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

Mapping needs to be created with the imported source.

• Creation of Connection in Workflow Manager:

A relational ODBC type connection should be created.

Session level Connection should be created with below details:

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

And create a workflow to run the mapping.

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

• Select the data set

• Go to Insert and then Table tab.

• Create the table and then Name the table

• Finally Save the sheet in 2007 format.