Faster processing of large CSV in powershell

Question

I have a CSV containing 300K+ records. Using this as a base, I need to extract the value from Login column, feed it in get-aduser command and extract reporting manager and one other custom property for each user. the 2 additional values need to be appended to a copy of this large CSV

Unfortunately I have had no luck optimising

I tried the hashtable approach

1. Since Login column of CSV has multiple rows containing same userid(each row corresponds to the different DB roles the user has), I extracted a list of unique userids. Out of 300K records, I got some 1700-1800 unique userids

2. Then I created a hashtable

  $f1 = import-csv -path $CSVFilePath/$file -Delimiter '|'  -Header 
    Login,Name,Role,System,Permission

  $h2 = @{}
       $f1 | ForEach-Object {
         $h2[$_.Login] = $_
       }

3. I tried updating the hashtable with the custom properties

$f1 |Foreach-object {
         $Login=$_.Login
         $Array | foreach-object{
         if($h2.ContainsKey( $_.userId)){
             $h2[$Login] | Add-Member -Name "REPORTING_MANAGER" -Value $_.Manager  -MemberType NoteProperty -Force       
             $h2[$Login]  | Add-Member -Name "CUSTOM_COLUMN" -Value $_.CustomProp-MemberType NoteProperty -Force   
      }
      }
      }

But the last loop is taking considerable time. Not sure how this can be made faster. Help !!

EDIT

Here are some details I forgot to include

$uniqueLogin = import-csv -path $CSVFilePath/$file -Delimiter '|'  | Sort Login -Unique | Select Login

$uniqueUsers = $uniqueLogin | Select-Object -Skip 1
$uniqueUsers.Count 
$Array = @() 
$uniqueUsers | % { 

$userid = $_."Login"

 $userProperties = get-aduser $userid -Server xxx -Properties * | select Manager,extensionAttribute7
 if($userProperties.Manager -ne $null){
 $manager = ($userProperties.Manager.Split(',')[0] + $userProperties.Manager.Split(',')[1]).Replace('\', ',').Replace('CN=', '')
 }
 if($userProperties.extensionAttribute7 -ne $null){
 $customprop=$userProperties.extensionAttribute7
 }
 $ResultObject = [PSCustomObject] @{ #Return Object
 userId = $userid
Manager = $manager
Customprop= $customprop
} 
$Array += $ResultObject 
 }

so basically $Array is an object of this kind

[
    {
        "userId":  "xxxx",
        "Manager":  "yyyy",
        "CustomProp":  "test1"
    },
    {
        "userId":  "yyyyyy",
        "Manager":  "zzzzz",
        "Customprop":  "test2"
    }
]

SAMPLE SOURCE CSV

Need EDITED CSV LIKE THIS

Answer 1

You'll want to use the same trick for the manager lookup - turn the array into a dictionary, at which point you can simply look up the corresponding manager and custom properties:

$lookupTable = @{}
Import-Csv -Path $CSVFilePath/$file -Delimiter '|' -Header Login,Name,Role,System,Permission |Sort-Object Login -Unique |Select-Object -Skip 1 -ExpandProperty Login |ForEach-Object {
  $userProperties = Get-ADUser $_ -Server xxx -Properties manager,extensionAttribute7

  $manager = if ($userProperties.manager) {
    ($userProperties.manager -split "(?<!\\),")[0].Split('=')[1].Replace('\', '')
  } else {
    '<No manager found>'
  }

  $lookupTable[$_] = [pscustomobject]@{
    manager = $manager
    customProp = $userProperties.extensionAttribute7
  }
}

Now it's as simple as piping the csv data to Select-Object:

Import-Csv -Path $CSVFilePath/$file -Delimiter '|' -Header Login,Name,Role,System,Permission |Select-Object *,@{Name='Manager';Expression={$lookupTable[$_.Login].manager}},@{Name='CustomProp';Expression={$lookupTable[$_.Login].customProp}} |Export-Csv $CSVFilePath/output.csv -NoTypeInformation

Answer 2

You built the hash table and then didn't use it. Should be :

$f1 |Foreach-object {
    $Login=$h2[$_.Login];
    $_ | Add-Member -Name "REPORTING_MANAGER" -Value $Login.Manager  -MemberType NoteProperty -Force       
    $_ | Add-Member -Name "CUSTOM_COLUMN" -Value $Login.CustomProp -MemberType NoteProperty -Force   

 }