Powershell CSV script Faster

Question

I am looking to make my script a lot faster and giving me an output faster. I am dealing with large sets of data in my csv, and it takes around an hour if I put in the full file to complete with the script.

 $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 anyone help me make it faster?

This would be a better post on CodeReview rather than StackOverflow. — BenH
– BenH, Commented Jun 15, 2017 at 15:31
That isn't all that large. Perhaps the Write-Host commands could write to a file instead. In any event, posting the question on Code Review rather than here does sound like a good suggestion. I don't know enough PowerShell to answer your question. — John Coleman
– John Coleman, Commented Jun 15, 2017 at 15:38
Even 330k isn't all that much. Nothing that explains 60 minutes of run-time for code which seems to be O(n) in the number of rows. Printing to a console is slow. If the bottleneck isn't all of those Write-Host commands then I have no idea what it is. Hopefully someone at Code Review (where working code is improved) would know. — John Coleman
– John Coleman, Commented Jun 15, 2017 at 15:46

thepip3r · Accepted Answer · 2017-06-15 17:29:06Z

1

Examples of what I was talking about in comments:

$t = New-Object System.Timers.Timer
$t.Start()

$arr1 = @('ACCOUNTS - DODSCAN'.'ACCOUNTS - Domain Admins','ACCOUNTS - LADM_WS_Admins','ACCOUNTS - Tech Enterprise')
$arr2 = @('esrxadm1n_esi','#Update','medco_tech','medco_admin')

$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"
Write-Host "Took $($t.Elapsed)ms to load and filter the CSV"

#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 $arr1)
         {$row."Excluded" = "True"}
    Else {$row."Excluded" = "False"}        
}
Else {
    $row."Unique Account Name" = "$($row.Netbios_name0) - $($row.Account0)"
    If ($row."Account0" -in $arr2)
         {$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

answered Jun 15, 2017 at 17:29

thepip3r

2,9757 gold badges34 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

MountaindewKing Over a year ago

I tried yours and when I ran it, it still took 54 minutes for the whole file. The one I did takes 49

thepip3r Over a year ago

What was the emitted time for the file load?

MountaindewKing Over a year ago

As in from creation of the file to the end of the script?

thepip3r Over a year ago

As in, I showed you how to create a timer object so you could see how you might be able to time out the different sections of your script. The example I gave was to emit the time it took immediately after you load and filter the CSV. My suspicion is that this will be the majority of the time. You can see it in the "Write-Host" immediately following your $csv assignment.

Esperento57 · Accepted Answer · 2017-06-16 15:03:33Z

1

And if you put all in the same line like this?

$tab1 = @('ACCOUNTS - DODSCAN', 'ACCOUNTS - Domain Admins','ACCOUNTS - LADM_WS_Admins','ACCOUNTS - Tech Enterprise')
$tab2 = @('esrxadm1n_esi','#Update','medco_tech','medco_admin')

Import-Csv 'U:\Local Group Members.csv' | Select "administrators", "Netbios_name0", "Name0", "Account0","category0","Domain0","Unique Account Name","Type0","caption0", "Excluded" | %{

 if ($_.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")
 {
    If ($_.Type0 -eq 'Domain') 
    { 
        $_."Unique Account Name" = "$($_.Domain0) - $($_.Account0)"
        $_."Excluded" = $_."Unique Account Name" -in $tab1   
    }
    Else 
    {
        $_."Unique Account Name" = "$($_.Netbios_name0) - $($_.Account0)"
        $_."Excluded" = $_."Account0" -in $tab2
    }

  $_

}

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

edited Jun 16, 2017 at 15:03

answered Jun 15, 2017 at 17:35

Esperento57

17.6k6 gold badges42 silver badges47 bronze badges

21 Comments

MountaindewKing Over a year ago

Unfortunately yours did not run when I put it in the powershell.

MountaindewKing Over a year ago

I added in the Unique Account Name, so it is giving me the following error because it is saying it is not in there.Exception setting "Unique Account Name": "The property 'Unique Account Name' cannot be found on this object. Verify that the property exists and can be set." At line:10 char:55 + $_."Unique Account Name" = "$($_.Domain0) - $($_.Account0)" + ~~~~~~~~~~~ + CategoryInfo : NotSpecified: (:) [], SetValueInvocationException

Esperento57 Over a year ago

Ok you have created this property into your select, i dont have seen that. Try my modified code please

MountaindewKing Over a year ago

I just tried it and it ran but it did not produce out any output into my CSV. It stayed at 0 kb

Esperento57 Over a year ago

Sorry, try again

|

Zach Alexander · Accepted Answer · 2017-06-15 16:43:42Z

0

Instead of having 2 Foreach blocks looping on the same object ($row in $csv), why don't you just combine them? If your CSV is large, why go through it twice?

Foreach ($row in $csv) {
If ($row.Type0 -eq 'Domain') { 
    $row."Unique Account Name" = "$($row.Domain0) - $($row.Account0)" 
    Write-Host $row."Unique Account Name"
} Else { 
    $row."Unique Account Name" = "$($row.Netbios_name0) - $($row.Account0)"
 }
If (($row.Type0 -eq 'Local') -and ($row.Account0 -in @('esrxadm1n_esi','#Update','medco_tech','medco_admin')) -and ($row."Unique Account Name" -in @('ACCOUNTS - DODSCAN'.'ACCOUNTS - Domain Admins','ACCOUNTS - LADM_WS_Admins','ACCOUNTS - Tech Enterprise')))  { 
    $row."Excluded" = "True" 
    Write-Host $row."Excluded"
 } Else { 
    $row."Excluded" = "False" 
 }
}

Also, where you do this match at the top:

($_.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")

Would you be able to reduce it to $_.caption0 -match "Microsoft Windows \d{1,2}" without it picking up rows you don't want, or is that out of the picture?

answered Jun 15, 2017 at 16:43

Zach Alexander

4034 silver badges9 bronze badges

1 Comment

MountaindewKing Over a year ago

That is kind of out of the picture since we do not want the Microsoft Windows Servers in there

TessellatingHeckler · Accepted Answer · 2017-06-15 19:17:41Z

I have a ~600k line CSV of event logs here, and I can test:

import-csv events.csv | select * | foreach { $_ } | Export-csv out.csv

and it takes 5 minutes 15 seconds (~180Mb / on an SSD). With a write-host it pushes up to 9 mins 36 seconds. So it's maybe something about your CSV being HUGE (lots of info per row?), loading it all into memory at once? or the write-host lines.

Try changing it to a streaming approach:

Import-Csv 'U:\Local Group Members.csv' |
    Where-Object {

        ($_.Name0 -eq "administrators") -and
        ($_.caption0 -match "Microsoft Windows (10 Enterprise|7 Enterprise|7 Professional|8 Enterprise|8 Pro|8.1 Enterprise|8.1 Pro)")

    } | ForEach-Object { 

        If ($row.Type0 -eq 'Domain')
        {
            $row."Unique Account Name" = $row.Domain0 + ' - ' + $row.Account0
            $row."Excluded" = ($row."Unique Account Name" -in @('ACCOUNTS - DODSCAN'.'ACCOUNTS - Domain Admins','ACCOUNTS - LADM_WS_Admins','ACCOUNTS - Tech Enterprise'))
        }
        Else
        {
            $row."Unique Account Name" = $row.Netbios_name0 + ' - ' + $row.Account0
            $row."Excluded" = ($row."Account0" -in @('esrxadm1n_esi','#Update','medco_tech','medco_admin'))
        }

    } | Select-Object "Netbios_name0", "Name0", "Account0",
                      "category0","Domain0","Unique Account Name",
                      "Type0","caption0", "Excluded" |
    Export-Csv U:\$(get-date -f yyyy-MM-dd-hh-mm)-TestOutput1.csv -NoTypeInformation

I also shuffled around some of the redundant "if (thing test is true) then (true) else if (thing test is false) then (false)" into just "(thing test)", removed subexpressions, shortened the regex a bit, and moved the select to the end on the guideline 'filter early, select late'.

But making it significantly faster than a PS approach is going to involve dropping the CSV processing from PowerShell down to .Net, or treating it as a text file and doing some filtering that way first, or something.

Collectives™ on Stack Overflow

Powershell CSV script Faster

4 Answers 4

4 Comments

21 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

21 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related