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 :).

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

  1. 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)

  2. 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?

  3. 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

    1. 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

  4. 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

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

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

  5. 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

  6. 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.

  7. 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

  8. 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?

  9. 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

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

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

    Any workaround?

    1. you mean as output? you can replace RetStr = RetStr & “,””” & rsData.Fields(K).Name & “”””
      for
      RetStr = RetStr & “.””” & rsData.Fields(K).Name & “”””

  11. 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.

    1. 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.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.