Exporting data from PowerPivot

Question

I have an enormous PowerPivot table (839,726 rows), and it is simply too big to copy-paste into a regular spread sheet. I have tried copying it and then reading it directly into R using the line data = read.table("clipboard", header = T), but neither of these approaches work. I am wondering if there is some add-on or method I can use to export my PowerPivot table as a CSV or .xlsx? Thanks very much

Answer 1

• Select all the PowerPivot table
• Copy the data
• Past the data in a text file (for example PPtoR.txt)
• Read the text file in R using tab delimiter: read.table("PPtoR.txt", sep="\t"...)

Answer 2

To get a PowerPivot table into Excel:

1. Create a pivot table based on your PowerPivot data.
2. Make sure that the pivot table you created has something in values area, but nothing in filters-, columns- or rows areas.
3. Go to Data > Connections.
4. Select your Data model and click Properties.
5. In Usage tab, OLAP Drill Through set the Maximum number of records to retrieve as high as you need (maximum is 9999999 records).
6. Double-click the measures area in pivot table to drill-through.

Answer 3

another solution is

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

here is an example

https://github.com/djouallah/PowerBI_Desktop_Export_CSV

Answer 4

A pure Excel / VBA solution is below. This is adapted from the code here to use FileSystemObject and write 1k rows at a time to the file. You'll need to add Microsoft ActiveX Data Objects Library and Microsoft Scripting Runtime as references.

Option Explicit

Public FSO As New FileSystemObject

Public Sub ExportToCsv()

    Dim wbTarget As Workbook
    Dim ws As Worksheet
    Dim rs As Object
    Dim sQuery As String

    'Suppress alerts and screen updates
    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
    End With

    'Bind to active workbook
    Set wbTarget = ActiveWorkbook

    Err.Clear

    On Error GoTo ErrHandler

    'Make sure the model is loaded
    wbTarget.Model.Initialize

    'Send query to the model
    sQuery = "EVALUATE <Query>"
    Set rs = CreateObject("ADODB.Recordset")
    rs.Open sQuery, wbTarget.Model.DataModelConnection.ModelConnection.ADOConnection
    Dim CSVData As String
    Call WriteRecordsetToCSV(rs, "<ExportPath>", True)

    rs.Close
    Set rs = Nothing

ExitPoint:
    With Application
        .ScreenUpdating = True
        .DisplayAlerts = True
    End With
    Set rs = Nothing
    Exit Sub

ErrHandler:
    MsgBox "An error occured - " & Err.Description, vbOKOnly
    Resume ExitPoint
End Sub



Public Sub WriteRecordsetToCSV(rsData As ADODB.Recordset, _
        FileName As String, _
        Optional ShowColumnNames As Boolean = True, _
        Optional NULLStr As String = "")
    'Function returns a string to be saved as .CSV file
    'Option: save column titles

    Dim TxtStr As TextStream
    Dim K As Long, CSVData As String

    'Open file
    Set TxtStr = FSO.CreateTextFile(FileName, True, True)

    If ShowColumnNames Then
        For K = 0 To rsData.Fields.Count - 1
            CSVData = CSVData & ",""" & rsData.Fields(K).Name & """"
        Next K

        CSVData = Mid(CSVData, 2) & vbNewLine
        TxtStr.Write CSVData
    End If

    Do While rsData.EOF = False
        CSVData = """" & rsData.GetString(adClipString, 1000, """,""", """" & vbNewLine & """", NULLStr)
        CSVData = Left(CSVData, Len(CSVData) - Iif(rsData.EOF, 3, 2))
        TxtStr.Write CSVData
    Loop

    TxtStr.Close

End Sub

Answer 5

Here is a lovely low-tech way: https://www.sqlbi.com/articles/linkback-tables-in-powerpivot-for-excel-2013/

I think the process is a little different in Excel 2016. If you have Excel 2016, you just go to the data tab, go to Get External Data, and then Existing Connections (and look under Tables).

The other important thing is to click on Unlink (under Table Tools - Design - External Table Data). This unlinks it from the source data, so it really is just an export.

You can copy that data into another workbook should you wish to.

Answer 6

Data in Power Pivot is modeled, using DAX Studio to export data to csv or SQL. after finished, you will see that Each model corresponds to a CSV file or SQL table.