我收到了一个工作簿,该工作簿包含两个以幂为单位的表(一个约一百万行,另外二十个磨行).我想将其剔除(实际上是任何东西-但可以说是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")
发布评论