How to create Organisational Units via VBA (Excel)?

Wanting to submit Organizational Unit data via VBA - tried to push the data via CSV and JSON with no luck.
URL I’m attempting to push to api/metadata?classKey=ORGANISATION_UNIT

JSON Format:
[
{
“name”: “Sample Location 1”,
“uid”: “Z1raiKXH6yv”,
“shortname”: “Sample Location 1”,
“parent”: “PqlFaeuPcF1”
}
]

Code to fire off the JSON:

Private Function dhis2APIpostJSON(strURL As String, strUsr As String, strPwd As String, strJSON As String) As String
Dim MyRequest As Object
On Error GoTo dhis2APIpostErr

Application.Cursor = xlWait
Set MyRequest = CreateObject(“WinHttp.WinHttpRequest.5.1”)
MyRequest.Open “POST”, strURL, False
MyRequest.setRequestHeader “Content-Type”, “application/json”
MyRequest.setRequestHeader “Authorization”, "Basic " & Encode64(strUsr & “:” & strPwd)
MyRequest.send strJSON
dhis2APIpostJSON = MyRequest.ResponseText
Application.Cursor = xlDefault
Exit Function

dhis2APIpostErr:
dhis2APIpostJSON = "API call failed. " & Err.Number & " - " & Err.Description
Application.Cursor = xlDefault

End Function

For the CSV - I tried the following:
MyRequest.setRequestHeader “Content-Type”, “application/csv”
and passed CSV looked like this:
Name,UID,ShortName,Parent
Sample Location 1,Z1raiKJH6yv,Sample Location 1,PqlFzhuPcF1

Any advise or direction to where I’m going wrong please advise. Thanks in advance.

Hi Matthew,

CSV import for orgUnits is a little bit picky. I’ve observed that you need to have all the fields even if they are not required.

Try with this sample we use to import OUs with coordinates (I had to attach it as .xls) csvOrgUnit.xls (34 KB) . Watch out the Opening Date field format when converting to CSV (it has to follow the pattern YYYY-MM-DD, might be recognised as a date)

@jomutsani, perhaps CoP should also allow to attach .csv files in the platform as is a common format used in DHIS2.

Thanks Eric for the assistance.

I ended up figuring it out by investigating the format that the API takes when doing it from the web app. For anyone looking for this (and similarly for other metadata types such as Data Elements) my import script looks like this.

strURL and strCSVFilePath are overwritten here to show working - additionally pushing the correct classkey as referenced from here: https://docs.dhis2.org/master/en/developer/html/webapi_csv_metadata_import.html

Private Function dhis2APIpostCSV(strURL As String, strUsr As String, strPwd As String, strCSVFilePath As String, ClassKey As String) As String
Dim Data As String

strURL = “https:///api/metadata.json?importMode=COMMIT&dryRun=undefined&identifier=UID&importReportMode=ERRORS&preheatMode=REFERENCE&importStrategy=CREATE_AND_UPDATE&atomicMode=ALL&mergeMode=MERGE&flushMode=AUTO&skipSharing=false&skipValidation=false&async=true&inclusionStrategy=NON_NULL&classKey=” & ClassKey & “&format=json”

'CSV File creation based on the structure - still need to write this creation with the VBNewLines

strCSVFilePath = “Name,UID,shortName” & vbNewLine & “SAMPLE 1,AoCzcvlpyuI,SAMPLE 1”

Dim MyRequest As Object
On Error GoTo dhis2APIpostErr ’

Application.Cursor = xlWait
Set MyRequest = CreateObject(“WinHttp.WinHttpRequest.5.1”)
MyRequest.Open “POST”, strURL, False
MyRequest.setRequestHeader “Content-Type”, “application/csv”
MyRequest.setRequestHeader “Authorization”, "Basic " & Encode64(strUsr & “:” & strPwd)
MyRequest.send strCSVFilePath
dhis2APIpostCSV = MyRequest.ResponseText
Application.Cursor = xlDefault
Exit Function

dhis2APIpostErr:
dhis2APIpostCSV = "API call failed. " & Err.Number & " - " & Err.Description
Application.Cursor = xlDefault

End Function

1 Like