When you use a MS Excel Spreadsheet as a data source in Informatica PowerCenter, there are some really bad restrictions to my mind. One of them is the fact that you must have defined ranges in your spreadsheet, so that PowerCenter can identify them as relational sources. That means if you get frequently new MS Excel files, you have to define the ranges manually every time.
In my last project I made a detour to connect from PowerCenter to MS Excel Spreadsheets to overcome that restriction. The name of the detour is MS Access.
Just create a new MS Access database, name it something like "Import_Excel" and go to the tab "External Data". Chose MS Excel as the datasource, in the following window you can link an Excel table to the Access database. Note that you don't import the Excel data to your Access database, it's a link, so you always get the up-to-date Excel data in your MS Access database.
The good thing is, that you can, but must not use the defined ranges from the Excel spreadsheet. You can also use one of the sheets in your Excel as a table.
Once you have created the link to the MS Excel file, you can read from the created table in your MS Access database like from a usual database table. You could also define a query using SQL to preprocess the data in your MS Access database for Informatica PowerCenter, but that's up to you.
Now you can create a ODBC connection to your MS Access database, that you can use as a data source in Informatica PowerCenter.
Another side effect is, that you need just one single ODBC connection for multiple MS Excel files.
Again let me redirect to another blog with a detailed explanation of how to connect to a ODBC source from Informatica PowerCenter: http://www.clearpeaks.com/blog/etl/ms-excel-spreadsheets-as-a-data-source-in-informatica-powercenter
And don't forget to set the Default buffer block size to a smaller value like "8" instead of "Auto" if you get a "terminated unexpectedly" error.
