How to create an odbc string connection in an excel spreadsheet to make it portable instead of setting up an odbc connection on every computer?

Question

I'm setting up an excel reporting using Excel 2013 for that.

The spreadsheet is retrieving data, through an odbc connection that I need to set on every computer in order to retrieve the data.

I tried to change the following:

Data > Query > Change the property of the connection chain

This is the connection string

DSN=BI_AZURE;DATABASE=dw;SERVER=mydb.postgres.database.azure.com;PORT=5432;UID=sqladmin@username;SSLmode=disable;ReadOnly=0;Protocol=7.4;FakeOidIndex=0;ShowOidColumn=0;RowVersioning=0;ShowSystemTables=0;ConnSettings=;Fetch=100;Socket=4096;UnknownSizes=0;MaxVarcharSize=255;MaxLongVarcharSize=8190;Debug=0;CommLog=0;Optimizer=0;Ksqo=1;UseDeclareFetch=0;TextAsLongVarchar=1;UnknownsAsLongVarchar=0;BoolsAsChar=1;Parse=0;CancelAsFreeStmt=0;ExtraSysTablePrefixes=dd_;LFConversion=1;UpdatableCursors=1;DisallowPremature=0;TrueIsMinus1=0;BI=0;ByteaAsLongVarBinary=0;UseServerSidePrepare=0;LowerCaseIdentifier=0;GssAuthUseGSS=0;XaOpt=1

Is there a way to remove the DSN=BI_AZURE and put a static string, that won't use the DSN and yet will be able to connect the reporting, to any machine, regardless if the odbc connection is existing?

Thanks.

Answer 1

You want to make sure you have an existing odbc driver in order to do this.

And tick the following box (mind the french system language)

Change your code with the following:

DRIVER={PostgreSQL UNICODE};DATABASE=dw;SERVER=mydb.postgres.database.azure.com;PORT=5432;UID=sqladmin@username;

And everything should be working.

Connection string source

Helpful source