It’s not obvious but following steps can take you through (or alternate method):

Step 1: In Microsoft Excel do the following:

1. Select the required rows to be read into PowerCenter.
2. Select Insert > Name > Define and
3. Enter a name for the range.
4. Click OK.
5. Save the worksheet.

Step 2: Create the ODBC DSN

On the Windows machine (hosting the PowerCenter client and server):

1. Go to Start > Settings > Administrative Tools > Data Sources (ODBC)
2. Click System DSN
3. Click Add…
4. Select Microsoft Excel Driver (*.xls) from the list.
5. Click Finish.
6. Configure the DSN.

Step 3: Create the source definition for the mapping

In PowerCenter Designer do the following:

1. In the Source Analyzer select Sources > Import from Database.
2. Select the ODBC data source name created in Step 3 above.
3. Leave Username, Password and Owner name blank.
4. Click Connect
5. 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:

1. Go to Connections > Relational
2. Click New… >
3. Select ODBC from the list.
4. Click OK.
5. Configure the connection:
   1. * Enter a name for the connection
   2. * Username=pmnulluser
   3. * Password=pmnullpasswd
   4. * Connect string=<ODBC name as defined in step 2>
6. 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:

1. Save the excel sheet as .csv file.
2. Use flat file as source in Informatica and export this file
3. Done!

If you get stuck with something do comment or contact me.