Split 1 row of data into multiple rows 1

[GJParker]

Is it possible when Importing data using a DTS Package to split 1 row of data into multiple rows i.e.

I have a list of consumable stores Items which uses a text field show which cost centres can use the items.

My data looks like this

Code Name Text
ABC1 SCREW Depts#01#02#03
ABC2 NAIL Depts#11SUR#20
ABC3 PUNCH Depts#16

What i need to do is write a row of data for each Code and Dept i.e.

Code Name Dept
ABC1 SCREW 01
ABC1 SCREW 02
ABC1 SCREW 03
ABC2 NAIL 11SUR
ABC2 NAIL 20
ABC3 PUNCH 16

I have written a script which loops through the item text and parses out the departments, but the transformation only writes the last line ti the database, i assume this is because it is just updating the same line.

'**********************************************************************
'  Visual Basic Transformation Script
'************************************************************************

'  Copy each source column to the destination column
Function Main()
'Declare and set required variables
Dim i
Dim HashCount
Dim strDept
Dim strChar

HashCount = 0
strDept = ""

'Loop through the item text 1 character at a time
	For i = 1 to Len( DTSSource("TEXT") )
		strChar = Mid(DTSSource("TEXT"),i,1)
		If strChar = "#" Then
		
		'Only print the line if not the 1st #	
			If strDept <> "" Then		
				DTSDestination("ITM_CODE") = DTSSource("CODE")	
				DTSDestination("ITM_NAME") = DTSSource("NAME")
 				DTSDestination("ITM_DEPT") = strDept
			End If

		'Update variables	
			HashCount = HashCount + 1
			strDept = ""
		
		Else
		'once past the 1st # strat to build dept string
			If HashCount <> 0 Then
				strDept = StrDept & strChar
			End If
		End If
			
	Next 
		'Write the last line
		If strDept <> "" Then		
		 	DTSDestination("ITM_CODE") = DTSSource("CODE")	
			DTSDestination("ITM_NAME") = DTSSource("NAME")
 			DTSDestination("ITM_DEPT") = strDept
		End If

	Main = DTSTransformStat_OK

End Function

can anyone point me in th eright direction to accomplish this

Thanks in advance

Gary Parker
Systems Support Analyst
Manchester, England

 

[MacLeod72]

The way I found to do this is to return "DTSTransformStat_SkipFetch" from Main to continue processing the same line, and use Global variables to hold my position. This particular code splits a row into 125 character segments, but it should give you a frame of reference for '#' parsing. I used the multiphase Transform Data Task so I could use the pre-source phase to set the global variable initially. I suppose this could have been done with an ActiveX task as well. By using the ActiveX task, I would not have needed to use the phases of the Transform Data Task. Anyhow, this is the code I used in my ActiveX transformation.

Function Main()
    DTSDestination("shortval") = Mid( DTSSource("val") , DTSGlobalVariables("SPLIT_POS") , 125 )

    If (DTSGlobalVariables("SPLIT_POS") + 125 > Len(DTSSource("val"))) Then
        DTSGlobalVariables("SPLIT_POS") = 1
        Main = DTSTransformStat_OK
    Else
        DTSGlobalVariables("SPLIT_POS") = DTSGlobalVariables("SPLIT_POS") + 125
        Main = DTSTransformStat_SkipFetch
    End If
End Function

Function PreSourceMain()
    DTSGlobalVariables("SPLIT_POS") = 1
    PreSourceMain = DTSTransformstat_OK
End Function

Hope this helps!

--Rob

 

[JVZ]

The problem is that you are not really looping. Check out the code sample below and the link provide. Let me know if you need anymore help.

Hope this help you.

---- Incoming Data (Source) ----
Code    Name    Text
ABC1    SCREW   Depts#01#02#03
ABC2    NAIL    Depts#11SUR#20
ABC3    PUNCH   Depts#16

' ------------------------------------------------------------------------
'  Purpose: 	To parse and insert data from a tab 
'			delimited file
'
'  Comments: 	
'		This task uses the DTSTransformStatus from
'		the Transform Data task to determine weather
'		or not to process the current row again. 
'		This task replies on a global variable: 
'		gv_ArrayElementNumber to hold the  position
'		within the array. 
'
'		The code below is based off of the following
'		article found of [URL unfurl="true"]www.sqlDTS.com[/URL]
'				
'			Article Title:	
'		Processing The Same Row More Than Once
'			Article URL:
'		[URL unfurl="true"]http://www.sqldts.com/default.aspx?266[/URL]
'			Author Name:	
'		By Allan Mitchell
'
'  Inputs: 	gv_ArrayElementNumber -
'			Holds the element/position within
'			the array
'
'  Returns:	DTSTransformStat_SkipFetch -
'			If the current row needs to be processed
'			again
'
'		DTSTransformStat_SkipInsert -
'			If the current row does not need to written
'			into the database
' -------------------------------------------------------------------------

Function Main()

	Dim DeptArray

	' The Split function will separates the string into 
	'substrings and will creates a  one-dimensional 
	'array where each substring is an element. 
	DeptArray  = Split(DTSSource("Text"),"#")
	
	' Check to see if all the "Dept" have been imported
	If DTSGlobalVariables("gv_ArrayElementNumber").Value <= Ubound(DeptArray) Then
	
		DTSDestination("Code") 		= DTSSource("Code")
		DTSDestination("Name") 		= DTSSource("Name")
		DTSDestination("Dept")		= DeptArray(DTSGlobalVariables("gv_ArrayElementNumber").Value)
		
		' Increase the element number so to pick 
		' up the next array element the next time 
		' we process the row
		DTSGlobalVariables("gv_ArrayElementNumber").Value = DTSGlobalVariables("gv_ArrayElementNumber").Value + 1
		
		' Set the return value to DTSTransformStat_SkipFetch 
		'forces DTS to reprocess the same row.
		Main = DTSTransformStat_SkipFetch
		
	Else
	
		' If "gv_ArrayElementNumber" equals the upper limit in 
		' the array then reset the element/position  number 
		' to 1 (we set this to one is because the first element 
		' in the array is "dept" which should  not be imported 
		' according to your post). Theb skip the insert and 
		' move to the next line. 
		DTSGlobalVariables("gv_ArrayElementNumber").Value = 1
		
		' Set The return status to DTSTransformStat_SkipInsert 
		' so that DTS  will not insert a NULL row into the table
		Main = DTSTransformStat_SkipInsert

	End If 
		
End Function

---- Output ----
Code    Name    	Dept		
ABC1	SCREW		01
ABC1	SCREW		02
ABC1	SCREW		03
ABC2	NAIL		11SUR
ABC2	NAIL		20
ABC3	PUNCH		16

 

[GJParker]

Thanks guys easy when you know how.

JVZ

You're code works fine apart from it returns the first occurrence of Depts form the array

i.e.

---- Output ----
Code    Name        Dept     
ABC1    SCREW        Depts
ABC1    SCREW        01
ABC1    SCREW        02
ABC1    SCREW        03
ABC2    NAIL        11SUR
ABC2    NAIL        20
ABC3    PUNCH        16

How do I stop this ?

Gary Parker
Systems Support Analyst
Manchester, England

 

[JVZ]

hummm.... Make sure when you create the global variable in DTS that you set it to 1 and not 0. This is due to the way you input string is formatted.

Original String: Depts#01#02#03

After the split function

DeptArray  = Split(DTSSource("Text"),"#")

your string becomes something like this:

DeptArray (0) = Depts
DeptArray (1) = 01
DeptArray (2) = 02
DeptArray (3) = 03

So if you set gv_ArrayElementNumber to 0 at first then DTS will at import/write to the DB "Depts"

Hope this helps?

 

[GJParker]

Thanks for the help JVZ, that did the trick.

Gary Parker
Systems Support Analyst
Manchester, England

 

[Bman2]

Just wanted to take a minute to thank you for this post
The info has helped me develop a process which has been haunting me for a week. Thanks