Krish and IT
MS Access VBA, JSON Import and JSON Export
Recently stumbled upon a task where JSON string was passed between two systems. While giving a solution for that, I also adapted JSON Import and Export for Ms Access.
Import
Somewhere similar to Application.ImportXML, it is now possible to create a table from JSON string. The importXML function allows:
acAppendData => adds new records to the table
acStructureAndData => Creates table and adds data
acStructureOnly => Just creates the table definition.
If you are not familiar with my previous vba_tools post, please do have a look at the post and download the sample Access project where you will find many interesting code including this magic one.
To import JSON string using the vba_tools.dll call following code:
gdll.ImportJson(JsonArrayAsString, “TargetTableName»,acStructureAndData, recreate:=true)
I have only tested with JSON arrays and an actual array will look like this. (sample taken from online)
After executing this code I can see a new table in the navigation pane. (F5 if you can’t see it)
Export
When we say export, we usually mean to export the content out of the host application. By that, either saving locally or sending/pushing to a different application. ExportToJSon can be used to export json into a file as well as it returns the json string as result. So it can be used to “convert table content to json” too.
In addition, unlike Access traditional export methods, ExportToJson takes an SQL command which can be exported to a file.
gdll.ExportToJSON(«select * from tbljsontest where authorize = true;»,»MyJson.Txt»,overwrite:=false,isRawSql:=true)
above code executes the SQL SELECT command and saves the result set as JSON formatted string.
gdll.ExportToJSON(«tbljsontest «,SaveAs:= «MyJson.Txt»,overwrite:=false,isRawSql:=false)
In this method, we have passed a table name/query name to the export function and set isRawSql = false. The export function will then generate SQL statement similar to “SELECT * FROM givenTableName/QueryName;” and perform the JSON Export.
If the SaveAs is empty, no file will be exported but the conversion will still happen and converted string will be returned as result.
results in immediate window.
Saved file content.
that’s all for now. Have a look at my GitHub Project for latest updates and codes. Enjoy coding
Fetching Json file from API to Microsoft Access
I have an API with JSON file that i need to fetch to Microsoft Access. The data has several pages and several counts, but only 500 records can be fetched at time. Can someone please help me to get this data with use of VBA in access? The data is coming from https://api.appfigures.com/v2/reviews? and i have the client key to gain access. Please help
Here is an example of the JSON data:
<
«total»: 16522,
«pages»: 661,
«this_page»: 1,
«reviews»: [
<
«author»: «Thruman Reed»,
«title»: «Great app»,
«review»: «Works great I love it»,
«original_title»: «Great app»,
«original_review»: «Works great I love it»,
«stars»: «5.00»,
«iso»: «ZZ»,
«version»: «4.6.0.0»,
«date»: «2016-01-21T14:21:42»,
«product»: 40149360006,
«weight»: 0,
«id»: «40149360006LKR0oc533z7ah3 aoA4JSeow» ,
«predicted_langs»: [
«en»,
«da»,
«nn»,
«no»
]
>,
<
Have a read through this:
This thread contains the code you will need to talk to the web site. JSON is just how the data is formatted.
In that thread, look at LSM COnsulting’s comment, which has a link to a JSON parser written in VBA.
Holler if you have any questions on that.
the Json object is in a list. Here is an example here\
Hi Jim,
I checked the link you recommended, but it is too complex for me. I was hoping to get more help. Also there are several pages of data with a maximum of 500 records to a page, and i need the ability to iterate through the data. Here is an example API string
ASKER CERTIFIED SOLUTION
Back in 2014, I created a JSON parser because I wasn’t happy with any of the VB implementations found during a web search. It populates an object with VB-friendly data types (dictionaries and collections) and does a reasonable job converting the dates. It is capable of parsing 800 JSON objects per second on my 1GHz laptop.
When it finishes, the result can be accessed like these two examples:
I wrote an iteration routine that walks through the object(s) and prints the data in a name:value manner. You would still need to retrieve the https result, most likely using the MSXML2 object and then pass the result to the parsing routine. If the VBA parsing routine is your stumbling block, I can post my parsing code.
I am still looking for a solution.
What part of the suggested solutions still evades you? I can post my parsing routine, but that might not be where you are hung up.
I was hoping to get help with an example, i was not able make out the suggestions that was given here
There are two parts to your question. The first is to use the MSXML2 object to retrieve content from the web site. Have you done this? Have you looked at the MSXML2 documentation related to supplying userid/pwd credentials?
You have not asked us many questions on this and have not posted a userid/pwd with which the experts might post some code that works with the web site.
Secondly, you have been pointed to a github project with conversion code. Have you downloaded it and played with it? Do you have any questions about that part of your problem?
========================
If you want an expert to do all of this for you, consider one of the following options:
* Look at the profiles of the top experts in Access, Excel, Office, VB classic topic areas. If they have enabled their Hire Me links, you might achieve your turn-key solution.
* EE just implemented some new problem-solution features. I think that «Live» might be a path worth exploring
* Do some leg work. The participating experts have helped you with our comments. There is some expectation that you will do some of the work, especially the testing. If you do not have the experience to do your part, please do one of the following:
A JSON parser database (1 Viewer)
isladogs
MVP / VIP
For the past couple of weeks, I’ve been working on a JSON file parser in Access
As many of you will already know, JSON files are increasingly used as the default file format for downloading data from online sources.
This is because the JSON format is very versatile and efficient allowing rapid data transfer.
However, the data then needs to be processed (parsed) before it can be used in Access.
Unfortunately, Access does NOT provide any easy method of importing JSON files.
By contrast, JSON data can be imported and parsed using Excel Power Query add-in (2010/2013) or the built-in Get & Transform feature in Excel 2016
This Access JSON Parser has been created to simplify the reading and parsing of JSON files into Access so the data can then be imported into normalised Access tables.
The program also makes use of JSON parser code available from https://github.com/VBA-tools/VBA-JSON
Alternative JSON parsers are available from other sources.
Example of simple JSON file:
Here is a more complex file:
The attached zip file includes over 30 JSON files varying in structure from very simple to highly complex
As each file is ‘unique’, they also have to be processed individually though parts of the process are very similar.
I have created a number of transform functions to handle over 20 of these files as exemplars.
This leaves 10 more for anyone else to use as practice files if interested.
NOTE:
I have also included a couple of ‘invalid’ JSON files.
The database includes code to fix many common JSON errors
After working through these, you should then be able to see how to parse & import any JSON file of your choice
In each case, I’ve done the following:
a) used HTTP GET to download the data to a JSON file — that’s the easy bit!
b) used file system object code to ‘read’ the file into memory & display it in Access
This is usually easy but I’ve had to allow for the tristate value to ‘read’ it correctly — most are TristateTrue (unicode) but a few are TristateFalse (ASCII) and one was TristateMixed! (UGH! )
c) Parsed the data using the GitHub parser then used a recordset to append the data to one or more tables
This was straightforward for some JSON files but quite tricky for some other files I tried
Links are also provided to various external tools for anyone who wants to try them
I’ve automated a significant portion of this process
The attached JSONParser.zipx file includes:
— Database JSONParser.accdb
— Images used by the program
— A PDF help file explaining how to use the program
— Over 30 JSON files
— a template text file used to create new transform functions
Please install this database to a trusted location to avoid issues the first time you run this
A short video file (18 sec) showing how to use Excel Power Query is also attached & referenced by the database
Due to forum file size limits, I had to leave it out of the JSONParserUPDATED.zipx file
Please copy it to the Videos subfolder for the program
NOTE:
I found the Mockaroo site https://www.mockaroo.com/ very helpful for creating a variety of JSON datafiles of different types based on mock data
I hope you find this JSON parser database useful.
It should work in both 32-bit & 64-bit Access
Please contact me by email (see link below) if you have any questions or to report any bugs you find with the database.
Any suggestions for improvements or additional features will also be considered for a future version
UPDATE:
I omitted a couple of important files from the original zipx file.
These have now been added to the updated version.
Using/parsing JSON with vba (1 Viewer)
jdraw
Super Moderator
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?
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
MVP / VIP
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
CJ_London
Super Moderator
jdraw
Super Moderator
I just voted. Thanks for the link and info.
isladogs
MVP / VIP
As mentioned in another thread, by pure chance I’ve just found a useful link about transferring data between Excel & JSON & back again.
I expect some of it is also applicable to Access
jdraw
Super Moderator
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:
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)
‘==========================
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?? )