Forum Discussion

DataFanMan's avatar
DataFanMan
Copper Contributor
Jan 10, 2022

Cannot load 11million record text file into Power Pivot

Hi,

 

My platform is Windows 10 Enterprises - Version 1903.

My Cdrive has 26.4 GB free.

I have a Ddrive where the following data resides.

 

I am able to successfully load a 10,389,648 record 4.062 GB text file into Power Pivot.

I'm now attempting to load the same type file, with 10,872,834 records, 4.249 GB.

(The file is comma delimited).

I'm getting the error:

 

"More Details: Failed to retrieve data from "Filename.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."

 

The error message does not appear to give a valid reason since I've been successful in loading the previous file.  I've searched for a solution but cannot find any.

All searches indicate that there are no limitations to the # of records or file size that can be loaded into Power Pivot.

 

Does anyone have any suggestions?

 

Thanks in advance.

 

 

 

excel

3 Replies

    • DataFanMan's avatar
      DataFanMan
      Copper Contributor
      Jan 11, 2022
      Hi Sergei -

      Thanks for responding.
      See below:
      Microsoft® Excel® for Microsoft 365 MSO (Version 2112 Build 16.0.14729.20156) 64-bit

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Jan 11, 2022

        DataFanMan 

        I see, thank you.

         

        To my knowledge size of Power Pivot table for 64 bit is limited only by memory. What else could be

        - you use legacy From Text File connector within Power Pivot to import text file. In background it uses ACE OLEDB connector (Access one) which could have 2GB limit. You may try Power Query (aka structured data source connector) to load file into data model only.

        - Excel file is hosted on SharePoint, it could have its own limit

        - your Excel file is in binary format (xlsb) which also could have limits

         

        Above only my guesses, I had no practical experience with text/csv of such size.

Resources