Microsoft Excel -从JSON列获取值

Question

我们正在使用Azure APIM服务和链接的App Insights来获取请求日志，用于分析目的。

当我们使用Imperva WAF时，我们正在通过App Insights日志(分析)获取Imperva in和请求标头中的客户端in。

所以获取csv格式的数据，就像这样

"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

现在我想从"Request-Incap-Client- IP“元素中提取IP，该元素以JSON格式存储，以"ApimanagementServiceName”开头。

我看了网络上的帮助，所有人都在谈论宏和自定义代码。

在我看来，excel应该具有解析json并从特定列中获取值的功能，我的意思是解决方案应该简单明了。

Answer 1

使用该定制库来解析/读/写JSON文件和流

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)

然后，您可以像这样检索该值：

Dim ipValue as String

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