Solved

Splitting out Data

Posted on 2016-11-18
14
47 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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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
 

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 120

Accepted Solution

by:
Rey Obrero (Capricorn1) 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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

840 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