Comparing two CSVs using one property to compare another

Question

File1

"FACILITY","FILENAME"
"16","abc.txt"
"16","def.txt"
"12","abc.txt"
"17","def.txt"
"18","abc.txt"
"19","abc.txt"

File2

"FACILITY","FILENAME"
"16","jkl.txt"
"16","abc.txt"
"12","abc.txt"
"17","jkl.txt"
"18","jkl.txt"
"19","jkl.txt"

I'm looking for this output:

"FACILITY","FILENAME"
"16","abc.txt"
"12","abc.txt"

For every FILENAME in File2, I would like to list if the filename is in File1 on a per FACILITY basis. So if there is file qwerty.txt listed next to FACILITY 20 in File1 and it matches a FILENAME in File2 with the filename qwerty.txt on the same line, then spit out the output.

Let's say there is a qwerty.txt file for differing Facilities, I do not want that in my output.

Here is something I've tried but I feel like I've butchered the commands because I don't fully understand them.

$file1 = Import-Csv $scriptPath'\loadedfiles.txt' -Delimiter ','
$file2 = Import-Csv $scriptPath'\filenames.txt' -Delimiter ','

$Header = $file1 | Get-Member | Where-Object -FilterScript {$_.MemberType -eq 'FACILITY'} | Select-Object -ExpandProperty FILENAME

Compare-Object -ReferenceObject $file1 -DifferenceObject $file2 -Property 'FILENAME' -PassThru | Select-Object -Property $Header |
Export-Csv -Path $scriptPath\test.csv -NoTypeInformation

I have seen plenty examples how to compart two lists, but I need to compare one property based on another which is why I'm struggling with just searching google for an answer.

Answer 1

The Property parameter accepts an array, so just specify both properties:

Compare-Object $file1 $file2 -Property 'Facility','Filename' -IncludeEqual -ExcludeDifferent -PassThru|select * -Exclude SideIndicator|export-csv -notype $scriptpath\test.csv

Here's how I tested, I started by recreating the source data (I didn't make files, just copied your text and used convertfrom-csv, but that should effectively be the same):

PS C:\Users\TMTech> $file1='"FACILITY","FILENAME"
"16","abc.txt"
"16","def.txt"
"12","abc.txt"
"17","def.txt"
"18","abc.txt"
"19","abc.txt"'|convertfrom-csv

PS C:\Users\TMTech> $file2='"FACILITY","FILENAME"
"16","jkl.txt"
"16","abc.txt"
"12","abc.txt"
"17","jkl.txt"
"18","jkl.txt"
"19","jkl.txt"'|convertfrom-csv

Then I used Compare-Object with just some basic parameters:

PS C:\Users\TMTech> Compare-Object $file1 $file2 -Property 'Facility','Filename'

Facility Filename SideIndicator
-------- -------- -------------
16       jkl.txt  =>           
17       jkl.txt  =>           
18       jkl.txt  =>           
19       jkl.txt  =>           
16       def.txt  <=           
17       def.txt  <=           
18       abc.txt  <=           
19       abc.txt  <=           

From that I excluded differences, and included where it was the same, and sent it to Select-Object to remove the SideIndicator property that gets added by Compare-Object:

PS C:\Users\TMTech> Compare-Object $file1 $file2 -Property 'Facility','Filename' -IncludeEqual -ExcludeDifferent -PassThru|select * -exclude sideindicator

FACILITY FILENAME
-------- --------
16       abc.txt 
12       abc.txt 

Seeing that output being what you wanted I just suggested piping to Export-Csv to output it to a file. This all worked fine for me, so if it isn't working for you maybe check your source files and make sure the column headers are identical, or that the data isn't formatted slightly different in the files (like full path vs filename or something).