Query Splitgraph with Excel and Power Query via ODBC
You can get DDN data in Excel via Power Query (Get & Transform) using the official PostgreSQL ODBC driver.
Note that in Office 2016 Professional, 2019 Professional and Microsoft 365 Apps for Enterprise, you have access to a direct PostgreSQL import functionality. These instructions use ODBC and are compatible with more Office versions (including Home/Business Standard and 365 Home/Personal/Business).
Note that these instructions are Windows only. Excel 2016/2019 and Excel for Microsoft 365 on Mac don't support creating new Power Query queries.
See the compatibility page for more details.
Start → Programs → Windows Administrative Tools → ODBC Data Sources (64-bit)
Click "Create New Data Source" and select "PostgreSQL Unicode (x64)".
Set up the data source as follows:
- Data Source: ddn (or any preferred name)
- Database: ddn
- Server: data.splitgraph.com
- Port: 5432
- User name: Splitgraph API key
- Password: Splitgraph API secret
- SSL Mode: require
Also make sure to disable "Server side prepare" (Datasource → Page 2) since the DDN currently doesn't support prepared statements.
Start Excel, then, in "Data", select "Get Data" → "From Other Sources" → "From ODBC".
Select the data source name you set up previously. At this stage, you can enter a query that will immediately load into your spreadsheet.
Alternatively, you can omit entering a custom query, which will take you to the Navigator window. This will contain a limited sample of Splitgraph datasets that are featured on the DDN or owned by you.
Once you've selected a dataset, you can use normal Power Query tools to prepare it for import.