Solved

Splitting out Data

Posted on 2016-11-18
14
38 Views
Last Modified: 2016-11-18
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
0
Comment
Question by:correlate
14 Comments
 
LVL 7

Expert Comment

by:COACHMAN99
ID: 41893328
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 41893335
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 41893343
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
 

Author Comment

by:correlate
ID: 41893460
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 41893476
I can only tell for sure if I can see the data.
upload sample data
0
 

Author Comment

by:correlate
ID: 41893510
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 41893511
<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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:correlate
ID: 41893521
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
 

Author Comment

by:correlate
ID: 41893533
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
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 450 total points
ID: 41893545
test this
Emails_rev.accdb
0
 

Author Comment

by:correlate
ID: 41893603
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
 
LVL 45

Assisted Solution

by:aikimark
aikimark earned 50 total points
ID: 41893605
In the first three rows, I see almost twice as many parsed ToName values as parsed ToAddress values.
0
 

Author Comment

by:correlate
ID: 41893618
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
 

Author Closing Comment

by:correlate
ID: 41893625
Guys, thank you very much for your help on this - real genius work thank you
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now