The other day we were doing some work with PowerPivot, we needed to union multiple excel files, without manual intervention or an SSIS integration, to create a single data source. After finding the following post; http://www.contextures.com/PowerPivot-Identical-Excel-Files.html, that task became pretty easy.
One really interesting thing that I learned after trying to select excel columns where a period was present, ie. ‘column.name’, is that you need to replace the ‘.’ with a ‘#’ for the query to work. The following statement
Select [Sheet1$].[Column.Name] from test.xlsx.[Sheet1$] would be replaced with Select [Sheet1$].[Column#Name] from test.xlsx.[Sheet1$]