Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Values from variable length flat files Expand / Collapse
Author
Message
Posted Thursday, April 3, 2008 10:50 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, October 17, 2013 2:02 PM
Points: 37, Visits: 114
I have a variable length flat file that is defined by a "record type" value (first two characters in each row), for example:

01value1value2value3valueINEEDvalue4value5
02newval1newval2newval3
03anotherval1
04differentval1differentval2differentval3differentval4differentval5differentval6

I can read the source file using a "flat file source", use a "script component" to break the file row into 2 columns ( rectype and rowdata), I can then use a "conditional split" to push the data to different destination sources (tables - flat files).

The problem is that the file has to be processed sequentially, there is a value, or column, in the "01" record type rows that I need to relate the following record type rows to the "parent" (01 record type rows). How can I grab a value from one column in the "01" type rows, and add that value as an new column in the Non 01 rows?

column I need is listed as "valueINEED"


example output:


01value1value2value3valueINEEDvalue4value5
02newval1newval2newval3valueINEED
03anotherval1valueINEED
04differentval1differentval2differentval3differentval4differentval5differentval6valueINEED


Post #479386
Posted Thursday, April 3, 2008 12:00 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:20 PM
Points: 7,120, Visits: 15,014
Michael Covington (4/3/2008)
I have a variable length flat file that is defined by a "record type" value (first two characters in each row), for example:

01value1value2value3valueINEEDvalue4value5
02newval1newval2newval3
03anotherval1
04differentval1differentval2differentval3differentval4differentval5differentval6

I can read the source file using a "flat file source", use a "script component" to break the file row into 2 columns ( rectype and rowdata), I can then use a "conditional split" to push the data to different destination sources (tables - flat files).

The problem is that the file has to be processed sequentially, there is a value, or column, in the "01" record type rows that I need to relate the following record type rows to the "parent" (01 record type rows). How can I grab a value from one column in the "01" type rows, and add that value as an new column in the Non 01 rows?

column I need is listed as "valueINEED"


example output:


01value1value2value3valueINEEDvalue4value5
02newval1newval2newval3valueINEED
03anotherval1valueINEED
04differentval1differentval2differentval3differentval4differentval5differentval6valueINEED




You don't necessarily need a script for that. Assuming there's a way to identify the pattern (your test data doesn't give me a hint on how to), take a look at what I posted over in this thread.

http://www.sqlservercentral.com/Forums/FindPost478822.aspx

You can then use the derived column transform to parse the stuff, and then your data "stays" with the identity column.


----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Post #479438
Posted Thursday, April 3, 2008 12:45 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, October 17, 2013 2:02 PM
Points: 37, Visits: 114
It was a simple fix... All I did was create a public property in the script component, add an if statement to test the record type, if it was "01" I set the property value to equal the id number that I needed from that row, and then pass that value back with the other columns.


Dim strRow As String
Dim strRowCaseID As String

Try
If (Row.RowContent.Length > 0) Then

strRow = Row.RowContent.ToString()

If (strRow.Length > 0) Then
Row.RecType = strRow.Substring(0, 2)
Row.RowData = Row.RowContent.ToString()
If (strRow.Substring(0, 2) = "01") Then
_rowCaseNumber = strRow.Substring(17, 4) + strRow.Substring(21, 3).TrimEnd() + strRow.Substring(24, 6)
End If
Row.RowCaseID = _rowCaseNumber.ToString()

End If

End If
Catch ex As Exception
System.Windows.Forms.MessageBox.Show(Row.RowContent)
End Try


Post #479473
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse