CODEWITHSUNDEEP
jsonexcelparsing
Manish Joisar
Manish Joisar

Reputation: 1310

Microsoft Excel - Get value from JSON column

We are using Azure APIM service & linked App Insights to get the log of request for analysis purpose.

As we are using Imperva WAF, we are getting Imperva IPs and getting client's IPs in request header thru App Insights Logs(Analytics).

So getting data in csv format, something like this

"2019-10-06T17:21:20.2264252Z","|key",,"OPTIONS /Endpoint/","https://APIM/query",True,200,"0.3565","<250ms",request,"{""ApimanagementServiceName"":""APIM_name"",""ApimanagementRegion"":""Country"",""HTTP Method"":""OPTIONS"",""API Name"":""API"",""Cache"":""None"",""Request-Incap-Client-IP"":""2a:23c4:1c4c:6c00:a458_IP""}","{""Client Time (in ms)"":0,""Response Size"":334,""Request Size"":0}","OPTIONS /EndPoint/",Key,,,,,,,PC,,,"0.0.0.0",,,"Country",,"APIM Country","APIM Country","key","App Insights","key","apim:0.81.21.0","key",1

Now i want to extract IP from "Request-Incap-Client-IP" element which is stored in JSON format starting from "ApimanagementServiceName".

I looked at help on web and all are talking about macro, custom code.

In my opinion, excel should have function to parse json and get value from specific columns i mean solution should be straightforward and simple.

Upvotes: 0

Views: 980

Answers (1)

LostReality
LostReality

Reputation: 687

Use that custom library to parse/read/write JSON files and streams

https://github.com/VBA-tools/VBA-JSON

Dim fso As New FileSystemObject
Dim JsonTS As TextStream
Dim Json As Dictionary
Dim JsonText As String

Set JsonTS = fso.OpenTextFile("yourfile.csv", ForReading)
JsonText = JsonTS.ReadAll
JsonTS.Close
'process the string to isolate JSON part of your CSV using Split/regexp
Set Json = JsonConverter.ParseJson(JsonText)

Then you can just retrieve the value like that :

Dim ipValue as String

ipValue = Json("Request-Incap-Client-IP")

Upvotes: 0

Related Questions