Solved

Access 2007 - Split a field with multiple inputs, into new records

Posted on 2009-04-14
5
668 Views
Last Modified: 2013-11-27
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!
0
Comment
Question by:jareddxp
5 Comments
 
LVL 15

Expert Comment

by:MNelson831
Comment Utility
Here is some cool VBA code that I stumbled upon surfing the net one day:

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;Data3;Data4",3,";")

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.
0
 

Author Comment

by:jareddxp
Comment Utility
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?
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
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
0
 

Author Comment

by:jareddxp
Comment Utility
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.
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

Open in new window

trialdatabase.mdb
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
Comment Utility
0

Featured Post

Backup Your Microsoft Windows Server®

Backup 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.

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

763 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

11 Experts available now in Live!

Get 1:1 Help Now