Posting request API to Statistics Canada. The Response string I'm getting is complex (at least to me) and I can not extract any value from it.
Tried many syntax possible to only get the "cansimId" without success.
My goal would be to list (array loop) the "dimensionPositionId":2 ("Principal statistics") and get all ("memberNameEn")
Many thanks! :)
Here's a sample from the response. Too big to post it all here 100K.txt:
[
{
"status": "SUCCESS",
"object": {
"responseStatusCode": 0,
"productId": "16100047",
"cansimId": "304-0014",
"cubeTitleEn": "Manufacturers' sales, inventories, orders and inventory to sales ratios, by industry (dollars unless otherwise noted)",
"cubeTitleFr": "Stocks, ventes, commandes et rapport des stocks sur les ventes pour les industries manufacturières, selon l'industrie (dollars sauf indication contraire)",
"cubeStartDate": "1992-01-01",
"cubeEndDate": "2021-08-01",
"frequencyCode": 6,
"nbSeriesCube": 2798,
"nbDatapointsCube": 935808,
"releaseTime": "2021-10-14T08:30",
"archiveStatusCode": "2",
"archiveStatusEn": "CURRENT - a cube available to the public and that is current",
"archiveStatusFr": "ACTIF - un cube qui est disponible au public et qui est toujours mise a jour",
"subjectCode": [
"1699",
"230402",
"330303",
"451003"
],
"surveyCode": [
"2101"
],
"dimension": [
{
"dimensionPositionId": 1,
"dimensionNameEn": "Geography",
"dimensionNameFr": "Géographie",
"hasUom": false,
"member": [
{
"memberId": 1,
"parentMemberId": null,
"memberNameEn": "Canada",
"memberNameFr": "Canada",
"classificationCode": "11124",
"classificationTypeCode": "1",
"geoLevel": 0,
"vintage": 2016,
"terminated": 0,
"memberUomCode": null
}
]
},
{
"dimensionPositionId": 2,
"dimensionNameEn": "Principal statistics",
"dimensionNameFr": "Les statistiques principales",
"hasUom": true,
"member": [
{
"memberId": 1,
"parentMemberId": null,
"memberNameEn": "Sales of goods manufactured (shipments)",
"memberNameFr": "Ventes de biens fabriqués (livraisons)",
"classificationCode": null,
"classificationTypeCode": null,
"geoLevel": null,
"vintage": null,
"terminated": 0,
"memberUomCode": 81
},
{
"memberId": 2,
"parentMemberId": null,
"memberNameEn": "New orders, estimated values of orders received during month",
"memberNameFr": "Nouvelles commandes, valeur estimative des commandes reçues durant le mois",
"classificationCode": null,
"classificationTypeCode": null,
"geoLevel": null,
"vintage": null,
"terminated": 0,
"memberUomCode": 81
},
{
"memberId": 3,
"parentMemberId": null,
"memberNameEn": "Unfilled orders, estimated values of orders at end of month",
"memberNameFr": "Commandes en carnet, valeur estimative des commandes à la fin du mois",
"classificationCode": null,
"classificationTypeCode": null,
"geoLevel": null,
"vintage": null,
"terminated": 0,
"memberUomCode": 81
},
{
"memberId": 4,
"parentMemberId": null,
"memberNameEn": "Raw materials, fuel, supplies, components, estimated values at end of month",
"memberNameFr": "Matières premières, combustibles, fournitures et composantes, valeur estimative à la fin du mois",
"classificationCode": null,
"classificationTypeCode": null,
"geoLevel": null,
"vintage": null,
"terminated": 0,
"memberUomCode": 81
},
{
"memberId": 5,
"parentMemberId": null,
"memberNameEn": "Goods or work in process, estimated values at end of month",
"memberNameFr": "Biens en cours de fabrication ou travaux en cours, valeur estimative à la fin du mois",
"classificationCode": null,
"classificationTypeCode": null,
"geoLevel": null,
"vintage": null,
"terminated": 0,
"memberUomCode": 81
},
{
"memberId": 6,
"parentMemberId": null,
"memberNameEn": "Finished goods manufactured, estimated values at end of month",
"memberNameFr": "Produits finis fabriqués, valeur estimative à la fin du mois",
"classificationCode": null,
"classificationTypeCode": null,
"geoLevel": null,
"vintage": null,
"terminated": 0,
"memberUomCode": 81
},
{
"memberId": 7,
"parentMemberId": null,
"memberNameEn": "Total inventory, estimated values of total inventory at end of the month",
"memberNameFr": "Total des stocks, valeur estimative des stocks à la fin du mois",
"classificationCode": null,
"classificationTypeCode": null,
"geoLevel": null,
"vintage": null,
"terminated": 0,
"memberUomCode": 81
},
{
"memberId": 8,
"parentMemberId": null,
"memberNameEn": "Ratio of total inventory to sales",
"memberNameFr": "Rapport du total des stocks aux ventes",
"classificationCode": null,
"classificationTypeCode": null,
"geoLevel": null,
"vintage": null,
"terminated": 0,
"memberUomCode": 270
},
{
"memberId": 9,
"parentMemberId": null,
"memberNameEn": "Ratio of finished goods to sales",
"memberNameFr": "Rapport des produits finis aux ventes",
"classificationCode": null,
"classificationTypeCode": null,
"geoLevel": null,
"vintage": null,
"terminated": 0,
"memberUomCode": 270
}
]
},
And here's the code:
Dim Request As New MSXML2.XMLHTTP60
Dim Body As String
Dim Json As Object
Body = "[{" & Chr(34) & "productId" & Chr(34) & ":16100047}]"
Request.Open "POST", "https://www150.statcan.gc.ca/t1/wds/rest/getCubeMetadata", False
Request.SetRequestHeader "Content-Type", "application/json"
Request.send Body
Set Json = JsonConverter.ParseJson(Request.ResponseText)
'Debug.print Json("status") 'Error !!!!
'Debug.Print Json("cansimId") 'Error!!!!
'Debug.print Json("object")("cansimId") 'Error!!!!
CodePudding user response：
Thank's Tomalak, you were right! One thing... All my arrays seems to be 'option base 1' (although not specify!) So Json(1)("status") is now working.
Finaly got what I needed:
Dim ColStatPrinc As Collection
Dim StatPrinc As Dictionary
x = 1
Set ColStatPrinc = Json(1)("object")("dimension")(2)("member")
With Sheets(1)
For Each StatPrinc In ColStatPrinc
.Cells(x, 1) = StatPrinc("memberId")
.Cells(x, 2) = StatPrinc("memberNameFr")
x = x 1
Next StatPrinc
End With