none
Splitting field RRS feed

  • Question

  • hi,

    I have an address field in access database which has street, state, city and zip in it.

    They are structured in this way:

    Name Address

    xxx     [{"street":"343 13th st", "state":"WI", "zip:"23232", "city":"xxxx"}, {"street":"455 13th st", "state":"WI", "zip:"64564", "city":"yyyy"}]

    A person can have two addresses. Now, how can I split these fields in address field to separate columns? Someone please guide me. Thanks for any help!

    Thursday, August 17, 2017 2:56 PM

Answers

  • Here's a little code that might help. Obviously you will need to adapt it to your circumstances ... After you do this for a while, you begin to recognize the "seams" in text patterns.
    Option Compare Database
    Option Explicit
    
    Private rst As Recordset    '   declared here so we can use it anywhere
    Private Torst As Recordset  '   declared here so we can use it anywhere
    Private theName As String   '   the name of the person
    
    Public Sub aaaRunParser()
        Dim r As Boolean
        r = ParseAddresses
    End Sub
    
    '
    Private Function ParseAddresses() As Boolean
    '   Purpose: ParseAddresses - split hairy addresses
        On Error GoTo errorexit
        Dim result As Boolean
        result = False
        '================================
    
        Dim db As DAO.Database
        Set db = CurrentDb
        Set rst = db.OpenRecordset("Table1", dbOpenDynaset)
        Set Torst = db.OpenRecordset("Table2", dbOpenDynaset)
        
        Do While Not rst.EOF
        '================================
            Dim addresses As String
            theName = rst!theName
            addresses = rst!Field2
            SplitThem addresses
    '        Debug.Print addresses
        '================================
    continue:
            rst.MoveNext
        Loop
        '================================
    
        rst.Close
        Set rst = Nothing
        Torst.Close
        Set Torst = Nothing
        db.Close
        Set db = Nothing
        
        result = True
    exitprocessing:
        ParseAddresses = result
        Exit Function
    
    errorexit:
        Debug.Print Err, "ParseAddresses", Erl
    #If PNRDEV Then
        Stop
        Resume
    #End If
        Resume exitprocessing
        Resume
    
    End Function
    
    Public Sub SplitThem(addresses As String)
        Dim address As String
        Do While AnotherAddress(addresses, address)
            Parse (address)
        Loop
    End Sub
    
    Public Function AnotherAddress(addresses As String, address As String) As Boolean
        '   find the start position
        Dim first As Long
        first = InStr(1, addresses, "{")
        If first = 0 Then   '    no more addresses
            AnotherAddress = False
            address = ""
            Exit Function
        End If
        
        '   find the last position
        Dim last As Long
        last = InStr(1, addresses, "}")    '   position of the }
        
        Dim length As Integer: length = last - 1 - first    '   always a little tricky
        
        address = Mid(addresses, first + 1, length)
    '    Debug.Print address
        
        '   eat the address from addresses
        addresses = Mid(addresses, last + 1) '   skip over the first }
    '    Debug.Print addresses
        AnotherAddress = True
    
    End Function
    
    
    Public Sub Parse(address As String)
        '   let's dump the quotes
        address = Replace(address, """", "")
        
        '   and the spaces following the commas
        address = Replace(address, ", ", ",")
        
        '   now let's split the fields
        Dim flds() As String   '    0-based
        flds = Split(address, ",")
        
        '   store them in the appropriate fields in the database
        '   in  the same table, or elsewhere
        '   some tedium here
        Dim nv() As String  '   name, value
        Torst.AddNew
            Torst.Fields("TheName") = theName
            nv = Split(flds(0), ":")
            Torst.Fields(nv(0)) = nv(1)
            
            nv = Split(flds(1), ":")
            Torst.Fields(nv(0)) = nv(1)
            
            nv = Split(flds(2), ":")
            Torst.Fields(nv(0)) = nv(1)
            
            nv = Split(flds(3), ":")
            Torst.Fields(nv(0)) = nv(1)
        Torst.Update
        
    End Sub
    


    peter n roth - http://PNR1.com, Maybe some useful stuff

    Wednesday, August 23, 2017 1:10 AM

All replies

  • Hi,

    This looks like a JSON format. You might try using a JSON parser.

    Just my 2 cents...

    Thursday, August 17, 2017 3:28 PM
  • You might try LEFT, InStr, & MID function --

    ---- UNTESTED ---

    FirstAddr: Left([YourTable].[YourAddrField], InStr([YourTable].[YourAddrField], "{") -3, & "]"

    2ndAddr: "[" & Mid([YourTable].[YourAddrField], InStr(9,[YourTable].[YourAddrField], "{"))


    Build a little, test a little


    Thursday, August 17, 2017 5:26 PM
  • Thanks QA Guy,

    your FirstAddr gives me just this: [ instead of 343 13th st

    this is what I've written.

    Where: Left([Contact].[Address],InStr([Contact].[Address],"{")-3)

    Please guide me through this.

    Monday, August 21, 2017 7:20 PM
  • Thanks .theDBguy,

    Could you please refer some articles? I've been trying split functions in VBA but its getting very hard with all the apostrophes to start where and end where. Extracting data from the apostrophes especially when there are multiple commas is very challenging.

    Monday, August 21, 2017 8:57 PM
  • Hi,

    I've had some success using the modules from this GitHub link: VBA-JSON

    Hope it helps...

    Monday, August 21, 2017 9:05 PM
  • Post how you want the results to appear.

    again --

    FirstAddr: Left([YourTable].[YourAddrField], InStr([YourTable].[YourAddrField], "},") -1)


    Build a little, test a little

    Monday, August 21, 2017 9:28 PM
  • QA Guy CommElec,

    This is the result I am expecting

    Street        State    ZipCode      City
    343 13th st     WI      23232       xxxx
    455 13th st    WI       64564       yyyy

    Thank you.

    Tuesday, August 22, 2017 1:24 PM
  • Here's a little code that might help. Obviously you will need to adapt it to your circumstances ... After you do this for a while, you begin to recognize the "seams" in text patterns.
    Option Compare Database
    Option Explicit
    
    Private rst As Recordset    '   declared here so we can use it anywhere
    Private Torst As Recordset  '   declared here so we can use it anywhere
    Private theName As String   '   the name of the person
    
    Public Sub aaaRunParser()
        Dim r As Boolean
        r = ParseAddresses
    End Sub
    
    '
    Private Function ParseAddresses() As Boolean
    '   Purpose: ParseAddresses - split hairy addresses
        On Error GoTo errorexit
        Dim result As Boolean
        result = False
        '================================
    
        Dim db As DAO.Database
        Set db = CurrentDb
        Set rst = db.OpenRecordset("Table1", dbOpenDynaset)
        Set Torst = db.OpenRecordset("Table2", dbOpenDynaset)
        
        Do While Not rst.EOF
        '================================
            Dim addresses As String
            theName = rst!theName
            addresses = rst!Field2
            SplitThem addresses
    '        Debug.Print addresses
        '================================
    continue:
            rst.MoveNext
        Loop
        '================================
    
        rst.Close
        Set rst = Nothing
        Torst.Close
        Set Torst = Nothing
        db.Close
        Set db = Nothing
        
        result = True
    exitprocessing:
        ParseAddresses = result
        Exit Function
    
    errorexit:
        Debug.Print Err, "ParseAddresses", Erl
    #If PNRDEV Then
        Stop
        Resume
    #End If
        Resume exitprocessing
        Resume
    
    End Function
    
    Public Sub SplitThem(addresses As String)
        Dim address As String
        Do While AnotherAddress(addresses, address)
            Parse (address)
        Loop
    End Sub
    
    Public Function AnotherAddress(addresses As String, address As String) As Boolean
        '   find the start position
        Dim first As Long
        first = InStr(1, addresses, "{")
        If first = 0 Then   '    no more addresses
            AnotherAddress = False
            address = ""
            Exit Function
        End If
        
        '   find the last position
        Dim last As Long
        last = InStr(1, addresses, "}")    '   position of the }
        
        Dim length As Integer: length = last - 1 - first    '   always a little tricky
        
        address = Mid(addresses, first + 1, length)
    '    Debug.Print address
        
        '   eat the address from addresses
        addresses = Mid(addresses, last + 1) '   skip over the first }
    '    Debug.Print addresses
        AnotherAddress = True
    
    End Function
    
    
    Public Sub Parse(address As String)
        '   let's dump the quotes
        address = Replace(address, """", "")
        
        '   and the spaces following the commas
        address = Replace(address, ", ", ",")
        
        '   now let's split the fields
        Dim flds() As String   '    0-based
        flds = Split(address, ",")
        
        '   store them in the appropriate fields in the database
        '   in  the same table, or elsewhere
        '   some tedium here
        Dim nv() As String  '   name, value
        Torst.AddNew
            Torst.Fields("TheName") = theName
            nv = Split(flds(0), ":")
            Torst.Fields(nv(0)) = nv(1)
            
            nv = Split(flds(1), ":")
            Torst.Fields(nv(0)) = nv(1)
            
            nv = Split(flds(2), ":")
            Torst.Fields(nv(0)) = nv(1)
            
            nv = Split(flds(3), ":")
            Torst.Fields(nv(0)) = nv(1)
        Torst.Update
        
    End Sub
    


    peter n roth - http://PNR1.com, Maybe some useful stuff

    Wednesday, August 23, 2017 1:10 AM
  • Hi roxannaappleby,

    If the address is a json string, you could decode it as json object to get the data and insert them to where you want.

    Here is the example.

    Private Sub Command0_Click()
    
        Dim objJSControl, objJSTypeInfos, objJSTypeInfo
    
        Set objJSControl = CreateObject("MSScriptControl.ScriptControl")
    
        Dim db As DAO.Database
    
        Dim rstSource As DAO.Recordset
    
        Dim rstTarget As DAO.Recordset
    
        Set db = CurrentDb
    
        Set rstSource = db.OpenRecordset("Table2", dbOpenDynaset)
    
        Set rstTarget = db.OpenRecordset("Table3", dbOpenDynaset)
    
        If Not (rstSource.EOF And rstSource.BOF) Then
    
         rstSource.MoveFirst
    
        Do Until rstSource.EOF = True
    
        jsStr = "var mydata=" & rstSource.Fields("Address").Value
    
           With objJSControl
    
              .Language = "javascript"
    
              On Error GoTo ErrorHandler
    
              .addcode jsStr
    
               Set objJSTypeInfos = .codeobject
    
               For Each objJSTypeInfo In CallByName(objJSTypeInfos, "mydata", VbGet)
    
               City = CallByName(objJSTypeInfo, "city", VbGet)
    
               State = CallByName(objJSTypeInfo, "state", VbGet)
    
               street = CallByName(objJSTypeInfo, "street", VbGet)
    
               zip = CallByName(objJSTypeInfo, "zip", VbGet)
    
               rstTarget.AddNew
    
               rstTarget.Fields("Name") = rstSource.Fields("Name")
    
               rstTarget.Fields("City") = City
    
               rstTarget.Fields("State") = State
    
               rstTarget.Fields("Street") = street
    
               rstTarget.Fields("ZipCode") = zip
    
               rstTarget.Update
    
               Next objJSTypeInfo
    
             End With
    
        rstSource.MoveNext
    
        Loop
    
       End If
    
        rstSource.Close
    
        rstTarget.Close
    
    Exit Sub
    
    ErrorHandler:
    
    MsgBox "Json string format is not correct: " & Err.Description
    
    End Sub

    The reason why I said if the address is Json string is that the string you shared is not a correct json string format. It missed a " after zip and in this situation, I think you have to decode it as normal string.

    Best Regards,

    Terry

    Wednesday, August 23, 2017 9:49 AM
  • Thanks Peter for the detailed explanation of the code. I really appreciate it.

    I will try it and see if that works for me. Meanwhile, I have some questions as I am still learning Access & VBA.

    What is recordset Torst used for in this example? Are we going to create a whole new table to insert these newly created fields?

    What will happen to 'Address' field after splitting into multiple fields. Can I just hide it or do something with it? This Address field is currently in a query and after writing this module, I am thinking of using the functions in the newly created fields. Am I understanding it correct? Please excuse if I am being dumb as I just want to clear my questions and dive into code with more clarity.

    Thanks again.


    Wednesday, August 23, 2017 1:22 PM
  • Hi terry,

    sorry, that must be a typo, not sure how it missed a ". Thanks for pointing it out. 

       [{"street":"343 13th st", "state":"WI", "zip":"23232", "city":"xxxx"}, {"street":"455 13th st", "state":"WI", "zip":"64564", "city":"yyyy"}]

    I do like the option to decode it into normal string. I will probably try your suggestion as well. Will keep you posted.

    Thank you.

    Wednesday, August 23, 2017 1:24 PM
  • The new table should have the fields ID, theName, street, etc. ID could be an autonumber, since now you may have records in which theName is duplicated. That’s easier to do in a new table than by writing into the original. That’s what Torst references, the new table.

    The ‘Address’ field in the original table is up for grabs. You can delete the column, but don’t be hasty. If there’s a problem with an address, you don’t want to throw away useful info.

    So your source for the address info is a query on a table? In that case, open the first rst on that query. 


    peter n roth - http://PNR1.com, Maybe some useful stuff

    Wednesday, August 23, 2017 2:09 PM
  • Yes, I have a query which has address field in it and thanks for the tip. I will open the first rst on the query. The query has joins to three different tables and has a total of 16 fields in it.

    Also, thanks for the Torst table explanation. It definitely seems to be easier to have a new table.

    I will probably keep the 'Address' field as I am going to display the results in new table.

    I did not understand the do while loop though. I hope you will clear this for me.

     Do While Not rst.EOF
        '================================
            Dim addresses As String
            theName = rst!theName
            addresses = rst!Field2
            SplitThem addresses
    '        Debug.Print addresses
    '================================
    continue:
            rst.MoveNext
        Loop
        '================================

    Should I be assigning the person's full name to 'theName' variable? To be specific I did not quite understand this line.

    theName=rst!theName

    Thank you.



    Wednesday, August 23, 2017 2:19 PM
  • The Do loop starts out like this:

    Do While Not rst.EOF
        '================================
    
       '================================
    continue:
            rst.MoveNext
    Loop 
    
    

    It is generated by one of my tools. The "continue:" label is there because sometimes I need to exit from what the code is doing and proceed to the next record.

    I’ve defined theName as one of the variables global to this module. I assume you have a similar name for the query field because Name is a reserved word. So you could write it

       theName = rst!YourName

    Suppose there are 3 fields associated with the name. rst points to the record, so you can do what you want with those 3 fields: perhaps

       theName = rst!firstName & “ “ & rst!lastName

    Field2 is a convenient way for me to refer to the address column of the query.


    peter n roth - http://PNR1.com, Maybe some useful stuff

    Wednesday, August 23, 2017 2:38 PM
  • ok. got it. I kinda figured it out myself but your explanation clarified it for me better.

    I've actually done something like this, as it kept throwing compile error:

     theName = rst.Fields("Full Name") instead of theName = rst!theName

    That's the name of my field(person's full name)

    and was able to clear that compile error.

    Stuck with another:

    While adding new fields to Torst, I am getting to 'errorexit:' for some reason.

    Is it because, I haven't created field names manually in the new table? From the code, I got the impression that

    Torst.AddNew will be creating the fields for me. Am I wrong in thinking so?

    Should I create all the 16 fields manually in the new table along with the newly split fields?

    Thanks a ton, I've learnt a lot so far. :)

    Wednesday, August 23, 2017 2:57 PM
  • I usually avoid blanks in field names, for convenience sake.

    Yes, you have to name your fields manually. ‘AddNew’ appends data to an already defined field.

    Also, from VBA, open the help file … it’s different from the Access help file.


    peter n roth - http://PNR1.com, Maybe some useful stuff

    Wednesday, August 23, 2017 3:10 PM
  • All right, Thank you so much for all the help.

    One last question, regarding rst.AddNew

    why are we inserting into nv(0) for each new field? It exists to error after this line:

     nv = Split(flds(1), ":")
            rst.Fields(nv(0)) = nv(1) // error after this line here

    For now, I am trying to insert into same table for testing purposes.

    Wednesday, August 23, 2017 3:33 PM
  • There are 4 fields saved into the “flds()” array.

    Each fld(i), i=0..3 is split at the “:” into the nv() array.

    nv(0) is the name of the field in your table, from the input pattern, supposedly like “street”. nv(1) is the value of that field.

    1.        In VBA, put a breakpoint on the line beginning with rst.Fields, the one after the Split.
    2.        On the View menu, open the Locals window. This will show you all the local variables and their values. nv will appear with a box with a + to the left of it. Click the plus to expand the display.

    You will find that nv(0) does NOT match the name of a field in your table. Why that is depends on how good the data is (any extra blanks?) and what has happened to it (or not) in the parsing process.


    peter n roth - http://PNR1.com, Maybe some useful stuff

    Wednesday, August 23, 2017 6:07 PM
  • Yes, I did debug and found that nv(1) holds the field value but I guess it's failing to insert into the newly created field. There must be something wrong with the way I am creating these new fields.

    So, I created new field in the existing query instead of a new table. I went into query design view and inserted new column and clicked zoom and gave a name to it. Is that the right way of doing? Or should I better go with the new table? It seems tedious to me to create new table with all the fields I want and then write code to fill values into them. So instead I am trying to insert into the existing query which is my source.

    Also, should I be calling the function on every new field that I am going to create?

    Thanks for being patient with my questions.


    Wednesday, August 23, 2017 6:19 PM
  • Aha! Now you’re asking yourself questions and giving yourself the right answers! ;o)

    Read over the VBA help file page devoted to the Recordset.AddNew method.


    peter n roth - http://PNR1.com, Maybe some useful stuff

    Thursday, August 24, 2017 2:32 AM
  • Alright, thanks Peter.  You've helped me a lot.

    It's not fair to leave me at the edge though. ;)

    How do I run this function, Peter? On address field? Or on each new field as an expression? That's one last question, please :)

    Thank you.

    Thursday, August 24, 2017 1:12 PM
  • Upon further debugging, I noticed that both nv(0) and nv(1) are pulling in right values i.e. nv(0) gives me correct field name for example Street and nv(1) gives me street address. THat part looks correct to me.

    So, all I am failing is how to invoke this function? I am not sure how to call this function, is it on address field? Also, the newly created four fields seem to be not working properly for some reason. I am creating them manually in the query design view.

    Please give some direction to follow. Thanks.

    Thursday, August 24, 2017 5:22 PM
  • we're all "on the edge", doncha know.

    A query is not updateable; therefore, you have to use a table.


    peter n roth - http://PNR1.com, Maybe some useful stuff

    Thursday, August 24, 2017 6:27 PM