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:


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:

HeaderDetail Data Flow

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.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.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.

This entry was posted in Uncategorized. Bookmark the permalink.

Comments are closed.