I am managing large CSV files (files ranging from 750 Mb to 10+ Gb), parsing their data into PSObjects, then processing each of those objects based on what is required.

I wrote the following script to churn through these files line by line, filter based on one of the data fields, then close the file. The script works but I feel that it could be faster. For instance, it took 4.5 hours to parse a 389k line csv file. Taking that filesize and timeline, it would take over two and a half days to work through just the sorting and filtering of the data!

Before anyone suggests "use a database!", I'm 100% with you and have started the RFC for adding a database server to our network. Unfortunately our CAB only meets quarterly and this wasn't deemed an emergency. So, I'm left without a database solution for at least 2-3 months.

Anyhow, here is the code:

[void][reflection.assembly]::LoadWithPartialName("Microsoft.VisualBasic")

$source = Get-FileName "C:\users\$env.username\Downloads"

$reader = New-Object Microsoft.VisualBasic.FileIO.TextFieldParser $source

$reader.SetDelimiters(",")

While(!$reader.EndOfData)
{
    $line = $reader.ReadFields()    
    
    $details = [ordered]@{
                            "Plugin ID" = $line[0]
                            CVSS = $line[2]
                            Risk = $line[3]
                            Host = $line[4]
                            Protocol = $line[5]
                            Port = $line[6]
                            Name = $line[7]
                            Description = $line[9]
                            Solution = $line[10]
                            "Plugin Output" = $line[12]
                         }


    $pluginID = $line[0]
    $risk = $line[3]

    if ($risk -eq "Critical" -or $risk -eq "High" -or $risk -eq "Medium" -or $risk -eq "Low")
    {
        $allVulns += New-Object PSObject -Property $details 
    }
    else
    {
        # Filters data into objects based on their plugin ID
        Switch ($pluginID)
        {
            11936 # OS Identification
            {
                $11936 += New-Object PSObject -Property $details
                break
            }
            14272 # Open Ports
            {
                $14272 += New-Object PSObject -Property $details
                break
            }
            20811 # Software Inventory
            {
                $20811 += New-Object PSObject -Property $details
                break
            }
            54615 # Device Type
            {
                $54615 += New-Object PSObject -Property $details
                break
            }
            66334 # Missing OS Patches
            {
                $66334 += New-Object PSObject -Property $details
                break
            }
        }
    }
}

# Close the read file
$reader.Close()

Is there a faster, more efficient way for this code to execute?

Thanks!