Informatica Excel Source
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](https://d1y19n2ra9pfoy.cloudfront.net/dwbi.org/media/yRqM0v91FErCQtcAHAi7caPlpOKpMUQ0QfOGJ3M3.png)
A range should be defined containing the data in the excel workbook like below and then save the workbook.
![Define Excel Range Name](https://d1y19n2ra9pfoy.cloudfront.net/dwbi.org/media/bzvH26NSkNNPgpgLBZpb85dhSlbjlwAWzg03JFDs.png)
This defined name “DEPT” will be treated as relational table by informatica.
- System DSN creation:
- DSN Name: DSN_EXEL_FILE
![Define System DSN](https://d1y19n2ra9pfoy.cloudfront.net/dwbi.org/media/rcT1X2jJPT7s32R7Y3NmwXnS0tJlixP4NPGkPqDS.png)
![Select the Workbook](https://d1y19n2ra9pfoy.cloudfront.net/dwbi.org/media/S82ZoqOf0ZVSsfbGIBwwdjtNd55P8qXBsJ7amSB3.png)
![Excel File](https://d1y19n2ra9pfoy.cloudfront.net/dwbi.org/media/piEqXmWOraVJR21kRilBBMnxcgnYcRPs4rKXk8xW.png)
- 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](https://d1y19n2ra9pfoy.cloudfront.net/dwbi.org/media/PIGUGWXQOIEf8JWddR9BrXHhyylZXG6qOIQQ17OY.png)
System DSN created above should be selected. No username/password is required here.
![Connect DSN](https://d1y19n2ra9pfoy.cloudfront.net/dwbi.org/media/hZvVXjBltwvWeLdQQXURCipcLkW7QB6cmrmZXX2t.png)
![Import Table](https://d1y19n2ra9pfoy.cloudfront.net/dwbi.org/media/YmU2cmAhYYreQQOJyHqXjlnxYApSK3Fj8Z5gPb5D.png)
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](https://d1y19n2ra9pfoy.cloudfront.net/dwbi.org/media/YPFo2uoaMRcwSNEXQ4Q6UXOW0FqHBs4i2HRNh0AW.png)
[N.B.: range name that is defined in excel is the table name]
Mapping needs to be created with the imported source.
![Mapping Designer](https://d1y19n2ra9pfoy.cloudfront.net/dwbi.org/media/6Tbgm5RbMf60YOogL7U2wTG3tELd7PaIJjnicvZ3.png)
- Creation of Connection in Workflow Manager:
A relational ODBC type connection should be created.
![Relational Connection](https://d1y19n2ra9pfoy.cloudfront.net/dwbi.org/media/z6BL99ARSiGEbweKb8GWR2Tz34gxBEUM78ktYt0T.png)
Session level Connection should be created with below details:
- Username: PmNullUser
- Pwd: PmNullPasswd
- Connection String:
![Connection Object Definition](https://d1y19n2ra9pfoy.cloudfront.net/dwbi.org/media/zR5C6HApaUaJtajZvjsMxdp0EWOZdhKa3eTKGvgc.png)
![Session Properties](https://d1y19n2ra9pfoy.cloudfront.net/dwbi.org/media/ZqclEhShsVSqp3EejotgyOQL9uIZVZ8gzo15t10v.png)
And create a workflow to run the mapping.
![Workflow](https://d1y19n2ra9pfoy.cloudfront.net/dwbi.org/media/xlM8dJZhGKRyDHgmLEpLW4prg15DaJo6jeZu7dGW.png)
Limitations:
- 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
![Excel 2007](https://d1y19n2ra9pfoy.cloudfront.net/dwbi.org/media/pFEH6g4ULUJlvR5JgVbDuB3ml9hm7FI0JyKEcH6i.png)
- Select the data set
![Excel 2007 data set](https://d1y19n2ra9pfoy.cloudfront.net/dwbi.org/media/jkHFKbrJh7n47xN4LNWQ619ykm5TYIvxa4mZlmr1.png)
- Go to Insert and then Table tab.
![Excel 2007 Table](https://d1y19n2ra9pfoy.cloudfront.net/dwbi.org/media/06UKVydSP5uc7KX1SO6ZVBm2xpJHA9IyVAyk82XE.png)
- Create the table and then Name the table
![Excel 2007 Table](https://d1y19n2ra9pfoy.cloudfront.net/dwbi.org/media/QHGgv25I9mUWCjvkyUNngpOYlFqFhwoSlg4oDQDN.png)
- Finally Save the sheet in 2007 format.
![Excel 2007 Table as Source](https://d1y19n2ra9pfoy.cloudfront.net/dwbi.org/media/XnJW5QaaIKjHOaN0MgD8mb7vgepfQBVFVxxb2hYz.png)