Team blog of the Microsoft EMEA support team for Analysis Services
We have been investigating reports that large text files are not completely imported into Powerpivot. And indeed a thorough investigation showed two unexpected things happening.
This applies to the SQL Server 2008 R2 and the SQL Server 2012 Powerpivot add-in for excel in both, 32 and 64 bit version.
The Powerpivot "from text" import icon invokes the Access Connectivity Engine (ACE) text data source driver.
This driver was designed to work with Access databases that have a 2 GB size limit.
Thus it is kind of understandable that not much effort went into extending the size limit for this driver. We could have done a much better job documenting the size limit though.
The second point is clearly a software problem. The text driver should have reported an error for the incomplete text import to Powerpivot.
With the latest Office cumulative update from April 2012 this issue was addressed. With the new ACE text driver version import of text files > 4 GB will fail and you receive error message:
"The query cannot be completed. Either the size of the query result is larger than the maximum size of a database (2 GB), or there is not enough temporary storage space on the disk to store the query result."
The error message references the 2 GB size limit of Access databases and is thus a bit misleading for the Powerpivot text import scenario. Remember that the real size limit is 4 GB.
But this is the best we could get as a quick fix and more importantly it stops the incomplete import of text files.
2598145 Description of the Access Connectivity Engine 2010 hotfix package (Ace-x-none.msp): April 24, 2012
This leaves of course the question "How do I import text files > 4 GB".
As of now the only solutions we have available are:
This is a huge limitation. I've read many postings of people running into this problem. In the real world, it's not uncommon to have 4 GBs of raw data. This shouldn't require importing into SQL prior to analyzing it in Powerpivot.
You Can use Power Query that will not have this limitation. Power Query does the query folding and it'll get access only to couple of thousand rows from entire GB of data. Once you apply all your self service required filters, slicing and dicing; Power Query will get the result set for further analytics. This way , you'll have access to entire data set (which is Self Service) , in the source (source could be Text or anything). Great option to handle the limitation with out of the box functionality
yes. with the availability of power query add-in for Excel 2013 we now have a good workaround. For large text files I'd recommend 64 bit power query. Will update the post asap.