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.
Install psqlODBC
Install the PostgreSQL ODBC driver from https://www.postgresql.org/ftp/odbc/versions/msi/. Use the latest
available version for your architecture (psqlodbc_13_00_0000-x64.zip at the time of writing).
Configure the DDN data source in ODBC
Start → Programs → Windows Administrative Tools → ODBC Data Sources (64-bit)
or run %windir%\\system32\\odbcad32.exe.
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.
Import data in Excel
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.
Use Power Query to browse the DDN and select a dataset
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.
