# PowerShell script to read line by line large CSV files

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
}
}
}
}

# 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,erichards0@businessweek.com,201.133.112.30 2,Jimmy,Scott,jscott1@clickbank.net,103.231.149.144 3,Marilyn,Williams,mwilliams2@chicagotribune.com,52.180.157.43 4,Frank,Morales,fmorales3@google.ru,218.175.165.205 5,Chris,Watson,cwatson4@ed.gov,75.251.1.149 6,Albert,Ross,aross5@abc.net.au,89.56.133.54 7,Diane,Daniels,ddaniels6@washingtonpost.com,197.156.129.45 8,Nancy,Carter,ncarter7@surveymonkey.com,75.162.65.142 9,John,Kennedy,jkennedy8@tumblr.com,85.35.177.235 10,Bonnie,Bradley,bbradley9@dagondesign.com,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.

• Once all the arrays are created I call a different script for each object and parse the various columns for whatever data the plugin captures (see the original post for recently added sample data). The actual creation of the reports is of acceptable speed and certainly a lesser concern at the moment for me. I am going to modify the script to use your suggestion of an ArrayList though. I've only used PS for about a month so I'm still learning. This script was put together because the C-level people needed something to look at and it fell to me to give them something. – Tchotchke Sep 12 '16 at 15:28
• Reading the CSV as s database via OleDb seems to very smart performance wise. Chrissy LeMaire used the same technique to import a CSV to a SQL server DB: blog.netnerds.net/2015/01/… – Adamar Sep 12 '16 at 16:04
• @Matt, thanks for the suggestion of .Add()! Sped the code up from 4.5 hours to a little over 100 seconds! I have some downstream changes to make now but those are easy-peasy. Thanks again! – Tchotchke Sep 13 '16 at 16:23
• @Tchotchke Glad to help – Matt Sep 13 '16 at 16:38