来自Power Pivot的“Rip”数据（“Item.data”） [英] Rip data from Power Pivot ("Item.data")

问题描述

我收到了一个包含两个表的功能手册（一个大约1个行，另外20个行）的工作簿。我想把这个（因为任何事情真的 - 但是让我们说一个CSV），以便我可以在R + PostGreSQL中使用它。

I have received a workbook which contains two tables in power-pivot (one around 1 mill 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.

我不能导出到Excel表中有超过100万行;并且复制粘贴数据仅在我选择大约20万行时才起作用。所以我有点卡住了！我尝试将xlsx转换成zip，并在notepad ++中打开item.data文件，但是它已经被加密了。

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. So I'm a bit stuck! I tried converting the xlsx into a zip and opening the "item.data" file in notepad++, however it was encrypted in something.

我会感谢任何解决方案使用VBA，Python，SQL）

I would appreciate any solution (happy to use VBA, Python, SQL)

编辑：我把一些VBA可以工作，大约0.5 mill行，

I put together some VBA which works OK for around 0.5 mill rows however breaks for the 17 mill row document:

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

推荐答案

DAX Studio 将允许您查询e数据模型在Excel工作簿中并输出到各种格式，包括平面文件。

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

您需要的查询只是：

EVALUATE
<table name>

这篇关于来自Power Pivot的“Rip”数据（“Item.data”）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！