For the past couple of weeks, I've been working on a JSON file parser
This is in response to a recent thread started by jdraw
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
does NOT provide any easy method of importing JSON
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
The program also makes use of JSON parser code
available from https://github.com/VBA-tools/VBA-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.
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!
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
- Database JSONParser.accdb
used by the program
- A PDF help file
explaining how to use the program
- Over 30 JSON
- 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
I found the Mockaroo
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
I omitted a couple of important files from the original zipx file.
These have now been added to the updated version.