Powershell : Comparing Variable from one CSV to another CSV and create new variable on based of that

Question

I've two CSV File

File1 :

Server  HotfixID    CommanName
apdk778 kb3333  apdk778 kb3333
apdk778 yn2919  apdk778 yn2919
apdk778 lk4898  apdk778 lk4898
iijka211    kb3333  iijka211 kb3333
iijka211    yn2919  iijka211 yn2919
iijka211    lk4898  iijka211 lk4898

File2 :

Server  HotfixID    CommanName
apdk778 kb3333  apdk778 kb3333
apdk778 yn2919  apdk778 yn2919
apdk778 lk4898  apdk778 lk4898
iijka211    yn2919  iijka211 yn2919
iijka211    lk4898  iijka211 lk4898
mmmm23  ram9999 mmmm23 ram9999
nnn30292    ram9999 nnn30292 ram9999
iijka211    xxx9999 iijka211 xxx9999
iijka211    xxx9999 iijka211 xxx9999
iijka211    xxx9999 iijka211 xxx9999
apdk778 xxx9999 apdk778 xxx9999
apdk778 ram9999 apdk778 ram9999
apdk778 ram9999 apdk778 ram9999

Wanted-Output : "Patched_Success" added in File1 and compared with File2, if CommanName present in file2 then Patched Success = "yes" else "no"

Server  HotfixID    CommanName  Patched Success
apdk778 kb3333  apdk778 kb3333  Yes
apdk778 yn2919  apdk778 yn2919  Yes
apdk778 lk4898  apdk778 lk4898  Yes
iijka211    kb3333  iijka211 kb3333 No
iijka211    yn2919  iijka211 yn2919 Yes
iijka211    lk4898  iijka211 lk4898 Yes

I've tried below code : But didn't get success, please help.

foreach ($f1 in $file1) {foreach($f2 in $file2) {if ($f1.CommanName -eq $f2.CommanName) New-Object psobject -pro
perty @{Patched="Success"}}}

Answer 1

ConnorLSW's helpful answer works well, but you can simplify to a single Select-Object call that defines a calculated property, which also performs better:

$file1Objects = Import-Csv .\file1.csv -Delimiter "`t"
$file2Objects = Import-Csv .\file2.csv -Delimiter "`t"

$decoratedFile1Objects = $file1Objects | Select-Object *,
  @{ n='Patched'; e={ ('No', 'Yes')[$file2Objects.CommanName -contains $_.CommanName] } }

Get-Help -Detailed Select-Object briefly explains how calculated properties work; for a more detailed explanation, see this answer of mine.

Note the use of idiom (<choice-A>, <choice-B>)[<Boolean-Test>] for selecting one of two choices based on the outcome of a Boolean test:

• ('No', 'Yes') is a string array with elements No (index 0) and Yes (index 1).

• $file2Objects.CommanName -contains $_.CommanName is a Boolean expression that, when used in the context of an array index ([...]) is coerced to either 0 (if $False) or 1 (if $True).

• In other words:

  • if the Boolean expression returns $False, index 0 is used, resulting in string No.
  • if the Boolean expression returns $True, index 1 is used, resulting in string Yes.

• Note that this approach is a workaround for Windows PowerShell's lack of ternary conditionals that other languages, such as C#, have and that the order in which the branches are specified is reversed:

  • C# pseudo-code: <Boolean-Test> ? "Yes" : "No"

  • Windows PowerShell workaround: ('No', 'Yes')[<Boolean-Test>]

  • Aside from the ordering of branches / choices, there is another important difference: if your array elements are commands/expressions, they are all evaluated, irrespective of which one is chosen (no short-circuiting).

  • Fortunately, Windows PowerShell's modern, cross-platform successor, PowerShell (Core) 7+ now does have ternary conditionals so that the following works:

    # PowerShell 7+ only
    $file2Objects.CommanName -contains $_.CommanName ? 'Yes' : 'No'
    

---

As for what you tried:

Your nested foreach-loop approach:

• doesn't create an output object for un-patched rows
• and, for a patched row, creates a new object whose only property is the new one.

The following code fixes these issues, again relying on Select-Object to add the new property:

$decoratedFile1Objects = foreach ($f1 in $file1) {
  # Add property and default to 'No'
  $f1Decorated = Select-Object -InputObject $f1 *, @{ n='Patched'; e={ 'No' } } 
  foreach($f2 in $file2) {
    if ($f1.CommanName -eq $f2.CommanName) { 
      # Match found: set property to 'Yes' and exit the inner loop
      $f1Decorated.Patched = 'Yes'
      break
    }
  }
  $f1Decorated  # Output the decorated object.
}

However, use of PowerShell's -contains and -in array-membership test operators is not only much more convenient, but also performs better.

Answer 2

your csv files aren't properly formatted so I can't be sure if this is exactly what you need, but this should point you in the right direction:

$File1 = Import-Csv .\file1.csv -Delimiter "`t"
$File2 = Import-Csv .\file2.csv -Delimiter "`t"

$NewFile1 = $File1 | % {
    $Obj = [PSCustomObject]$_
    $Obj | Add-Member -Mem NoteProperty -Name Patched -Value ([bool]($_.CommanName -in $File2.CommanName))
    return $Obj
}

$NewFile1

Which will output

Server   HotfixID CommanName      Patched
------   -------- ----------      -------
apdk778  kb3333   apdk778 kb3333     True
apdk778  yn2919   apdk778 yn2919     True
apdk778  lk4898   apdk778 lk4898     True
iijka211 kb3333   iijka211 kb3333   False
iijka211 yn2919   iijka211 yn2919    True
iijka211 lk4898   iijka211 lk4898    True

I replaced the first two sets of spaces with tabs in order to read in the csv files.