How do I parse a string response into a temporary table? MS Access 2007/10 VBA

[summerrain]

Hi,

I thought it would be good to post this as the service at www.getaddress.io seems a good one and the results may be of use to other access users.

I have just started using a free account from "getaddress" to see if this can be used to populate address fields within an access database.

It is relatively simple to set up and use the account.
A request is sent to the web service with a specific postcode attached to the request string.

In this example I sent the postcode SY186BN

The response string is as follows:

{"Latitude":52.448386,"Longitude":-3.540556,"Addresses":["Arcadia, 6 Great Oak Street, , , , Llanidloes, Powys","Bistro Hafren, 2 Great Oak Street, , , , Llanidloes, Powys","D'Eco, 4 Great Oak Street, , , , Llanidloes, Powys","Flat 1-2, 2 Great Oak Street, , , , Llanidloes, Powys","Ingrams, 3 Great Oak Street, , , , Llanidloes, Powys","Llanidloes Town Council, Town Hall, Great Oak Street, , , Llanidloes, Powys","Milwyn Jenkins & Jenkins, Mid Wales House, Great Oak Street, , , Llanidloes, Powys","The Kitchen, 5 Great Oak Street, , , , Llanidloes, Powys","Town Hall, Great Oak Street, , , , Llanidloes, Powys"]}

The data I am interested in initially is the list of addresses each with 7 fields which is enclosed between the square brackets []
The above example has 9 records of seven fields.
The number of records will vary but the number of fields within each record will be the same even if many of them are null.

What I need to achieve is the best way to take the response string and with it populate a table with the 7 data fields * no of records. The table will already be created so this does not need to be done programmatically.

TblAddrTemp:
Fields:
AddrTempID
TempAddr1
TempAddr2 etc to TempAddr7

I am fairly adept at VBA and SQL so once I have the data in a temp table I should be able to devise ways to use it to populate the main address tables fairly easily.

What I am not so good at (yet!) is complex text handling/parsing.

Any help much appreciated.

[Seth Schrock]

My first thought would be to us the InStr() function to find the location of the [" at the beginning and then the "] at the end, and then calculate the difference between these two points, which gets you the length of the text you care about. Then use the Mid() function to parse out the section using the information gathered above. You can then use the Split() function, using "," as delimiter, you will then have an array of each record. You can then loop through each element of this array and again use the Split() function using the , as the delimiter and you will then get the individual fields for each record. In your loop you can then write your information to the table.

[summerrain]

Thanks Seth, that gives me a starting point. I'll post the code if I come up with anything that looks like it has a chance of working.
It might take me some while!
Meanwhile if anyone has some aircode I can play with I would much appreciate it as I've never had to tackle this kind of string manipulation before.

[Seth Schrock]

Here is the string parsing part of it. I just have Debug.Prints instead of writing to a table though.

Public Sub ParseString()
Dim strReturnString As String
Dim intStart As Integer
Dim intLength As Integer
Dim strInside As String
Dim strRecords() As String
Dim i As Integer
 
strReturnString = "{""Latitude"":52.448386,""Longitude"":-3.540556,""Addresses"":[""Arcadia, 6 Great Oak Street, , , , Llanidloes, Powys"",""Bistro Hafren, 2 Great Oak Street, , , , Llanidloes, Powys"",""D'Eco, 4 Great Oak Street, , , , Llanidloes, Powys"",""Flat 1-2, 2 Great Oak Street, , , , Llanidloes, Powys"",""Ingrams, 3 Great Oak Street, , , , Llanidloes, Powys"",""Llanidloes Town Council, Town Hall, Great Oak Street, , , Llanidloes, Powys"",""Milwyn Jenkins & Jenkins, Mid Wales House, Great Oak Street, , , Llanidloes, Powys"",""The Kitchen, 5 Great Oak Street, , , , Llanidloes, Powys"",""Town Hall, Great Oak Street, , , , Llanidloes, Powys""]}"
 
intStart = InStr(strReturnString, "[""") + 2
intLength = InStr(strReturnString, """]") - intStart
 
strInside = Mid(strReturnString, intStart, intLength)
 
strRecords = Split(strInside, """,""")
 
For i = 0 To UBound(strRecords)
    Debug.Print
    Debug.Print "Record " & i + 1
    Debug.Print "Field 1: " & Split(strRecords(i), ",")(0), _
                "Field 2: " & Split(strRecords(i), ",")(1), _
                "Field 3: " & Split(strRecords(i), ",")(2), _
                "Field 4: " & Split(strRecords(i), ",")(3), _
                "Field 5: " & Split(strRecords(i), ",")(4), _
                "Field 6: " & Split(strRecords(i), ",")(5), _
                "Field 7: " & Split(strRecords(i), ",")(6)
Next
 
End Sub

[summerrain]

That works a treat. Many thanks you've saved me hours of headache time!!