Powershell: Replacing strings in csv files leads to "Exception of type 'System.OutOfMemoryException' was thrown."

As @BACON alludes, the core issue here is caused by looping through (likely) several thousand replacements.

Every time the replacement line executes:

$txtsourceFile = $txtsourceFile -replace  $findReplaceitem.$OldIssueKey , $findReplaceitem.$IssueKey

PowerShell first has a chunk of memory for the $txtsourceFile. It allocates a new chunk of memory to store a copy of the data after the text replacements.

This is normally "ok" as you will have one valid chunk of memory with the replacement text, and an "invalid" copy with the original text. Since most people have (relatively) lots of memory, and we normally can handle this "leaking" in .NET by periodically running a garbage collector in the background to "clean up" this invalid data.

The trouble we run into is that when we loop several thousand times rapidly, we generate several thousand copies of the data rapidly as well. You eventually run out of available free memory before the Garbage Collector has a chance to run and clean up the thousands of invalid copies of data (i.e. 3.2GB). See: No garbage collection while PowerShell pipeline is executing

There are a couple of ways to work around this:

Solution 1: The Big and Slow Method and Inefficient Way

If you need to work with the whole file (i.e. across newlines) you can use the same code and manually run the Garbage Collector periodically during the execution to manage the memory "better":

$count = 0

ForEach ($findReplaceItem in $findReplaceList) {
    $txtsourceFile = $txtsourceFile -replace  $findReplaceitem.$OldIssueKey, $findReplaceitem.$IssueKey

    if(($count % 200) -eq 0)
    {
        [System.GC]::GetTotalMemory('forceFullCollection') | out-null
    }
    $count++
}

This does 2 things:

1. Run the Garbage Collection every 200 loops ($count modulus 200).
2. Stop the current execution and force the collection.

Note:

Normally you use:

[GC]::Collect()

But according to Addressing the PowerShell Garbage Collection bug at J House Consulting this doesn't always work when trying to force the collection inside a loop. Using:

[System.GC]::GetTotalMemory('forceFullCollection')

Fully stops execution until the Garbage collection is complete before resuming.

Solution 2: The Faster, More memory efficient method, one line at a time

If you can perform all the replacements one line at a time, then you can use the [System.IO.StreamReader] to stream in the file and process one line at a time and [System.IO.StreamWriter] to write it.

try
{
    $SR = New-Object -TypeName System.IO.StreamReader -ArgumentList $sourceFile.FullName
    $SW = [System.IO.StreamWriter] $outputFullFileName

    while ($line = $SR.ReadLine()) {
        #Loop through Replacements
        ForEach ($findReplaceItem in $findReplaceList) {
            $Output = $line -replace  $findReplaceitem.$OldIssueKey, $findReplaceitem.$IssueKey
        }
        $SW.WriteLine($output)
    }

    $SR.Close() | Out-Null
    $SW.Close() | Out-Null
}
finally
{
    #Cleanup
    if ($SR -ne $null)
    {
        $SR.dispose()
    }
    if ($SW -ne $null)
    {
        $SW.dispose()
    }
}

This should run an order of magnitude faster because you will be working a line at a time and won't be creating thousands of copies of the entire file with every replacement.

I found the answer and comments above very helpful and implemented a solution that is close to the answer here: I split the $findReplaceList in multiple batches (it is around 37000 entries long, i started splitting into 1000) and work on bath by batch with GC in-between. Now i can watch the memory usage climb up during a batch and jump down again when one is done.

With that I found an interesting behavior: the memory issue came still up in a few of batches... So I analysed the findReplaceList further with the following result:

There are cases where there are NO $OldIssueKey in the file..

Can it be that PS then sees that as an empty string and tries to replace all those?