Powershell - capture all non-blank lines between key words

Your script needs only a few tweaks:

• Add the full line, reflected in $_, to your $Flowerbox list
• Also do so in the 'SELECT' and 'FROM ' branches.

# ...
$Flowerbox.Clear()  # empty the list for the next run
$treat = $false
switch -Regex -CaseSensitive -File $CBLFile.FullName {
  '^\s*SELECT\s' { 
    Write-Host "Found Select" -ForegroundColor DarkYellow
    # start collecting lines from here    
    $treat = $true
    $Flowerbox.Add($_) # Add this line to the list.
    continue # So that the FROM branch needn't also be checked.
  }
  '^\s*FROM\s' {
    Write-Host "Found From" -ForegroundColor DarkRed
    if ($treat) {
      $Flowerbox.Add($_)  # Add this line to the list.
      # stop collecting Flowerbox lines and output what we already have
      # output an object with the two properties you need
      [PsCustomObject]@{
        Program     = $CBLFile.Name  # or $CBLFile.FullName
        Description = $Flowerbox -join [environment]::NewLine
      }
      $Flowerbox.Clear()  # empty the list for the next run
      $treat = $false
    }
  }
  default {
    # When inside a block of interest, add all non-blank lines.
    if ($treat -and $_ -match '\S') {
      $Flowerbox.Add($_)  # Add this line to the list.
    }
  }
}
# ...

Note that, in the interest of minimizing the risk of false positives:

• -CaseSensitive was added to the switch statement for case-sensitive matching.

• The regexes were made more robust to prevent accidental substring matching and to tolerate whitespace variations (spaces vs. tabs).

Also, to exclude blank (empty and all-whitespace) lines, condition -match '\S' was added to the default branch.

You could try using this function to find all those files that contain those fragments starting on SELECT and ending on FROM, the function would output objects with the captured fragment (Value), Index, Path and Length. The regex is likely to be improved, but this worked for me with some test files.

NOTE: this answer requires that the files fit in memory, as the functions reads the files as a whole string.

using namespace System.IO

function Find-String {
param(
    [parameter(ValueFromPipeline, Mandatory)]
    [Alias('PSPath')]
    [FileInfo]$Path,
    [parameter(Mandatory, Position = 0)]
    [regex]$Pattern,
    [switch]$AllMatches
)

    process {
        $content = [File]::ReadAllText($Path)
        $match = if($AllMatches.IsPresent) {
            $Pattern.Matches($content)
        }
        else {
            $Pattern.Match($content)
        }

        if($match.Success -notcontains $true) {
            return
        }

        foreach($m in $match) {
            [pscustomobject]@{
                Path   = $path.FullName
                Value  = $m.Value
                Index  = $m.Index
                Length = $m.Length
            }
        }
    }
}

Get-ChildItem -Path 'C:\src' -Filter '*.pl1m' -Recurse |
    Find-String -Pattern '(?msi)SELECT.*?FROM.*?$' -AllMatches |
    Export-Csv path\to\export.csv -NoTypeInformation

Output with one particular file looks like this:

Path   : /home/user/Documents/test.txt
Value  : SELECT  FIL_ID_NUM,             00481002
         ...
         ...
         ...
         FROM SOMETABLE
Index  : 11
Length : 449

Path   : /home/user/Documents/test.txt
Value  : SELECT  FIL_ID_NUM,             00481002
         ...
         ...
         ...
         FROM SOMEOTHERTABLE
Index  : 501
Length : 522

I love Santiago Squarzon's approach, but his code doesn't seem to remove blank lines. This code is a little bit of a hybrid of everyone else's code. I learned a lot about switch by doing this, amazing what you can get switch to do once you get your head wrapped around what it is doing.

function Process-Line {
    Process {
        ($Input -Split ',' | ForEach-Object { $_.Trim() } ) -join ', ' 
    }
}
function Process-File {
    Process {
        $Inside = $false;
        switch -Regex -File $Input.FullName {
            '^\s*$' { continue }
            '(?i)^\s*SELECT\s.*$' { $Inside = $true }
            '^.+$'                { if($Inside) { $Matches[0] | Process-Line } }
            '(?i)^\s*from\s.*$'   { $Inside = $false }
            default { continue }
        }
    }
}
$Result = Get-ChildItem -Path 'D:\Sync\Projects\ps1\Crypto' -Filter '*.pl1m' -Recurse | Process-File
$Result | Out-File -FilePath 'D:\Sync\Projects\ps1\Crypto\SQL_Desc.csv'

Taking your data exactly as is, it produced this, which I'm nearly 100% sure isn't exactly what you want. If you give some more details on the exact layout of the CSV, and if you are sure the source data is missing commas, I think we can figure a way to beat it into shape. But we need to get it into a uniform pattern before packaging it in a PSCustomObject and piping it to Export-CSV.

SELECT   FIL_ID_NUM, 00481002
LAU_CRE_DTS, 00482002
LAU_CD, 00483002
LAU_EFF_DT, 00484002
LAU_EFF_TIM, 00485002
LAU_EXP_DT, 00486002
LAU_EXP_TIM, 00487002
LAU_SERV_REAS1_CD, 00488002
LAU_SERV_REAS2_CD, 00489002
LAU_SERV_REAS3_CD, 00489102
LAU_CARE_IND, 00489202
SEG_UPDT_LOCK_NUM, 00489302
LAU_AGRMT_PER_NAM, 00489402
LAU_AGRMT_DSGT_NAM, 00489502
LAU_AGRMT_DIR_TXT, 00489602
LAU_TERMS_TXT                                           00489702
00520002
FROM TLEGAL

Also, do you want:

1. A header line in the csv file? And if so, what column headers do you want?
2. Are there really numbers, such as the last number (00520002), on a line by themselves?

3. Do you want the "SELECT" and "FROM TLEGAL" to appear? They don't fit well in the CSV format.