2

Microsoft states that for a 64 bit environment there should be no hard limit on file size. Being as my machine has 32GB of RAM and no other applications running I thought that I might be able to import a 7.7 GB csv into powerpivot. However upon attempting to do so it says that the file size limit is 2GB or I am restricted by memory which is consistent with 32-bit environment which is what I DO NOT have.

Excel 2013

32-bit environment is subject to 2 gigabytes (GB) of virtual address space, shared by Excel, the workbook, and add-ins that run in the same process. A data model’s share of the address space might run up to 500 – 700 megabytes (MB), but could be less if other data models and add-ins are loaded.

64-bit environment imposes no hard limits on file size. Workbook size is limited only by available memory and system resources.

A little looking around online and it would seem that I am not the only person with this issue. Has Microsoft mislead the purchasers of EXCEL 2013 into believing that they would no longer be capped?

Tyler Cowan
  • 820
  • 4
  • 13
  • 35

1 Answers1

0

What is the memory utilization during your import of the CSV file? I'd be surprised to see memory utilization >2x source file size, but it is possible you're exhausting the memory on your laptop.

The compression phase of importing data to Power Pivot or a Tabular model uses more RAM than the source data size.

Without understanding what's happening, it's hard to say if your hardware or Excel is the limiting factor here. The error messages are, unfortunately, identical between 32-bit and 64-bit versions.

greggyb
  • 3,728
  • 1
  • 11
  • 32
  • Well I have monitored this and being as I have 32 GB of RAM and no other applications running this should be trivial... I already have been accessing this data via python. I guess what I am asking has anyone actually opened anything larger than 2GB in powerpivot? – Tyler Cowan Jan 25 '16 at 14:08
  • So, I've just imported a 3.5GB flat file (tab-separated) into Power Pivot with only 8GB of RAM on my machine. I ran into an interesting error when I tried opening a file that was still being written to - an Access driver error. Access is strictly limited to 2GB in a database - if Power Pivot uses Access code for flat files, you may be bumping into those limits. On a side note, my max Excel process memory utilization when reading in the 3.5GB file was only 1.6GB. Can you test importing the CSV into a database first, before importing to Power Pivot? – greggyb Jan 25 '16 at 17:51
  • 1
    I have managed to load in 11 GB of data using Power Query. Not sure why I couldnt load directly from powerpivot but PowerQuery seems to resolve the issue managed to load in over 100 Million rows – Tyler Cowan Jan 25 '16 at 17:59
  • 1
    My guess would be that Power Query doesn't use the same driver for accessing the contents of text files as Power Pivot. Being designed from the ground up as a data import and transformation tool, it isn't surprising to me that Power Query has better facilities for handling various data sources. I'm glad you found a workaround. – greggyb Jan 25 '16 at 18:18