locked
Exporting directly from PowerPivot to CSV to TXT RRS feed

  • Question

  • Hello all

    We have a PowerPivot table with more than 2.5 million rows and want to export it to csv or txt.
    I´d like to know if there´s a fast and easy way to do it with Excel 2013 64b.
    (Copy and paste in a txt file doesn´t work as Excel Freezes.)

    How can we do it?

    Wednesday, May 24, 2017 2:23 PM

Answers

  • It´s very strange to have platforms that can easily manage millions of rows without the ability to export all the data easily too.

    Actually PowerPivot does not let you manage data. You can't edit or change any data in PowerPivot - you have to have pulled it from some other source, so the answer here is usually to just pull the data from the original source.

    One other option that "may* work is to use DAX Studio http://daxstudio.codeplex.com to run a query against your table (eg EVALUATE 'MyTable' ) and export it directly to a csv. However this mechanism can only handle XMLA result sets up to 2Gb (which probably translates to .csv file of a few hundred Mb in size) so depending on how many columns you have this may or may not work.


    http://darren.gosbell.com - please mark correct answers

    Monday, May 29, 2017 11:41 PM

All replies

  • Hi NicoPer,

    Thanks for your question.

    If copy and paste in a txt file doesn´t work, you may try to export a table from PowerPivot to CSV or TXT using VBA.

    For more detailed information, please refer to below blog:
    Export a table or DAX query from Power Pivot to CSV using VBA

    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com


    Thursday, May 25, 2017 2:05 AM
  • Hi Willson and thanks for your reply.

    I didn´t know that using VBA was possible in Powerpivot.
    I read the reference but still have no idea how to do that. 

    Are there any precise instructions to follow? (I couldn´t find any).

    Or maybe there is an easier way?

    Thursday, May 25, 2017 5:25 PM
  • Hi NicoPer,

    Thanks for your response.

    another solution is
    •import the Powerpivot model to PowerBi desktop
    •export the results from PowerBI desktop using a Powershell script

    Following is an example

    https://github.com/djouallah/PowerBI_Desktop_Export_CSV


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, May 26, 2017 5:44 AM
  • Ugh, I guess I was waiting for an easy Export or Save As button either in PowerPivot or PowerQuery.
    Maybe this is a good idea for the developers to add.

    It´s very strange to have platforms that can easily manage millions of rows without the ability to export all the data easily too.
    Friday, May 26, 2017 11:58 AM
  • It´s very strange to have platforms that can easily manage millions of rows without the ability to export all the data easily too.

    Actually PowerPivot does not let you manage data. You can't edit or change any data in PowerPivot - you have to have pulled it from some other source, so the answer here is usually to just pull the data from the original source.

    One other option that "may* work is to use DAX Studio http://daxstudio.codeplex.com to run a query against your table (eg EVALUATE 'MyTable' ) and export it directly to a csv. However this mechanism can only handle XMLA result sets up to 2Gb (which probably translates to .csv file of a few hundred Mb in size) so depending on how many columns you have this may or may not work.


    http://darren.gosbell.com - please mark correct answers

    Monday, May 29, 2017 11:41 PM
  • Hello, 

    I just wanted to add to Darren's comment. So, I have had quite a few projects where I had to squeeze the data from the PP and DAX Studio was and still remains best tool for this kind of a job. 

    You will need to use Dynamic Management Views (DMVs) as a query to pull the data out. For example, the query below will get you all the tables and its fields. 

    SELECT [TABLE], [OBJECT]
    FROM $System.DISCOVER_CALC_DEPENDENCY
    WHERE OBJECT_TYPE = 'HIERARCHY' AND  [OBJECT] <> '__XL_RowNumber' 
    ORDER BY [TABLE] ASC

    Thanks, Nick - 


    Wednesday, May 31, 2017 3:56 AM
  • Hi Darren

    Is the 2GB limit imposed by DAX Studio or Excel?

    If it is Excel then would upgrading to 64 bit fix the issue?

    Thanks,
    Simon

    Monday, June 26, 2017 11:52 AM