Connect to a PostgreSQL database through ODBC on Excel 2016

Question

I'm having trouble using ODBC to connect from Excel 2016 to a PostgreSQL database.

I tried to follow this answer, without success, and this solution from MS, without any better results...

Has anyone ever succeeded at this? I saw that Excel 2016 had some troubles with ODBC drivers, but that was like a year ago, so I was wondering if things may be better now?

Thank you for your help.

Solution 1

My guess is this is a 32/64 bit thing. It's the single biggest issue we've had with setting people up on PostgreSQL within Excel/Access.

Bottom line: if your Excel instance is 32-bit (which is often the case), then you need to use the 32-bit ODBC driver. The latest Pg ODBC driver has both bundled in the same assembly, so the trick is to go into 32-bit ODBC and set up the driver that way.

From there, it should all be straight-forward:

• Data Tab
• From Other Data Sources
• Microsoft Query
• Select the Data source you just set up

And so on.

Solution 2

II am working on a Windows 10, 64-bit version, but turns out my Office package is 32-bit. Once I manually installed the 32-bit postgresql driver here: https://www.postgresql.org/ftp/odbc/versions/msi/, i was then able to correctly enter the Driver along the lines of these guidelines: https://www.connectionstrings.com/postgresql/

Final connection string looked like: Driver={PostgreSQL ANSI};Server=name.text.ap-southeast-2.rds.amazonaws.com;Port=5432;Database=myDBname;

Solution 3

I had this as a comment before, but another commentor suggested I make this an answer: on excel 2016 for windows, don't waste your time with Data>Get Data >From Database. Instead use Data>Get Data >From Other Sources> From ODBC. To set up the ODBC: Click on the Start Menu. Select Control Panel. Select Administrative Tools and double click the Data Sources (ODBC) icon. Click on the System DSN tab. Here is a more detailed article that I pulled the ODBC set-up text from. Besides being much easier to set up, "From ODBC" comes with Office Business, or Office Home, while "From Database" only comes with Office Pro.