Connect Excel to Heroku Postgres Database

0
votes

I've set up a Heroku Postgres database on my app using the Hobby-basic tier, and successfully inserted some rows into my tables, which I can verify through the CLI.

Now I'd like to use Excel to explore the contents of those tables.

From the documentation I can find, it seems I should be able to...

Unfortunately, I have yet to accomplish this successfully.

  • If I enter the server exactly as listed on the credentials page,

    eg. ec2-xx-xxx-xx-xx.xxx.amazon.com

    I get:

    Unable to connect

    We encountered an error while trying to connect.

    Details: "An error happened while reading data from the provider: 'The remote certificate is invalid according to the validation procedure."

  • If instead I annotate that hostname with postgres:// in front, I'm able to reach a dialog to enter my username and password credentials, which I again paste from the Heroku page. But then I get a similar error:

    Details: "An error happened while reading data from the provider: 'No such host is known'"

I've also tried appending :PORT at the end, where PORT stands for the port number provided on the Heroku Postgres Credentials tab, with the same results. And I tried pasting the whole URI from the credentials tab, but Excel complains that the hostname must be at most 128 characters.

I'm sure I must be doing something silly like providing the host name in the wrong format or with the wrong protocol, but I haven't found documentation of the right string to use, and the error messages are not yielding strong leads.

Browsing past Q&A, I found this, which I thought might be applicable if PowerBI and Excel use a similar implementation, but the connection string described in the link does not appear to be accepted by Excel. (It says "The supplied server or database name is invalid.")

Can you recommend ways that I can view my data, or other troubleshooting steps I should try?

1
excelpostgresqlherokupowerquery

1 Answers

0
votes

Aha, I think I've got it now. I was mistakenly on the 32-bit version of Excel, so the instructions at the reddit thread linked above weren't working with the 64-bit driver I had. Fixing my Excel version, they worked like a charm.

So here's what I did:

  • Installed the PostgreSQL ODBC driver from here (psqlodbc_13_00_0000-x64.zip)

  • Installed 64-bit version of Excel

  • Selected Data > Get Data > From Other Sources > From ODBC

  • In the From ODBC Window, selected...

    • Data source name (DSN): (None)

    • Advanced options > Connection string: Driver={PostgreSQL Unicode};Server=ec2-xx-xxx-xx-xx.xxx.amazon.com (Using the host from the Heroku Postgres credentials tab)

  • Clicked OK to reach the credentials dialog

    • User name & Password: from credentials tab

    • Credential connection string properties:

      PORT=HEROKU_PORT;DATABASE=HEROKU_DATABASE;POOLING=True;MINPOOLSIZE=1;MAXPOOLSIZE=20;HOST=HEROKU_HOST;COMPATIBLE=2.2.3.0;USER ID=HEROKU_USER;PASSWORD=HEROKU_PASSWORD;SSLMODE=require

    (Substituting the relevant info from the credentials tab for HEROKU_PORT, HEROKU_DATABASE, HEROKU_HOST, HEROKU_USER, and HEROKU_PASSWORD)

...and I was able to import my data successfully.