Connection string ODBC, Excel 2016 Get & Transform

Question

I'm using Excel 2016's Get & Transform to connect to a Postgres DB. Trying to connect ODBC data source and it asks for a connection string. A connection string I previously successfully used in Excel 2013 documents fails.

let
    Source = Odbc.DataSource("dsn=PostgreSQL32", [HierarchicalNavigation=true]),
    ops_Database = Source{[Name="ops",Kind="Database"]}[Data],
    public_Schema = ops_Database{[Name="public",Kind="Schema"]}[Data],
    monthly_stats_View = public_Schema{[Name="monthly_stats",Kind="View"]}[Data]
in
    monthly_stats_View

[HierarchicalNavigation] is not allowed, and Excel suggests to use SqlCapabilities, and after they also fail, it asks for a connection string. I tried to use connection string from old file and enter this:

provider=Microsoft.Mashup.OleDb.1;
data source=$EmbeddedMashup(10245e6d-0d7e-4d2c-a98e-ec01c3a30e6c)$;location=monthly_stats;
extended properties="UEsDBBQAAgAIAJpYcEnkIV3hrAAAPssAAAASABwAQ2

But it doesn't work. I tried a string from here: https://www.connectionstrings.com/microsoft-ole-db-provider-for-sql-server-sqloledb/ without luck.

Could you please provide me a working example of connection string to read data from ODBC data source in Excel 2016 Get&Transform?

Alejandro Lopez-Lago - MSFT · Accepted Answer · 2016-11-21 19:47:32Z

1

What version of Excel 2016 are you running? If it hasn't been updated, it might be missing the update that added the HierarchicalNavigation option to Odbc.DataSource.

answered Nov 21, 2016 at 19:47

Alejandro Lopez-Lago - MSFT

4,18422 silver badges25 bronze badges

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

Comments

Carl Walsh · Accepted Answer · 2016-11-22 18:08:50Z

1

If you're touching connection strings with Microsoft.Mashup.OleDb.1 in it you're likely going down the wrong path. If you have the workbook file with the Power Query connection, does that just work if you just open it in Excel 2016? Otherwise, make sure you're not using the top-level From Odbc option, and instead use the Get&Transform menu to get data from Odbc.

[HierarchicalNavigation] has been supported in Odbc.DataSource for many months, but it was added this year. You could check that your version of Excel Get&Transform is at least version 2.34.

answered Nov 22, 2016 at 18:08

Carl Walsh

7,2273 gold badges53 silver badges58 bronze badges

Comments

Eugene · Accepted Answer · 2016-12-03 00:13:20Z

First, you should not edit a connection string that appears in the properties of a datasources (or can be found in Data/Connections). As Carl says, do not touch anything like

provider=Microsoft.Mashup.OleDb.1.............

It is another connector - it connects your table to PowerQuery.

Also you're trying to use MS SQL Server connection string to connect to PostgreSQL. You should use a string from here: https://www.connectionstrings.com/pgoledb/

To properly connect to your database you should (in Excel) go to Data/New Query/From Database/From PostgreSQL Database. There you will set up connection to the DB.

Then, use

PostgreSQL.Database(server as text,  optional options as nullable record) as table

to connect. You can find description for this function here.

If you insist on connecting via ODBC, notice that help provides another syntax for ODBC.DataSource:

Odbc.DataSource(connectionString as any, optional options as nullable record) as table

Also, when you add a new connection "From ODBC", you are asked for connection string and credentials.

Ivan Medvediev · Accepted Answer · 2017-11-07 16:00:00Z

1

Issue was resolwed so simple... This is Microsoft bug, which was fixed in updates.

Sollution was: - tip Install office updates together with windows updates (Start->sttings->Update & Security) - run update After that Get & Transform load data from DB without any issues

answered Nov 7, 2017 at 16:00

Ivan Medvediev

841 gold badge1 silver badge4 bronze badges

Collectives™ on Stack Overflow

Connection string ODBC, Excel 2016 Get & Transform

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related