我有一个巨大的PowerPivot表格(839,726行),它太大了,无法复制粘贴到常规电子表格中。我尝试将其复制并直接使用以下命令将其读入R:data = read.table("clipboard", header = T)
,但这两种方法都不起作用。我想知道是否有一些附加功能或方法可以将我的PowerPivot表格导出为CSV或.xlsx文件?非常感谢。
我有一个巨大的PowerPivot表格(839,726行),它太大了,无法复制粘贴到常规电子表格中。我尝试将其复制并直接使用以下命令将其读入R:data = read.table("clipboard", header = T)
,但这两种方法都不起作用。我想知道是否有一些附加功能或方法可以将我的PowerPivot表格导出为CSV或.xlsx文件?非常感谢。
这里有一个可爱的低技术方法: https://www.sqlbi.com/articles/linkback-tables-in-powerpivot-for-excel-2013/
我认为在Excel 2016中,这个过程有点不同。如果你有Excel 2016,你只需要进入数据选项卡,然后选择获取外部数据,再选择现有连接(并查看表格)。
另一个重要的事情是点击取消链接(在表格工具-设计-外部表格数据下)。这将其与源数据取消链接,因此它确实只是一种导出。
如果您希望,可以将该数据复制到另一个工作簿中。
另一种解决方案是
这里有一个例子
将PowerPivot表格导入Excel:
Option Explicit
Public FSO As New FileSystemObject
Public Sub ExportToCsv()
Dim wbTarget As Workbook
Dim ws As Worksheet
Dim rs As Object
Dim sQuery As String
'Suppress alerts and screen updates
With Application
.ScreenUpdating = False
.DisplayAlerts = False
End With
'Bind to active workbook
Set wbTarget = ActiveWorkbook
Err.Clear
On Error GoTo ErrHandler
'Make sure the model is loaded
wbTarget.Model.Initialize
'Send query to the model
sQuery = "EVALUATE <Query>"
Set rs = CreateObject("ADODB.Recordset")
rs.Open sQuery, wbTarget.Model.DataModelConnection.ModelConnection.ADOConnection
Dim CSVData As String
Call WriteRecordsetToCSV(rs, "<ExportPath>", True)
rs.Close
Set rs = Nothing
ExitPoint:
With Application
.ScreenUpdating = True
.DisplayAlerts = True
End With
Set rs = Nothing
Exit Sub
ErrHandler:
MsgBox "An error occured - " & Err.Description, vbOKOnly
Resume ExitPoint
End Sub
Public Sub WriteRecordsetToCSV(rsData As ADODB.Recordset, _
FileName As String, _
Optional ShowColumnNames As Boolean = True, _
Optional NULLStr As String = "")
'Function returns a string to be saved as .CSV file
'Option: save column titles
Dim TxtStr As TextStream
Dim K As Long, CSVData As String
'Open file
Set TxtStr = FSO.CreateTextFile(FileName, True, True)
If ShowColumnNames Then
For K = 0 To rsData.Fields.Count - 1
CSVData = CSVData & ",""" & rsData.Fields(K).Name & """"
Next K
CSVData = Mid(CSVData, 2) & vbNewLine
TxtStr.Write CSVData
End If
Do While rsData.EOF = False
CSVData = """" & rsData.GetString(adClipString, 1000, """,""", """" & vbNewLine & """", NULLStr)
CSVData = Left(CSVData, Len(CSVData) - Iif(rsData.EOF, 3, 2))
TxtStr.Write CSVData
Loop
TxtStr.Close
End Sub