Edit JSON parser code to add function to write JSON file

Background information (Can be skipped if not required)

I am interested in using VBA on JSON files.

Since I am very weak on Java I searched the net for existing code. After testing & discarding several VBA projects, I finally found code that properly parsed the contents of JSONs. (Unfortunately I've forgotten where I found this code. If anybody recognises the code below, please let me know the website and the creator so I can add acknowledgement)

This was working great - except for one minor issue that I soon solved (Footnote: This issue is because the code failed to return certain values. It would return them as [object]. The code would then retry fetching the data through use of an alternative function 'pfnobjGetObjectProperty'. This did not work for me. Through trial and error, I wrote a replacement function 'pfnvarGetObjectProperty_EXPERIMENTAL'. My new function successfully returns these types of values as a 2d array)

Now the reason I am opening this thread.



The Thread Question

Using this modified JSON parsing code, I can read the contents of multiple JSON files into a global array. But I want to write some changes I have made in this array back to the JSON file(s) and I have no function for this.

I need a function that will replace the entire contents of a single JSON file with my updated data for that file.

The input data for this desired function will be in the same format/layout as what I am using in avarDbValues in the function 'fnavarReadMultipleJSONsIntoAnArray'

(I plan to extract the edited JSON array from the global array and then pass this to the 'write JSON' function.)

Please Login or Register  to view this content.

Originally Posted by Kyle123

You’d be better off using this: https://github.com/VBA-tools/-JSON/b...nConverter.bas

Sounds promising. The link gives me a 404 error though?

Originally Posted by Kyle123

The library you’re using requires you to write JavaScript

I never learnt JavaScript so I didn't realise that this library was using JS! I just tried different VBA code on the web until I found one that returned the contents of JSON files as an array of fields and values.

Originally Posted by Kyle123

(note that this isn’t Java - they’re completely different programming languages despite the name)

Heh. That was one of the first things I was taught when I tried to learn Java 2 years ago! (Java Good, JavaScript Bad! Tried to ride on the success of Java by using similar name )

Originally Posted by Kyle123

Dunno what happened to the link, try this: https://github.com/VBA-tools/VBA-JSON

Thanks Kyle. I will see if I can integrate this with my project near the end of this week and see how it goes.

So I downloaded the module, imported into the project, enabled the reference library & edited the project to use the functions in this module.

Like the previous code I was using, this new code also runs into problems when trying to read certain JSON values. To be specific - If the datatype of the returned value is a non-scalar value such as an array or a dictionary/collection, the code runs into an error. (The previous code returned a useless string of "[object]". This one triggers a 450 error.)

On the previous code I added code that managed to work around this read issue. Relevant extract below:

Please Login or Register  to view this content.

On the JsonConverter code I am not finding it so easy. I edited the main code to this:

Please Login or Register  to view this content.

I can convert these values to a 1d array using the function below:

Please Login or Register  to view this content.

But if the value I am trying to read from the JSON is in the format of a 2d array (Well a Dictionary if you like. The value is a 2 column 2d array of (sub-)fields and their associated (sub-)values) then I cant read it.

I am not an expert on JSONs but I'm puzzled that none of the VBA JSON code I have tried supports reading non-scalar values from JSONs?

What scares me is that if the code can't handle reading these values then it wont support writing these either.

Originally Posted by Kyle123

I think we need to see some sample JSON that doesn’t parse correctly

Fair enough. I have placed some JSON contents into the code below for testing. (All confidential information removed. Most of the pairs removed to speed up testing.)

Please Login or Register  to view this content.

Well it will do, you need to use Set to assign objects, you’re also trying to assign hierarchical data to a flat array, that’s not going to work unless you walk the tree - you’ve got nested objects in arrays without keys.

You’re working with dictionaries of dictionaries, on possible multiple levels - you can’t represent that in a flat array, you’d end up with multiple keys per value and in the case of an array in the json a single key with multiple nested objects.

Why don’t you just work with the data in the dictionary that’s returned, there’s no reason you can’t have a dictionary of dictionaries where the file path is the key and the value is the parsed object. That way you’d know where to write the files back to

Thanks Kyle. I will look into your suggestion. It will be difficult though since I am used to arrays and not Dictionaries. I have tried-and-tested functions for working with (editing, filtering & sorting) arrays which I don't have for working with Dictionaries - I will have to create equivalents for Dictionaries which will mean starting from square one.