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 :).
30 Replies to “Export a table or DAX query from Power Pivot to CSV using VBA”
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)
Is there a way to export the DAX query result from tabular cube into SQL?
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!
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”)
subWriteThisRecordset rs, j, i ‘procedure that writes this recordsets to where they belonge.
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 ?
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!
Brilliant, thanks you Kasper!!!!
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
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 & “”””
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.