Processing a Flat File with Header and Detail Rows

It’s been tough finding time to write any content recently, but this was a scenario that came up recently both at work and on the forums, so I decided to write it up. It’s another variation on processing flat files (they seem to be endless). This scenario deals with an input file that contains two types of records, headers and details. This processing for this is based off another post (Handling Flat Files with a Varying Number of Columns), so I’m only going to cover what is unique in this scenario.

The input file looks like this:

HL111Header1

DLDetail1

DLDetail2

HL222Header2

DLDetail3

DLDetail4

The rows that begin with HL are header lines, the rows with DL are detail lines. One additional requirement in this example is that the header code (the three digits following the HL in each header line) must be included into each detail row.

The data flow looks like this:

The Flat File Source is bringing in each row as a single column, delimited by CR/LF. The script component has two outputs defined, both as synchronous, and with an exclusion group greater than zero. (For more info on this, please see this post from Jamie Thompson.) One output will include the Header row, and the other will include the Detail rows. Note that each output must have unique column names defined.

The script appears below. It is simply parsing the Line column (that contains the entire row of data), and putting the values into the appropriate columns on each output. Note that the headerCode variable is stored each time a header row is encountered, and then used to populate the code value on the detail output. 

Public Class ScriptMain

    Inherits UserComponent

    Private headerCode As Integer

    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

        If Row.Line.Substring(0, 2) = “HL” Then

            headerCode = CType(Row.Line.Substring(2, 3), Integer)

            Row.DirectRowToHeader()

            Row.HRRecordType = Row.Line.Substring(0, 2)

            Row.HRCode = CType(Row.Line.Substring(2, 3), Integer)

            Row.HRName = Row.Line.Substring(5)

        ElseIf Row.Line.Substring(0, 2) = “DL” Then

            Row.DirectRowToDetail()

            Row.RecordType = Row.Line.Substring(0, 2)

            Row.Code = headerCode

            Row.Name = Row.Line.Substring(2)

        End If

    End Sub
End Class

There it is. Fairly straightforward, but it was quick to put together. The sample files are attached below.