Udostępnij za pośrednictwem

4 GB Limit for Powerpivot text file imports via ACE text data source driver

  • Artykuł

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.

  1. The text import is cut off at 4 GB
  2. 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.

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:

  •  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.

Comments

  • Anonymous
    January 29, 2014
    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.
  • Anonymous
    February 27, 2014
    The comment has been removed
  • Anonymous
    March 09, 2014
    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.