Small Script - Massive Memory consumption, low CPU but takes hours to complete??

PowerShell Help
21

[quote quote=270571]seems still too much anyway.

if you don`t want to post the sample publicly, you can send them to me here

$mail = “cAByAGEAegBvAG4AaQBuAHMAdAByAHUAbQBAAGMAbABvAHUAZABtAGUAdABhAHYAZQByAHMAZQAuAGMAbwBtAA==”

need the ref, and sample of file to process

[/quote]
Hi, I’ve sent over a couple of files via email. They are tiny but it should give you an idea

22

[quote quote=270583]Hmm … I believe you have gone back to your original script which would be:

$file | foreach { ….

I was commenting based on the code provided by prazoninstrum

Again, not sure removing the split will help performance.

[/quote]
Ah, sorry - total crossed wires.

I think I need to find a solution whereby only the 3rd column is brought it to be looked at which will cut down on file size.

Kev

23

Chrissy LeMaire has written some great articles on processing large CSV files.

You can modify her script to do other stuff with the data, you don’t have to upload to a SQL server.

My modified example below just outputs matches in column3 when comparing to a list of data in a text file - similar to what you want to do.

I used the All Countries text file (1.2GB and 10 million+ rows) linked in her article with a random list of 23 countries in my lookup file. It took just under 11 minutes to process 12 million rows.

$elapsed = [System.Diagnostics.Stopwatch]::StartNew() 

$lookups = Get-Content 'E:\Temp\lookup.txt'    

# CSV variables
$csvFile = 'E:\Temp\allcountries.txt'
$csvDelimiter = "`t"
$firstRowColumnNames = $false

$batchSize = 50000

# Create the datatable, and autogenerate the columns.
$datatable = New-Object System.Data.DataTable

# Open the text file from disk
$sr = New-Object System.IO.StreamReader($csvFile)
$columns = (Get-Content $csvfile -First 1).Split($csvdelimiter)

if ($firstRowColumnNames -eq $true) { 
    
    $null = $reader.readLine()

}

foreach ($column in $columns) { 

    $null = $datatable.Columns.Add()
    
}

# Read in the data, line by line
while (($line = $sr.ReadLine()) -ne $null) {
    
    $i++
    $null = $datatable.Rows.Add($line.Split($csvdelimiter))
    
    if (($i % $batchSize -eq 0)) {
        
        Write-Output $datatable.where({$_.column3 -in $lookups}).column3
        Write-Host "$i rows have been processed in $($elapsed.Elapsed.ToString())." 
        $datatable.clear()        
    
    }
    
}
        
# Add in all the remaining rows since the last clear
if($datatable.Rows.Count -gt 0) {

    Write-Output $datatable.where({$_.column3 -in $lookups}).column3
    $datatable.Clear()
    
}

$sr.Close()

Write-Host "$i rows processed."
Write-Host "Total Elapsed Time: $($elapsed.Elapsed.ToString())"

You can modify the data in the data table directly if you want and then export it to CSV.

There are some examples here although unfortunately some of the example images are missing: