DataTable in PowerShell for crazy fast filters
Recently I was working with a 60,000 row CSV and applying several filters to find matching rows. This took between 1.0-1.8 seconds per row. While OK in small scale (few hundred) the script run duration grew to several hours. There had to be a better way.
Enter [System.Data.DataTable]
Native PowerShell collections are simple to create and easy to work with. However, in large scale I find DataTable to perform better. It requires more up front coding to get ready (columns, schema, populate rows) but then runs crazy fast. Sample code below.
Enjoy!
PowerShell
# Load CSV
$coll = Import-CSV "people.csv"
# Create table
$dtPeople = New-Object System.Data.DataTable("people")
$cols = @("userlogin","username","managerlogin","managername","department")
# Schema (columns)
foreach ($col in $cols) {
$dtPeople.Columns.Add($col) | Out-Null
}
# Values (rows)
foreach ($c in $coll) {
$row = $dtPeople.NewRow()
foreach ($col in $cols) {
$row[$col] = $c.$col
}
$dtPeople.Rows.Add($row) | Out-Null
}
# Do we have this user?
$findUser = "TEST1"
# DataView rapid filter
$dvPeople = New-Object System.Data.DataView($dtPeople)
$dvPeople.RowFilter = "UserLogin = '$findUser'"
# Result
if ($dvPeople.Count -gt 0) {
Write-Host "Found" -ForegroundColor Yellow
$dvPeople | ft
} else {
Write-Host "Not Found!" -ForegroundColor Red
}People.CSV
"userlogin","username","managerlogin","managername","department" TEST1,Test Guy1,mgr1,Manager Guy1,IT TEST2,Test Guy2,mgr1,Manager Guy1,IT
