从.responseText提取特定的JSON字段到单个Excel单元格 [英] Extracting specific JSON field from .responseText to single excel cell
问题描述
我正在尝试从JSON检索特定字段,以进行解析.我不确定如何才能获得这一领域.我添加了Msgbox [Exists& [失败],以查看代码是否能够读取单元格中的解决"一词,但是我以失败告终.
I am trying to retrieve a particular field, resolve, from JSON. I am not sure as to how I can go about getting that one field. I added the Msgbox [Exists & Fail] to see if the code is able to read the word resolve within the cell, however i am returned with fail.
有什么办法我只能让现场解决吗?请协助.
Is there any way i can get only the field resolve? Kindly assist.
谢谢!
TargetURL = "https://api.passivetotal.org/v2/dns/passive?query=passivetotal.org"
actionType = "Content-Type"
actionWord = "application/json"
With CreateObject("Microsoft.XMLHTTP")
.Open "GET", TargetURL, False
.setRequestHeader actionType, actionWord
.setRequestHeader "Authorization", "Basic <Encoded 64>"
.send
If .Status = 200 Then
Sheets(6).Cells(Count, 10).Value = "Connected"
Debug.Print .responseText
MsgBox .responseText
Set JSON = ParseJson(.responseText)
Sheets(6).Cells(Count, 8).Value = .responseText
If Sheets(6).Cells(Count, 8).Value = ("resolve") Then
MsgBox ("Exists")
Else
MsgBox ("Fail")
End If
Else
MsgBox .Status & ": " & .StatusText
End If
End With
推荐答案
解析JSON响应:
以下内容从文件中读取结果json并解析出每个解析.它使用JSONConverter.bas
.请注意,我已经在我的python脚本中提取了结果" JSON集合,这与您通过Set json = JsonConverter.ParseJson(.responseText)("results")
对转换后的JSON字符串进行json("results")
一样.
Parsing the JSON response:
The following reads in the results json from a file and parses out each resolve. It uses JSONConverter.bas
. Note I have extracted the"results" JSON collection in my python script which would be the same as you doing json("results")
on the converted JSON string via Set json = JsonConverter.ParseJson(.responseText)("results")
.
在您的项目中添加JSONConverter.bas
后,您需要进入工具>参考>添加对Microsoft Scripting Runtime
After adding JSONConverter.bas
to your project you need to go tools > references > Add reference to Microsoft Scripting Runtime
Option Explicit
Public Sub GetJSONExtract()
Dim fso As Object, jsonFile As Object, jsonText As String, json As Object, item As Object
Set fso = CreateObject("Scripting.FileSystemObject")
Set jsonFile = fso.OpenTextFile("C:\Users\User\Desktop\Sample.json")
jsonText = jsonFile.ReadAll
Set json = JsonConverter.ParseJson(jsonText) '<== Using results collection
'Set json = JsonConverter.ParseJson(.responseText)("results") '<== In your vba XMLHTTP version
For Each item In json
Debug.Print item("resolve")
Next
End Sub
就像您在解析我所显示的JSON一样.
As you were after how to parse the JSON that it was I have shown.
我实际上使用了下面显示的python脚本;改编自API文档.然后,我添加了一些代码,将响应写到JSON文件中,以便以后导入.使用Anaconda/Spyder运行.
I actually used the python script shown below; adapted from the API documentation. I then added in a bit of code to write the response out to a JSON file for later import. Run using Anaconda/Spyder.
import requests
import json
username = 'xxx'
key = 'yyy'
auth = (username, key)
base_url = 'https://api.passivetotal.org'
def passivetotal_get(path, query):
url = base_url + path
data = {'query': query}
response = requests.get(url, auth=auth, json=data)
return response.json()
pdns_results = passivetotal_get('/v2/dns/passive', 'passivetotal.org')
for resolve in pdns_results['results']:
print('Found resolution: {}'.format(resolve['resolve']))
with open(r"C:\Users\User\Desktop\Output.json", "w") as text_file:
text_file.write(json.dumps(pdns_results['results']))
这将打印出所有解析.
原始返回的JSON结构如下:
The original returned JSON structure looks like:
返回的对象是词典的集合.您可以通过字典键"resolve"
The object returned is a collection of dictionaries. You access the required value by the dictionary key "resolve"
这篇关于从.responseText提取特定的JSON字段到单个Excel单元格的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!