Microsoft Excel - deltadna documentation

This type of connection is no longer actively supported and we cannot guarantee that newer versions of Excel will work correctly with Direct Access.

There are a few different ways of connecting to your data with Excel.

You can simply add a connection to your data and drop it on a worksheet.
This will try and download ALL your data to the worksheet by default, so you MUST modify the default query before adding it to the worksheet.
You can add script to your worksheet to manage the connection and run queries.
This can create more interesting custom reports, but is a bit more effort to set-up.
You can create a Microsoft Excel ODBC Query File (.dqy) and add it to Excel

We will just cover a simple connection here.

Create a New workbook in Excel the Select Data >> Connections option from the menu strip. This will open the Workbook connections panel so we can add a connection to our data.

Click the Add button and a panel showing Existing Connections will open. We are going to create a new connection, so press the Browse for More button at the bottom of the panel.

A file dialogue box will open prompting you to open an existing data source file, as we don’t have one yet we should press the New Source button.

Select the ODBC DSN data source type and press Next

Then select the Data Source that you created earlier in the PostgreSQL ODBC Connection Guide then press Next.

The Datatabase and Table panel should open and display 4 tables that you have access to. There is an events table and a user table for each of your environments. Choose the events_live table and press the Finish button.

At this point we could close this panel and add the connection and its default SQL query to our worksheet, but this would be bad!

The default Excel SQL query will attempt to retrieve all data from your table, this will naturally fail if you have a large data set. You need to change the query that Excel is using in order to LIMIT the number of rows and columns to something a bit more sensible.

Select your connection and press the Properties button so you can modify the default query.

Select the Definition tab and take a look at the default query, it will cause the Excel to try and retrieve all rows and columns.

Change it to something a bit more sensible either by putting a LIMIT on the end of your query

SELECT * FROM "demo-co.my-demo-game"."schema1"."events_live" LIMIT 100

or design your query so it can only return a finite amount of data.

SELECT count(distinct(userID) )
  FROM "demo-co.my-demo-game"."schema1"."events_live"
  WHERE eventDate >= CURRENT_DATE -7
GROUP BY eventDate
ORDER BY eventDate;

SELECT count(distinct(userID) )

FROM "demo-co.my-demo-game"."schema1"."events_live"

WHERE eventDate >= CURRENT_DATE -7

GROUP BY eventDate

ORDER BY eventDate;

When you’ve got your query is in better shape press the OK button , then say Yes on the next dialogue panel.

We are finally ready to add the data to our Worksheet. Select cell A1 then Press the Data >> Existing Connections button. Choose your connection and press Open.

You will be given a chance to confirm the cell you want to put your data in, or change the cell or type of import. For now we’ll just add the data as a table to cell A1.

Press the OK button and after a few seconds your data should appear in Excel. You can use the Refresh All button to refresh your data.