Often users want to import multiple identical Excel files into PowerPivot as one table. Unfortunately PowerPivot does not have native support for this.
The good thing is that there is a nice work around which someone else already blogged about. You can find out how to do it here: PowerPivot from Identical Structure Excel Files
Great! That was exactly the work around I was looking for. But it seems to be limited to approx 1m rows. At least I can’t do more…
Interesting. What I’m wondering is: “what” is evaluating that SQL statement? Is it somehow using a database engine?
Ah, I see, in fact by selecting a file, you’re creating a connection string that invokes Office Access Connectivity Engine (ACE), basically the Access database engine. I guess that the SQL statement is evaluated by that component.