Go Back   Access World Forums > Microsoft Access Reference > Sample Databases

Thread Tools Rating: Thread Rating: 2 votes, 3.00 average. Display Modes
Prev Previous Post   Next Post Next
Old 09-12-2017, 10:59 AM   #1
Gold Supporter
ridders's Avatar
Join Date: Jan 2017
Location: Somerset, UK
Posts: 4,069
Thanks: 66
Thanked 976 Times in 910 Posts
ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough
A JSON parser database

For the past couple of weeks, I've been working on a JSON file parser in Access

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.

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.

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

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

I omitted a couple of important files from the original zipx file.
These have now been added to the updated version.
Attached Images
File Type: png MainForm.PNG (70.2 KB, 1753 views)
File Type: png MainForm1.PNG (77.4 KB, 1226 views)
Attached Files
File Type: zip ExcelPowerQuery.zip (906.3 KB, 192 views)
File Type: zipx JSONParserUPDATED.zipx (1.68 MB, 213 views)

Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left and leave a comment.

New example databases:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by ridders; 09-13-2017 at 07:34 AM. Reason: Updated attachment to include files omitted by mistake
ridders is offline   Reply With Quote
The Following User Says Thank You to ridders For This Useful Post:
jdraw (09-12-2017)

json parser database

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
downloading a JSON file and updating database Cowboy_BeBa Modules & VBA 1 03-23-2016 02:20 AM
Import table to access 2003 database from json file MBMSOFT Visual Basic 1 05-16-2015 02:46 PM
TextBox Parser kLAcK Modules & VBA 6 08-30-2007 02:27 PM
Parser help needed RSW Modules & VBA 11 02-05-2007 08:02 AM
XML Parser juliebme Modules & VBA 4 03-13-2002 10:16 AM

All times are GMT -8. The time now is 10:04 PM.

Microsoft Access Help
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit

Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World