CODEWITHSUNDEEP
odbcconnection-stringpowerqueryexcel-2016
Ivan Medvediev
Ivan Medvediev

Reputation: 84

Connection string ODBC, Excel 2016 Get & Transform

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?

Upvotes: 0

Views: 10670

Answers (4)

Ivan Medvediev
Ivan Medvediev

Reputation: 84

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

Upvotes: 1

Eugene
Eugene

Reputation: 1264

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.

Upvotes: 1

Carl Walsh
Carl Walsh

Reputation: 6949

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.

Upvotes: 1

Alejandro Lopez-Lago - MSFT
Alejandro Lopez-Lago - MSFT

Reputation: 4134

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.

Upvotes: 1

Related Questions