CODEWITHSUNDEEP
jsonexcelvbaapiweb-scraping
user6502520
user6502520

Reputation: 35

Extracting specific JSON field from .responseText to single excel cell

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.

Thank you! 

 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

Upvotes: 2

Views: 1014

Answers (1)

QHarr
QHarr

Reputation: 84465

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").

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

As you were after how to parse the JSON that it was I have shown.

Additional notes:

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']))

That prints out all the resolves.

The original returned JSON structure looks like:

structure

The object returned is a collection of dictionaries. You access the required value by the dictionary key "resolve"

Upvotes: 2

Related Questions