It’s not obvious but following steps can take you through (or alternate method):
Step 1: In Microsoft Excel do the following:
- Select the required rows to be read into PowerCenter.
- Select Insert > Name > Define and
- Enter a name for the range.
- Click OK.
- Save the worksheet.
Step 2: Create the ODBC DSN
On the Windows machine (hosting the PowerCenter client and server):
- Go to Start > Settings > Administrative Tools > Data Sources (ODBC)
- Click System DSN
- Click Add…
- Select Microsoft Excel Driver (*.xls) from the list.
- Click Finish.
- Configure the DSN.
Step 3: Create the source definition for the mapping
In PowerCenter Designer do the following:
- In the Source Analyzer select Sources > Import from Database.
- Select the ODBC data source name created in Step 3 above.
- Leave Username, Password and Owner name blank.
- Click Connect
- Expand the worksheet name and select the range created in Step 1 above.
Step 4: Create the relational connection for the session
In PowerCenter Workflow Manager do the following:
- Go to Connections > Relational
- Click New… >
- Select ODBC from the list.
- Click OK.
- Configure the connection:
- * Enter a name for the connection
- * Username=pmnulluser
- * Password=pmnullpasswd
- * Connect string=<ODBC name as defined in step 2>
- Use this connection for the source in the session.
Alternative shortcut to import Excel 2007 file as my source file in informatica?
Yes, there is a shortcut to everything. Follow the steps below:
- Save the excel sheet as .csv file.
- Use flat file as source in Informatica and export this file
If you get stuck with something do comment or contact me.