来自Power Pivot的“Rip”数据（“Item.data”）

本文介绍了来自Power Pivot的“Rip”数据（“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>