Identifying strings to ignore

[isladogs]

Hi

This ought to be easy but I can't come up with a solution.

First some background

I've been updating the analysis feature used in my JSON parser database https://www.access-programmers.co.uk/forums/showpost.php?p=1545990&postcount=1

I've used code to extract the field names & datatypes and subarrays for a variety of JSON files.

So for example, using this JSON file :

{"id":"0001","type":"donut","name":"Cake","image":{"url":"images/0001.jpg","width": 200,"height": 200},"thumbnail":{"url":"images/thumbnails/0001.jpg","width": 32,"height": 32}}

gives me this result

It works well in almost all cases BUT a few JSON files are 'badly' designed:

{"base":"GBP","date":"2017-09-19","rates":{"AUD":1.6891,"BGN":2.2069,"BRL":4.2234,"CAD":1.6603,"CHF":1.3016,"CNY":8.8953,"CZK":29.454,"DKK":8.3968,"HKD":10.543,"HRK":8.4341,"HUF":348.5,"IDR":17927.0,"ILS":4.7509,"INR":86.905,"JPY":150.57,"KRW":1525.1,"MXN":23.978,"MYR":5.6645,"NOK":10.541,"NZD":1.8503,"PHP":68.875,"PLN":4.8278,"RON":5.191,"RUB":78.431,"SEK":10.749,"SGD":1.8225,"THB":44.688,"TRY":4.7126,"USD":1.3509,"ZAR":17.992,"EUR":1.1284}}

Now, in this case, I don't want each currency value as a separate field so I've used this code to exclude them:

If StrComp(UCase(strFieldName), strFieldName, vbBinaryCompare) = 0 Then GoTo NextItem

NOTE: in the above expression, if the 2 items being compared are equal, the result = 0 (confusingly!)

which gives the desired result:

However this one has me foxed:

{"aliceblue":"#f0f8ff","antiquewhite":"#faebd7","aqua":"#00ffff","aquamarine":"#7fffd4","azure":"#f0ffff","beige":"#f5f5dc","bisque":"#ffe4c4","black":"#000000","blanchedalmond":"#ffebcd","blue":"#0000ff","blueviolet":"#8a2be2"}

There should only be 2 fields: Colour & ColourValue but I get all the data:

I can't see anyway of using StrComp as there's nothing I can think of to compare with. One solution I came up with is:

If Left(strValue, 1) = "#" Then GoTo NextItem

This almost works ...

However, this is likely to exclude 'valid' fields I want to keep in other files
Going round in circles on this ....

Hope all that waffle made sense.
Can anyone point me in the right direction here?

 

[static]

I don't know JSON. I guess a group within a group would need a name, so if a name isn't given for the container group ask if it should have one?

{
"base":"GBP",
"date":"2017-09-19",
"rates":{
"AUD":1.6891,
"BGN":2.2069,
...
"EUR":1.1284
}
}

"color":{
"aliceblue":"#f0f8ff",
...
"blue":"#0000ff",
"blueviolet":"#8a2be2"
}

 

[isladogs]

Hi static

Thanks for this ...

JSON is just a text file based on Javascript with its own set of rules
Unfortunately these rules are very flexible!

Interestingly, your suggestion is exactly the workround I'm doing for now.
If there is no name for the array, use the file name as the array (or container) name

Here's an example of this in use ...

ModifyJSON:
    'add this section to enclose the whole JSON string if no overall group is supplied
    'in this example, overall group = "result"
    If InStr(strJSON, "[") = 1 Then
    'add dummy text to make parsing easier
        strJSON = "{""result"":" & strJSON & "}"
       ' Debug.Print strJSON
    End If

If you are interested, you could have a look at the JSON parser in sample databases

 

[static]

Yeah, I know what it is. I know XML so no need to learn something else that does the same thing.

Glad you got it sorted.

 

[isladogs]

As you say very much like XML but increasingly used instead of XML for data downloads.

Unfortunately Access doesn't provide its own JSON importer/parser which is why I've been building my own.

I've managed to make it almost entirely automated - just doing final bells & whistles now