Hi,
What is the best way to determine the data types within JSON?
Below is an snippet of the type of code I'm dealing with.
It is a very large file overall and I'm looking to insert it in to an Access DB so I can easily search and produce reports etc so the parsing is done using VBA.
The problem comes when I need to decide what data type the the columns need to be when creating the table.
There is no schema available and I'm not sure that is even an option with the library I'm using.
At first, I'm thinking that I'll need to iterate over everything and see what datatype is best but that changes as fnt:g for id1 would be text however when you get to id 2 for fnt:g, it would be better with text format.
The crux of it is that cannot take the data type of the first element you come across as at some point, that data may well change.
I cannot add it all in as text as that doesn't make for very good use of the queries as you're never quite sure whether to try and convert the text to numbers when doing comparisons.
Is there an easier way to do this?
What is the best way to determine the data types within JSON?
Below is an snippet of the type of code I'm dealing with.
It is a very large file overall and I'm looking to insert it in to an Access DB so I can easily search and produce reports etc so the parsing is done using VBA.
The problem comes when I need to decide what data type the the columns need to be when creating the table.
There is no schema available and I'm not sure that is even an option with the library I'm using.
At first, I'm thinking that I'll need to iterate over everything and see what datatype is best but that changes as fnt:g for id1 would be text however when you get to id 2 for fnt:g, it would be better with text format.
The crux of it is that cannot take the data type of the first element you come across as at some point, that data may well change.
I cannot add it all in as text as that doesn't make for very good use of the queries as you're never quite sure whether to try and convert the text to numbers when doing comparisons.
Is there an easier way to do this?
JSON:
{
"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"
}
]
}
