jareddxp
asked on
Access 2007 - Split a field with multiple inputs, into new records
Currently, I have an Excel Spreadsheet with a list of 30,000 e-mail subjects, addressees, sender, and subject. The "To" field often contains more than one address (sometimes as many as 50). I would like to be able to normalize the data in Access2007 as follows:
Current File:
Date | From | To | Subject|
1/2/09 |xx@aol.com |tom@aol.com; bob@aol.com; jack@msn.com| Lunch this week
To be converted to:
1/2/09 |xx@aol.com |tom@aol.com | Lunch this week
1/2/09 |xx@aol.com | bob@aol.com| Lunch this week
1/2/09 |xx@aol.com |jack@msn.com| Lunch this week
Any easy way to do this?
Thank You!
Current File:
Date | From | To | Subject|
1/2/09 |xx@aol.com |tom@aol.com; bob@aol.com; jack@msn.com| Lunch this week
To be converted to:
1/2/09 |xx@aol.com |tom@aol.com | Lunch this week
1/2/09 |xx@aol.com | bob@aol.com| Lunch this week
1/2/09 |xx@aol.com |jack@msn.com| Lunch this week
Any easy way to do this?
Thank You!
ASKER
Thanks for the prompt response. Unfortunately, I don't know VBA well enough to put it to use. Any idea in how to accomplish in SQL or Access itself?
IMHO, VBA would be your best shot.
Can I presume that there will be more than one record having this format?
"I don't know VBA well enough to put it to use."
Normally, I would agree, but if this is a one shot conversion, you will just be running the code.
What's to understand?
;-)
Let us know
JeffCoachman
JeffCoachman
Can I presume that there will be more than one record having this format?
"I don't know VBA well enough to put it to use."
Normally, I would agree, but if this is a one shot conversion, you will just be running the code.
What's to understand?
;-)
Let us know
JeffCoachman
JeffCoachman
ASKER
If I am running and not writing, it works for me.... but so far no luck. Yes, there are about 5000 records..
I have been trying to get the code below to work, but nothing so far...I tried pasting the following code into the immediate window, with no results.
Where I am using: [IX] (as the message hash value), and [TO] as the sender field
I also have uploaded a file with a small number of records...
any/all help is GREATLY appreciated.
I have been trying to get the code below to work, but nothing so far...I tried pasting the following code into the immediate window, with no results.
Where I am using: [IX] (as the message hash value), and [TO] as the sender field
I also have uploaded a file with a small number of records...
any/all help is GREATLY appreciated.
Dim dbCurr As DAO.Database
Dim rsInput As DAO.Recordset
Dim rsOutput As DAO.Recordset
Dim lngLoop As Long
Dim strTO As String
Dim varTos As Variant
Set dbCurr = CurrentDb()
Set rsInput = dbCurr.OpenRecordset("SELECT IX, To FROM NameSplit")
Set rsOutput = dbCurr.OpenRecordset("SELECT IX, To FROM NameSplit2 WHERE False")
Do While rsInput.EOF = False
varTos = Split(rsInput!To, ";")
For lngLoop = LBound(varTos) To UBound(varTos)
strTO = Trim(varTos(lngLoop))
rsOutput.AddNew
rsOuput!SKU = rsInput!IX
rsOutput!Option = Tos
rsOutput.Update
Next lngLoop
rsInput.MoveNext
Loop
rsInput.Close
Set rsInput = Nothing
rsOuput.Close
Set rsOutput = Nothing
Set dbCurr = Nothing
End Sub
trialdatabase.mdb
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Public Function ParseText(TextIn As String, x As Integer, stDelimiter As String) As Variant
On Error Resume Next
Dim Var As Variant
Var = Split(TextIn, stDelimiter, -1)
ParseText = Var(x)
End Function
When you call that function like this:
ParseText("Data1;Data2;Dat
It returns "Data3" because data 3 is in the third slot. So you could use it using a loop and an incrementing integer to return the first address, then the second, then the third, and so on and so on.