从Power Pivot抓取2000万行("Item.data") [英] Rip 20 million rows from Power Pivot ("Item.data")

问题描述

我收到了一个工作簿，该工作簿包含两个以幂为单位的表(一个约一百万行，另外二十个磨行).我想将其剔除(实际上是任何东西-但可以说是CSV)，以便可以在R + PostGreSQL中使用它.

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.

我无法导出到Excel表，因为行数超过一百万；和复制粘贴数据仅在选择大约200,000行时有效.
我尝试将xlsx转换为zip，然后打开"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.
I tried converting the xlsx into a zip and opening the "item.data" file in notepad++, however it was encrypted.

我整理了一些适用于约0.5轧机行的VBA:

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

推荐答案

DAX Studio 将允许您查询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抓取2000万行("Item.data")的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！