6

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

Picture of Error Message

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.

Improve this question
4
  • 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? Commented Feb 18, 2018 at 3: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? Commented Feb 18, 2018 at 6: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. Commented Feb 19, 2018 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. Commented Feb 19, 2018 at 12:30

2 Answers 2

Reset to default
4

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

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?
2

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.

Improve this answer

3 Comments

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 Could you please elaborate on what you mean by "Legace Data Connection Wizzard"? Or maybe you found other solution? The one offered here does not allow for query folding so not workable for me unfortunately. Thanks
@Confounded this is best method to connect to a PostgreSQL db in Excel: 1 Download the PostgreSQL ODBC driver for Windows -I use v10.3 2 Enter your DB credentials. Go Start > ODBC Data Sources (64 bit) 3 Open Excel > File > Options > Data > Tick the 'From Data Connection Wizard (Legacy)' option 4 Click Excel Data Tab>Get Data>Legacy Wizards>From Data Connection Wizard (Legacy) > ODBC DSN > [Your ODBC Datasource name] > Choose View 5 Data loads into table and you can right click & refresh for live data

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.