Speed up CSV Powershell script

Question

I've got a Powershell script that functions but it takes ages to complete. I'm a newbie with Powershell and i can't find a solution to speed up the proces. Hopefully somebody can show me to the right direction.

Example. I've got 2 csv files.

CSV 1:

CI Name,Last Logon Account
Computer1, User1
Computer2, User2
Computer3, User3

CSV 2:

Device Display Label,Subscriber Employee Id
Computer1, User1
Computer2, User2
Computer3, User6

I want to have all the Ci names in the first column with the last logon account in the second column and match subscriber employee id with ci name from the first file.

Resulting in:

Ci name, Last logon Account, Subscriber Employee Id
Computer1,User1,User1
Computer2,User2,User2
Computer3,User3,User6

I have the following script in Powershell:

$Data = Import-csv 'C:\Temp\Excel\CSV\file1.csv'   
$Data2 = Import-Csv 'C:\Temp\Excel\CSV\file2.csv' 
$combine = @() 

foreach ($first in $Data) {
  foreach ($second in $Data2) {
    if ($second.'Device Display Label' -eq $first.'CI Name') {
        $match = New-Object PSObject
        $match | Add-Member Noteproperty "Ci Name" $first.'CI Name'
        $match | Add-Member Noteproperty "Last Logon Account" $first.'Last Logon Account'
        $match | Add-Member Noteproperty "Subscriber Employee Id" $second.'Subscriber Employee Id'
        $combine += $match
    }
  }
}
$Combine

It works and it gives the desired result.

The only problem is that both csv files have 15000 lines. So it takes ages to finish the script. Is there a way to speed up the proces. I hope somebody can point me to the right direction.

user_712649 · Answer

Use a hashtable to build an index out of one of the CSV files - this way you don't need the nested loops and the runtime should drop significantly:

# Build index/reference table from first data set
$DataTable = @{}
Import-csv 'C:\Temp\Excel\CSV\file1.csv' |ForEach-Object {
  $DataTable[$_.'CI Name'] = $_
}

# No need to store the second data set in an intermediate variable
$combine = Import-Csv 'C:\Temp\Excel\CSV\file2.csv' |ForEach-Object {
  if($DataTable.ContainsKey($_.'Device Display Label')){
    # Take the existing object from the first data set 
    # and add the subscriber from the second data set
    $DataTable[$_.'Device Display Label'] |Add-Member NoteProperty "Subscriber Employee Id" $_.'Subscriber Employee Id' -PassThru
  }
}

Speed up CSV Powershell script

1 Answers