Comma separated Values into separate columns

[Sweet]

Hi friends,

Would request a kind help.

I have a column which stored value as follows

Record1 - aaaa , 1111111, jjjjjjj , kkkkkkkkkkk
Record2 - hhhhhhh, 77777 , 99999999, gggg
Record3 - tttttt, 666666 , dddddd , 33333
Record4 - aaaa , 2222 , 77777 , rrrrrr

How do I separate the values above to be populated into 4 different columns ??
As you may also notice the string between values are of varying length.



Thanks in advance

 

[Brian]

Two questions:

1. Are there always four elements and only four?
2. Where do you want the value to be separated? In a report, in query
results, or into a table, each stored as a separate field or record?

Two ideas:
A. If the only spaces in the records are next to the commas and not between
the elemens, you can use a nested Replace to replace all spaces empty strings
and each comma with perhaps a Tab or several spaces to get the column spacing
even.
B. Use the Split function to actually separate each element into a separate
array member. This would be useful if you want to store the data separately
as four separate fields in one record or as four separate records.

Which you use will depend on your answer to #2 at the top. I would guess
that you would probably want to get storage normalized and use a recordset
loop to Split each one into four separate fields within one record. If so, I
have an example, so post back.

 

[Sweet]

1. Are there always four elements and only four?
- Nope, they are variable
2. Where do you want the value to be separated? In a report, in query
results, or into a table, each stored as a separate field or record?
Into a table ,split into 4 columns. Each stored as a separate field.

 

[Brian]

I think I detect a little conflict between your two answers. If there are a
variable number of elements, how will you determine how many fields there are
in the destination table? That is, if one of them has four elements and one
has twenty-nine, it would seem that you need a table with twenty-nine fields,
and some records will have some fields empty. In that case, would the
four-field record be just in the first four fields, or is there some criteria
for determining which field the data goes into when it has fewer fields than
the maximum number in the batch. Post back whether you want to:

1. Determine the maximum number of fields required before you begin and make
the table beforehand, then run this as an append query, or
2. Use a make-table query and include a process to find the record having
the highest number of members at runtime.

Either can be done.

Third question: will there ever be spaces within the individual members, or
only next to the commas? This is important when coding the Replace. If there
are no spaces in the members, then we can Replace all spaces with commas, as
in the optional line below; if not, it will be more complicated.

In the meantime, here is code that will split a delimited field like this
into separate records (not fields). It is not quite what you want, but will
demonstrate the process. Your answers to the questions above regarding number
of fields will help define getting it to separate them into fields instead of
records.

Private Sub ButtonRun_Click()
'requires tblA & tblB, each with at least one text field named Field1
'tblA (source table) to have Field1 populated with comma-delimited
'tblB
Dim txtStatus As Variant 'used for status bar
Dim varSplit As Variant 'used to split values into array
Dim recCount As Long 'used to count records to show progress on status bar
Dim recCurrent As Long 'used to show current position on status bar
Dim ValInput As String 'used to look up the original value
Dim SplitVal As String 'used for each element of array
Dim strSQL As String 'used to build SQL expression used to append records
Dim rsTest As Recordset
Set rsTest = CurrentDb.OpenRecordset("tblA", dbOpenSnapshot)
rsTest.MoveLast 'force accurate record count
recCount = rsTest.RecordCount 'get record count for status bar meter
rsTest.MoveFirst 'start at the beginning
txtStatus = SysCmd(acSysCmdInitMeter, "Populating tblB...", recCount) 'start
the status bar meter
Do While Not rsTest.EOF
recCurrent = recCurrent + 1
txtStatus = SysCmd(acSysCmdUpdateMeter, recCurrent) 'increment the
status bar meter
ValInput = rsTest.Fields("Field1").Value 'get the field value
' ValInput = Replace(ValInput," ","") 'enable this line to strip out all
spaces
varSplit = Split(ValInput, ",", -1, 0) 'split it into an array
Dim intMembers As Integer 'used to identify how many members are in this
particular record/array
intMembers = UBound(varSplit) 'identify highest array member
Dim intCurrent As Integer 'used to loop through members
'insert each part of the original string as a separate record
For intCurrent = 0 To intMembers
SplitVal = varSplit(intCurrent)
strSQL = "INSERT into tblB (Field1) SELECT '" & SplitVal & "'"
CurrentDb.Execute strSQL, dbFailOnError
Next intCurrent
rsTest.MoveNext 'move to next record
Loop 'go back & process the next record
txtStatus = SysCmd(acSysCmdClearStatus) 'clear the status bar
MsgBox "Done" 'notify user that it is done
End Sub