sql - 从 Power Pivot ("Item.data"提取 2000 万行)

Question

我收到了一个工作簿，其中包含两个 power-pivot 表(一个大约一百万行，另外一个 20 行)。我想把它撕掉(就像任何东西一样 - 但让我们说一个 CSV)，以便我可以在 R + PostGreSQL 中使用它。
我无法导出到 Excel 表格，因为有超过 100 万行；只有当我选择大约 200,000 行时，复制粘贴数据才有效。
我尝试将 xlsx 转换为 zip 并在记事本++ 中打开“item.data”文件，但它已被加密。
我整理了一些适用于大约 0.5 行轧机的 VBA:

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 1

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

您需要的查询只是:

EVALUATE
<table name>