[Dhis2-devs] Web API in Excel

Yes, I agree with Markus,
Excel is good tool to do deep formula to calculate in data elements. And offline too.

Thanks Bob,

  1. I have not established a preference here - your point on using CSV is a good one. CSV will probably be easier to “deserialize” in most cases. JSON or XML on the other hand, will probably be a little more robust to changes in the WebAPI output.

  2. I like it. Excel is a good tool accessible to many people. I have been using an excel sheet to manage some data through the API, but the sheet I made contains some “hardcodings” that is made for my specific case. I have been thinking about making a generic version of my Excel, that can be used by anyone to post, put and maybe also get data through the API.

Best regards

Markus

p.s. I deleted the stackexchange question again, as the question and answer was generic Excel, and not so DHIS related. The link still works, but here is the code snippet.

It is possible to use Excel Macros to call the Web API. Posting to the API can be done like this:

    Public Function postData(ByVal address As String, ByVal json As String, ByVal mode As String) If (mode = "Yes" Or mode = "Debug") Then On Error GoTo Errorhandler Dim objHTTP As New WinHttpRequest URL = address objHTTP.Open "POST", URL, False objHTTP.Option(WinHttpRequestOption_SslErrorIgnoreFlags) = 13056 objHTTP.setRequestHeader "Content-Type", "application/json" objHTTP.setRequestHeader "Authorization", "Basic " & EncodeBase64("admin:district") objHTTP.send json If (mode = "Debug") Then postData = objHTTP.ResponseText Else postData = objHTTP.statusText End If Else postData = mode End If Exit Function Errorhandler: postData = Err.Description End Function

For doing a GET, you should be able to just replace the “POST” with a “GET”, and send the request without the json.

···

Markus Bekken markus.bekken@gmail.com wrote:

  1. jun. 2015 kl. 13.40 skrev Bob Jolliffe bobjolliffe@gmail.com:

Hi Markus

Thanks for this information. That’s a really useful snippet for
anyone to get started who might want to do this. I have a quick
question and a comment/suggestion:

  1. I can see from the link that you can indeed parse/serialize json
    from VBA - I guess it would have been surprising if you couldn’t :slight_smile:
    Would you do this in preference to CSV?

  2. On a a more general point, there has been interest in (and a long
    historical relationship) between DHIS and Excel. Ranging from its
    early origins in DHIS1.4 through to primitive efforts to populate
    spreadsheet data using mydatamart. But the use cases from excel as
    data collection instrument, through to excel for analysis (pivot
    tables) and simple metadata management are very varied. It strikes me
    that a library of DHIS2-web-api macros similar to those you presented
    in your link would be hugely interesting to a lot of people. Have you
    considered collecting/publishing such a thing?

Cheers
Bob

On 23 June 2015 at 12:06, Markus Bekken markus.bekken@gmail.com wrote:

Hi Channara and Bob

I have been using macros to POST and PUT data to the API, and GET requests
should work just as well.

I added your question and put my macro code here:
https://webapps.stackexchange.com/questions/79521/can-i-use-excel-to-get-json-from-the-dhis-api/79522#79522

See the comment at the end, seems it is possible to parse the JSON in a
macro also.

Thank you,
Markus

  1. jun. 2015 kl. 12.13 skrev Bob Jolliffe bobjolliffe@gmail.com:

You can’t use excel to get json data from dhis2 web api. At least not
directly.

You could possibly write an excel macro to “get” the data through the
web api (I am really not sure - it is far too many years since i wrote
an excel macro), but you are still left with the problem of consuming
what you have got (json) into cells in a spreadsheet.

I believe all (or certainly almost all) of the data which is available
as json is also available as csv. If you are looking at getting dhsi2
data into excel in general, I would look at csv rather than json if
you want to minimize the processing/transformation that would
otherwise have to happen in between getting the data and consuming it.

https://www.dhis2.org/doc/snapshot/en/developer/html/dhis2_developer_manual.html

On 23 June 2015 at 07:47, channara rin rin.channara@gmail.com wrote:

Hi all DHIS2 developer,
Do you know can i use Microsoft Excel to get json data from DHIS2 web API?

thank you
channara


Mailing list: https://launchpad.net/~dhis2-devs
Post to : dhis2-devs@lists.launchpad.net
Unsubscribe : https://launchpad.net/~dhis2-devs
More help : https://help.launchpad.net/ListHelp


Mailing list: https://launchpad.net/~dhis2-devs
Post to : dhis2-devs@lists.launchpad.net
Unsubscribe : https://launchpad.net/~dhis2-devs
More help : https://help.launchpad.net/ListHelp

The excel thing is obviously something that we should address at some point. I have also made an unfinished attempt during the Ebola crisis. What I did was to provide a UI that allows users to upload excel file, do mapping between columns and data elements or attributes and finally upload the cell values.

···

On Tue, Jun 23, 2015, 14:15 rin.channara@gmail.com rin.channara@gmail.com wrote:

Yes, I agree with Markus,
Excel is good tool to do deep formula to calculate in data elements. And offline too.

Markus Bekken markus.bekken@gmail.com wrote:

Thanks Bob,

  1. I have not established a preference here - your point on using CSV is a good one. CSV will probably be easier to “deserialize” in most cases. JSON or XML on the other hand, will probably be a little more robust to changes in the WebAPI output.
  1. I like it. Excel is a good tool accessible to many people. I have been using an excel sheet to manage some data through the API, but the sheet I made contains some “hardcodings” that is made for my specific case. I have been thinking about making a generic version of my Excel, that can be used by anyone to post, put and maybe also get data through the API.

Best regards

Markus

p.s. I deleted the stackexchange question again, as the question and answer was generic Excel, and not so DHIS related. The link still works, but here is the code snippet.

It is possible to use Excel Macros to call the Web API. Posting to the API can be done like this:

    Public Function postData(ByVal address As String, ByVal json As String, ByVal mode As String) If (mode = "Yes" Or mode = "Debug") Then On Error GoTo Errorhandler Dim objHTTP As New WinHttpRequest URL = address objHTTP.Open "POST", URL, False objHTTP.Option(WinHttpRequestOption_SslErrorIgnoreFlags) = 13056 objHTTP.setRequestHeader "Content-Type", "application/json" objHTTP.setRequestHeader "Authorization", "Basic " & EncodeBase64("admin:district") objHTTP.send json If (mode = "Debug") Then postData = objHTTP.ResponseText Else postData = objHTTP.statusText End If Else postData = mode End If Exit Function Errorhandler: postData = Err.Description End Function

For doing a GET, you should be able to just replace the “POST” with a “GET”, and send the request without the json.

  1. jun. 2015 kl. 13.40 skrev Bob Jolliffe bobjolliffe@gmail.com:

Hi Markus

Thanks for this information. That’s a really useful snippet for
anyone to get started who might want to do this. I have a quick
question and a comment/suggestion:

  1. I can see from the link that you can indeed parse/serialize json
    from VBA - I guess it would have been surprising if you couldn’t :slight_smile:
    Would you do this in preference to CSV?

  2. On a a more general point, there has been interest in (and a long
    historical relationship) between DHIS and Excel. Ranging from its
    early origins in DHIS1.4 through to primitive efforts to populate
    spreadsheet data using mydatamart. But the use cases from excel as
    data collection instrument, through to excel for analysis (pivot
    tables) and simple metadata management are very varied. It strikes me
    that a library of DHIS2-web-api macros similar to those you presented
    in your link would be hugely interesting to a lot of people. Have you
    considered collecting/publishing such a thing?

Cheers
Bob

On 23 June 2015 at 12:06, Markus Bekken markus.bekken@gmail.com wrote:

Hi Channara and Bob

I have been using macros to POST and PUT data to the API, and GET requests
should work just as well.

I added your question and put my macro code here:
https://webapps.stackexchange.com/questions/79521/can-i-use-excel-to-get-json-from-the-dhis-api/79522#79522

See the comment at the end, seems it is possible to parse the JSON in a
macro also.

Thank you,
Markus

  1. jun. 2015 kl. 12.13 skrev Bob Jolliffe bobjolliffe@gmail.com:

You can’t use excel to get json data from dhis2 web api. At least not
directly.

You could possibly write an excel macro to “get” the data through the
web api (I am really not sure - it is far too many years since i wrote
an excel macro), but you are still left with the problem of consuming
what you have got (json) into cells in a spreadsheet.

I believe all (or certainly almost all) of the data which is available
as json is also available as csv. If you are looking at getting dhsi2
data into excel in general, I would look at csv rather than json if
you want to minimize the processing/transformation that would
otherwise have to happen in between getting the data and consuming it.

https://www.dhis2.org/doc/snapshot/en/developer/html/dhis2_developer_manual.html

On 23 June 2015 at 07:47, channara rin rin.channara@gmail.com wrote:

Hi all DHIS2 developer,
Do you know can i use Microsoft Excel to get json data from DHIS2 web API?

thank you
channara


Mailing list: https://launchpad.net/~dhis2-devs
Post to : dhis2-devs@lists.launchpad.net
Unsubscribe : https://launchpad.net/~dhis2-devs
More help : https://help.launchpad.net/ListHelp


Mailing list: https://launchpad.net/~dhis2-devs
Post to : dhis2-devs@lists.launchpad.net
Unsubscribe : https://launchpad.net/~dhis2-devs
More help : https://help.launchpad.net/ListHelp


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


Thank you,

Abyot

Is anyone using Excel to create requirements and initial configuration for a DHIS2 instance? My team is trying to create one big spreadsheet that defines the data dictionary and, to the extent possible, the data model to set up a simple DHIS2 implementation. Worksheets would include indicators, data elements, categories, option sets, etc. … I know that we probably won’t be able to define everything in one spreadsheet (see the following except from Bob Jolliffe from another thread), but it sure would be helpful to use for requirements definition and for working with clients. And also to create templates for quick implementations to shorten the setup period.

I know that Juan has created some beautiful spreadsheets for user names and organizational units – has anyone else done this type of thing?

Thanks

  • Gillian

image

image

···

On Tue, Jun 23, 2015, 14:15 rin.channara@gmail.com rin.channara@gmail.com wrote:

Yes, I agree with Markus,

Excel is good tool to do deep formula to calculate in data elements. And offline too.

Markus Bekken markus.bekken@gmail.com wrote:

Thanks Bob,

  1. I have not established a preference here - your point on using CSV is a good one. CSV will probably be easier to “deserialize” in most cases. JSON or XML on the other hand, will probably be a little more robust to changes in the WebAPI output.
  1. I like it. Excel is a good tool accessible to many people. I have been using an excel sheet to manage some data through the API, but the sheet I made contains some “hardcodings” that is made for my specific case. I have been thinking about making a generic version of my Excel, that can be used by anyone to post, put and maybe also get data through the API.

Best regards

Markus

p.s. I deleted the stackexchange question again, as the question and answer was generic Excel, and not so DHIS related. The link still works, but here is the code snippet.

It is possible to use Excel Macros to call the Web API. Posting to the API can be done like this:

Public Function postData(ByVal address As String, ByVal json As String, ByVal mode As String)
    If (mode = "Yes" Or mode = "Debug") Then
        On Error GoTo Errorhandler
        Dim objHTTP As New WinHttpRequest
        URL = address
        objHTTP.Open "POST", URL, False
        objHTTP.Option(WinHttpRequestOption_SslErrorIgnoreFlags) = 13056

        objHTTP.setRequestHeader "Content-Type", "application/json"
        objHTTP.setRequestHeader "Authorization", "Basic " & EncodeBase64("admin:district")
        objHTTP.send json

        If (mode = "Debug") Then
            postData = objHTTP.ResponseText
            Else
            postData = objHTTP.statusText
        End If
    Else
        postData = mode
    End If

    Exit Function
    Errorhandler:
        postData = Err.Description
End Function

For doing a GET, you should be able to just replace the “POST” with a “GET”, and send the request without the json.

  1. jun. 2015 kl. 13.40 skrev Bob Jolliffe bobjolliffe@gmail.com:

Hi Markus

Thanks for this information. That’s a really useful snippet for

anyone to get started who might want to do this. I have a quick

question and a comment/suggestion:

  1. I can see from the link that you can indeed parse/serialize json

from VBA - I guess it would have been surprising if you couldn’t :slight_smile:

Would you do this in preference to CSV?

  1. On a a more general point, there has been interest in (and a long

historical relationship) between DHIS and Excel. Ranging from its

early origins in DHIS1.4 through to primitive efforts to populate

spreadsheet data using mydatamart. But the use cases from excel as

data collection instrument, through to excel for analysis (pivot

tables) and simple metadata management are very varied. It strikes me

that a library of DHIS2-web-api macros similar to those you presented

in your link would be hugely interesting to a lot of people. Have you

considered collecting/publishing such a thing?

Cheers

Bob

On 23 June 2015 at 12:06, Markus Bekken markus.bekken@gmail.com wrote:

Hi Channara and Bob

I have been using macros to POST and PUT data to the API, and GET requests

should work just as well.

I added your question and put my macro code here:

https://webapps.stackexchange.com/questions/79521/can-i-use-excel-to-get-json-from-the-dhis-api/79522#79522

See the comment at the end, seems it is possible to parse the JSON in a

macro also.

Thank you,

Markus

  1. jun. 2015 kl. 12.13 skrev Bob Jolliffe bobjolliffe@gmail.com:

You can’t use excel to get json data from dhis2 web api. At least not

directly.

You could possibly write an excel macro to “get” the data through the

web api (I am really not sure - it is far too many years since i wrote

an excel macro), but you are still left with the problem of consuming

what you have got (json) into cells in a spreadsheet.

I believe all (or certainly almost all) of the data which is available

as json is also available as csv. If you are looking at getting dhsi2

data into excel in general, I would look at csv rather than json if

you want to minimize the processing/transformation that would

otherwise have to happen in between getting the data and consuming it.

https://www.dhis2.org/doc/snapshot/en/developer/html/dhis2_developer_manual.html

On 23 June 2015 at 07:47, channara rin rin.channara@gmail.com wrote:

Hi all DHIS2 developer,

Do you know can i use Microsoft Excel to get json data from DHIS2 web API?

thank you

channara


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

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

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

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


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

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

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

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


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


Thank you,

Abyot