PowerShell script to compare two files and merge the output the into a third file

Question

I have have 2 files which I need to compare with PowerShell and generate the 3rd file with the merge output which contains all the lines from file1.txt and file2.txt.

This is an example of file1.txt and file2.txt:

file1.txt content

"Name","RcopyGroup","State","Type","Prov","UsrCPG","VSize_MB","Usr_Rsvd_MB","Usr_Used_Perc","Usr_Used_MB","Snp_Rsvd_MB","Snp_Used_Perc"
".srdata","-","normal","base","full","-","81920","81920","100.0","81920","0","0.0"
"admin","-","normal","base","full","-","12288","12288","100.0","12288","0","0.0"
"LUN1","-","normal","base","tpvv","SSD_CPG","1331200","1159552","85.6","1139240","17408","0.0"
"LUN2","-","normal","base","tpvv","FC_CPG","122880","82944","52.3","64304","17408","0.0"
"LUN3","-","normal","base","tpvv","SSD_CPG","71680","56320","73.7","52861","1024","0.0"
"LUN4","-","normal","base","tpvv","FC_CPG","40960","33792","77.9","31922","1024","0.0"
"LUN5","-","normal","base","tpvv","FC_CPG","10240","11264","99.7","10209","1024","0.0"

File2.txt Content

"Name","LunID","PresentTo","vvWWN"
"LUN1","10","Host1","60002AC0000000000000004400015537"
"LUN1","10","Host3","60002AC0000000000000004400015537"
"LUN1","110","Host2","60002AC0000000000000004400015537"
"LUN1","209","Host5","60002AC0000000000000004400015537"
"LUN1","110","Host4","60002AC0000000000000004400015537"
"LUN2","111","Host2","60002AC000000000080019FC000153AF"
"LUN3","110","Host2","60002AC000000000080019FD000153AF"
"LUN4","210","Host2","60002AC0000000000A004BC500015537"
"LUN5","211","Host2","60002AC0000000000A004BC700015537"
"LUN4","210","Host5","60002AC0000000000A004BC500015537"
"LUN5","211","Host5","60002AC0000000000A004BC700015537"

and i want the output on file3.txt to be like this

"Name","RcopyGroup","State","Type","Prov","UsrCPG","VSize_MB","Usr_Rsvd_MB","Usr_Used_Perc","Usr_Used_MB","Snp_Rsvd_MB","Snp_Used_Perc","PresentTo"
".srdata","-","normal","base","full","-","81920","81920","100.0","81920","0","0.0",""
"admin","-","normal","base","full","-","12288","12288","100.0","12288","0","0.0",""
"LUN1","-","normal","base","tpvv","SSD_CPG","1331200","1159552","85.6","1139240","17408","0.0","Host1;Host3;Host2;Host5;Host4"
"LUN2","-","normal","base","tpvv","FC_CPG","122880","82944","52.3","64304","17408","0.0","Host2"
"LUN3","-","normal","base","tpvv","SSD_CPG","71680","56320","73.7","52861","1024","0.0","Host2"
"LUN4","-","normal","base","tpvv","FC_CPG","40960","33792","77.9","31922","1024","0.0","Host2;Host5"
"LUN5","-","normal","base","tpvv","FC_CPG","10240","11264","99.7","10209","1024","0.0","Host2;Host5"

I have check the similar post in the forum Powershell compare two files and generate third file but it is not producing the result which i required. Can someone help to think of a way to do this.

Thank you.

Can you provide your code attempt, and where you believe it may be failing at? — Abraham Zinala, Aug 18 '21 at 04:58
Using this [`Join-Object script`](https://www.powershellgallery.com/packages/Join)/[`Join-Object Module`](https://www.powershellgallery.com/packages/JoinModule) (see also: [In Powershell, what's the best way to join two tables into one?](https://stackoverflow.com/a/45483110/1701026)): `$file1 |Join $file2 -On Name` — iRon, Aug 18 '21 at 09:20

score 1 · Accepted Answer · answered Aug 18 '21 at 05:14

You can use Group-Object cmdlet to work out the hosts for each name and then add a new property to each of the new objects from file1 with the grouped data joined using -join ";".

$file1 = Import-Csv .\file1.csv
$file2 = Import-Csv .\file2.csv

# Group file2 by Name and output as a hashtable 
$group2 = $file2 | Select-Object Name, PresentTo | Group-Object -Property Name -AsHashTable

$file1 | ForEach-Object {
    # Add the new property (column) 'PresentTo' with the matching file2 Hosts joined by ';'
    $_ | Add-Member -NotePropertyName PresentTo -NotePropertyValue ($group2.($_.Name).PresentTo -join ';') -PassThru
} | Export-Csv .\file3.csv

# have a look at our new file
Import-Csv .\file3.csv | Format-Table *

Output

Name    RcopyGroup State  Type Prov UsrCPG  VSize_MB Usr_Rsvd_MB Usr_Used_Perc Usr_Used_MB Snp_Rsvd_MB Snp_Used_Perc PresentTo
----    ---------- -----  ---- ---- ------  -------- ----------- ------------- ----------- ----------- ------------- ---------
.srdata -          normal base full -       81920    81920       100.0         81920       0           0.0
admin   -          normal base full -       12288    12288       100.0         12288       0           0.0
LUN1    -          normal base tpvv SSD_CPG 1331200  1159552     85.6          1139240     17408       0.0           Host1;Host3;Host2;Host5;Host4
LUN2    -          normal base tpvv FC_CPG  122880   82944       52.3          64304       17408       0.0           Host2
LUN3    -          normal base tpvv SSD_CPG 71680    56320       73.7          52861       1024        0.0           Host2
LUN4    -          normal base tpvv FC_CPG  40960    33792       77.9          31922       1024        0.0           Host2;Host5
LUN5    -          normal base tpvv FC_CPG  10240    11264       99.7          10209       1024        0.0           Host2;Host5

PowerShell script to compare two files and merge the output the into a third file

1 Answers1