none
Error while importing text file to Power Pivot via Table Import Wizard

    Question

  • Hello there,

    I'm a rookie at handling Excel to be honest. I've been trying to import a .txt file to Excel (it contains around 14 million rows of information) but Excel won't let me since it can only handle around 1 million rows per sheet (too troublesome to do such thing 14 times in 14 different sheets). Then I googled that Power Pivot let me handle around 100 million rows in a Pivot.

    I'm trying to import this .txt file to Power Pivot by doing the following:

    Home tab > From Text > File Path (insert .txt location here) > Finish

    After that it shows the Table Import Wizard retrieving the rows from the .txt file. Problem is... at the end of the import (once it hits row 14,000,000 or so) it shows an error. It states the following:

    "Memory error: Allocation failure : Not enough storage is available to process this command. .

    The current operation was cancelled because another operation in the transaction failed."

    Please note that the size of the .txt file is 900 MB.

    Another thing is that, when I select the .txt file from its location at the File Path section, the "preview" field only shows 50 rows, not all of them. Is that okay to happen?

    Is it because the size of the .txt file is too big?

    Please let me know if you need more info in regards to this.

    Thanks in advance.

    Friday, August 15, 2014 8:56 PM

Answers

  • Hi AdiUser,

    Only showing 50 rows for the preview is normal behaviour. Based on what you've described, it sounds like Power Pivot is running out of memory when you are loading this .txt file. Are you running the 32 bit or 64 bit version of Excel. Also, if you are running the 64 bit version how much RAM is available?

    If you're running the 32 bit version then the available RAM for Power Pivot is less than 1 GB even if the system has more than this installed. You can address this by installing the 64 bit version. If you're running the 64 bit version then it may be that you don't have enough RAM to accommodate this amount of data and you may consider upgrading the RAM. Alternatively, you could try and reduce the size of the table in memory by using the preview screen to remove any columns that are not of use. This is especially important if there are any column with a high number of unique values that aren't actually needed in the model. In addition to removing columns altogether, you could try filtering the data so that only a subset of the rows are imported. This can also be done from the preview screen.


    Regards,

    Michael Amadi

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

    Website: http://www.nimblelearn.com
    Blog: http://www.nimblelearn.com/blog
    Twitter: @nimblelearn

    Monday, August 18, 2014 1:37 PM
    Moderator