create multiple rows from a column data (1 Viewer)

[gilli]

HI,
I have a query which has few temp columns that call one of my functions.
What the function does is, it takes a field value for example address field is passed into the function, and then I am using vba json parser to parse address field and then store appropriate value into the calling field. That means if temp column street calls the function, the address field gets parsed and street name gets picked and stored in the street column in query. So far so good. All the rest of the temp columns does the same. My problem is when address field contains multiple addresses, how do I insert a new row to the query results and create the second address along with the other columns.
The address field is in json format and can contain multiple addresses separated by comma.
It looks something like this:
Full Name county Address
John, doe Monroe "[
{
""where"": ""H"",
""street"": ""RD 1, Country Lane Road"",
""state"": ""PA"",
""zipcode"": ""16052"",
""city"": ""Prospect""
},
{
""where"": ""O"",
""street"": ""306 E. Water St."",
""state"": ""PA"",
""zipcode"": ""16057"",
""city"": ""Slippery Rock""
}
]"

Thanks for any help.

 

[isladogs]

See if this post (UK Postal Addresses database) can help you manage what you want.

https://www.access-programmers.co.uk/forums/showthread.php?t=295378

It parses JSON data using VBA code & a recordset

 

[gilli]

Hi Ridders,
Thanks for the quick response. That's a great work.
I have managed to parse JSON data but am not sure how to create new rows for second address in the array. I am not sure how to write query in access to insert new row after parsing address field if there are multiple addresses. So far I am inserting only first address in the existing row but failing to insert second address in the next row with same id. Do you have any suggestions. Thanks again.

 

[isladogs]

Hi

Its by no means an easy task to parse JSON in Access
The structure of each file is different so the solution is unique as well.
So I can't give you a nice simple answer

Some suggestions:
1. Use a function rather than a query
2. Split the data into normalised tables
3. Try doing this using Excel Power Query first
4. Study the code I used in my UK postal addresses database

I would suggest you read through both of these threads:
https://www.access-programmers.co.uk/forums/showthread.php?t=294906

http://www.utteraccess.com/forum/index.php?showtopic=2045170&hl=

 

[sxschech]

I haven't downloaded it yet as only saw these posts a few minutes ago, arnelgp wrote a class for JSON with address, maybe it is adaptable to your situation.

https://access-programmers.co.uk/forums/showthread.php?t=295458