3

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.

Community
  • 1
  • 1
BMN
  • 8,253
  • 14
  • 48
  • 80
  • The Excel 2016 issues with ODBC looked like they had more to do with OSX. If you are on Windows, my guess is there should be no problem. An issue as serious as that on Windows, one would think, would garner some very serious attention – Hambone Aug 12 '16 at 16:05
  • Are you running on OS X or Windows? (Excel 2016 is available for both.) What version of PostgreSQL? These details (and maybe others) are key to helping you... I can say that [my employer](http://www.openlinksw.com/) makes [ODBC drivers for PostgreSQL](http://uda.openlinksw.com/odbc-postgres/), which we have used successfully with Excel 2016 on both OS X and Windows... – TallTed Aug 12 '16 at 17:09
  • 1
    This killed me for a while, so to future readers: 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. More detailed articel that I pulled the ODBC set-up text from: http://www.stoneedge.com/help/mergedProjects/3rdparty/Creating_ODBC_Connections_in_Microsoft_Windows.htm – Albert Rothman Jul 19 '17 at 17:44
  • @AlbertRothman Post that as an answer! Your comment saved me. Nothing else I tried worked. – Logical Fallacy Mar 06 '18 at 02:15

4 Answers4

6

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.

Hambone
  • 15,600
  • 8
  • 46
  • 69
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.

Albert Rothman
  • 998
  • 2
  • 9
  • 27
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;

crazy8
  • 308
  • 3
  • 16
2

1.Excel 2016, x86

2.You need x86 odbc-driver https://www.postgresql.org/ftp/odbc/versions/msi/

I used psqlodbc_13_02_0000-x86.zip

3.Create DataSource

-Control Panel
-Administration
-Open the 32 bit ODBC Administrator
-User DSN
-Add new User DSN (MyPostgreDsn), check connection

4.Connect Excel to Postgre

-Data 
-From other sources 
-OleDb wizard 
-Other/Advanced
-Microsoft OLE DB Provider for ODBC drivers 
-Connection - use connection string - Build 
-Machine Data Source 
-Choose created data source (MyPostgreDsn) 
-Ok 
-Test Connection 
-Ok 
-Choose table…
Billy Bons
  • 91
  • 1
  • 7