Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Parse JSON Array

Parse JSON Array

RE: Parse JSON Array

Actually, not easily!

I wrote a longer, more sophisticated version of this code as well, but never posted it to tek-tips as far as I recall. And I managed a head crash on the drive it was on since then, and never bothered to try and recover it

There are a number of json serialiser/deserialiser VBA/VBScript libraries you can google for

or you could look at PowerShell ...

RE: Parse JSON Array

Ok, thanks strongm!


RE: Parse JSON Array


I looked and found this one.


I am trying to parse the below JSON.

It looks like it can do an array but I am having some issues with pulling that data. I have the other JSON data parsing just fine with the below code. Any ideas? Basically I want to pull the *scans* information.


oxmlhttp2.Open "GET", url, False
    oxmlhttp2.setRequestHeader "Authorization", bearer
    Set oJSON = New aspJSON
    'Load JSON string
    jsontxt = oxmlhttp2.responseText
    response.write jsontxt
    if InStr(1, jsontxt, "Barcode not found.") > 0 then
        response.write "<strong>" & oJSON.data("message") & "</strong>"
        response.write "<strong>IMB:</strong> " & oJSON.data("data").item("imb") & "<br>"
        response.write "<strong>Mail Class:</strong> " & oJSON.data("data").item("mail_class") & "<br>"
        response.write "<strong>Mail Shape:</strong> " & oJSON.data("data").item("mail_shape") & "<br>"
        response.write "<strong>Start The Clock Date:</strong> " & oJSON.data("data").item("start_the_clock_date") & "<br>"
        response.write "<strong>Expected Delivery Date:</strong> " & oJSON.data("data").item("expected_delivery_date") & "<br>"
        response.write "<strong>Anticipated Delivery Date:</strong> " & oJSON.data("data").item("anticipated_delivery_date")
    end if 


{"message":null,"data":{"piece_id":"6676bb60-bb4b-11ea-9f8d-f50eb2f71890","mail_class":"Marketing","start_the_clock_facility_city":null,"start_the_clock_facility_zip":null,"mail_shape":"Letter","expected_delivery_date":"2020-07-02","start_the_clock_facility_state":null,"edoc_submitter_crid":"213519","anticipated_delivery_date":"2020-07-03","edoc_job_id":"MWD14375","parent_container_edoc_imcb":[],"scans":[{"scan_date_time":"2020-07-02T19:34:17","scanner_type":"MPE","scan_facility_zip":"17107","mail_phase":"Phase 3c- Destination Sequenced Carrier Sortation","scan_facility_locale_key":"X18801","scan_event_code":"919","handling_event_type":"A","scan_facility_state":"PA","scan_facility_name":"HARRISBURG","machine_name":"DIOSS-016","scan_facility_city":"HARRISBURG"},{"scan_date_time":"2020-07-02T16:23:00","scanner_type":"MPE","scan_facility_zip":"17107","mail_phase":"Phase 3c- Destination Sequenced Carrier Sortation","scan_facility_locale_key":"X18801","scan_event_code":"918","handling_event_type":"A","scan_facility_state":"PA","scan_facility_name":"HARRISBURG","machine_name":"DIOSS-016","scan_facility_city":"HARRISBURG"}],"start_the_clock_facility_locale_key":null,"parent_tray_edoc_imct":[],"start_the_clock_facility_name":null,"start_the_clock_date":"2020-06-29","imb":"0026120083200382963617356814721"}} 


RE: Parse JSON Array

Got this sorted.


for each scan in oJSON.data("data").item("scans")
        set thisscan = oJSON.data("data").item("scans").item(scan)
        response.Write "<strong>Scan Date Time:</strong> " & thisscan.item("scan_date_time")


RE: Parse JSON Array

Actually, now that I've seen your JSOn string, my code would have handled that with a simple (and cleaner)

For Each scan In .data.scans

in the With block

RE: Parse JSON Array

Thanks, I'll take a look at your previous code. Appreciate the help!


RE: Parse JSON Array

My version would look something like (but remember that msscript.ocx is a 32bit control, so you need to make sure you run from 32bit cscript or wscript if you platform is 64bit):


Dim fso 
Dim strText 
Const ForReading=1

Set fso= CreateObject("Scripting.FileSystemObject")
strText = fso.OpenTextFile("G:\Downloads\json.txt", ForReading).ReadAll

Dim scan
Set oJSON = vbsJSON(strText)

wscript.echo "IMB: " & oJSON.data.imb
wscript.echo "Mail Class: " & oJSON.data.mail_class
wscript.echo "Mail Shape: " & oJSON.data.mail_shape
wscript.echo "Start The Clock Date: " & oJSON.data.start_the_clock_date
wscript.echo "Expected Delivery Date: " & oJSON.data.expected_delivery_date
wscript.echo "Anticipated Delivery Date:" & oJSON.data.anticipated_delivery_date

For Each scan In oJSON.data.scans
    wscript.echo "Scan Date Time: " & scan.scan_date_time

Function vbsJSON(JSONString)
    With  CreateObject("MSScriptControl.ScriptControl")
            .Language = "JScript"
            Set vbsJSON = .Eval("(" + JSONString + ")")
    End With
End Function 

RE: Parse JSON Array

Thanks, I will try it out!


Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close