none
Advice needed: The way to solve out of memory problem (or the way to work with big csv files) RRS feed

  • Question

  • Hello:)

    I'm in trouble: I have a big csv file (over 5gb of web-analytics data) and my 64 bit excel (and 6gb ram)
    I cant load file to data model because of it's size. There is an error "out of memory" in power query. 

    This is the first time when I encountered such a problem.

    What options do I have to work with such a file? To increase memory in my computer? Would it solve the problem? How much do I need to work with 6gb csv? 

    Or may be I can upload my data somewhere to azure and work with it there? 

    So the problem - is there any way to deal with big files using power query? Or I need to become a developer and learn sql or other languages? 

    Thanks in advance.

    Max

    Sunday, June 29, 2014 10:37 AM

Answers

All replies

  • Anybody? 
    Tuesday, July 1, 2014 10:56 AM
  • Hi Max,

    Given that you are already using 64-bit Excel and Power Query, your only other option is to increase the RAM. The "how much" question truly depends on what other programs are you running while refreshing your query and how much memory they are consuming. In my personal experience, using a 64-bit environment and 8 GB of RAM, I've been able to load files of up to 10 GB into the Data Model.

    Alternatively, look at ways in which you can filter down the file (maybe removing some columns, or filtering out unnecessary rows) in Power Query, before loading the query result into the Data Model.

    Hope this helps.

    Thanks,
    M.

    Tuesday, July 1, 2014 5:10 PM
  • Hi Miguel!

    Thanks for your answer. 

    I've tried to load this file on virtual pc from azure cloud with this config:

    I have increased memory limit in power query settings:

    And still, the proble is the same:

    What I do wrong? 

    Wednesday, July 2, 2014 12:07 AM
  • It seems like there is no problem with memory. 


    Wednesday, July 2, 2014 12:13 AM
  • And just in case - my MS excel config:

    Wednesday, July 2, 2014 12:14 AM
  • Thanks for the details. Would you be willing to share a copy of the CSV file and the workbook where you repro this issue, so that we can investigate further?

    Monday, July 7, 2014 3:40 PM
  • Can you please send me your email, to provide a link for file for you? 
    Monday, July 21, 2014 8:55 PM
  • Hi Miguel!

    I have the same problem. Is there any news about it?

     And my files are just 150mb in total. But I have a very long algorithm of transformation...


    Sunday, December 21, 2014 5:41 AM
  • Same problem here.

    Super powerful virtual machine but still out of memory. Does not matter if I run my query on my laptop or on the VM. The issue is the same for certain data sources.

    DZ

    Tuesday, March 28, 2017 9:48 AM