Using foreach loop in powershell to compare two csv files

Question

Using foreach loop in powershell to compare two csv files

Springer, Brian 1

Hello,
I'm trying to compare two csv files using Powershell. One .csv is $ReferenceSoftware which is a list of about 500 software titles and then $DifferenceSoftware, which is what each user currently has installed. Ideally, I want to end up with a .csv file of only the software titles that appear on both lists. I've tried using the compare object with the -include equal flag but that looks to only give the == results when the exact cell row match. Do I need to use a foreach loop to so it takes each cell in $DifferenceSoftware and compares it with each individual cell in $ReferenceSoftware? Thanks in advance for any potential help you may be able to provide!

0 comments

2 answers

Your answer

Answer 1

Hello there,

The PowerShellImport-Csv cmdlet is an excellent way of reading data from a tabulated source such as a CSV file. You can use the ForEach loop to then iterate over each row in the CSV data.

In the below script you can replace user list with reference software and user data with difference software.

$Path = "C:\PowerShell"
$UserList = Import-Csv -Path "$($path)\Userlisr.csv"
$UserData = Import-Csv -Path "$($path)\UserData.csv"
$UserOutput = @()

ForEach ($name in $UserList)  
{  

    $userMatch = $UserData | where {$_.UserName -eq $name.usernames}  
    If($userMatch)  
    {  
        # Process the data  

        $UserOutput += New-Object PsObject -Property @{UserName =$name.usernames;column1 =$userMatch.column1;column2 =$userMatch.column2}  
    }  
    else  
    {  
    $UserOutput += New-Object PsObject -Property @{UserName =$name.usernames;column1 ="NA";column2 ="NA"}  
    }  
}

$UserOutput | ft

-----------------------------------------------------------------------------------------------------------------------------

--If the reply is helpful, please Upvote and Accept it as an answer--

Answer 2

Rich Matheisen 48,116

In simple terms, you want the intersection of the two sets.

You haven't provided the names of the CSV columns that hold the names of the software, or the names of any other columns. This code will produce the intersection of the sets and export only the values that appear in both of the sets. It doesn't output a CSV, but just the values in the intersection.

$Ref = @{}  
$Intersection = @{}  
  
# populate the reference set  
Import-CSV c:\junk\ReferenceSoftware.csv |  
    ForEach-Object{  
        $Ref[$_.RefSoftwareName] = ""  
    }  
# process the difference set  
# and find the intersection of the sets  
Import-Csv c:\junk\DifferenceSoftware.csv |  
    ForEach-Object{  
        if ($Ref.ContainsKey($_.DifSoftwareName)){  
            $Intersection[$_.DifSoftwareName] = "" # populate the intersection  
        }  
    }  
  
$Intersection.Keys |  
    Out-File c:\junk\Intersection.txt

Springer, Brian 1

Thanks Rich! I don't mean to be dull, but I've just started working with Powershell the past few months and could use some additional help.. I've added what I have been using for all my testing and failed attempts. The names of the columns are "RefSoftwareName" for ReferenceSoftware.csv and just "Name" for the DifferenceSoftware.csv. I had tried implementing what you provided, but it had failed, so I assume i'm doing something incorrrect.

$ComputerName = get-content c:\temp\ComputerList.txt  
  
Get-InstalledSoftware $ComputerName | Out-File -FilePath C:\Temp\DifferenceSoftware.  
  
  
$DifferenceSoftware = Get-Content -Path "C:\Temp\DifferenceSoftware.csv"  
$ReferenceSoftware = Get-Content -Path "C:\Temp\ReferenceSoftware.csv"  
  
  
  
 $Ref = @{}  
 $Intersection = @{}  
      
 # populate the reference set  
 Import-CSV c:\temp\ReferenceSoftware.csv |  
     ForEach-Object{  
         $Ref[$_.RefSoftwareName] = ""  
     }  
 # process the difference set  
 # and find the intersection of the sets  
 Import-Csv c:\temp\DifferenceSoftware.csv |  
     ForEach-Object{  
         if ($Ref.ContainsKey($_.Name)){  
             $Intersection[$_.Name] = "Name" # populate the intersection  
         }  
     }  
      
 $Intersection.Keys |  
     Out-File c:\temp\Intersection.txt  
  
> Blockquote

Rich Matheisen 48,116 Reputation points

2022-09-21T21:42:16.297+00:00

Let's start with this: Windows-PowerShell-4. Download the free PDF and work your way through the 1st half of the book, doing the exercises (don't skip them unless you're conversant with what they're doing!). After that you can start on the second half of the book. That half deals with applying PowerShell to common administrative tasks. Again, work your way through, chapter by chapter.

I'm going to assume that the file "c:\temp\ComputerList.txt" contains one computer name on each line. The Get-Content would fill the variable with either a single scalar value (if there was only one name in the file, or with an array of elements whose values are the lines from the file.

But what does you function "Get-InstalledSoftware" do? Does the function take an array as its only parameter? Does the function return a stream of installed software names, or does it return some sort of object? You're piping the output from that function to a text file, but the contents of that file will be just one long set of lines. Also, it looks like you omitted the "csv" suffix on line #3.

On lines 6 and 7 you look to be simply reading the contents of the reference and difference files instead of using Import-CSV. That will get you lines of text, but what I think you want is an array of objects that named properties (the names being the column names in the CSVs. Line 7 is unnecessary because the correct action is taking place on line #15!
The same is true of lines 6 and 21.

Share via

Using foreach loop in powershell to compare two csv files

2 answers

Your answer