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

 
Reply
 
Thread Tools Rating: Thread Rating: 2 votes, 3.00 average. Display Modes
Old 09-12-2017, 10:59 AM   #1
ridders
AWF VIP
Gold Supporter
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 2,893
Thanks: 52
Thanked 680 Times in 628 Posts
ridders has a spectacular aura about ridders has a spectacular aura about
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
https://www.access-programmers.co.uk...highlight=JSON

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.
Attached Images
File Type: png MainForm.PNG (70.2 KB, 1220 views)
File Type: png MainForm1.PNG (77.4 KB, 753 views)
Attached Files
File Type: zip ExcelPowerQuery.zip (906.3 KB, 106 views)
File Type: zipx JSONParserUPDATED.zipx (1.68 MB, 109 views)

__________________
Colin
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.
, Tapatalk


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

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)
Old 09-12-2017, 12:27 PM   #2
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 10,865
Thanks: 41
Thanked 1,735 Times in 1,686 Posts
jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice
Re: A JSON parser database

Thank you Colin. You have done a lot of work to get this to a readable, usable and documented database. I'm sure it will be helpful to many. Congrats.

Update: I am getting an invalid attachment from the forum when trying to open the zipx???
__________________

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 jdraw; 09-12-2017 at 01:20 PM.
jdraw is offline   Reply With Quote
Old 09-12-2017, 03:50 PM   #3
ridders
AWF VIP
Gold Supporter
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 2,893
Thanks: 52
Thanked 680 Times in 628 Posts
ridders has a spectacular aura about ridders has a spectacular aura about
Re: A JSON parser database

Hi Jack

Not sure if you realised but I updated the zipx file a couple of hours ago.
Anyway, I just checked downloading & opening the updated zipx file & it worked fine for me.
Is the issue that its zipx rather than zip?

__________________
Colin
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.
, Tapatalk


If this answer has helped, please click the Thanks button and/or click the 'reputation' scales symbol on the left and leave a comment. Cheers!
ridders is offline   Reply With Quote
Old 09-13-2017, 03:40 AM   #4
ridders
AWF VIP
Gold Supporter
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 2,893
Thanks: 52
Thanked 680 Times in 628 Posts
ridders has a spectacular aura about ridders has a spectacular aura about
Re: A JSON parser database

The file in question is a zipx file which newer versions of Winzip and other utilities like Winrar and indeed Windows 10 can read.
I used that in order to get below the forum size limit of 2MB

If it's a problem for others, I'll split the file further and repost as standard zip files

For info, the supplied file products.json (item 23) was unintentionally complicated. Attached is a simpler version which is straightforward to parse

I've zipped it as the site won't accept .json files
Attached Files
File Type: zip Products.zip (401 Bytes, 76 views)
__________________
Colin
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.
, Tapatalk


If this answer has helped, please click the Thanks button and/or click the 'reputation' scales symbol on the left and leave a comment. Cheers!
ridders is offline   Reply With Quote
Old 09-13-2017, 04:24 AM   #5
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 10,865
Thanks: 41
Thanked 1,735 Times in 1,686 Posts
jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice
Re: A JSON parser database

Colin,

I have winzip9 installed and running. It will not open zipx. I did some googling and found that WinRar will open the zipx--which I did.
I did not see an option in Windows 10 (I have WIN10 HOME) to open the Zipx.

I will download again since you have updated the zipx since I retrieved a copy. (DONE)
__________________

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 jdraw; 09-13-2017 at 04:40 AM.
jdraw is offline   Reply With Quote
Old 09-13-2017, 04:47 AM   #6
ridders
AWF VIP
Gold Supporter
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 2,893
Thanks: 52
Thanked 680 Times in 628 Posts
ridders has a spectacular aura about ridders has a spectacular aura about
Re: A JSON parser database

Hi Jack

Winrar is also very good

I have winzip 14.5 dating from 2010 - that does create/open zipx files as well as zip.

Its currently on version 21 so perhaps we should both upgrade?

I was wrong about Win10 natively opening zipx files but the solution is very simple.
Rename as .zip and you can then open in Explorer!

I only have Win10 Pro so can't tell if it works in Home version
__________________
Colin
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.
, Tapatalk


If this answer has helped, please click the Thanks button and/or click the 'reputation' scales symbol on the left and leave a comment. Cheers!
ridders is offline   Reply With Quote
Old 09-15-2017, 07:36 AM   #7
Gasman
Access newbie
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 1,600
Thanks: 204
Thanked 221 Times in 207 Posts
Gasman will become famous soon enough Gasman will become famous soon enough
Re: A JSON parser database

I could open the zipx file renamed as zip in win10 pro 32.bit.
However I had errors when trying to extract certian files. The folders Backups and Files were created before the error, however I have 7Zip which does the job.

__________________
Access novice. Sometimes trying to give something back.
Access 2007
Gasman is offline   Reply With Quote
Old 09-22-2017, 03:25 AM   #8
ridders
AWF VIP
Gold Supporter
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 2,893
Thanks: 52
Thanked 680 Times in 628 Posts
ridders has a spectacular aura about ridders has a spectacular aura about
Re: Using/parsing JSON with vba

For anyone who is interested, I've done some more work on my JSON parser database
I would be very interested in any feedback on this (see below)

Version 3.1 of the parser includes the following changes:
a) updated code so it should now work in Access 2007
b) new analyser feature which determines the field names / datatypes & field size then creates the table automatically
For example:





If the JSON file contains subarrays, these are also identified by the analyser & recommended action for splitting the data into several tables is provided:



Next I'm going to write code to automatically create multiple tables based on the analysis.

Finally I hope to automate the process of creating the transform function, again based on the analysis

I would be VERY grateful if others would be willing to test the new version and provide feedback.

Unfortunately the files are now too big to upload to the forum.
However if anyone is willing to do so, please email me using the link below or send me a private message.
I will then provide you with a link so you can download the latest version.

See attached PDF file (zipped) which gives details about using the latest version
Attached Images
File Type: png JSONAnalysis.PNG (78.5 KB, 81 views)
File Type: png JSONAnalysis2.PNG (83.6 KB, 88 views)
File Type: png TableDesign.PNG (24.6 KB, 85 views)
Attached Files
File Type: zip Using The JSON Parser.zip (900.3 KB, 85 views)
__________________
Colin
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.
, Tapatalk


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

Last edited by ridders; 09-22-2017 at 07:39 AM.
ridders is offline   Reply With Quote
Old 11-12-2017, 02:15 PM   #9
ridders
AWF VIP
Gold Supporter
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 2,893
Thanks: 52
Thanked 680 Times in 628 Posts
ridders has a spectacular aura about ridders has a spectacular aura about
Re: A JSON parser database

For anyone who has been following this, I’ve just released an updated version (4.05) of the JSON Parser database to my website

Features include:
• Fully automated analysis including handling of subarrays (up to 2 levels deep)
• Fully automated creation of tables & transform functions based on file analysis
• Almost 90 sample JSON files (63 fully analysed / 26 more partly analysed)

This is intended to be the final pre-release version.
I'm once again asking for forum users who are willing to test it in return for a free copy of the final version …

Its too big to post here but is available at a hidden page on my website.

If you are willing and able to get involved, please email me using the link below in the signature line. I will then send you the download link

###############################

I'm attaching a zipped copy of the user guide.
Even that is 2.2MB which is larger than the forum limit.
I've split it into 2 parts to get around that limit and renamed one part - also to fit forum guidelines.

Instructions for unzipping:
1. Download both parts :
- JSON Parser User Guide v405.zipx & JSON Parser User Guide v405.zx01.zip
2. REMOVE the added .zip suffix from the larger file so its renamed JSON Parser User Guide v405.zx01
3. Select the smaller file in the usual way & unzip it into a selected folder
It SHOULD recombine with the other part ... hopefully!
Attached Files
File Type: zip JSON Parser User Guide v405.zip (848.0 KB, 37 views)
File Type: zip JSON Parser User Guide v405.z01.zip (1.39 MB, 44 views)
__________________
Colin
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.
, Tapatalk


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

Last edited by ridders; 11-12-2017 at 03:18 PM.
ridders is offline   Reply With Quote
The Following User Says Thank You to ridders For This Useful Post:
MrHans (11-15-2017)
Old 12-08-2017, 02:33 PM   #10
ridders
AWF VIP
Gold Supporter
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 2,893
Thanks: 52
Thanked 680 Times in 628 Posts
ridders has a spectacular aura about ridders has a spectacular aura about
Re: A JSON parser database

Just to say the release version (4.20) of my JSON Parser database has just been uploaded to my website

It has been renamed as JSON Analyse & Transform for Access (JATFA) to better explain what the app does.

It makes importing and handling JSON files in Access both straightforward and a largely automated task.

Features include:
a) Fully automated analysis of JSON files including handling of subarrays
b) Fully automated creation of tables and transform functions based on file analysis.
c) Import JSON data into normalised Access tables based on file analysis
d) Over 90 sample JSON files are supplied as examples

If anyone is interested, this is available at a 50% discount for Dec/Jan (discount code WIN2017)

As its intended mainly for developers, the app is supplied as an ACCDB file with full program code

However, as promised, anyone who assisted me in testing earlier versions of this application is entitled to a free copy.

If this applies to you, please email me using the link below & I will send you the download link & a license key (needed for installation)

NOTE: earlier versions of the database will still be available at no cost from the website.
Useful for evaluation for anyone who wants to try the app before committing themselves

__________________
Colin
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.
, Tapatalk


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

Last edited by ridders; 12-09-2017 at 06:59 AM.
ridders is offline   Reply With Quote
Reply

Tags
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 05:17 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


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