none
Text file partial import using powerpivot when excel imports all lines

    Question

  • Hello

    I have a weird problem with a text file that I can't fully import.

    This is a 37 MB file, tab delimited, with 463,628 rows + headline.

    Procedure:

    * open a new workbook > open powerpivot > import from other sources > TXT > Import (without any filter)
    => 214,101 rows are imported

    * open a new workbook > Excel "Data" tab : Text File > Import to spreadsheet + Add to data model
    => 463,628 rows are imported in both the spreadsheet AND the PP datamodel

    I have not been able to find anything special on rows which are not imported. Column data types are automatic and are similar between both procedures.

    Can anyone help me on such a strange behavior?

    Thanks

    Wednesday, December 18, 2013 10:21 AM

Answers

  • Have you tried to use Power Query to directly load the data into Power Pivot?

    it may also reveal some more information on what might gone wrong with your other imports

    hth,
    gerhard


    - www.pmOne.com -

    • Marked as answer by lourson Monday, January 06, 2014 4:50 PM
    Thursday, January 02, 2014 10:42 AM

All replies

  • Hi lourson,

    The maximum number of row in the PowerPivot data model is 1,999,999,997. For detail information, please refer to the document below:
    http://office.microsoft.com/en-in/excel-help/data-model-specification-and-limits-HA102837464.aspx

    I tried to import 780,000 rows to PP data model from a text file(40.3M) by using Excel 2013 64bit, it was fine. Please see the screenshot below:

    What's the specific version of you SQL Server PowerPivot for Excel add-in? Currently, here is a workaround for your reference:

    1. Import text file record into Excel worksheet.
    2. Import Excel worksheet records into PowerPivot data model.

    Regards,


    Elvis Long
    TechNet Community Support

    Thursday, December 26, 2013 2:50 AM
  • Thanks for your answer Elvis Long. I'm using Excel 2013 with an Office 365 subscription. I never had this problem before and I don't have it on all my files. I tested on another computer and this file is imported correctly. Therefore I would assume there is something wrong with my installation... but I can't know what.

    My file will grow above the Excel limit, so importing it into an XLSX is not a long term solution.

    Monday, December 30, 2013 8:42 AM
  • Thanks for your answer Elvis Long. I'm using Excel 2013 with an Office 365 subscription. I never had this problem before and I don't have it on all my files. I tested on another computer and this file is imported correctly. Therefore I would assume there is something wrong with my installation... but I can't know what.

    My file will grow above the Excel limit, so importing it into an XLSX is not a long term solution.

    Hello,

    Thanks for your response. Please let us know whether you solve this issue by reinstalling Microsoft Excel.

    Regards,


    Elvis Long
    TechNet Community Support

    Thursday, January 02, 2014 2:35 AM
  • Have you tried to use Power Query to directly load the data into Power Pivot?

    it may also reveal some more information on what might gone wrong with your other imports

    hth,
    gerhard


    - www.pmOne.com -

    • Marked as answer by lourson Monday, January 06, 2014 4:50 PM
    Thursday, January 02, 2014 10:42 AM
  • Have you tried to use Power Query to directly load the data into Power Pivot?

    it may also reveal some more information on what might gone wrong with your other imports

    hth,
    gerhard


    - www.pmOne.com -

    Thanks Gerhard for your suggestion. I never used Power Query before so I just gave it a try. And the first thing I must say is: it's so fast! Astonishing to see how fast it is to filter and shape a big text file, using filters on columns, while doing the same thing on the Powerpivot import wizard is painfully slow to the point it's sometime not even usable.

    I have successfully filtered and imported all the rows I needed using Power Query on a new workbook. It seems then that only powerpivot struggles with this file... :(

    Thursday, January 02, 2014 4:50 PM
  • what exactly do you mean by "Powerpivot struggles with this file?"

    - www.pmOne.com -

    Thursday, January 02, 2014 4:58 PM
  • what exactly do you mean by "Powerpivot struggles with this file?"

    - www.pmOne.com -

    I mean that I can't import it. Either I don't have the correct number of rows imported (cf 1st message of this thread) or after having loaded 2.5-3M rows, it stops with an Error telling me the data source might not be available anymore (which it is). I must say that I've just uninstalled my office 365 (using MS Fixit software) and reinstalled it again => still the same bug.

    I just tried to use power query in my original workbook to create a query and import to the existing data model, this is working fine at least. 

    As I don't know Power Query, can someone tell me if I can delete the tab created by powerpivot after I loaded the data in the powerpivot? If I do so, will I still be able to update this data and eventually update the query?

    Thanks

    Friday, January 03, 2014 9:57 AM
  • in Power Query you have the option "Load to Data Model" and "Load to Workbook"

    "Load to Data Model" loads the data directly to your Power Pivot model

    "Load to Workbook" also loads the data to the workbook

    in your case you only need the "Load to Data Model", make sure to deselect "Load to Workbook"


    - www.pmOne.com -

    Friday, January 03, 2014 10:06 AM
  • in Power Query you have the option "Load to Data Model" and "Load to Workbook"

    "Load to Data Model" loads the data directly to your Power Pivot model

    "Load to Workbook" also loads the data to the workbook

    in your case you only need the "Load to Data Model", make sure to deselect "Load to Workbook"


    - www.pmOne.com -

    Thanks a lot. Power Query does the job much better than the default feature in powerpivot to import data.
    Monday, January 06, 2014 4:51 PM