Parse JSON with Excel VBA

[Ferdi24]

Hi Experts,

I'm trying to parse a JSON string in Excel VBA into an array so that I can write it in table-format into a sheet.

Example: I have this JSON string in cell A1 "rows":[["20120604", "ABC", "89"],["20120604", "BCD", "120"],["20120604", "CDE","239"]]

The final result in my sheet should look like this:

20120604 | ABC | 89
20120604 | BCD | 20
20120604 | CDE | 239

The format of the content inbetween the [ ] could be anything.

Example 1:
"rows":[["20120604","122"],["20120604","239"],["20120604","150"]]
Example2:
"rows":[["ABC","34"],["BCD","111"],["CDE","459"]]
Example3:
"rows":[["20120604", "ABC", "89"],["20120604", "BCD", "120"],["20120604", "CDE","239"]]
Example4:
"rows":[["XYZ", "ABC", "89","15"],["WXY", "BCD", "120","11"],["VWX", "CDE","239","19"]]

Does anyone have any ideas and would be willing to share his/her wisdom ith me?
Thanks a lot for reading

 

[Flash2605]

Hi All,

Thank you very much for your detailed information, especially Juan.

I tried to get this into my Excel sheet. Unfortunately without success.

I need to get this into an table:

{"companyAverageLastWeek":7.5837} OR {"ratings":[{"score":"10","userName":"Ed Test","from":"006\/114745","ipAddr":"XX.XX.XX.XX","comment":"Simply very good hosting provider, good on price, good on support, good on billing and sales.","ticket":"https:\/\/otrs.\/otrs\/index.pl?Action=AgentTicketZoom;TicketID=72098","created":1369231853},{"score":"10","userName":"Kat Lewis","from":"003\/647347","ipAddr":"XX.XX.XX.XX","comment":"Vielen Dank.","ticket":"https:\/\/otrs.\/otrs\/index.pl?Action=AgentTicketZoom;TicketID=72016","created":1369231611}

<tbody>
</tbody>

I really appreciate your Help.

Thanks in advance.

Chris

 

[dbooksta]

Juan's sample doesn't appear to work: You can't access jsonObj("rows") if jsonObj is a Dictionary (which it is) in VBA for Excel.

You can do something like

For Each v In jsonObj.keys
  ws.Cells(currentRow, 1).Value = v
  ws.Cells(currentRow, 2).Value = jsonObj.Item(v)

But it doesn't take long to discover that this JSON parser library doesn't support nested JSON!

If someone comes across a parser that does I'd be very appreciative!

 

[drgs]

Here is an example of the JSON that I would like to parse. This one contains 4 rows and 19 columns.

{"schedules":[{"summary":"Sign in","executedOn":"10/Oct/12 1:50 PM","cycleName":"asdf","cycleID":15,"label":"1, 2, 3, 4, 5","issueId":123,"versionName":"asdf","issueID":123,"defects":[{"key":"124","status":"Closed","summary":"Title"},{"key":"asdf","status":"Closed","summary":"asdfasdf"}],"executedByDisplay":"Name of person","executionStatus":"2","htmlComment":"asdfasd","projectID":"asdf","executedBy":"asdasg","component":"","versionID":"adasd","issueKey":"asdf","scheduleID":73,"comment":"adsfasdf"},{"summary":"asdf","executedOn":"10/Oct/12 1:17 PM","cycleName":"asdf","cycleID":15,"label":"1, 2, 3, 4, 5, 6, 7, 89, 5, 34","issueId":10012,"versionName":"sdf","issueID":10012,"defects":[{"key":"asdf","asdf":"asdf","summary":"asdf"},{"key":"asdf","status":"Closed","summary":"asdf"}],"executedByDisplay":"asdf","executionStatus":"2","htmlComment":"asdf","projectID":10002,"executedBy":"asdf","component":"","versionID":10001,"issueKey":"Edf","scheduleID":18,"comment":"asdf"},{"summary":"asdf","executedOn":"10/Oct/12 1:20 PM","cycleName":"asdf","cycleID":15,"label":"1, 2","issueId":10011,"versionName":"asdf","issueID":10011,"defects":[{"key":"asdf","status":"Closed","summary":"asdf"},{"key":"asdf","status":"Closed","summary":"asdf - asdf"}],"executedByDisplay":"asdf","executionStatus":"2","htmlComment":"asdf","projectID":10002,"executedBy":"asdf","component":"","versionID":10001,"issueKey":"asdf","scheduleID":17,"comment":"asdf"},{"summary":"asdfasdf","executedOn":"10/Oct/12 1:26 PM","cycleName":"asdf","cycleID":15,"label":"1,2","issueId":10010,"versionName":"asdf","issueID":10010,"defects":[{"key":"asdf","status":"Closed","summary":"asdfa"},{"key":"asdf","status":"Closed","summary":"asdf"}],"executedByDisplay":"asdfasf","executionStatus":"2","htmlComment":"asdfafd","projectID":10002,"executedBy":"asdf","component":"","versionID":10001,"issueKey":"afgaf","scheduleID":16,"comment":"asdf"}]}

Any ideas would be greatly appreciated!

You have some repeating keys (issueId / issueID) with the same value 123
Keys in VBA collection object are not case-sensitive, so if you don't need two cases of issueID this will work:

Public Function JSONDecode(ByVal strJSON As String) As Object    Dim objMSScriptControl As Object
    
    Set objMSScriptControl = CreateObject("MSScriptControl.ScriptControl")
    objMSScriptControl.Language = "JScript"
    On Error Resume Next
    Set JSONDecode = objMSScriptControl.Eval("(" + strJSON + ")")
    On Error GoTo 0
End Function

 

[louwa]

I get the same error, did you find any solution?

Hi Juan,

I am really glad to have found this thread, as I am trying to do the exact same thing.

However, I am a newbie to Excel VBA, and I can't get it to work.

Following line gives me a "Type missmatch" error:

For Each jsonRow In jsonRows

Doesn't "jsonRow" need to be assigned a value somewhere?

Many thanks,
Johan

 

[billgeo]

I've imported the modules stated in a previous post into my project but how am I to use this? I don't see it as a macro to run. Sorry, I'm new to JSON and now find a need to be able to convert a large JSON file to Excel data. Thanks for any insights!
Bill

 

[billgeo]

Ok, I found the macro on the first page of this thread to call the process but I too am receiving an error in the line;

Set jsonRows = jsonObj("rows")

But my error is "Object required"
Any insights?
Thanks
Bill

 

[tprebynski]

Hi Bill,

I also ended up here today looking for the same solution (and I got the same error message). Further searching led me here How to populate Excel from jSon - Desktop Liberation where I found a bunch of helpful stuff for populating Excel from JSON. I don't know if it will work for your needs, but it's worth a look.

Good luck,
Todd

 

[billgeo]

Thanks Todd.
I too had seen that page and other pages on that site. I just could not get my hands around it though. I downloaded that cDataSet.xlsm workbook he states to use but could not make head or tails on what to do with it. I felt I was close with what I had done (above) but just can't get past this error I (and now, we) are seeing.
If you find the resolution let me know and I'll do the same.
Bill

 

[tprebynski]

Yeah, that cDataDet.xlsm is pretty overwhelming. I did get it to work for the JSON I am pulling from an API. Pulling from a file appears similar, so this might work for you. Here is what I think you should try (I customized it for pulling from a file, rather than from an API URL like I did).

First, you need to add an item to the "RestLibrary" (even though you aren't actually pulling from a REST API). The "url" doesn't even need to be valid if you are never going to execute it. The important bit is what you put in for "results" (replacing the SOMETHING below). This will tell Excel where in the JSON structure it should start looking. What you put in will depend on how your JSON is structured. If you can post a sample of your JSON, I might be able to help pinpoint what you need.

''
'' Add this to the "createRestLibrary" Function in the "restLibrary" Module in cDataset.xlsm 
''
	With .add("Load JSON File")
        .add "restType", erSingleQuery
        .add "url", "http://somehere.com/someURL?someparameter="
        .add "results", "SOMETHING"
        .add "treeSearch", True
        .add "ignore", vbNullString
    End With

Next, create a sheet in the workbook to place the resulting table. In the top row, fill in the columns with the names of the values you want to pull from the JSON. I used the info on this page JSON arrays of objects - Desktop Liberation to figure out what to name my columns, and to figure out what to use for the "results" in the restLibrary entry above.

Then, you can use the following code to load the JSON file into a String, and then pass the String to be processed to populate the sheet.

Sub JSONTest()
''
'' Load JSON file into a String
''
    Dim testString As String


    With CreateObject("ADODB.Stream")
        .Open
        .Charset = "UTF-8"
        .LoadFromFile ActiveWorkbook.Path & "\\test.json"
        testString = .ReadText(-1)
        .Close
    End With

''
'' Pass the JSON String "testString" to be processed and populate Workbook Sheet "Sheet1", using the RestLibrary called "Load JSON File"
''
    restQuery("Sheet1", "Load JSON File", , , , , , , , , True, testString).tearDown

End Sub

I hope this helps.
-Todd

 

[billgeo]

Awesome Todd! I'll give this a go a bit later today!!