Using/parsing JSON with vba

That was my suggestion.
It runs without issues for me in Excel 2010 and is meant for 2013 as well.
20160 has Get & Transform instead which has additional functionality.

I'll see if I can block out some time later this week to give it a go.

Having looked at a variety of Json-Access articles and some code, I can now see that although there is a structure to json,
there is no data that directly relates to table column, related table as we have in database. What the various converters do
is very dependent on the json string(file) supplied. This is especially true when you have arrays and embedded arrays in the
json.Many json to csv will give a field per every field in every record.
The json to text test in the xlsm example at vba JSON parser example xlsm from
https://github.com/omegastripes/VBA-JSON-parser/releases by omegastripes@yandex.ru is worth
investigating if you need to parse json and have Excel familiarity.

Various json to csv converters work well if the json is fairly simple --no embedded arrays (at least that's what I have found and
I admit to little knowledge of json and Excel).

The jsonToAccess utility (Shiji Pan at https://www.withdata.com/jsontoaccess/download.html) can work well with simple json
with a little manipulation (no embedded arrays) and handles multiple single arrays (that is not embedded). Shiji did respond to my
email request, but I find the material lacks documentation for the average user. There is a cost for the routine ~$50 US. He offers
other converters (for a fee) --I tried the DataFileConverter and found that it did not process the sample json file with embedded
arrays ( a somewhat complex structure).

The regex json parser by omegastripes@yandex.ru at Stack Overflow seems to be the most professional and thorough routine,
but it is not designed to create Access tables. However, I think it is a base that a "programmer" could evolve/adapt.
In my limited tests (and knowledge) it certainly handles embedded arrays etc. and claims the parser to be based on a json standard.

I am still looking for sample data from peskywinnets whose post got me interested in looking at ways to handle webservice
responses in json format with Access and vba.

Ridders and sxschech have offered some other routines/methods.

PS: I have attached the modified json file MultiSquad_JED_Modified_json.txt. My original MultiSquad_JED.json was modified by the supplier of the JsonToAccess utility mentioned earlier in this thread. Both versions of that file passed the json validator/viewer, but neither was successfully processed by the JsonToAccess utility.
Had to change the extension in order to attach it here.

Hi again

Previously when I used Excel to import JSON files, all I was trying to do was get all the data into 1 table - so it wasn't normalised

I've just had another go at converting the two JSON files into normalised data in Excel & which can then be imported direct into Access tables.

a) The original JSON100 was converted using Excel 2010 PowerQuery into 3 'tables' i.e. worksheets

b) The modified MultiSquad JSON was converted using Excel 2016 Get & Transform Data into 3 'tables'

I didn't use any code or other converters
All done in Excel & then imported to Access.
I'm not yet proficient with this but I'm getting there

See the attached files and let me know what you think.

Each zip file contains JSON, XLSX & ACCDB versions

This is in reply to jdraw in another thread.

Hi Jack, Yes I have been following the thread. (As in This Thread) I have a web development use for just such a Class. I export a web page created in Elementor, (a WordPress page builder) and it's in a very complicated looking JSON file. I have hundreds of YouTube videos that I need to add to my website.

I'd like to automatically replace the YouTube links, YouTube video names, description and the like in the exported template and then load the template back into WordPress. There is a JSON Class module for Excel somewhere. A few years back, I did make a feeble attempt at adapting it for use in Access, but couldn't make it work, and gave up. It might be worth another look, as in two heads are better than one, so what might happen if a forum full of heads looked at it?

BTW I'm sort of out of the Access picture for a couple of weeks as I'm looking after me mum, as she's broken her ankle! I still drop in here via "mobile" occasionally!

Sent from my SM-G925F using Tapatalk

Tony,
Hope you mum is recovering well.

Colin,

Just looked at your attachments. Looks very good. Both the 2010 and 2016 Power Query results are impressive.
I wonder if we can find 2 or 3 representative json responses from web service, then document procedures to get the data from same.
website->excel->access

Good stuff.

I found this online JSON formatter website handy:-

https://www.freeformatter.com/json-formatter.html

I've done a couple of videos showing how to parse JSON files using Excel Power Query into a normalised structure ready for import into Access

1. The basics - simple JSON files
2. Intermediate - more complex files e.g. parsing 'multivalued' fields

To follow:
3. Advanced - complex JSON parsing e.g. arrays (probably using Excel 2016 Get & Transform
4. Expert - IF I ever get that good.... ???

After I've edited the videos further, I'll upload to You Tube & provide links here.

In the meantime, if anyone would like to see them, send me a private message with your email & I'll send you a link for the files

Good stuff Colin. I'm sure they will be helpful to people following this thread, and for those searching and finding JSON->Access information and examples. The Power Query approach seems quite viable to get JSON formatted data into Access tables.

I found 2 videos re Power Query by Brian Grant that put the product/Excel add-in into context.
https://www.youtube.com/watch?v=7Vn6uOxcAc0 Intro to Power Query
https://www.youtube.com/watch?v=dTkIgvRR1yg Intro to Handmade queries

and a whole playlist (I have not watched yet)
Power Query Jumpstarter

Still working (intermittently) on these videos and hope to add another soon.
Will then upload to You Tube for anyone who may be interested.

If anyone has some 'interesting' JSON files I can use to test parsing in Excel PowerQuery, please could you upload here.
That's 'interesting' in the sense that they may be a challenge to parse correctly!

In the meantime, just noticed another idea added to the Access user voice forum:
Add Power Query to Access

Please VOTE for that as well

Yet another site to convert JSON files to CSV / XML / YAML etc:
https://codebeautify.org/json-to-csv#

I expect some of you will already be aware of the CodeBeautify site.
This has a huge range of conversion tools some of which are shown in the attached screenshot

I've just returned to this after a couple of weeks doing other things.

The prompt was this thread at Utter Access:
http://www.utteraccess.com/forum/index.php?showtopic=2045170&st=0&gopid=2656810&#entry2656810

I've been working today on my Access JSON parser and have successfully transformed a variety of JSON files & saved in Access tables.

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 has been tristatemixed! (UGH!)

c) Parsed the data using the GitHub parser then used a recordset to append the data to a table
This was straightforward for some JSON files e.g. addresses from Ideal-Postcodes website & FreeGeoIP data
I also found the Mockaroo site brilliant for downloading a variety of mock data of different types

Some files have been tricky including the very simple but definitely odd example submitted by @jleach at UA.
However I've done it using a bit of trickery ....



Here's another example using mock data from Mockaroo






Still working on other files - some of which may need a different approach e.g. use of split function



When I've tried a few more examples, I will upload my importer/parser.
I would be grateful for any other 'interesting' JSON files (or the website & API details to get them) so I can add them to my tests

Each type of file still needs to have its own individual solution.
In reality only worth the effort for files you need to import into Access on a regular basis e.g. address files / currency exchange rates

However, its still my view that for one-off or occasional use files, its quicker & easier to parse into normalised tables using Excel Power Query then import the results to Access

Update!

My Mum's recovering well, and staying with my sister at the moment.

Looking through my JSON notes I found this excellent video by Steve Bishop:-

https://www.youtube.com/watch?v=5qpUFV5Gtb0&feature=youtu.be

Should provide some insight to parsing JSON with VBA ...

This is just to inform anyone who is interested that I have just uploaded my JSON parser database to the sample databases area.
https://www.access-programmers.co.uk/forums/showthread.php?t=295789

It includes over 20 JSON files that I have parsed in Access together with a further 10 for others to try out.

Attached is a zipped PDF file explaining the use of this database

Tony:
Thanks for the link to Steve Bishop's video - I still haven't got around to watching it ....

Thanks again for the reminder Tony
Finally got around to watching Steve Bishop's JSON parsing video (after posting mine to sample databases!)

It was good to know that I've been doing it properly in my own version!

However he only covered simple JSON files with no subarrays

So I'm going to add my own video to explain how to parse JSON subarrays in Access .... as used in some example files in my sample database
Will do so in the next few days

ridders said:

I'm going to add my own video to explain how to.....

Click to expand...

I highly recommend Screen O Matic for doing screen recordings. The free version is adequate for most things, however at around $15 a year I highly recommend signing up for the full version you don't get much extra just a bit longer record time and a few other frills...

I did a YouTube video demo of one of the most recent additions to its functionality here:- https://youtu.be/Xbnx_B1Mik4

This recently added feature allows you to easily edit the audio.

Hi Tony

I currently use BB Flashback Standard from https://www.flashbackrecorder.com.
It now comes in 2 versions : Express (free) & Pro with extra features.

My Standard version has most of the Pro features but not all

I'll have a look at the one you recommended as well

Tony,
I tried the link in your #47 and did not get a video.
I've attached screen shot of where I landed??

I tried it & it took me to my own list of You Tube videos ...
Still at least I know how to get there in one click now!

Haven't uploaded one for over 3 years. Time I did so ...