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.
- Manual intervention is required to select the range in Excel.
- Different DSN and Connection should be made for different excel workbook.
- 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.