VB Script to convert TWO delimted columns to multiple rows.

[ChrisMarin]

I have the following data:

KeyField,listField1,ListField2
123,"122,345,321","2,3,1"
124,"222,221","5,2"

I need to convert it to:

KeyField,ListItem1,ListItem2
123,122,2
123,345,3
123,321,1
124,222,5
124,221,2

I have managed to do just one column resulting in:

KeyField,ListItem1,ListItem2
123,122,"2,3,1"
123,345,"2,3,1"
123,321,"2,3,1"
124,222,"5,2"
124,221,"5,2"

I need the ListField2 to be split out too...


The above result was using a For Each loop:

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Dim keyField As Integer = Row.KeyField
Dim itemList As String = Row.ListField
Dim delimiter As String = ","

If Not (String.IsNullOrEmpty(itemList)) Then

Dim inputListArray() As String = _
itemList.Split(New String() {delimiter}, _
StringSplitOptions.RemoveEmptyEntries)

For Each item As String In inputListArray
With Output0Buffer
.AddRow()
.KeyField = keyField
.ListItem = item
.ListItem2 = Row.ListField2

End With
Next

End If

End Sub

Many thanks in advance,

Chris

 

[ChrisMarin]

I have now managed to sort this with the below code:

Dim keyField As Integer = Row.KeyField
Dim itemList As String = Row.listField1
Dim itemList2 As String = Row.ListField2
Dim delimiter As String = ","

If Not (String.IsNullOrEmpty(itemList)) Then

Dim inputListArray() As String = itemList.Split(New String() {delimiter}, StringSplitOptions.RemoveEmptyEntries)
Dim inputListArray2() As String = itemList2.Split(New String() {delimiter}, StringSplitOptions.RemoveEmptyEntries)
Dim counter As Integer = 0

For Each item As String In inputListArray
With OutputBuffer
.AddRow()
.KeyField = keyField
.ListItem = item
.ListItem2 = inputListArray2.GetValue(counter)
counter = counter + 1
End With
Next
End If