PowerShell script to read line by line large CSV files

Question

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!

EDIT: Some sample data by request! This is two of the plugins I'm parsing that don't have endless amounts of Plugin Output data.

Plugin ID,CVE,CVSS,Risk,Host,Protocol,Port,Name,Synopsis,Description,Solution,See Also,Plugin Output
11936,,,None,GCAB-L7-449096R,tcp,0,OS Identification,It is possible to guess the remote operating system.,"Using a combination of remote probes (e.g., TCP/IP, SMB, HTTP, NTP,
SNMP, etc.), it is possible to guess the name of the remote operating
system in use. It is also possible sometimes to guess the version of
the operating system.",n/a,,"
Remote operating system : Microsoft Windows 7 Enterprise Service Pack 1
Confidence level : 100
Method : SMB


The remote host is running Microsoft Windows 7 Enterprise Service Pack 1"
14272,,,None,GCAB-L7-449096R,udp,53125,netstat portscanner (SSH),Remote open ports are enumerated via SSH.,"This plugin runs 'netstat' on the remote machine to enumerate open
ports.

See the section 'plugins options' about configuring this plugin.",n/a,https://en.wikipedia.org/wiki/Netstat,Port 53125/udp was found to be open

Answer 1

It might be a little hard to make a suggestion about this as I cannot see how the data is being used beyond this. However I can point out the large performance flaw in your logic.

Building arrays with +=

You have multiple lines of code that go like this:

$20811 += New-Object PSObject -Property $details

What is actually happening there is PowerShell is destroying the array $20811 and creating a new one that is one element larger to house the data on the right hand side. For small operations this performance hit is negligible. However you will certainly feel it when you get into the thousands of elements. You are rebuilding new arrays with every operation.

Consider array lists

I would instead just create all of the custom objects in one pass into one large variable instead. Then you could use Where-Object to process the groups of rows as you see fit. Or you can still keep them as separate objects. Either way I would use an arraylist instead.

$11936 = New-Object System.Collections.ArrayList

# truncated ....
11936 # OS Identification
{
    [void]($11936.Add($details))
    break
}

The [void] cast suppresses the output created from the Add method

This should be significantly faster.

Read the CSV as a database

There are methods to read the CSV as a database as well. The one I tested was using the Microsoft.ACE.OLEDB.12.0 provider. This method allows you to use SQL statements against the CSV file. I don't really have the time to properly benchmark this but this should be faster than your current method as well. Consider a simple example using mock "employee" data:

@"
id,first_name,last_name,email,ip_address
1,Edward,Richards,[email protected],201.133.112.30
2,Jimmy,Scott,[email protected],103.231.149.144
3,Marilyn,Williams,[email protected],52.180.157.43
4,Frank,Morales,[email protected],218.175.165.205
5,Chris,Watson,[email protected],75.251.1.149
6,Albert,Ross,[email protected],89.56.133.54
7,Diane,Daniels,[email protected],197.156.129.45
8,Nancy,Carter,[email protected],75.162.65.142
9,John,Kennedy,[email protected],85.35.177.235
10,Bonnie,Bradley,[email protected],255.67.106.193
"@ | Set-Content .\test.csv 

$conn = New-Object System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source='C:\Users\Cameron';Extended Properties='Text;HDR=Yes;FMT=Delimited';")
$cmd=$conn.CreateCommand()
$cmd.CommandText="Select * from test.csv where first_name like '%n%'"
$conn.open()
$data = $cmd.ExecuteReader()

$data | ForEach-Object{
    [pscustomobject]@{
        id=$_.GetValue(0) 
        first_name=$_.GetValue(1) 
        last_name=$_.GetValue(2)
        ip_address=$_.GetValue(4)
    }
}

$cmd.Dispose()
$conn.Dispose()

I have a SQL statement in there that will return all the records where the first name has a "n" in it. Using the field indecies we build a custom psobject that gets sent down the pipe.

Note that the source in the connection string is the folder that contains the csv file. And the table in the SQL statement is the CSV file name.