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

Creating PostgreSQL ODBC data source for Excel and Power Query

Set up the data source as follows:

Creating PostgreSQL ODBC data source for Excel and Power Query

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

Disable "server side prepare" in PostgreSQL ODBC

Import data in Excel

Start Excel, then, in "Data", select "Get Data" "From Other Sources" "From ODBC".

Setting up ODBC data source in Excel

Select the data source name you set up previously. At this stage, you can enter a query that will immediately load into your spreadsheet.

Select DDN ODBC data source in Excel and enter a query

Query results loaded into Excel

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.

Datasets from Splitgraph DDN explored in Power Query Navigator

Once you've selected a dataset, you can use normal Power Query tools to prepare it for import.

Datasets from Splitgraph DDN loaded in Power Query for transforms