Using VBA to convert JSON, with multi-level dictionaries

oitbc

New Member
Joined
Mar 11, 2019
Messages
1
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]
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
5,958
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:

Forum statistics

Threads
1,078,461
Messages
5,340,440
Members
399,375
Latest member
alwayssunny

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top