从Power Pivot抓取2000万行("Item.data")

本文介绍了从Power Pivot抓取2000万行("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>