JSON Data Types when importing to Access (1 Viewer)

[GPGeorge]

Yup.

I thought it bore reinforcing.

Thank you both. I am not sure where and how I picked up the wrong assumption about items in an array. It pays to cross check information on the internet.

 

[cosmarchy]

Ok, after another thrilling day mulling this over, I can see the issue.

Yes, the strings have quotes in the JSON file but the library returns a dictionary of values without the quotes so testing for strings will not work as I never see the quotes so 5.5 could be a string in the JSON file, I'd just never know it.

With this in mind, I'm calling it a day. The library simply isn't easily modifiable and I don't have the time to sit down and re-write it.

I have had some limited success though by identifying numbers, but strings are just not going to work.

Thanks for your help.

 

[Edgar_]

I'm skeptical, I've never had such a result with that library. Why don't you show the code you're using and the JSON that is triggering that? it'd be interesting to see and good to point it out to the developers.

 

[cosmarchy]

Hi @Edgar_

Well, the first step for me was to determine the data types so I tried to modify so that a dictionary was returned with the names in the keys and the items containing either the data type.

I'd managed to get the numbers returned, but not strings:

and this is the JSON file I tested it against:

{
    "dte": "3rd August 2022",
    "bnd": [
        {
            "id": 1,
            "a": "S",
            "b": "",
            "c": 0,
            "d": 8300,
            "s": "Not Allocated",
            "v": 1
        },
        {
            "id": 2,
            "a": "P",
            "b": "",
            "c": 8300,
            "d": 9000,
            "s": "Not Allocated",
            "v": 1
        }
    ],
    "fnt": [
        {
            "id": 1,
            "g": "5.53",
            "t": "Lorem ipsum dolor sit amet"
        },
        {
            "id": 1,
            "g": "5.54",
            "t": "Phasellus condimentum, tellus at faucibus feugiat, odio lacus consequat nulla, ac molestie enim orci sit amet mi."
        },
        {
            "id": 2,
            "g": "5.54A",
            "t": "Integer sodales sem non tempus pulvinar"
        },
        {
            "id": 2,
            "g": "5.54B",
            "t": "Vestibulum tempus magna vitae tempor interdum"
        }
    ]
}

So, limited success.

The code I have is to set this rolling is:

Private Function test_JsonConverterModifications()

    Dim sJSONText As String
    Dim elements As Scriptin.Dictionary
    Dim element As Scripting.Dictionary

    sJSONText = JSON("C:\Users\cm\OneDrive\temp\Sample.json")
    
    Set elements = ParseJson(sJSONText)

    For Each element In elements("bnd")
        Debug.Assert False 'stop here so I can examine element
    Next
    
End Function

Function JSON(strFileName As String) As String

    Dim fso As New FileSystemObject
    Dim JSONts As TextStream
    
    Set JSONts = fso.OpenTextFile(strFileName, ForReading)
    JSON = JSONts.ReadAll
    JSONts.Close
    
End Function

and the modified library is attached as it it makes the post too long to post. It has a .txt extension but will need changing to .bas to enable it to be imported to access.

Please bear in mind that this will no longer work as the library was intended as my goal was to try and determine the data types in isolation before working out how to integrate in to a solution. It's a bit messy but it's probably obvious where I've made changes...

 

[Edgar_]

Dim elements As Scriptin.Dictionary

That's a typo, it's Scripting, not Scriptin.

For Each element In elements("bnd")

You declared element as a dictionary, why not just object? The library outputs collections too, as far as I recall. Not that it would in this case, but I've had better luck late binding here.

 

[Edgar_]

When I use the library, I check the type of data by looking at how it's parsed in the result. So far, I've never had trouble figuring out the data type this way. I never thought about changing the library except for removing the scripting runtime reference. After comparing the original library with your modified version, it seems like you've invested some time there, since the library is over 1k lines long, I agree with you that we don't have enough time to go through all the original developers' work and rewrite everything, plus do the testing just to get the data types. It's easier to work with the results as they are in my opinion. Good luck.