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.

In case you do not want to reinvent the wheel: Using this [`Join-Object`](https://www.powershellgallery.com/packages/Join) cmdlet (see also: [In Powershell, what's the best way to join two tables into one?](https://stackoverflow.com/a/45483110/1701026)): `Import-csv 'C:\Temp\Excel\CSV\file1.csv' |Join (Import-Csv 'C:\Temp\Excel\CSV\file2.csv') -On 'CI Name' -Equals 'Device Display Label'` — iRon, Mar 31 '21 at 13:41

Mathias R. Jessen · Accepted Answer · 2021-04-01T12:44:54.367

0

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
  }
}

edited Apr 01 '21 at 12:44

answered Mar 31 '21 at 11:05

Mathias R. Jessen

157,619
12
148
206

Mathias, Thnx for the great reponse. I tried the code you provided only at the last line a get an error. Add-Member : Cannot bind argument to parameter 'InputObject' because it is null. At line:12 char:21 + ... aTable[$_] |Add-Member NoteProperty "Subscriber Employee Id" $_.'Subs ... + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidData: (:) [Add-Member], ParameterBindingValidationException The code creates perfectly the hast table. I cannot figure out why it sais "Because it's null" – Predletter Mar 31 '21 at 12:30
It works beautifully now. From 9 minutes to 10 seconds. Whoah awesome! – Predletter Apr 01 '21 at 19:43

Speed up CSV Powershell script

1 Answers1