# Filtering and processing computers listed in a CSV file

I am trying to make my script run a lot faster. I have a csv that has about 330k of lines on it that I plan on running my script with and it has been taking an extremely long time. I just did ran the script and it 50 mins to generate the new csv. What the code does is it adds 2 more fields and filters only the administrators and The windows operating systems. Below are before and after pictures of the output.

$csv = Import-Csv 'U:\Local Group Members.csv' | Where-Object {($_.Name0 -eq "administrators") -and ($_.caption0 -match "Microsoft Windows 10 Enterprise|Microsoft Windows 7 Enterprise|Microsoft Windows 7 Professional|Microsoft Windows 8 Enterprise|Microsoft Windows 8 Pro|Microsoft Windows 8.1 Enterprise|Microsoft Windows 8.1 Pro")} | Select-Object "Netbios_name0", "Name0", "Account0","category0","Domain0","Unique Account Name","Type0","caption0", "Excluded" #Modify each line Foreach ($row in $csv) { If ($row.Type0 -eq 'Domain') {
$row."Unique Account Name" = "$($row.Domain0) -$($row.Account0)" If ($row."Unique Account Name" -in @('ACCOUNTS - DODSCAN'.'ACCOUNTS - Domain Admins','ACCOUNTS - LADM_WS_Admins','ACCOUNTS - Tech Enterprise'))
{$row."Excluded" = "True"} Else {$row."Excluded" = "False"}
}
Else {
$row."Unique Account Name" = "$($row.Netbios_name0) -$($row.Account0)" If ($row."Account0" -in @('esrxadm1n_esi','#Update','medco_tech','medco_admin'))
{$row."Excluded" = "True"} Else {$row."Excluded" = "False"}
}
Write-Host $row."Unique Account Name" Write-Host$row."Excluded"
}

#Export CSV
$csv | Export-Csv U:\$(get-date -f yyyy-MM-dd-hh-mm)-TestOutput1.csv -NoTypeInformation


Can someone help me make my script faster?

• How long is an extremely long time? It'd be nice if you also added a sample of your CSV and told us what you are doing with it. Jun 15, 2017 at 16:56
• @t3chb0t I just revised it, it took about 50 mins to generate the csv. The original file is about 33mb. I added pictures as well. Jun 15, 2017 at 17:57
• Ok, 50min for only 330k rows and a 30mb large file is IMO really way too much. Can you explain what the filtering is about? I'm pretty sure this is the bottleneck. Jun 15, 2017 at 18:00
• @t3chb0t In the Name0 column we just want the Administrators so we are filtering just for those. The file comes with a bunch of other names in the Name0 column so we are filtering those first. Then we are also filtering the operating systems that we need, which are provided above. Otherwise they also have servers and we just need the operating systems. Jun 15, 2017 at 18:06
• Take a look at this Stack Overflow question and answers: stackoverflow.com/questions/4780305/… These are methods you can use to "profile" your code to determine what is the slowest part. Jun 15, 2017 at 18:53

As far as performance goes there are a few places to make changes.

## Make better use of the pipeline

You read in the csv to a variable then process. Convert the script to process these records directly. There would be performance increases this way. Insanely brief example...

Import-Csv 'U:\Local Group Members.csv' |
Where-Object {($_.Name0 -eq "administrators")} Select-Object "Netbios_name0", "Name0" | ForEach-Object { # Something happens. } | Export-Csv ....  ## Wasted console output I cannot imagine you are checking ~1000's of lines of console output. You are wasting processing time but putting that data to console. Remove the write-host lines. Even if you have this running as a task. ## Where-Object is slow This is really easy to use but is not very performance friendly. I can tell you have at least PowerShell 3.0 but if you have at least v4 then you have access to the where and foreach methods of collections. The syntax as you will see is very similar but there should be performance boons here as well. Example: (Import-Csv -Path C:\temp\devices.csv).where({$_.DeviceType -ne "BlackBerry"})


I think you are already doing this but order your conditions in order of least likely to most likely. PowerShell will not evaluate the RHS of -and if the LHS is already false.

I tried to improve the logic in your regex but it was at a cost of performance everytime. As redundant as that string is it seems to be a faster choice given other options.