Using VBA to convert JSON, with multi-level dictionaries

oitbc

New Member
Joined
Mar 11, 2019
Messages
12
Hi All,

I am trying to use the NVD database JSON files (available to download from https://nvd.nist.gov/vuln/data-feeds - ) so I can construct a sheet of all the vulnerailities and then I can sort them in Excel. Yes, I realise that NVD does offer a search function so I could limit it to Oracle or SAP, but then I have to go into each vulnerability to find out the score and the attack paths etc. The JSON file contains it all (I am using the recent file)

I found https://codingislove.com/excel-json/ and used their .bas file and turned on the ms scripting flag and had some success. I was able to pull out the ID, the explanation and the time of submisison and update. Then I hit an issue. Its to do with the fact that I need to check the vendor name and sometimes vendor name is there and sometimes it isnt. So I did some more research and found https://stackoverflow.com/questions/21936044/checking-if-a-nested-dictionary-key-exists-in-vba which showed me how to handle nested .exists statements, except I can't get it to work for this particular one. It keeps erroring with Error 05. Essentially it works for the first 3 entries as they have a vendor_name, but item 4 does not, although it has a vendor_data which is the level above. I've tried numerous if checks with a .exists but it just won't seem to take.

Code:
Sub getJSON()

Dim FSO As New FileSystemObject
Dim JsonTS As TextStream

Set JsonTS = FSO.OpenTextFile("C:/Users/pt/Documents/ExcelJSON/test.json", ForReading)
JsonText = JsonTS.ReadAll
JsonTS.Close
    
Set Json = ParseJson(JsonText)

i = 1

For Each Item In Json("CVE_Items")

Worksheets("Sheet1").Cells(i, 1).Value = Item("cve")("CVE_data_meta")("ID")
'Worksheets("Sheet1").Cells(i, 2).Value = Item("cve")("affects")("vendor")("vendor_data")(1)("vendor_name")  <--- this errors if vendor_name does not exist, so I tried an if

'MsgBox TypeName(Item("cve")("affects")("vendor")("vendor_data")(1)("vendor_name")) <---- also errors if vendor_name does not exist, otherwise returns vendor name

'If Not Item("cve")("affects")("vendor")("vendor_data")(1).Exists("vendor_name") Then <--- check to see if it doesnt exist doesnt work

If Item("cve")("affects")("vendor")("vendor_data").Exists("vendor_name") Then  ' <----works for 1st 3 items in list as they have vendor_names then it crashes as 4th does not have vendor name
Worksheets("Sheet1").Cells(i, 2).Value = Item("cve")("affects")("vendor")("vendor_data")(1)("vendor_name")
End If
Worksheets("Sheet1").Cells(i, 3).Value = Item("cve")("description")("description_data")(1)("value") <--- works
Worksheets("Sheet1").Cells(i, 4).Value = Item("publishedDate") <--- works
Worksheets("Sheet1").Cells(i, 5).Value = Item("lastModifiedDate") <--- works

i = i + 1
Next

End Sub

The JSON schema I have checked with http://json.parser.online.fr/

one without looks like this

Code:
"cve":{
[LIST]
[*]"data_type":"CVE", 
[*]"data_format":"MITRE", 
[*]"data_version":"4.0", 
[*]"CVE_data_meta":{
[LIST]
[*]"ID":"CVE-2017-3164", 
[*]"ASSIGNER":"cve@mitre.org" 
[/LIST]
}, 
[*]"affects":{
[LIST]
[*]"vendor":{
[LIST]
[*]"vendor_data":[] 
[/LIST]
  
[/LIST]
  
[/LIST]
and a good one looks like this

Code:
"cve":{
[LIST]
[*]"data_type":"CVE", 
[*]"data_format":"MITRE", 
[*]"data_version":"4.0", 
[*]"CVE_data_meta":{
[LIST]
[*]"ID":"CVE-2017-12447", 
[*]"ASSIGNER":"cve@mitre.org" 
[/LIST]
}, 
[*]"affects":{
[LIST]
[*]"vendor":{
[LIST]
[*]"vendor_data":[
[LIST=1]
[*]{
[LIST]
[*]"vendor_name":"gnome", 
[/LIST]
  
[/LIST]
  
[/LIST]
  
[/LIST]
  
[/LIST]


[/CODE]
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,604
Are you still stuck on this? I think the problem is that the vendor_data item exists, but its associated array (collection) is empty, signified by the empty square brackets:

"vendor_data" : [ ]

Here's one way to handle this:

Code:
        If item("cve")("affects")("vendor")("vendor_data").Count > 0 Then
            Worksheets("Sheet1").Cells(i, 2).Value = item("cve")("affects")("vendor")("vendor_data")(1)("vendor_name")
        End If
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,230
Messages
5,594,947
Members
413,953
Latest member
Arthur1471

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top