Solved

Splitting out Data

Posted on 2016-11-18
14
54 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

707 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