SPLIT the Address thru SQL

[sandeepk66]

Hi Geeks,
Want to split the below Address Field in MS Access, which is coming in JSON.
needs to be split the this into multiple fields, Ideas Please

Address
[ { "street": "2 Willedrobe Rd.", "state": "IL", "zipcode": "67050", "city": "Indiana" }]

[sandeepk66]

Hi,
Want to split the below Address Field in MS Access, which is


coming in JSON.
needs to be split the this into multiple fields, Ideas Please


Address
"[
{
""street"": ""678 Willedrobe Dr."",



""state"": ""IL"",
""zipcode"": ""34567"",



""city"": ""Indiana""
}
]"

[kd2017]

How about a json library for vba? https://github.com/VBA-tools/VBA-JSON

Google "vba json" for other ideas.

IF this is the exact format of all the json you will ever need to parse then you could make a vba function to parse it fairly easily. But if it might ever change I wouldn't reinvent the wheel and I'd go with a vba json library.

[Homegrownandy]

Could you show a screenshot so I can better understand?

[orange]

More info required.
Is this a 1 record, 1 time thing?
Or is it a recurring result from a recognized webservice?

Sandeep I realize this is only your second post here, but we need some facts/details to make a meaningful response. Forum participants are very good, but not clairvoyant.

Tell us in simple, plain English where the json data is coming from, the volume of data or requests and perhaps how and where it is used in/by your application.

Good luck with your project.

Here is small demo for your specific question

'===============
'---------------------------------------------------------------------------------------
' Procedure : parseAJsonString
' Author    : mellon
' Date      : 25-Aug-2017
' Purpose   : parsing this specific json string as a demo
' see https://www.accessforums.net/showthread.php?t=67719
'[ { "street": "2 Willedrobe Rd.", "state": "IL", "zipcode": "67050", "city": "Indiana" }]
' remove all ddouble quotes from string
'---------------------------------------------------------------------------------------
'
Sub parseAJsonString()
          Dim js As String
          Dim i As Integer
          Dim j As Integer
          Dim arr
          Dim Final
10       On Error GoTo parseAJsonString_Error

20        js = "[ { street: 2 Willedrobe Rd., state: IL, zipcode: 67050, city: Indiana }]"
30        arr = Split(js, ",") ' break json parts at comma to deal with individual name/value component
         ' Debug.Print UBound(arr)
40        For i = 0 To UBound(arr)
         ' Debug.Print arr(i) for testing
         'remove json specific array, object markers
50            Final = Trim(Replace(arr(i), "[", ""))
60            Final = Trim(Replace(Final, "{", ""))
70            Final = Trim(Replace(Final, "}", ""))
80            Final = Trim(Replace(Final, "]", ""))
90            Final = Split(Final, ":")
100           Debug.Print Final(0) & vbTab & Final(1)  ' name/value pair
110       Next
        
parseAJsonString_Exit:
120      Exit Sub

parseAJsonString_Error:
130      MsgBox "Error " & err.number & " in line " & Erl & " (" & err.Description & ") in procedure parseAJsonString of Module AWF_Related"
140      Resume parseAJsonString_Exit
End Sub

Result:

street   2 Willedrobe Rd.
state    IL
zipcode  67050
city     Indiana

[sandeepk66]

Thanks Orange.
I have a query in access database which is joined with three different tables. One of the fields in the query has json formatted data. for example, an address field with all the key value pairs(street, state,zip, city).
I need a find a way to split address field into 4 different fields(street, state,zip, city). Address can sometimes have an array of objects i.e, multiple addresses for a person. Those needs to be created as a separate row for the same person.
This split fields needs to be called from the query itself. I have tried couple of ways but since I am novice in Access, couldn't figure out things. Any help would be great.

[orange]

Might be better if you posted a copy of your database with some sample records showing a json array with multiple addresses. Where do you get the json data from? Perhaps you should process it separately, then bring appropriate records together in Access.

We need to know more about:
--your requirements
--your current database

Where do People fit in this set up? You say you have json formatted address data that can be json array with multiple addresses per person. How can you relate the Person to the json data?