Connect Excel to PostgreSQL via ODBC

Question

I am trying to connect to a PostgreSQL database table from Excel via the PostgreSQL ODBC 32-bit driver.

In Excel, I go to Data>Get Data> From Other Sources> From ODBC. I navigate to the ODBC data source I set up, enter the credentials, and it clearly connects as the available tables appear. The preview fails and the query fails when I hit "Load" giving the error:

DataSource.Error: ODBC: ERROR [HY000] Error while executing the query
Details:
    DataSourceKind=Odbc
    DataSourcePath=dsn=PostgreSQL
    OdbcErrors=Table

When I test the connection in ODBC admin it is successful. I have tried both the ANSI and Unicode drivers. TIBCO Spotfire connects to the ODBC datasource and pulls the data in just fine.

Any help you can provide would be greatly appreciated.

Do you have any special permissions/restrictions on viewing the specific table from a DBA standpoint? Have you tried using a full admin's credentials? — Joseph, Feb 18 '18 at 03:44
Can you please showing at least the structure of the database and the table name? Especially if there is a underscore `_` in it like `the_name`? — Axel Richter, Feb 18 '18 at 06:52
@JosephSerido Not that I am aware of. This is a hosted, AWS RDS PostgreSQL database. I am using the credentials I set up with the instance. Every other viewing platform like Spotfire or PGadmin, is reading the table(s) with no extra effort. — bneelon, Feb 19 '18 at 12:00
@AxelRichter yes, sorry. It contained a customer name so I didn't want to. Neither the database name nor the table name contains an underscore. The formats are 'ABCquickbase' and 'CompanyTracking', respectively. The structure of 'CompanyTracking' is 131 records by 14 fields. It should be noted that the error occurs for both tables in the database. Thanks. — bneelon, Feb 19 '18 at 12:30

score 2 · Accepted Answer · answered Mar 06 '18 at 02:03

This appears to be a bug with the latest psqlODBC driver, which is psqlodbc_09_06_0500 at the time I'm writing this. I have access to my PostgreSQL server logs. Here's the error message and the offending query:

ERROR:  syntax error at or near "ta" at character 553
STATEMENT:  select ta.attname, ia.attnum, ic.relname, n.nspname, tc.relname from pg_catalog.pg_attribute ta, pg_catalog.pg_attribute ia, pg_catalog.pg_class tc, pg_catalog.pg_index i, pg_catalog.pg_namespace n, pg_catalog.pg_class ic where tc.relname = 'rates' AND n.nspname = 'public' AND tc.oid = i.indrelid AND n.oid = tc.relnamespace AND i.indisprimary = 't' AND ia.attrelid = i.indexrelid AND ta.attrelid = i.indrelid AND ta.attnum = i.indkey[ia.attnum-1] AND (NOT ta.attisdropped) AND (NOT ia.attisdropped) AND ic.oid = i.indexrelid order by ia.attnumselect ta.attname, ia.attnum, ic.relname, n.nspname, NULL from pg_catalog.pg_attribute ta, pg_catalog.pg_attribute ia, pg_catalog.pg_class ic, pg_catalog.pg_index i, pg_catalog.pg_namespace n where ic.relname = 'rates_pkey' AND n.nspname = 'public' ANDic.oid = i.indexrelid AND n.oid = ic.relnamespace AND ia.attrelid = i.indexrelid AND ta.attrelid = i.indrelid AND ta.attnum = i.indkey[ia.attnum-1] AND (NOT ta.attisdropped) AND (NOT ia.attisdropped) order by ia.attnum

Here's the context around character 553: order by ia.attnumselect ta.attname, ia.attnum. Note that it's missing a comma between two field names.

I was able to get it working with psqlodbc_09_06_0200, which is about a year old. Since it sounds like you use 32-bit Office, you can download psqlodbc_09_06_0200-x86.zip from https://www.postgresql.org/ftp/odbc/versions/msi/. (Use x64 if you have 64-bit Office installed.)

You might be able to experiment with driver versions between psqlodbc_09_06_0200-x86.zip and psqlodbc_09_06_0500-x86.zip as the bug was presumably introduced somewhere between those two versions.

I'm running into the same issue but using PostgreSQL Unicode (x64) driver version 10.03 which is a later version than the above driver. Do you know any other way of resolving this issue? — rup, Feb 07 '23 at 13:41

score 2 · Answer 2 · edited Jun 06 '20 at 08:00

You can skip this error by removing the navigate step and querying the information you need throw an SQL statement.

For example if you want to retry some information from LineItemExport the default query is something like this:

let
    Source = Odbc.DataSource("dsn=name", [HierarchicalNavigation=true]),
    quickbase_Database = Source{[Name="quickbase",Kind="Database"]}[Data],
    public_Schema = quickbase_Database{[Name="public",Kind="Schema"]}[Data],
    LineItemExport = public_Schema{[Name="LineItemExport",Kind="View"]}[Data]
in
    LineItemExport

You have to change the source step in the query editor or change the code to something like this:

let
    Source = Odbc.Query("dsn=name", "select * from public.LineItemExport")
in
    Source

This is the way you can came over the error, otherwise you should try to change your drivers version.

Have you found any other solutions to solving this issue, as I feel this is a bit long-winded for some users, especially those who are not used to changing / reading code. Unfortunately I have run into the same issue as OP and your solution works, but it's not ideal for someone who frequently pulls in 5+ data sources a day. Interestingly you can pull this through using the "Legacy Data Connection Wizard" - but this doesn't have the nice search bar UI. — rup, Feb 07 '23 at 13:43

Connect Excel to PostgreSQL via ODBC

2 Answers2