Parse Json values in Excel cells

[manurockz007]

How do i separate all the values from the following

{ "anonymousId": "213f9272-ebb4-4c07-8fb0-85f4ff0b3ad6", "context": { "library": { "name": "analytics.js", "version": "3.0.0" }, "page": { "path": "/prem/", "referrer": "http://m.facebook.com/", "search": "", "title": "प्रेम", "url": "http://hamroasa.com/prem/" }, "userAgent": "Mozilla/5.0 (Linux; Android 5.0; Lenovo A1000 Build/S100; wv) AppleWebKit/537.36 (KHTML, like Gecko) Version/4.0 Chrome/43.0.2357.121 Mobile Safari/537.36 [FB_IAB/FB4A;FBAV/34.0.0.43.267;]", "ip": "103.214.79.6" }, "event": "Gospel View Depth", "integrations": {}, "messageId": "ajs-bd3e7cbad4f5d0efe006c419b5f1e878", "properties": { "gospel_play_percentage": "0.926", "gospel_video_duration": 148.95600907029478, "gospel_video_position": 138, "gospel_video_url": "https://www.youtube.com/watch?v=HrsImLMpP0c", "site_type_id": 6 }, "receivedAt": "2016-12-09T09:25:22.484Z", "sentAt": "2016-12-09T09:26:38.120Z", "timestamp": "2016-12-09T09:25:22.475Z", "type": "track", "userId": null, "originalTimestamp": "2016-12-09T09:26:38.111Z" } To get an output where all the values should be separate.

<tbody>
</tbody>

 

[manurockz007]

Thanks it's working
but i am not able get url in excel data from the code u gave me its showing some error. I am not able to understand.
Seems like need to make any modification in code to capture data.
And How to get data from segment tool to excel any possible way before it goes to data base.

 

[John_w]

but i am not able get url in excel data from the code u gave me its showing some error. I am not able to understand.

Which URL? The code in my earlier post http://www.mrexcel.com/forum/excel-questions/980302-parse-json-values-excel-cells.html#post4704611 extracts the "gospel_video_url" from the Excel cell. What is the error?

Seems like need to make any modification in code to capture data.
And How to get data from segment tool to excel any possible way before it goes to data base.

Sorry, I don't understand you.

 

[manurockz007]

Url or title that is in context section or page section from the data

"anonymousId": "m shgfwhwpiu,
"context": {
"library": {
"name": "analytics.<acronym title="JavaScript" style="border-width: 0px 0px 1px; border-top-style: initial; border-right-style: initial; border-bottom-style: dotted; border-left-style: initial; border-top-color: initial; border-right-color: initial; border-bottom-color: rgb(0, 0, 0); border-left-color: initial; border-image: initial; cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">js</acronym>",
"version": "3.0.0"
},
"page": {
"path": "/prem/",
"referrer": "http://m.ddaaf.com/",
"search": "",
"title": "प्रेम",
"url": "http://xvsfgs.com/vdva/"
},
should i write .item("page") or .item("context") for a json.

 

[John_w]

"title" and "url" are within the context->page path, therefore:

    With JSONdict.item("context").item("page")
        Worksheets("Sheet1").Range("A11").Value = .item("title")
        Worksheets("Sheet1").Range("A12").Value = .item("url")
    End With

 

[manurockz007]

Thanks it works fine.
One more question we use segment tool to capture data that flows in database are there any way to collect it directly to excel sheet without referring to segment web debugger tool so that we can automation that work.
thanks in advance

 

[John_w]

I'm not aware of this 'segment tool' so I don't know the answer, sorry.

 

[manurockz007]

Oh Thanks for all your help