Environment

• Windows 10
• Excel 2016
• PostgreSQL 9.3

Here, I used PostgreSQL database, but substantially it is the same for other databases.

Create Connection from Excel

First, configure ODBC. Configuration procedure is in Windows: Configure ODBC for PostgreSQL.

After ODBC configuration, you can use the database from Excel. (OLE DB is also OK, but here I use ODBC.)

Create connection to the ODBC in Excel.

Launch ODBC configuration wizard from Data ribbon.

Choose ODBC DSN as data source. ODBC stands for Open Database Connection, DSN stands for Data Source Name.

Next, you can see ODBC which you created. Choose one you want to use.

Then you can see table list.

Check off Connect to a specific table.

You can change file name and Friendly Name. The connection configuration you created is saved as a file in the computer.

After that, table selection dialog will appear. You can proceed or cancel and exit. In this article, go cancel the dialog. (If proceed, process is also like below.)

Retrieve the Data

Using the connection, retrieve the data from the database.

First, choose connection, from the button in Data ribbon.

Next, choose the connection and click Open.

You can see the dialog to choose the table. If you want to use one table for retrieving the data, you can choose the table. But now I want to show the data of SQL, choose nothing and click OK.

Last confirmation dialog to import data appears, then click the bottom left button, Properties.

In Definition tab, write SQL which you want to get data with.

The connection configuration is changed then the following warning dialog appears. Click Yes.

Finished. Click OK and wait until data is imported.

The data will be imported as below.

This time, it is simple SQL to get 10 IDs, so it is shown as 1 column data.

Change SQL

If the SQL is wrong, you can modify it. Put the cursor in the table area, click Connection Properties in Data ribbon.

Then the dialog to input SQL will be shown. Change SQL and click OK, then the latest data will be retrieved.

Update the Data

When the database values are updated, put the cursor in the table area and click Refresh All, then the latest data will be shown.

Delete the Connection

To delete the connection, show the connection list on Data ribbon.

Click the left below button, Browse for More ….

The file choosing dialog will be shown. You can see existing connections, then right click and delete or press delete key.