Had an interesting question today. Someone did some great transformations combining multiple files into a single table and adding some calculations using Power Query and Power Pivot. Now he wanted to load that data into CloudML to do some machine learning on top of this data. Currently cloudML takes csv files so the question was how can I get data from the Power Pivot model into a CSV even when there are more than 1.000.000 rows.
It turned out to be pretty easy by combining two other blog posts: one of my blogs on how to access the data model combined with this blog I found on how to write to CSV from vba.
Merging these two together gives us a new macro that we can run.
Please note the following:
- Change the query used below, you either need to do “EVALUATE <TABLENAME>” or write your own query
- Make sure you have the file that I am writing to below
- Add a reference to the ADO library in your VBA code.
All of this gives the following Macro :
Option Explicit
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 <YOURPRODUCT>"
Set rs = CreateObject("ADODB.Recordset")
rs.Open sQuery, wbTarget.Model.DataModelConnection.ModelConnection.ADOConnection
Dim CSVData As String
CSVData = RecordsetToCSV(rs, True)
'Write to file
Open "C:tempMyFileName.csv" For Binary Access Write As #1
Put #1, , CSVData
Close #1
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 Function RecordsetToCSV(rsData As ADODB.Recordset, _
Optional ShowColumnNames As Boolean = True, _
Optional NULLStr As String = "") As String
'Function returns a string to be saved as .CSV file
'Option: save column titles
Dim K As Long, RetStr As String
If ShowColumnNames Then
For K = 0 To rsData.Fields.Count - 1
RetStr = RetStr & ",""" & rsData.Fields(K).Name & """"
Next K
RetStr = Mid(RetStr, 2) & vbNewLine
End If
RetStr = RetStr & """" & rsData.GetString(adClipString, -1, """,""", """" & vbNewLine & """", NULLStr)
RetStr = Left(RetStr, Len(RetStr) - 3)
RecordsetToCSV = RetStr
End Function
Happy exporting :).
Thanks Kasper – this is really very useful.
But for very large Power Pivot tables this method is not ideal. Running in batches is an option that won’t hang Excel. But would you know of any more efficient programmatic options for exporting very large Power Pivot tables to csv? (>1 million records)
Hi Kasper,
Is there a way to export the DAX query result from tabular cube into SQL?
Hi Kasper,
Nice article, thanks.
Actually I am triyng to run your code, but it failed.
Error is “An error occurred – Automation error”.
Code string that failed is: “rs.Open sQuery, wbTarget.Model.DataModelConnection.ModelConnection.ADOConnection”.
Could you help me?
Is that any help for exporting very large Power Pivot tables to csv(>1M rows), if I deploye the Power Pivot to SSAS Tabular?
Thanks Kasper, this is really helpful and works fine – as long as you keep these 2 things under control:
1) Your data shouldn’t contain any “. Replace them by ‘ if necessary
2) If you export your data on a regular basis, make sure you empty the file before you refill it. Otherwise the old lines at the end will remain, once your new file is shorter than the one before.
Any idea on how to fill the column titles with the text from the original columns only (at the moment the filenames will be part of every column title as a prefix: filename[original column name] ?
Thanks again and keep on posting usefull stuff like this!
Imke
This code doesn’t work for tables bigger than 64.000 rows.
sQuery = “EVALUATE
What you have to do is to read chunks smaller than 64.000 rows.
There has to be some column by which you can filter your table in 64.000 rows chunks.
In example below I am slicing my table by weeks.
For i = 2012 To 2017
For j = 1 To 53
sQuery = “EVALUATE FILTER(‘MyTable’, ‘MyTable'[year] = “”” & i & “””” & _
” && weeknum(‘MyTable'[date]) = ” & j & “)”
Set rs = CreateObject(“ADODB.Recordset”)
rs.Open sQuery,
wbActive.Model.DataModelConnection.ModelConnection.ADOConnection
subWriteThisRecordset rs, j, i ‘procedure that writes this recordsets to where they belonge.
Next j
Next i
Can this be done in excel 2010?
Hi, thanks for this post, it looks very promising.
Unfortunately, I’m getting a “Method ‘Open’ of object ‘_Recordset’ failed” on line :
rs.Open sQuery, wbTarget.Model.DataModelConnection.ModelConnection.ADOConnection
Could that come from the query declaration, or is it another problem ?
Thks,
A
Did you ever find a solution to this?
Yes, I rename my table “test” everytime. But in the end it doesn’t support very large volumes (2G +), so i’m still stuck.
Does this extract measures too?
hi Kasper, I get an Automation Error on the line “rs.Open sQuery wbTarget….” Any suggestions. Cheers, Dan
Did you ever find a solution to this????
I removed the and if the table had a white space in it, inclosed it with ‘ ‘ instead. e.g.
sQuery = “EVALUATE ‘Yahoo Data'”
Great post!!!! thank you for share it
This is a brilliant bit of code that I’ve been using extensively
I’n now trying getting the contents of a data table to an access table
Is there a way this can be done by adapting this code
I’m thinking along the lines of copying the record set contents to a new record set then exporting to this to access table
Of course, I could just create CSV files and import them into Access but trying to remove this step
Can anyone tell me the largest data set they have been able to run this on?
The code works fine for me with my smaller tables, but the one with 2.5 million rows produces the error message seen in the attached image. What I was really wanting to do, was to run this on a table with around 6 million rows (though I’m not sure if that would even write to csv). BTW, running the code included via the in-article hyperlink tells me that my table with 2.5 million rows, has a memory size of 4449 kb.
I solved this. Posting the answer in case it can help others.
It was about adding reference to Microsoft ActiveX Data Objects 2.8 Library in the tools=>references. And remember not to be in break mode otherwise you can’t access references.
Kasper, THANKS a lot for this life-saver!!!
============================================================
Hi Kasper, and hello all !
I am trying to use this, desperately need it.
But the compiler throws an error relating to user-defined type in public function arguments.
I think it is about “adobe.recordset”. I have no experience with this [yet].
Can someone help?
Thank you in advance!
Lana
Brilliant, thanks you Kasper!!!!
Hi Everyone,
Where are you pasting this vba code into?? Just a regural excel workbook, or does it go directly into the power query via the advanced editor view?
Which ADO library do you mean?
Where can I find other good examples of Power pivot programming in vba?
I found the object library, but it is a it hard for me to decipher, I really need to see examples
Also anyone know how to use MDX on power pivot models to extract contents of cube views and hierarchies to recordsets
I’m using Excel 2010. This code fails on the line…
rs.Open sQuery, wbTarget.Model.DataModelConnection.ModelConnection.ADOConnection
Any workaround?
In VBA editor, tools–>References and check Microsoft ActiveXData Objects 2.8 Library
This works perfectly. Thank you for sharing. You saved a lot of extra work
are there anyway to replace comma?
you mean as output? you can replace RetStr = RetStr & “,””” & rsData.Fields(K).Name & “”””
for
RetStr = RetStr & “.””” & rsData.Fields(K).Name & “”””
Do i need to use EVALUATE with the TableName or Can i use other SELECT, TRANSFORM etc? Right now i am trying to use TRANSFORM and SELECT to pivot the Recordset data, but it is shooting up an error.
P.S: I am able to select the Table using EVALUATE TableName.
you need to use EVALUATE for every query to be returned. You can use other functions to filter and transform data but all have to happen with DAX queries.