none
Excel 2013 File Size Limits / PowerPivot

    Question

  • Hi All --

    Reading on the official PowerPivot page that Excel 2013 and PowerPivot no longer have hard data limit sizes. I'm seeing that "workbook size is limited only by the availability of disk and memory resources on your computer." via (http://office.microsoft.com/en-us/excel-help/whats-new-in-powerpivot-in-excel-2013-HA102893837.aspx#_Toc335818198)

    Yet, when I try to load a 20 gb tab-separated database in, I get a file limit error:

    "Failed to retrieve data from 1SortedBigDataSentiment#txt. Reason: 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."

    Does this mean that I'd need over 20gb of free RAM and HDD space to open the file? I ask, because the error message would suggest it's a HDD space issue, not a RAM issue. I have over 20gb of free HDD space. I appreciate the feedback.


    Tuesday, February 05, 2013 5:59 PM

Answers

  • I just noticed in Excel Help ... PowerPivot ... Supported Data Sources that PowerPivot uses the ACE 14 OLE DB provider to import .txt .csv and .tab text files. So it appears that text files are subject to the limitations of Access . It's looking like I'm going to have to find another way to get my data into PowerPivot.
    Wednesday, February 20, 2013 12:44 PM

All replies

  • After some further reading, it could be that Excel itself no longer has hard limits, but PowerPivot still may. Can anyone confirm that is the case?
    Tuesday, February 05, 2013 6:30 PM
  • Hi,

    First, make sure that the version of Excel 2013 you used is 64 bit. The maximum file size limitation is gone only applies to Excel 2013 64 bit.

    Second, check to see whether some "Other limits" in the following link is disobedient:

    http://office.microsoft.com/en-gb/excel-help/data-model-specification-and-limits-HA102837464.aspx


    Jaynet Zhang
    TechNet Community Support

    Wednesday, February 06, 2013 6:57 AM
  • Thanks Jaynet!


    However, I am running the 64-bit, and while importing a text file over 2gb, I still get the standard error that says it can only load roughly 1 million rows. This is the same error as 2007 et. al. Can you confirm that you can load a file with more rows? I've tried on several PCs and have not been able to have any success.

    Wednesday, February 06, 2013 3:56 PM
  • I am having the same problem.  Using 64-bit Excel and PowerPivot as part of Microsoft Office Professional Plus 2013.  Machine has 8GB of RAM, trying to import an 11GB text file, and I get the following error:

    An error occurred while loading the file.

    More Details:Failed to retrieve data from bigfile#txt. Reason: 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.

    DG

     
    Wednesday, February 13, 2013 9:55 PM
  • My confusion stems from this info on the Microsoft Office website:

    Support for larger file sizes in 64-bit Excel

    Previous releases limited the size of a workbook to 2 gigabyte (GB) on disk and 4 GB in memory. This limitation ensured that Excel workbooks containing PowerPivot data would always fit under the maximum file upload size set by SharePoint.

    Now that the data model is part of Excel, the maximum file size limitation is gone. If you’re using 64-bit Excel, workbook size is limited only by the availability of disk and memory resources on your computer.

    Limits set by other platforms still apply. SharePoint Server 2013 still has a 2 GB maximum file upload size. Similarly, if you’re sharing a workbook in SharePoint Online or Office Web Apps, you might be directed to open the workbook in Excel if the file is too big to open in a browser. More about file size limits in Data Model specification and limits.


    Wednesday, February 13, 2013 10:14 PM
  • Also, I tried connecting to the file via the ODBC text driver, and got a similar error:

    Failed to retrieve data from bigfile.tab. Reason: ERROR [HY001] [Microsoft][ODBC Text Driver] 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.

    Wednesday, February 13, 2013 10:32 PM
  • I get the same Failed to retrieve data error when I try to import a 125M row, 6 GB text file into 64 bit Excel 2013 with PowerPivot. It fails identically on a laptop with 8 GB memory running Windows 7 Enterprise, another laptop with 4 GB memory running Windows 8 Pro, and a 4 core 32 GB VM running Windows 7 Enterprise. If I reduce the file size to 85M rows (4.215 GB) it still fails to import. If I reduce it again to 80M rows (3.958 GB) it works. The Windows 7 laptop originally had 32 bit Office 2010, then 64 bit Office 2010 and finally 64 bit Office 2013. The Windows 8 laptop and the VM were fresh installations of Windows and Office 2013.
    Thursday, February 14, 2013 3:39 PM
  • So it seems like there is some cap on the size of a text file you can import into PowerPivot?  Any response from MSFT?

    For me, this is a pretty big limitation, to the point where I'm going to have to use another tool (probably SiSense).

    DG

    Thursday, February 14, 2013 5:39 PM
  • Anybody home?  Can someone file a bug?
    Friday, February 15, 2013 9:08 PM
  • Seriously. This a serious bug. Someone needs to step in here. This is why I bought 2013.
    Friday, February 15, 2013 9:49 PM
  • So to re-recap: it appears that PowerPivot for 64-bit Excel 2013 has an undocumented limit of 4GB for importing text files, despite marketing collateral to the contrary.

    It would be great if we could at least get confirmation that this is a known bug.

    DG

    Monday, February 18, 2013 11:05 PM
  • I don't know exactly where the limit is. 64 bit Excel 2010 also failed to import large text files, but in a different way. PowerPivot would go through the motions of importing data for several minutes before halting with a similar error message. I could see that one of the available memory categories displayed by RamMap from Sysinternals went to 0 when the failure occurred (sorry I can't remember which category and I don't have access to 64 bit Excel 2010 to try it again). With 64 bit Excel 2013 the error message comes up immediately without even displaying a preview of the data and column headers.
    Tuesday, February 19, 2013 1:07 PM
  • Right, but it's pretty suggestive that in 64-bit PowerPivot for Excel 2013:

    - a 4.215 GB text file fails to import
    - a 3.958 GB successfully imports

    That indicates to me that there's an undocumented 4GB limit on importing text files into PowerPivot.

    Still hoping for a response from MSFT ... 

    DG 

    Tuesday, February 19, 2013 6:09 PM
  • Right, but it's pretty suggestive that in 64-bit PowerPivot for Excel 2013:

    - a 4.215 GB text file fails to import
    - a 3.958 GB successfully imports

    That indicates to me that there's an undocumented 4GB limit on importing text files into PowerPivot.

    Still hoping for a response from MSFT ... 

    DG 

    That is what I am seeing as well. It appears anything larger than 4GB, regardless of row/column and "Other limits" specified, Excel 2013 running 64 bit will not import the file.

    So, Microsoft, can you confirm that YOU, internally, can indeed load a, say, 20GB text file into Excel 2013? Assuming you follow all the rules specified here: http://office.microsoft.com/en-gb/excel-help/data-model-specification-and-limits-HA102837464.aspx does it work for you? Is there some other pre-requiste that needs to be selected for this to work right? Does the text file need to be in a particular format that isn't listed in the noted link?

    In other words, how the heck do you get this feature to work?

    • Edited by ABCFED Tuesday, February 19, 2013 7:03 PM
    Tuesday, February 19, 2013 6:57 PM
  • I just noticed in Excel Help ... PowerPivot ... Supported Data Sources that PowerPivot uses the ACE 14 OLE DB provider to import .txt .csv and .tab text files. So it appears that text files are subject to the limitations of Access . It's looking like I'm going to have to find another way to get my data into PowerPivot.
    Wednesday, February 20, 2013 12:44 PM