Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2014
    Posts
    2

    Question Unanswered: Splitting one column into many

    Hey everyone,

    I have a table that I imported from excel. One of the columns has several pieces of information that I would like to divide up and place into other columns. The column name is "Opportunity Contact with Role"
    The info that I would like to divide up looks like: (I remove the actual names, but each Full Name would be different)
    Full Name | Key Decision Maker <> Full Name | Key Decision Maker<> Full Name | Key Decision Maker<> Full Name | Key Decision Maker<> Full Name | Key Decision Maker.
    So what I would like to do, if possible is have it automatically take the info in "Opportunity Contact with Role" and put it into different columns...ie Name1, Role1, Name2, Role2.

    A couple of points, each name/role combo is separated by <>
    If a name has a corresponding role it will be separated by |
    There maybe 1 contact or 20 contacts

    There are thousands of records so this would save a lot of time figuring this out

    Full disclosure...I am new to this.
    Any help would be much appreciated, figuring this out will save me daysof work each quarter

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You do not provide information about where you want to store the splitted information. If, as I understand, each splitted piece would be stored into a different column of one table, you'll first need to determine what's the maximum number of pieces (hence of columns) because
    Quote Originally Posted by philip.palmer View Post
    There maybe 1 contact or 20 contacts
    However you cannot have a table with a varying number of columns.

    As far as splitting the imported column into pieces, the process is rather simple (with Data being the information to be split):
    Code:
    Dim varX As Variant
    varX = Split(Data, "|")
    With your example, this yields an array such as:
    Code:
    Index    :  Contents
    ...........................................
    varX(0)  :  Full Name
    varX(1)  :  Key Decision Maker <> Full Name
    varX(2)  :  Key Decision Maker<> Full Name
    varX(3)  :  Key Decision Maker<> Full Name
    varX(4)  :  Key Decision Maker<> Full Name
    varX(5)  :  Key Decision Maker
    You can now process each line for further splitting:
    Code:
    Dim i As Long
    For i = 0 To Ubound(varX)
        varx(i) = Split(varX(i), "<>")
    Next i
    We now have:
    Code:
    Index    :  Index 0    : Contents           : Index 1    : Contents           
    ......................................................................
    varX(0)  :  varX(0)(0) : Full Name          : varX(0)(1) : (Nothing)        
    varX(1)  :  varX(1)(0) : Key Decision Maker : varX(1)(1) : Full Name
    varX(2)  :  varX(2)(0) : Key Decision Maker : varX(2)(1) : Full Name
    varX(3)  :  varX(3)(0) : Key Decision Maker : varX(3)(1) : Full Name
    varX(4)  :  varX(4)(0) : Key Decision Maker : varX(4)(1) : Full Name
    varX(3)  :  varX(5)(0) : Key Decision Maker : varX(5)(1) : (Nothing)
    This being done, you can use a dynamic query or a Recordset to insert the data into a table. If you use a dynamic query (SQL), the table must have an identity column to be able to determine in which row insert the data.
    Have a nice day!

  3. #3
    Join Date
    Apr 2014
    Posts
    2

    Thank you

    I appreciate you giving me help on this. Please forgive me, because I am new. I am not sure where I am putting this code???

    As for the table construct.
    It will have
    OpportunityContact|Contact1|Role1|Contact2|Role2| as so on till 20
    Has org info | new based on your code

    Any help you can give (and the more dumb down the better) will be extremely appreciated!!!!

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    This works (replace TableName by the actual name of the table):
    Code:
    Sub SplitContacts()
    
        Dim rst As DAO.Recordset
        Dim varX As Variant
        Dim varY As Variant
        Dim i As Long
        
        Set rst = CurrentDb.OpenRecordset("TableName", dbOpenDynaset)
        With rst
            Do Until .EOF
                varX = Split(![Opportunity Contact with Role], "<>")
                ReDim varY(0 To UBound(varX))
                For i = 0 To UBound(varX)
                    varY(i) = Split(varX(i), "|")
                Next i
                .Edit
                For i = 0 To UBound(varY)
                    .Fields("Contact" & i + 1).Value = varY(i)(0)
                    .Fields("Role" & i + 1).Value = varY(i)(1)
                Next i
                .Update
                .MoveNext
            Loop
            .Close
        End With
            Set rst = Nothing
    End Sub
    Note: Notice that the design of such a table with repeating columns tends to demonstrate that your database is not normalized. see: Database Normalization Basics: Normalizing Databases or http://www.iai.uni-bonn.de/III//lehr...DWA/WS07/1.pdf for instance.
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •