Export a table or DAX query from Power Pivot to CSV using VBA

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?

  • Eva

    Is that any help for exporting very large Power Pivot tables to csv(>1M rows), if I deploye the Power Pivot to SSAS Tabular?

  • Imke

    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

    • Dušan Roknić

      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

  • Mike

    Can this be done in excel 2010?

  • Antoine Chamba

    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

    • Erin Moriarty

      Did you ever find a solution to this?

      • AC

        Yes, I rename my table “test” everytime. But in the end it doesn’t support very large volumes (2G +), so i’m still stuck.

  • Jon

    Does this extract measures too?

  • AOM

    hi Kasper, I get an Automation Error on the line “rs.Open sQuery wbTarget….” Any suggestions. Cheers, Dan

    • Erin Moriarty

      Did you ever find a solution to this????

      • Chris

        I removed the and if the table had a white space in it, inclosed it with ‘ ‘ instead. e.g.
        sQuery = “EVALUATE ‘Yahoo Data'”

  • Julian Alberto Castiblanco Pal

    Great post!!!! thank you for share it

  • Anthony Newell

    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

  • Chris

    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.

  • Lana B

    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

  • Lana B

    Brilliant, thanks you Kasper!!!!

  • Harry

    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?

  • Dylan Morgan

    Which ADO library do you mean?

  • John Young

    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

  • Pingback: #Excel Super Links #89 – shared by David Hager | Excel For You()

  • Dan

    I’m using Excel 2010. This code fails on the line…

    rs.Open sQuery, wbTarget.Model.DataModelConnection.ModelConnection.ADOConnection

    Any workaround?