I'm trying to create an Excel workbook, then populate the cells with data found from searching many txt files.

I read a file and extract all comments AFTER I find "IDENTIFICATION DIVISION" and BEFORE I find "ENVIRONMENT DIVISION" I then populate two cells in my excel workbook. cell one if the file and cell two is the comments extracted.

I have 256GB of memory on the work server. less than %5 is being used before Powershell throws the memory error.

Can anyone see where I'm going wrong?

Thanks, -Ron

$excel = New-Object -ComObject excel.application
$excel.visible = $False
$workbook = $excel.Workbooks.Add()
$diskSpacewksht= $workbook.Worksheets.Item(1)
$diskSpacewksht.Name = "XXXXX_Desc"
$col1=1
$diskSpacewksht.Cells.Item(1,1) = 'Program'
$diskSpacewksht.Cells.Item(1,2) = 'Description'

$CBLFileList = Get-ChildItem -Path 'C:\XXXXX\XXXXX' -Filter '*.cbl' -File -Recurse
$Flowerbox = @()

ForEach($CBLFile in $CBLFileList) {
    $treat = $false
    Write-Host "Processing ... $CBLFile" -foregroundcolor green      
    Get-content -Path $CBLFile.FullName |
    ForEach-Object {
        if ($_ -match 'IDENTIFICATION DIVISION') {
#             Write-Host "Match IDENTIFICATION DIVISION" -foregroundcolor green      
            $treat = $true
        }
        if ($_ -match 'ENVIRONMENT DIVISION') {
#             Write-Host "Match ENVIRONMENT DIVISION" -foregroundcolor green 
             $col1  
             $diskSpacewksht.Cells.Item($col1,1) = $CBLFile.Name
             $diskSpacewksht.Cells.Item($col1,2) = [String]$Flowerbox
             $Flowerbox = @()
             continue
        }
        if ($treat) {
            if ($_ -match '\*(.{62})') {
                Foreach-Object {$Flowerbox  = $matches[1]   "`r`n"}
         $treat = $false
            }
        }
    }
}

$excel.DisplayAlerts = 'False'
$ext=".xlsx"
$path="C:\Desc.txt"
$workbook.SaveAs($path) 
$workbook.Close
$excel.DisplayAlerts = 'False'
$excel.Quit()

CodePudding user response：

Not knowing what the contents of the .CBL files could be, I would suggest not to try and do all of this using an Excel COM object, but create a CSV file instead to make things a lot easier.
When finished, you can simply open that csv file in Excel.

# create a List object to collect the 'flowerbox' strings in
$Flowerbox = [System.Collections.Generic.List[string]]::new()
$treat = $false

# get a list of the .cbl files and loop through. Collect all output in variable $result
$CBLFileList = Get-ChildItem -Path 'C:\XXXXX\XXXXX' -Filter '*.cbl' -File -Recurse
$result = foreach ($CBLFile in $CBLFileList) {
    Write-Host "Processing ... $($CBLFile.FullName)" -ForegroundColor Green
    # using switch -File is an extremely fast way of testing a file line by line.
    # instead of '-Regex' you can also do '-WildCard', but then add asterikses around the strings
    switch -Regex -File $CBLFile.FullName {
        'IDENTIFICATION DIVISION' { 
            # start collecting Flowerbox lines from here    
            $treat = $true
        }
        'ENVIRONMENT DIVISION' {
            # stop colecting 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 {
            # as I have no idea what these lines may look like, I have to
            # assume your regex '\*(.{62})' is correct..
            if ($treat -and ($_ -match '\*(.{62})')) { 
                $Flowerbox.Add($Matches[1])
            }
        }
    }
}

# now you have everything in an array of PSObjects so you can save that as Csv
$result | Export-Csv -Path 'C:\Desc.csv' -UseCulture -NoTypeInformation

Parameter -UseCulture ensures you can double-click the file so it will open correctly in your Excel

You can also create an Excel file from this csv programmatically like:

$excel = New-Object -ComObject Excel.Application 
$excel.Visible = $false
$workbook = $excel.Workbooks.Open('C:\Desc.csv')
$worksheet = $workbook.Worksheets.Item(1)
$worksheet.Name = "XXXXX_Desc"

# save as .xlsx
# 51 ==> [Microsoft.Office.Interop.Excel.XlFileFormat]::xlWorkbookDefault
# see: https://docs.microsoft.com/en-us/office/vba/api/excel.xlfileformat
$workbook.SaveAs('C:\Desc.xlsx', 51) 

# quit Excel and remove all used COM objects from memory
$excel.Quit()
$null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($worksheet)
$null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbook)
$null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()