none
Cannot load more than 1,048,576 rows to a worksheet. RRS feed

  • Question

  • Hi, I really like the product. But I can't believe it's not possible to load a CSV file with more than 1,048,576 rows.

    Is there any fix coming for this?

    Thanks!

    Monday, July 8, 2013 10:41 PM

Answers

  • There are two ways to load data when importing. The default is to load data into the sheet, in which case you are bound by the excel limit of around a million rows as you found out.

    However, the query pane that shows up on the side while data is loading lets you disable "load to worksheet". The data will continue to download. Once the download is done, you can click on "Load to Data Model" which will then get the data into the underlying xVelocity/data model.

    Load to Data Model will not show up as an option if you are using Excel 2010. In 2010, you will need to use Power Pivot to pull on the connection created by Power Query to load data into the data model.

    Hope this helps.

    Faisal Mohamood | Program Manager | Microsoft

    Tuesday, July 9, 2013 5:43 PM

All replies

  • There are two ways to load data when importing. The default is to load data into the sheet, in which case you are bound by the excel limit of around a million rows as you found out.

    However, the query pane that shows up on the side while data is loading lets you disable "load to worksheet". The data will continue to download. Once the download is done, you can click on "Load to Data Model" which will then get the data into the underlying xVelocity/data model.

    Load to Data Model will not show up as an option if you are using Excel 2010. In 2010, you will need to use Power Pivot to pull on the connection created by Power Query to load data into the data model.

    Hope this helps.

    Faisal Mohamood | Program Manager | Microsoft

    Tuesday, July 9, 2013 5:43 PM
  • Hi Faisal,

    I have Excel 2010 and am using power pivot to automate a  reports.  Basically I'm trying to get to "Ending Subscriptions Count".  The format of the report  goes as follows: 

    • Beginning  Subscriptions
    • Registrations
    • + New Billing Subscriptions 
    • - Churned Subscriptions 
    • Ending Subscriptions (formula: Beg subs + New Bills - Churned Bills = End Subs) 

    Currently I am exporting all of our customer data from MySQL into excel and then using a bunch of "VLOOKUPS" to clean up the data. Once I have done this I use "COUNTIFS" statements to calculate the numbers above for a specific period of time (monthly).  The problem with this is that my spreadsheet is starting to get too big.  With that being said I turned  to power Pivot, but am having a hard time replicating the format above. Is there way to reference data from power pivot in excel without having to do a pivot table?  In other words can I reference the data without having to import it into the spreadsheet? 

    Thanks in advance for the help!

    Nik 

    Friday, August 16, 2013 5:58 PM
  • Once you bring the data in the Power Pivot model you can use it in Excel  in a pivot or using cube functions

    It seems to me that the format of the report you want to create can be done using cube functions .

    To learn about cube functions you can watch a session I gave back in 2008

    http://www.ssas-info.com/analysis-services-webcasts/64-webcasts/1042-webcast-microsoft-office-excel-and-microsoft-sql-server-analysis-services-an-in-depth-look-at-integration

    Saturday, August 17, 2013 6:29 PM
  • Awesome, thank you for the help!  I have one last question:

    • Is there a way to drag cube functions?  For example if I have a "CUBEMEMEMBER" function in A1 that returns  the month "Jan-12", can I then drag that over to B1, C1, D1, so that it will have the subsequent months ( Feb-12, Mar-12, Apr-12)?  Currently I am having to manually rewrite each function.

    Thanks again for the help!

    Nik 

    Tuesday, August 20, 2013 5:09 PM
  • Hi,

    I tried the suggested Workaround: "In 2010, you will need to use Power Pivot to pull on the connection created by Power Query to load data into the data model."

    But it seems that the Connection only Shows up if the data is loaded to the worksheet. Is there any way around this in Excel 2010?

    Thanks!

    Wednesday, December 11, 2013 12:45 PM
  • Hi.

    Unfortunately, your solution for Excel 2010 is not working for me. I am running Excel 2010 on a Windows7-32bit computer and trying to process a 5Gb file. I created the connection with Power Query (stored as connection). Then I went to the Power Pivot design tab and clicked on "Existing Connections". Basically, when I follow the wizard and I click finish, it starts loading the data into Excel and stops when it hits a limit (be it my RAM or the 1 million lines of Excel). Is there a solution for this so that I can do pivots and such with such a big file and the combination Power Pivot + Power Query?

    Thanks so much

    Friday, June 8, 2018 1:52 PM