VBScript - Convert 2 delimted colmns to multi rows

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

VBScript - Convert 2 delimted colmns to multi rows

Author

Topic

chrismarin
Starting Member

2 Posts

Posted - 2012-08-10 : 09:05:28

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-10 : 11:04:09

i would have done this in t-sql using execute sql task

see scenario 4

http://visakhm.blogspot.com/2010/01/multipurpose-apply-operator.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

chrismarin
Starting Member

2 Posts

Posted - 2012-08-10 : 11:33:39

Thanks for the reply,

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-11 : 10:46:53

quote:
Originally posted by chrismarin

Thanks for the reply,

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

the t-sql approach i suggested is set based whereas this looks iterative (loop based)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources