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
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.
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
.regfile 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 Sourcecalled
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
defaultfor your view, you can click on
Datasourceto get to the Advanced Options, then
Page 2and 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 (
mzin our example):
Default or Customtab and click
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 Allto 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
Refresh every _ minutesand set the frequency & press
To refresh more frequently than once per minute will require a custom VBA script. Luckily, we’ve written it for you.
Alt-F11to open the VBA editor. On the left side, navigate to the VBAProject for your open spreadsheet. Right click on
Microsoft Excel Objectsand select
Insert -> Module
In the module editor, copy and paste this code into the module. Edit the
Application.OnTimevalues for different refresh rates as required.
💡 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.
' Set the data connection to refresh every 15 seconds
Dim conn As WorkbookConnection
For Each conn In ThisWorkbook.Connections
.BackgroundQuery = True
.RefreshPeriod = 0.25 ' The property takes minutes as input. 0.25 minutes is equivalent to 15 seconds.
' Set the macro to run itself again in 30 seconds
Application.OnTime Now + TimeValue("00:00:15"), "AutoRefresh"
Alt-qto close the VBA editor and return to Excel. From Excel, you can use
Alt-F8to open up the Macro window and run the
AutoRefreshmacro. Excel should now start updating your Materialize data at the refresh rate set in the macro.
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.