Solved

Splitting out Data

Posted on 2016-11-18
14
27 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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
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…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

705 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

18 Experts available now in Live!

Get 1:1 Help Now