Skip Header Row in a High Performance Powershell Regex Script Block

Question

I received some amazing help from Stack Overflow ... however ... it was so amazing I need a little more help to get to closer to the finish line. I'm parsing multiple enormous 4GB files 2X per month. I need be able to be able to skip the header, count the total lines, matched lines, and the not matched lines. I'm sure this is super-simple for a PowerShell superstar, but at my newbie PS level my skills are not yet strong. Perhaps a little help from you would save the week. :)

Data Sample:

ID         FIRST_NAME              LAST_NAME          COLUMN_NM_TOO_LON5THCOLUMN
 10000000001MINNIE                 MOUSE              COLUMN VALUE LONGSTARTS 
 10000000002MICKLE ROONEY          MOUSE              COLUMN VALUE LONGSTARTS 

Code Block (based on this answer):

#$match_regex matches each fixed length field by length; the () specifies that each matched field be stored in a capture group:
[regex]$match_regex = '^(.{10})(.{50})(.{50})(.{50})(.{50})(.{3})(.{8})(.{4})(.{50})(.{2})(.{30})(.{6})(.{3})(.{4})(.{25})(.{2})(.{10})(.{3})(.{8})(.{4})(.{50})(.{2})(.{30})(.{6})(.{3})(.{2})(.{25})(.{2})(.{10})(.{3})(.{10})(.{10})(.{10})(.{2})(.{10})(.{50})(.{50})(.{50})(.{50})(.{8})(.{4})(.{50})(.{2})(.{30})(.{6})(.{3})(.{2})(.{25})(.{2})(.{10})(.{3})(.{4})(.{2})(.{4})(.{10})(.{38})(.{38})(.{15})(.{1})(.{10})(.{2})(.{10})(.{10})(.{10})(.{10})(.{38})(.{38})(.{10})(.{10})(.{10})(.{10})(.{10})(.{10})(.{10})(.{10})(.{10})(.{10})(.{10})(.{10})(.{10})(.{10})(.{10})(.{10})(.{10})(.{10})(.{10})(.{10})(.{10})(.{10})(.{10})(.{10})(.{10})$'

Measure-Command {
        & { 
          switch -File $infile -Regex  {
            $match_regex {
               # Join what all the capture groups matched with a tab char.
               $Matches[1..($Matches.Count-1)].Trim() -join "`t"
            }
          }
        } | Out-File $outFile
}

Answer 1

You only need to keep track of two counts - matched, and unmatched lines - and then a Boolean to indicate whether you've skipped the first line

$first = $false
$matched = 0
$unmatched = 0
. {
    switch -File $infile -Regex  {
        $match_regex {
            if($first){
                # Join what all the capture groups matched with a tab char.
                $Matches[1..($Matches.Count-1)].Trim() -join "`t"
                $matched++
            }
            $first = $true
        }
        default{
            $unmatched++
            # you can remove this, if the pattern always matches the header
            $first = $true
        }
    }
} | Out-File $outFile

$total = $matched + $unmatched

Answer 2

Using System.IO.StreamReader reduced the processing time to about 20% of what it had been. This was absolutely needed for my requirement.

I added logic and counters without sacrificing much on performance. The field counter and row by row comparison is particularly helpful in finding bad records.

This is a copy/paste of actual code but I shortened some things, made some things slightly pseudo code, so you may have to play with it to get things working just so for yourself.

Function Get-Regx-Data-Format() {
    Param ([String] $filename)

    if ($filename -eq 'FILE NAME') {
        [regex]$match_regex = '^(.{10})(.{10})(.{10})(.{30})(.{30})(.{30})(.{4})(.{1})'
    }
    return $match_regex
}

Foreach ($file in $cutoff_files) {

  $starttime_for_file = (Get-Date)
  $source_file = $file + '_' + $proc_yyyymm + $source_file_suffix
  $source_path = $source_dir + $source_file

  $parse_file = $file + '_' + $proc_yyyymm + '_load' +$parse_target_suffix
  $parse_file_path = $parse_target_dir + $parse_file

  $error_file = $file + '_err_' + $proc_yyyymm + $error_target_suffix
  $error_file_path = $error_target_dir + $error_file

  [regex]$match_data_regex = Get-Regx-Data-Format $file

  Remove-Item -path "$parse_file_path" -Force -ErrorAction SilentlyContinue
  Remove-Item -path "$error_file_path" -Force -ErrorAction SilentlyContinue

  [long]$matched_cnt = 0
  [long]$unmatched_cnt = 0
  [long]$loop_counter = 0
  [boolean]$has_header_row=$true
  [int]$field_cnt=0
  [int]$previous_field_cnt=0
  [int]$array_length=0

  $parse_minutes = Measure-Command {
    try {
        $stream_log = [System.IO.StreamReader]::new($source_path)
        $stream_in = [System.IO.StreamReader]::new($source_path)
        $stream_out = [System.IO.StreamWriter]::new($parse_file_path)
        $stream_err = [System.IO.StreamWriter]::new($error_file_path)

        while ($line = $stream_in.ReadLine()) {

          if ($line -match $match_data_regex) {

              #if matched and it's the header, parse and write to the beg of output file
              if (($loop_counter -eq 0) -and $has_header_row) {
                  $stream_out.WriteLine(($Matches[1..($array_length)].Trim() -join "`t"))

              } else {
                  $previous_field_cnt = $field_cnt

                  #add year month to line start, trim and join every captured field w/tabs
                  $stream_out.WriteLine("$proc_yyyymm`t" + `
                         ($Matches[1..($array_length)].Trim() -join "`t"))

                  $matched_cnt++
                  $field_cnt=$Matches.Count

                  if (($previous_field_cnt -ne $field_cnt) -and $loop_counter -gt 1) {
                    write-host "`nError on line $($loop_counter + 1). `
                                The field count does not match the previous correctly `
                                formatted (non-error) row."
                  }

              }
          } else {
              if (($loop_counter -eq 0) -and $has_header_row) {
                #if the header, write to the beginning of the output file
                  $stream_out.WriteLine($line)
              } else {
                $stream_err.WriteLine($line)
                $unmatched_cnt++
              }
          }
          $loop_counter++
       }
    } finally {
        $stream_in.Dispose()
        $stream_out.Dispose()
        $stream_err.Dispose()
        $stream_log.Dispose()
    }
  } | Select-Object -Property TotalMinutes

  write-host "`n$file_list_idx. File $file parsing results....`nMatched Count = 
  $matched_cnt  UnMatched Count = $unmatched_cnt  Parse Minutes = $parse_minutes`n"

  $file_list_idx++

  $endtime_for_file = (Get-Date)
  write-host "`nEnded processing file at $endtime_for_file"

  $TimeDiff_for_file = (New-TimeSpan $starttime_for_file $endtime_for_file)
  $Hrs_for_file = $TimeDiff_for_file.Hours
  $Mins_for_file = $TimeDiff_for_file.Minutes
  $Secs_for_file = $TimeDiff_for_file.Seconds 
  write-host "`nElapsed Time for file $file processing: 
  $Hrs_for_file`:$Mins_for_file`:$Secs_for_file"

}

$endtime = (Get-Date -format "HH:mm:ss")
$TimeDiff = (New-TimeSpan $starttime $endtime)
$Hrs = $TimeDiff.Hours
$Mins = $TimeDiff.Minutes
$Secs = $TimeDiff.Seconds 
write-host "`nTotal Elapsed Time: $Hrs`:$Mins`:$Secs"