Compare two CSV files with different column numbers on basis of a common column

Question

Using powershell for the first time. I have 2 csv files. Each have different number of columns, and have different number of entries and not in same sequence.

Here are some examples of the first couple lines

File1.csv

    Name,Alias,Category
    Comdty,CONTRACT FOR DIFFERENCE,Future
    Comdty,Calendar Spread Option,Future
    Corp,Bond,FixedIncome
    Corp,EURO-DOLLAR,FixedIncome
    Corp,FLOORSPREAD,FixedIncome
    Corp,FRA,FixedIncome

File2.CSV

Alias,Category
EURO-DOLLAR,FixedIncome
Bond,FixedIncome
Preferred,Equity

I need to compare the files using PowerShell script on the basis of Alias column,and show the matching values together and showing blank where the value not exist for either file. Something like :

   Alias,Alias, Category ------------------------- MacthCase
   ___,CONTRACT FOR DIFFERENCE,Future ------------ false
   ___,Calendar Spread Option,Future ------------- false
   Bond,Bond,FixedIncome ------------------------- true
   EURO-DOLLAR,EURO-DOLLAR,FixedIncome ----------- true
   ___,FLOORSPREAD,FixedIncome ------------------- false
   ___,FRA,FixedIncome --------------------------- false
   Preferred,___,Equity -------------------------- false

Answer 1

try this

#import files with select columns
$file1=import-csv C:\temp\file1.csv | select Alias,Category, @{N="file";E={"file1"}}
$file2=import-csv C:\temp\file2.csv | select Alias,Category, @{N="file";E={"file2"}}

#merge into one list object
$allfile=$file1
$allfile+=$file2

#group by Alias and build objects result
$allfile | group Alias | foreach{

    if ($_.Count -ge 2)
    {
       [pscustomobject]@{Alias1=$_.Name;Alias2=$_.Name;Categorie=$_.Group.Category[0];MatchCase=$true}
    }
    else
    {
        if ($_.Group.file -eq "file1")
        {
           [pscustomobject]@{Alias1="____";Alias2=$_.Name;Categorie=$_.Group.Category;MatchCase=$false}
        }
        else
        {
           [pscustomobject]@{Alias1=$_.Name;Alias2="____";Categorie=$_.Group.Category;MatchCase=$false}
        }
    }


}