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.
- The text import is cut off at 4 GB
- Import is shown as successful.
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.
This leaves of course the question "How do I import text files > 4 GB".
As of now the only solutions we have available are:
- import text file into a SQL Server table via "import data task"/SSIS package and subsequently load the SQL Server table into Powerpivot (2 step process)
- Use third party text driver that does not have a 4 GB size limit.