Splitting out Data

Dear Experts

I have exported a few thousand emails so I can rebuild an address book. The data contains both the name and email address of the recipient in separate columns [ToName] & [ToAddress]

Where there is only one recipient this is easy to deal with, but where there are more than one then I need those records split and added to the end of the list (example screenshot below)

The data in each field is separated by a semi colon (;) so for example in the [ToName] field / column this looks like dave smith;simon jones with the corresponding [ToAddress] field / column being dave.smith@aol.com;simon.jones@hotmail.com.  (example screenshot below)

Screenshot
I can happily do this in either excel or access, can anyone help as there are a good few thousand to go through?

Many thanks
correlateAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
test this
Emails_rev.accdb
0
 
COACHMAN99Commented:
you would need a loop to iterate through all the records in the source table, and write to a second table
inside the loop you would
  redim an array of strings (to clear the previous results)
  populate the array using the SPLIT function (which splits up the long string into individual values based on ';')
  for each cell in the array
    take the left value (string before the '@') and assign it to the first field in a new record in another table
    take the whole cell and assign it to the second field in a new record in another table
0
 
Rey Obrero (Capricorn1)Commented:
here try this codes
create first a new table tblEmails_new

Sub SplitEmails()
Dim rs As DAO.Recordset, rsNew As DAO.Recordset, db As DAO.Database
Dim nameArr() As String, addArr() As String, j As Integer, i As Integer
Set db = CurrentDb
Set rs = db.OpenRecordset("tblEmails")
Set rsNew = db.OpenRecordset("tblEmails_new")

Do Until rs.EOF
    nameArr = Split(rs!ToName, ";")
    addArr = Split(rs!ToAddress, ";")
    
    If UBound(nameArr) > 0 Then
        For j = 0 To UBound(nameArr)
            For i = 0 To UBound(addArr)
                If Replace(nameArr(j), " ", ".") = Split(addArr(i), "@")(0) Then
                    With rsNew
                        .AddNew
                        !ToName = nameArr(j)
                        !ToAddress = addArr(i)
                        .Update
                    End With
                End If
            Next
        Next
    Else
    
    End If
    
rs.MoveNext
Loop
rs.Close
rsNew.Close
End Sub

Open in new window

0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Rey Obrero (Capricorn1)Commented:
oops, forgot the case for a single name, here is the correction


Sub SplitEmails()
Dim rs As DAO.Recordset, rsNew As DAO.Recordset, db As DAO.Database
Dim nameArr() As String, addArr() As String, j As Integer, i As Integer
Set db = CurrentDb
Set rs = db.OpenRecordset("tblEmails")
Set rsNew = db.OpenRecordset("tblEmails_new")

Do Until rs.EOF
    nameArr = Split(rs!ToName, ";")
    addArr = Split(rs!ToAddress, ";")
    
    If UBound(nameArr) > 0 Then
        For j = 0 To UBound(nameArr)
            For i = 0 To UBound(addArr)
                If Replace(nameArr(j), " ", ".") = Split(addArr(i), "@")(0) Then
                    With rsNew
                        .AddNew
                        !ToName = nameArr(j)
                        !ToAddress = addArr(i)
                        .Update
                    End With
                End If
            Next
        Next
    Else
                    With rsNew
                        .AddNew
                        !ToName = nameArr(0)
                        !ToAddress = addArr(0)
                        .Update
                    End With
        
    End If
    
rs.MoveNext
Loop
rs.Close
rsNew.Close
End Sub

Open in new window

0
 
correlateAuthor Commented:
Hi Rey,  thanks for this, I get an error "run-time error 9", "subscript out of range", I've stripped the data back to records with only multiple recipients, but the error still exists.  A good slug of the "ToName" contains email addresses rather than 'real names' & was wondering if that might be the problem?
0
 
Rey Obrero (Capricorn1)Commented:
I can only tell for sure if I can see the data.
upload sample data
0
 
correlateAuthor Commented:
Hi Rey,

Attached is an uploaded copy of the Database, As it contains personal contact details I've mashed up a chunk of the letters, so apologies if its not so easy to check the results visually, but I'm sure you understand
Emails---Copy.accdb
0
 
Rey Obrero (Capricorn1)Commented:
<A good slug of the "ToName" contains email addresses rather than 'real names' & was wondering if that might be the problem? > yes I believe that this will cause problem, how do you want to deal with this kind of record?
0
 
correlateAuthor Commented:
I am happy to just return whatever is in the name field, even if its an email address, using excel I know I can happily sort these out using a mixture of "if cell contains @ then = 1 if not = 0" & then sorting by email address and then that return to de-duplicate and provide the "real name" (where we have it) for each email address
0
 
correlateAuthor Commented:
Hi Rey,

my other thought if it makes it easier for you is I could do a simple find & replace across the [ToName] to remove the @ and "." replacing them with a.n.other character?  It might make the final tidy-up bits on the name harder, but if this achieves the goal the happy days.

many thanks

Tom
0
 
correlateAuthor Commented:
Hi Rey,  Sadly I don't think its capturing all the records - I ran the test on this & in excel it showed there were 423 ";" in the ToName column which would imply there should be 423 records but alas here were getting 117 results.  I can't really work out why, sorry about this

Tom
0
 
aikimarkConnect With a Mentor Commented:
In the first three rows, I see almost twice as many parsed ToName values as parsed ToAddress values.
0
 
correlateAuthor Commented:
Aggh, you're right, I just ran a character count across the email field in an excel download and spotted a number of columns with over 255 characters.  Unbelievably well spotted!  As there are only a handful (which happen to be at the top of the list) I'll manually get these split into more manageable chunks and deal with it from there
0
 
correlateAuthor Commented:
Guys, thank you very much for your help on this - real genius work thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.