Rip 20 million rows from Power Pivot (“Item.data”)

I received a workbook which contains two tables in power-pivot (one around one million rows, another 20 mill rows). I would like to rip this out (as anything really – but let’s say a CSV) so that I can use it in R + PostGreSQL.

I can’t export to an Excel table as there are more than 1 million rows; and copy-pasting the data only works when I select around 200,000 rows.
I tried converting the xlsx into a zip and opening the “item.data” file in notepad++, however it was encrypted.

I put together some VBA which works for around 0.5 mill rows:

Public Sub CreatePowerPivotDmvInventory()
    Dim conn As ADODB.Connection
    Dim sheet As Excel.Worksheet
    Dim wbTarget As Workbook
    On Error GoTo FailureOutput
     
    Set wbTarget = ActiveWorkbook
    wbTarget.Model.Initialize
    
    Set conn = wbTarget.Model.DataModelConnection.ModelConnection.ADOConnection

    ' Call function by passing the DMV name
    ' E.g. Partners
    WriteDmvContent "Partners", conn
     
    MsgBox "Finished"
    Exit Sub
     
FailureOutput:
    MsgBox Err.Description
End Sub
 
Private Sub WriteDmvContent(ByVal dmvName As String, ByRef conn As ADODB.Connection)
    Dim rs As ADODB.Recordset
    Dim mdx As String
    Dim i As Integer
 
    mdx = "EVALUATE " & dmvName
     
    Set rs = New ADODB.Recordset
    rs.ActiveConnection = conn
    rs.Open mdx, conn, adOpenForwardOnly, adLockOptimistic
     
    ' Setup CSV file (improve this code)
    Dim myFile As String
    myFile = "H:output_table_" & dmvName & ".csv"
    Open myFile For Output As #1
    
    ' Output column names
    For i = 0 To rs.Fields.count - 1
        If i = rs.Fields.count - 1 Then
            Write #1, rs.Fields(i).Name
        Else
            Write #1, rs.Fields(i).Name,
        End If
    Next i

    ' Output of the query results
    Do Until rs.EOF
        For i = 0 To rs.Fields.count - 1
            If i = rs.Fields.count - 1 Then
                Write #1, rs.Fields(i)
            Else
                Write #1, rs.Fields(i),
            End If
        Next i
        rs.MoveNext
    Loop
    Close #1
    rs.Close
    Set rs = Nothing
    
    Exit Sub
 
FailureOutput:
    MsgBox Err.Description
End Sub

Answer

DAX Studio will allow you to query the data model in an Excel workbook and output to various formats, including flat files.

The query you’ll need is just:

EVALUATE
<table name>

Advertisement

Answer