How to pull data from Materialize into Excel

important

As a database with an engine that inverts the traditional query model and handles most computation on write, Materialize provides users with some mind-melting new capabilities. But we want to put these capabilities in the hands of anyone who knows SQL, so our goal is to make the rest of Materialize as familiar, predictable, well-integrated as possible. That’s the motivation behind this Excel integration, and it’s also the motivation behind our Postgres wire-compatibility that makes the integration so easy.

About the Materialize Excel Integration

Materialize to Excel

It is possible to insert a table of continually updated results from Materialize in Excel, allowing you to build spreadsheets using that data. The only requirement is that the machine have the psqlODBC driver installed, which is distributed as part of Postgres. So far, this has only been tested on Windows machines.

Steps

Install the latest version of the Postgres ODBC driver onto your Windows machine from here.
From the Windows control panel, find the Set up ODBC data sources (64-bit) option (assuming you are using 64-bit version of Excel):

If you are deploying to a bunch of machines and don’t want to use the GUI to create the ODBC settings, you could instead create a .reg file to deploy the registry settings. Here is an Excel .reg file example.
If you have successfully installed pgODBC, you should see an option in Create A New Data Source called PostgreSQL Unicode(x64):
Setup configuration will use values from the Materialize console under Connect -> External Tools, where Password is your App Password
If you are using a cluster besides default for your view, you can click on Datasource to get to the Advanced Options, then Page 2 and add set cluster = <clustername>; in the Connect Settings box.
Now open Excel, and go to the Data toolbar. Click on Get Data -> From Other Sources -> From ODBC
Select the ODBC connection we created earlier (mz in our example):
Select the Default or Custom tab and click Connect:
Use the navigator to drill down into the database and schema that your view is in, and select the view you want to pull into Excel:
At this point, you should see the data imported into your Excel spreadsheet. You can manually click on Data -> Refresh All to refresh the data. If you want it to automatically refresh on a cadence, continue below.
To refresh as frequently as once per minute, you can simply set the Query to refresh. Go to Data -> Queries & Connections -> (rt click on Query) -> Properties
Check Refresh every _ minutes and set the frequency & press ok.
To refresh more frequently than once per minute will require a custom VBA script. Luckily, we’ve written it for you.
Type Alt-F11 to open the VBA editor. On the left side, navigate to the VBAProject for your open spreadsheet. Right click on Microsoft Excel Objects and select Insert -> Module
In the module editor, copy and paste this code into the module. Edit the RefreshPeriod and Application.OnTime values for different refresh rates as required.

note

💡 As long as you have indexed the view, Materialize will not be adversely impacted by quite frequent refresh times - it’s just a cache read. The limiting factor here will be Excel, which will throw an error if it doesn’t finish a refresh before the next one is launched. Depending on your machine, this might be anywhere from 5 to 15 seconds.

Sub AutoRefresh()
' Set the data connection to refresh every 15 seconds
Dim conn As WorkbookConnection
For Each conn In ThisWorkbook.Connections
    With conn.OLEDBConnection
        .BackgroundQuery = True
        .RefreshPeriod = 0.25   ' The property takes minutes as input. 0.25 minutes is equivalent to 15 seconds.
        .Refresh
    End With
Next conn

' Set the macro to run itself again in 30 seconds
Application.OnTime Now + TimeValue("00:00:15"), "AutoRefresh"
End Sub

Type Alt-q to close the VBA editor and return to Excel. From Excel, you can use Alt-F8 to open up the Macro window and run the AutoRefresh macro. Excel should now start updating your Materialize data at the refresh rate set in the macro.

Conclusion

Whether you just need the latest data quickly, or you choose to auto-refresh your spreadsheet continually, this integration works as a way to quickly pull data from Materialize into Excel. Once in Excel, you’re free to use it as input to formulas, pivot tables, etc…

If you’d like to see how Materialize works for your use case, you can get immediate access to our platform with a free 14-day trial here, or you can get in touch with our field engineering team to get a demo and talk through your use case here.