• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3938
  • Last Modified:

split a text field into multiple columns in Access 2007

I have a text field formatted as such "001, 002, 003" There could by any number of comma separated values in the field. I would like to parse this text field apart into N columns (anywhere from 1 to N). I know this has been covered before but i couldn't quite figure it out based on other posts. Thanks.

The field text is called [Item Numbers], and the new fields should be called [Item1], [Item2], [Item3]. etc...

0
Yaniv Schiff
Asked:
Yaniv Schiff
  • 7
  • 3
  • 2
1 Solution
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I'm not sure what you mean by "columns". Generally, you'd insert each of those items into a Row in a table, not in columns across a single Row.

You can use the Split function to do this:

Dim rst As DAO.Recordset
Dim arr() As String
Dim i As Integer

Set rst = Currentdb.OpenRecordset("SELECT * FROM YourTAble")
arr = Split(rst("YourField"), ",")

For i = 0 to Ubound(arr)
  '/do something here
Next i
0
 
Yaniv SchiffDirector of Digital Forensics Author Commented:
that's pretty much what i have so far, it's the "do something here" part that i'm getting stuck at. What i want it to do is create a new column for each item number and insert the item number into the field. Attached is the code i have so far which creates the columns, i'm not sure how to insert the item number value into each field as it works it's way through the table though. Does that make sense?
Sub test()
DoCmd.SetWarnings (WarningsOff)

Dim s As String
Dim a() As String
Dim i As Integer

Dim strFieldName As String

s = "001, 002, 003, 004, 005"


a = split(s, ",")

For i = 0 To UBound(a, 1)
strFieldName = "Item" & i

CreateField "Slips", strFieldName

Debug.Print a(i)

Next i


End Sub


Function CreateField( _
      ByVal strTableName As String, _
      ByVal strFieldName As String) _
      As Boolean

   'References: Microsoft Access 11.0 Object Library, Microsoft DAO 3.6 Object Library
   'Set references by Clicking Tools and Then References in the Code View window
   'Creates a Text field, other data types listed
   '
   ' strTableName : Name of table in which to create the field
   '
   ' strFieldName : Name of the new field to add to table
   ' Returns True on success, false otherwise
   '
   'USAGE: CreateField "TABLENAME", "FIELDNAME"

   On Error GoTo errhandler

   Dim Db As DAO.Database
   Dim fld As DAO.field
   Dim tdf As DAO.TableDef

   Set Db = Application.CurrentDb
   Set tdf = Db.TableDefs(strTableName)

   ' First create a field with data type = Text
   Set fld = tdf.CreateField(strFieldName, dbText)

   'A few Alternate datatypes: for DAO - Note: The listed Complex data types require
         ' Access 2007 or higher
   'Long = dbLong or dbComplexLong
   'Single = dbSingle or dbComplexSingle
   'Double = dbDouble or dbComplexDouble
   'Integer = dbInteger
   'Decimal = dbDecimal or dbComplexDecimal
   'Text = dbText or dbComplexText
   'Memo = dbMemo
   'Currency = dbCurrency
   'Yes/No = dbBoolean
   'Date = dbDate

   ' Appending the field
   With tdf.Fields
      .Append fld
      .Refresh
   End With
   CreateField = True

ExitHere:
   Set fld = Nothing
   Set tdf = Nothing
   Set Db = Nothing

   Exit Function

errhandler:
   CreateField = False

   With Err
 
   End With

   Resume ExitHere

End Function

Open in new window

0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Creating a column for each field does NOT make sense. As I stated earlier, you really should be putting these in Rows of your table.

What exactly does the data represent?
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
Yaniv SchiffDirector of Digital Forensics Author Commented:
Each row represents a work ticket. Each work ticket is associated with multiple Items (as indicated by the [Item Numbers] column). Each item number is 3 digits (ie. 001, 002) separated by a comma. I would like to take each item number and store it in a separate column for each work ticket. The first screen shot(singlecolumn), shows how the data is currently stored. The second, (multicolumn) shows how i would like it.

SingleItemNumberColumn.jpg
MultipleItemNumberColumn.jpg
0
 
Patrick MatthewsCommented:
Forensicon,

I urge you to re-read LSMConsulting's comment http:#a31405421, and to take his advice to heart.  Putting this into separate columns is a stupendously bad idea.  All the principles of proper database design would have you place these items into separate rows, and not separate columns.

Patrick
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
In a relational database, you would have a WorkTicket table, and you'd also have a separate table to handle those "items", whatever they may be. You would "relate" that "item" table back to the WorkTicket table by storing the WorkTicket.ID value in the Item table row.
0
 
Yaniv SchiffDirector of Digital Forensics Author Commented:
I agree with both of you that creating multiple columns is a bad idea. The problem i'm running into though is that all this information is coming out of sharepoint and i'm trying to make do with what i have. So i understand, you guys are suggesting that instead of creating multiple columns to store the item numbers, that i create a new row for each item number. EG.

ItemNumber                       WorkTicketNumber
001, 002, 003                         1234

Would result in:

ItemNumber                       WorkTicketNumber
001                                     1234
002                                     1234
003                                     1234


Is that correct?
0
 
Patrick MatthewsCommented:
That's the idea :)
0
 
Yaniv SchiffDirector of Digital Forensics Author Commented:
i'm beginning to get it., Attached is the code i have so far which works well to loop through the whole table [Slips] and parse out each item number for each row. My thought is to create another table called [ItemNumberWorkTickets] which will have 2 columns, "ItemNumber" and "SP_WorkTicketID". As i loop through my Slips table i want to pull out each ItemNumber and WorkTicketID and insert it into the [ItemNumberWorkTickets] table. This way i can run join queries later as you guys suggested. What would be the sql statement to do this? Thanks for the good advice guys.
Sub split1()

Dim strFieldName As String
Dim sql As String

Dim rst As DAO.Recordset
Dim arr() As String
Dim i As Integer

Set rst = CurrentDb.OpenRecordset("SELECT Slips.[Item Numbers], Slips.SP_WorkTicketID FROM Slips where [Item Numbers] is not null;")

Do While Not rst.EOF


arr = split(rst("[Item Numbers]"), ",")

    For i = 0 To UBound(arr)
    Debug.Print arr(i)

sql = "INSERT INTO ItemNumberWorkTickets ( ItemNumber, SP_WorkTicketID )SELECT " & arr(i) & " AS Slips.[Item Numbers], Slips.SP_WorkTicketID FROM Slips;"
DoCmd.RunSQL sql
  
  

    Next i
rst.MoveNext
Loop

End Sub

Open in new window

0
 
Yaniv SchiffDirector of Digital Forensics Author Commented:
any thoughts?
0
 
Yaniv SchiffDirector of Digital Forensics Author Commented:
i figured it out using the code provided by lsm and other posts. i included it below for reference. Thanks to both experts.
Public Function BreakToWords()
Dim rsOrig As DAO.Recordset
Dim rsNew As DAO.Recordset
Dim vArr As Variant 'array to hold the split phrase
Dim i As Integer 'counter

Set rsOrig = CurrentDb.OpenRecordset("SELECT Slips.[Item Numbers], Slips.SP_WorkTicketID FROM Slips where [Item Numbers] is not null;")
Set rsNew = CurrentDb.OpenRecordset("ItemNumberWorkTickets")

If rsOrig.RecordCount <> 0 Then

'loop the rsOrig records
rsOrig.MoveFirst
While Not rsOrig.EOF
'split the phrase on a space delimiter
vArr = split(rsOrig("[Item Numbers]"), ",")

'loop the array (words) and add to rsNew
For i = 0 To UBound(vArr)
With rsNew
Debug.Print vArr(i)
.AddNew
.Fields("SP_WorkTicketID") = rsOrig("SP_WorkTicketID")
.Fields("[ItemNumber]") = Trim(vArr(i))
.Update
End With
Next

rsOrig.MoveNext
Wend

End If

rsOrig.Close
rsNew.Close
Set rsOrig = Nothing
Set rsNew = Nothing

MsgBox "Complete"


End Function

Open in new window

0
 
Yaniv SchiffDirector of Digital Forensics Author Commented:
based on LSM's suggestions i was able to use the code provided and other code found on EE.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 7
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now