Excel data connection options

0down votefavorite

I’m interested in creating a data connection in Excel to access data from the DHIS2 web api. Essentially I want to be able to generate refreshable tables in an Excel data model from DHIS2 sources: API queries or SQL views. Excel 2016 (or with PowerPivot/PowerQuery add-ins in previous versions) enables connections to OData Data Feeds, but the DHIS2 web api does not use the OData protocol (http://www.odata.org/). I have not found an alternative method. Any suggestions? Are there any plans to develop an OData compatible api?

Hi Ned,

I don’t think there are any plans to support OData at this point.

I would suggest you have a look at our documentation and utilize the metadata and analytics API for this. VBA seems to be capable of making HTTP requests. It might not be the plug and play, standardized solution you are looking for however, but should be possible.

Regards,

Jason Pickering

···

On Nov 26, 2017 21:12, “Ned Jaszi” ned.jaszi@gmail.com wrote:

0down votefavorite

I’m interested in creating a data connection in Excel to access data from the DHIS2 web api. Essentially I want to be able to generate refreshable tables in an Excel data model from DHIS2 sources: API queries or SQL views. Excel 2016 (or with PowerPivot/PowerQuery add-ins in previous versions) enables connections to OData Data Feeds, but the DHIS2 web api does not use the OData protocol (http://www.odata.org/). I have not found an alternative method. Any suggestions? Are there any plans to develop an OData compatible api?


Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp

Thanks. I figured out a more plug and play solution using Excel Get/Transform (previously PowerQuery). The steps are: 1) Create a New Query from Other Sources/Web. 2) Enter the API call URL 3) Enter credentials in Basic tab when prompted

I’ve tested so far with calls for CSV format. Some fiddling is required to set the query to read as CSV, but altogether pretty straightforward. The source step of the query will end up looking something like: = Csv.Document(Web.Contents(“https://URL/api/dataElements.csv?paging=false”),[Delimiter=","])

Best,

Ned

···

On Mon, Nov 27, 2017 at 8:59 AM, Jason Pickering jason.p.pickering@gmail.com wrote:

Hi Ned,

I don’t think there are any plans to support OData at this point.

I would suggest you have a look at our documentation and utilize the metadata and analytics API for this. VBA seems to be capable of making HTTP requests. It might not be the plug and play, standardized solution you are looking for however, but should be possible.

Regards,

Jason Pickering

On Nov 26, 2017 21:12, “Ned Jaszi” ned.jaszi@gmail.com wrote:

0down votefavorite

I’m interested in creating a data connection in Excel to access data from the DHIS2 web api. Essentially I want to be able to generate refreshable tables in an Excel data model from DHIS2 sources: API queries or SQL views. Excel 2016 (or with PowerPivot/PowerQuery add-ins in previous versions) enables connections to OData Data Feeds, but the DHIS2 web api does not use the OData protocol (http://www.odata.org/). I have not found an alternative method. Any suggestions? Are there any plans to develop an OData compatible api?


Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp

Hi Ned,

That’s quite an interesting approach. Great to see.

If you need to add basic authentication details, where would you add it in the example query format you sent?

Thanks,

Ime

photo



Ime Asangansi MD,PhD
Chief Excellence Officer, ehealth4everyone

+2348180790150

ime@ehealth4everyone.com

Skype: asangansiime

www.ehealth4everyone.com

···

On Mon, Nov 27, 2017 at 9:13 PM, Ned Jaszi ned.jaszi@gmail.com wrote:

Thanks. I figured out a more plug and play solution using Excel Get/Transform (previously PowerQuery). The steps are: 1) Create a New Query from Other Sources/Web. 2) Enter the API call URL 3) Enter credentials in Basic tab when prompted

I’ve tested so far with calls for CSV format. Some fiddling is required to set the query to read as CSV, but altogether pretty straightforward. The source step of the query will end up looking something like: = Csv.Document(Web.Contents(“https://URL/api/dataElements.csv?paging=false”),[Delimiter=","])

Best,

Ned


Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp

On Mon, Nov 27, 2017 at 8:59 AM, Jason Pickering jason.p.pickering@gmail.com wrote:

Hi Ned,

I don’t think there are any plans to support OData at this point.

I would suggest you have a look at our documentation and utilize the metadata and analytics API for this. VBA seems to be capable of making HTTP requests. It might not be the plug and play, standardized solution you are looking for however, but should be possible.

Regards,

Jason Pickering

On Nov 26, 2017 21:12, “Ned Jaszi” ned.jaszi@gmail.com wrote:

0down votefavorite

I’m interested in creating a data connection in Excel to access data from the DHIS2 web api. Essentially I want to be able to generate refreshable tables in an Excel data model from DHIS2 sources: API queries or SQL views. Excel 2016 (or with PowerPivot/PowerQuery add-ins in previous versions) enables connections to OData Data Feeds, but the DHIS2 web api does not use the OData protocol (http://www.odata.org/). I have not found an alternative method. Any suggestions? Are there any plans to develop an OData compatible api?


Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp

Excel will prompt you for the credentials in a separate dialogue box. These are saved and applied for all queries to the same URL. I’m not yet sure how this works when sharing the file with other users.

···

On Nov 28, 2017 12:05 AM, “Ime Asangansi” imeasangansi@gmail.com wrote:

Hi Ned,

That’s quite an interesting approach. Great to see.

If you need to add basic authentication details, where would you add it in the example query format you sent?

Thanks,

Ime

photo



Ime Asangansi MD,PhD
Chief Excellence Officer, ehealth4everyone

+2348180790150

ime@ehealth4everyone.com

Skype: asangansiime

www.ehealth4everyone.com

On Mon, Nov 27, 2017 at 9:13 PM, Ned Jaszi ned.jaszi@gmail.com wrote:

Thanks. I figured out a more plug and play solution using Excel Get/Transform (previously PowerQuery). The steps are: 1) Create a New Query from Other Sources/Web. 2) Enter the API call URL 3) Enter credentials in Basic tab when prompted

I’ve tested so far with calls for CSV format. Some fiddling is required to set the query to read as CSV, but altogether pretty straightforward. The source step of the query will end up looking something like: = Csv.Document(Web.Contents(“https://URL/api/dataElements.csv?paging=false”),[Delimiter=","])

Best,

Ned


Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp

On Mon, Nov 27, 2017 at 8:59 AM, Jason Pickering jason.p.pickering@gmail.com wrote:

Hi Ned,

I don’t think there are any plans to support OData at this point.

I would suggest you have a look at our documentation and utilize the metadata and analytics API for this. VBA seems to be capable of making HTTP requests. It might not be the plug and play, standardized solution you are looking for however, but should be possible.

Regards,

Jason Pickering

On Nov 26, 2017 21:12, “Ned Jaszi” ned.jaszi@gmail.com wrote:

0down votefavorite

I’m interested in creating a data connection in Excel to access data from the DHIS2 web api. Essentially I want to be able to generate refreshable tables in an Excel data model from DHIS2 sources: API queries or SQL views. Excel 2016 (or with PowerPivot/PowerQuery add-ins in previous versions) enables connections to OData Data Feeds, but the DHIS2 web api does not use the OData protocol (http://www.odata.org/). I have not found an alternative method. Any suggestions? Are there any plans to develop an OData compatible api?


Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp