Using/parsing JSON with vba (1 Viewer)

jdraw

Super Moderator
Staff member
Local time
Today, 13:02
Joined
Jan 23, 2006
Messages
13,409
Does anyone have an example showing the use of Access vba to request data from a website and parse the response that is formatted as JSON? Have you got a working solution that you can share?

I have seen this reference on github vba-json
and I am aware of the Steve Bishop youtube tutorials on the subject 34---37.

Just trying to find out who may be using this combination and willing to assist others on the forum.
Also trying to determine if this is a subject that others have real interest in.

Thanks in advance.
 

isladogs

CID VIP
Local time
Today, 18:02
Joined
Jan 14, 2017
Messages
14,038
Allowing Access & JSON to work together with a new dll (MSJSON.dll) has been suggested in the Access user voice forum

Give us a new dll - MSJSON.dll

Currently to work with XML objects from API calls we have an object model from referencing MSXML6.dll that make iterating through the response easy. With JSON we can only get strings of data that we have interpret. If you created MSJSON.dll for us we could manipulate JSON data as easily as XML and make life easier for developers dealing with API calls.

Might be worth adding your vote to this idea & perhaps MS will take it up

https://access.uservoice.com/forums/319956-access-desktop-application/suggestions/18917560-give-us-a-new-dll-msjson-dll
 

CJ_London

Super Moderator
Staff member
Local time
Today, 18:02
Joined
Feb 19, 2013
Messages
12,613
Also trying to determine if this is a subject that others have real interest in.
my impression is that JSON seems to be coming more popular as an output from websites, so think it is likely to receive more interest as time goes on. To date, it has not been an issue for me, but I'm starting to get questions around importing JSON files so I have a watching interest rather than any practical solutions at this time.
 

jdraw

Super Moderator
Staff member
Local time
Today, 13:02
Joined
Jan 23, 2006
Messages
13,409
@Colin,

I just voted. Thanks for the link and info.
 

jdraw

Super Moderator
Staff member
Local time
Today, 13:02
Joined
Jan 23, 2006
Messages
13,409
Colin,
I did some searching last night and found an "Excel Liberation" area that I believe is
hosted/answered/moderated by BMcpher. He (Bruce) seemed to be the guru, but it was less than readable to me anyway[I do acknowledge my not having familiarity with javascript]. A lot of it involved script control and javascript. I don't know javascript and did not find examples that were easy to read --I have to do some research and learning obviously. I saw reference to cjObject?? and a few others (jsObj) I didn't recognize.

It seems to me, and I admit to be extremely novice re JSON and javascript, that you can not easily see/find/retrieve the "field names" in a json formatted string. It seems strange that there are so few examples and/or tutorials that show accessing a website/service, submitting a request, getting a json response and being able to easily understand the results of functions/methods such as json.parse or json deserialize.

I'm sure others, using Access and vba, have encountered JSON and have found solutions. I have seen many references to MS Script Control and using javascript with vba. It's difficult to just find more about javascript functions and a library that can be referenced with Access that gives accessibility to "proven javascript" methods/functions like serialize, parse, deserialize. Seem it would make an excellent set of tutorials/videos, if only we had the interest to someone familiar with all these pieces.
And, restating that I am not familiar with javascript or json, there may be technical roadbloacks to all of this of which I am unaware. I don't mean to make this trivial --it may not be practical/feasible.

Similarly, if there is a vote on UserVoice for a dll to "handle json from vba", it would seem others -familiar with vb.net or C# - may already have built such an animal that could be referenced and used with Access vba (and other office products).


Here are a couple of links I found in this general area:

https://blogs.msdn.microsoft.com/of...does-javascript-a-vba-developers-perspective/

https://stackoverflow.com/questions/30510570/parsing-json-feed-automatically-into-ms-access

https://www.mrexcel.com/forum/excel-questions/640780-parse-json-excel-vba.html

https://developers.google.com/maps/documentation/directions/web-service-best-practices#ParsingJSON

Also, I did find these 2 sample routines:

The first showing a javascript function and usage.( I adjusted this to not use the excel specifics)

Code:
Sub JavaScript_in_VBA()
    'Go to Menu -> Tool -> References -> Microsoft Script Control 1.0 & Enable by Clicking it
    Dim jsObj As New ScriptControl
    Dim InputValue1 As Integer, InputValue2 As Integer, Result As Integer
 
    'Get Sample Parameters
    'InputValue1 = ThisWorkbook.Sheets(1).Cells(1, 1)
    'InputValue2 = ThisWorkbook.Sheets(1).Cells(1, 2)
    InputValue1 = InputBox("Enter first number", , 1)
    InputValue2 = InputBox("Enter second number", , 2)
 
    'Define Scripting Language
    jsObj.Language = "JScript"
    With jsObj
 
        'Add JavaScript Code to Script Control Object
        .AddCode "function xProduct(a,b) {return (a*b);}"
 
        'Execute Added Script & Get result
        Result = .Run("xProduct", InputValue1, InputValue2)
       ' Result = .Eval("xProduct (1,2)")
 Debug.Print Result
 MsgBox Result
    End With
    
    'ThisWorkbook.Sheets(1).Cells(1, 3) = Result
End Sub
'==========================
This second one uses a specified Google Maps api. It sends a request to the Google service using xmlhttp, and gets a response as a JSON formatted string. Within the vba code, it uses predefined Google javascript functions (getAddressLine, getGeodata, getObjLength) to get information from the json response.

Here is a link to info on getAddressLine (Note:I just found some of this so where it fits in overall context is very unclear. Or whether it really applies --I haven't found anything specific to javascript ===the link I gave suggests it's java??)

Code:
'code below from google groups
'https://groups.google.com/forum/#!searchin/excel-ramblings/vba$20json%7Csort:relevance/excel-ramblings/F1B49-k962c/ynT58WOnlrgJ
'
Sub GeoLocate()


    Dim jsObj As Object
    Dim x As Long
    Dim strJson As String
   
    With CreateObject("msxml2.xmlhttp")
        .Open "GET", "http://maps.googleapis.com/maps/api/geocode/json?address=1600+Amphitheatre+Parkway,+Mountain+View,+CA&sensor=false", False
        .Send
        strJson = .responseText
        Debug.Print strJson
    End With
   
   
    With CreateObject("ScriptControl")
        .Language = "JScript"
        .AddCode "function getAddressLine(json, line, prop) { return json.results[0]['address_components'][line][prop]; }"
        .AddCode "function getGeoData(json, prop) { return json.results[0]['geometry']['location'][prop]; }"
        .AddCode "function getObjLength(json, prop) { return json.results[0][prop].length; }"
       
       
        Set jsObj = .Eval("(" & strJson & ")")
       
        For x = 1 To .Run("getObjLength", jsObj, "address_components") - 1
            Debug.Print .Run("getAddressLine", jsObj, x, "long_name")
            Debug.Print .Run("getAddressLine", jsObj, x, "short_name") 'jdraw
        Next x
   
        Debug.Print "Lat - " & .Run("getGeoData", jsObj, "lat")
        Debug.Print "Lng - " & .Run("getGeoData", jsObj, "lng")
       
    End With


End Sub
 
Last edited:

isladogs

CID VIP
Local time
Today, 18:02
Joined
Jan 14, 2017
Messages
14,038
Hi Jack

Yes its the same site though I haven't had time to look at it properly.
My knowledge of JavaScript and JSON is possibly less than yours but its one of those things I feel I ought to learn.

As always, I will do so when something triggers a need for me to do so.
Recently I incorporated text and voice messaging capability into one of my databases using APIs provided by Twilio. I started a thread on this back in Feb.
Their website is full of example scripts using e.g Ruby, Python, JSON and other things that meant little to me but also some in VBA.

That may be an area that could provide a learning point?

Not sure if its already been listed but this site is also good
https://github.com/VBA-tools/VBA-Web
 

isladogs

CID VIP
Local time
Today, 18:02
Joined
Jan 14, 2017
Messages
14,038
Hi Minty

Excellent - and I think I even understand most of it!

So we can import / export to/from SQL server backend tables and then view the results in the Access FE.

Might even be worth upgrading from SSMS 2014 to SSMS 2016 to try it out!

Now if only MS provide a JSON dll as has been suggested, we could do all of it from the FE itself
 

jdraw

Super Moderator
Staff member
Local time
Today, 13:02
Joined
Jan 23, 2006
Messages
13,409
@Minty/Colin,

I just found this on youtube.

I generated a 100 random record json file and asked for 1 to 3 email addresses and 0 to 4 ipAddresses in order to get a "semi complex" json file with objects and arrays.
I downloaded the product from youtube (30 day trial).

It created a new table, and added the 100 records. It did put the array data into a single field. Not unexpected. But it did leave the json syntax eg "["value",...]" so it would be easy to split those records into a new table.
It processed the 100 records very quickly. It built me a new database and table. However, it was a .mdb (C:\Users\mellon\Documents\SampleJsonToAccess.mdb)

The json file is C:\Users\mellon\Documents\Json100.json, BUT I had to change extension to .txt to attach it (.json not acceptable)

It really shows that something can be done --it's far from perfect, but a start. The website and contact info doesn't seem to work?? I'd like to hear more about it--what is behind the scene. I don't want them to give away the code, but I'd like to know the technology involved(since there is little info available generally). What does "support" mean in real terms?

I have data (attached) (You can pass the json data thru a viewer to see the structure. I use Notepad++ with json plugin.

The json file and the resulting database with table.

The program also saves session , but uses a .jif extension???
 

Attachments

  • SampleJsonToAccess.mdb
    248 KB · Views: 344
  • json100json.txt
    19.9 KB · Views: 268
Last edited:

isladogs

CID VIP
Local time
Today, 18:02
Joined
Jan 14, 2017
Messages
14,038
Hi Jack

Many thanks for the You Tube link - that's a neat addin - pity its not free!
I'm still trying to get my head around all of this but its beginning to look very promising.

I don't know whether you watched the next couple of videos but this one was very interesting:

Import JSON Data Into Excel 2016 Using a Get & Transform Query

OK - its for Excel but well worth following up.
It depends on a feature in Excel 2016 called Get & Transform which can potentially do loads. I think it was added in a recent update.
See 30-feature-updates-to-get-transform-in-excel-2016-and-power-query-add-in

For Excel 2010 & 2013, similar functionality is available using the PowerQuery addin available here: https://www.microsoft.com/en-us/download/confirmation.aspx?id=39379

I took your json file and imported it into Excel 2010 using Import From xml on the PowerQuery menu.
Its even easier using 2016 as you just select Import from JSON

I followed the explanation in the video and within a few seconds had the attached Excel file.
It has several records for each person as I chose to expand the email & IP Address fields into separate records. So 100 records is now 456
Alternatively, you can choose to keep them as the dreaded multivalue fields

From there it was trivial to import the data into Access
Both files attached

The best part was I didn't need to understand anything at all - just do it.
Now if the process can be automated, that's $50 saved

What's really good about this site is teamwork ...

My next job is to watch Steve Bishop's videos
 

Attachments

  • ImportedJSONData.xlsx
    36.7 KB · Views: 164
  • JSONTest.accdb
    500 KB · Views: 254
Last edited:

jdraw

Super Moderator
Staff member
Local time
Today, 13:02
Joined
Jan 23, 2006
Messages
13,409
Colin/Minty,

I'm not a SQLServer user nor Excel. The Excel add-in looks nice -too bad it isn't available for Access.
I modified the database by creating a function to take the multiple values in the Email and IP_Address fields and created new tables.

Here is a new version (new name)of the database with the convert function and a test routine.

Just delete the tables and you can rerun the test routine.
 

Attachments

  • SampleJsonToAccess1.mdb
    360 KB · Views: 315
Last edited:

isladogs

CID VIP
Local time
Today, 18:02
Joined
Jan 14, 2017
Messages
14,038
Colin/Minty,

I'm not a SQLServer user nor Excel. The Excel add-in looks nice -too bad it isn't available for Access.
I modified the database by creating a function to take the multiple values in the Email and IP_Address fields and created new tables.

Here is a new version (same name)of the database with the convert function and a test routine.

Just delete the tables and you can rerun the test routine.

Jack/minty

I use both SQL server and excel though I'm not as capable on either as I am in access. I need to do some more reading and video watching to improve my understanding. However after that i'll see if I can find ways of automating the JSON import into excel then access. It won't be for a week or more however.

In the meantime, I've PM'd Jon asking for XML AND JSON to be added to the allowed file types. Copied you both into that

Had a quick look at the site in your last post. Many of the features they charge for can be done for free using the 2 Excel features I wrote about in my post.
 

isladogs

CID VIP
Local time
Today, 18:02
Joined
Jan 14, 2017
Messages
14,038
Back again

There is an online Json to CSV converter available here https://json-csv.com/

You can do up to 1mb per day free or paid options are available.
Just tried it. Its fast and works well
 

jdraw

Super Moderator
Staff member
Local time
Today, 13:02
Joined
Jan 23, 2006
Messages
13,409
I'm sure that forum participants can resolve this -- but it may not be pretty for a few iterations. If it was real simple, it would be available and used by many. As I see it, if people can put forward some ideas/samples and constructive critique, we can get something working that can be tested and refined.

If we have some c# or vb programmers, they may be able to construct some callable routine to parse json. If such a thing exists, we haven't found a reference to it, and it's on the UserVoice as a possible.

We'll watch this and see if there is interest. It's been a background thing --I'm retired 10 years - but I see json referenced more and more. It got re-triggered here because of a post by Pesky. If he has a need, and it could be solved such that the admitted non-programmer could use it with confidence, it seems a worthwhile effort.

There certainly can be some complexity in json responses from services, but they are usually documented by the service provider.

It seems we all have a little learning to do just to be conversant with the terminology.
 

ashleedawg

"Here for a good time"
Local time
Today, 10:02
Joined
Jun 22, 2017
Messages
154
Correct me if I'm wrong (and I hope I'm wrong) - as I see it, a huge part of the issue is that many JSON don't stick very closely to the "standards; often very slight differences -- which would be fine if each API provided a schema to use as a template when parsing, but's that's not the case (like XML's XSD files).

There are Standards and there are Schema Generators but it seems to be that JSON doesn't provide the same Schema-support that XML does.

This is fine for a person who is only interested in parsing JSON from a sole source, but beyond that can get confusing...

Don't get me wrong -- I'm sure "we'll" get to the bottom of it one way or another!
 

CJ_London

Super Moderator
Staff member
Local time
Today, 18:02
Joined
Feb 19, 2013
Messages
12,613
This is fine for a person who is only interested in parsing JSON from a sole source, but beyond that can get confusing...
So true. It is more akin to a written language interpreting/gathering data 'as it comes' with no real preplanning for an organised construct.

Something like jotting down notes as they occur to you, drawing arrows to link 'this thought with that thought', going back and adding a sort order etc. JSON just provides the piece of paper...

With NoSQL and relating multiple non relational datasets found on the web, I suspect it will always remain so. Ergo any JSON translator should take this into account and work with it rather than trying to force it to conform to a traditional table structure.
 

Users who are viewing this thread

Top Bottom