Compare CSV file with extra Column data

Question

I have two CSV files with once CSV having an extra column with ticket number. I am trying to create another csv file by comparing the two with output like below with ticket number matching the Alert_ID.

csv1
Alert_ID    Server  Type    Timestamp   Severity 
1234        Srv1    WIN 2023-03-10  Critical
1235        Srv1    Net 2023-03-9   Critical
1236        Srv2    WIN 2023-03-8   Critical
1237        Srv3    WIN 2023-03-10  Critical


csv2
Alert_ID    Server  Type    Timestamp   Severity  Ticket    
1234        Srv1    WIN 2023-03-10  Critical   INC1
1236        Srv2    WIN 2023-03-8   Critical   INC3
1238        Srv4    NET 2023-02-10  Critical   INC01
1239        Srv5    NET 2023-02-20  Critical   INC02     
 

Expected Output

csv3
Alert_ID    Server  Type    Timestamp   Severity  Ticket    
1234        Srv1    WIN 2023-03-10  Critical   INC1
1235        Srv1    Net 2023-03-9   Critical   
1236        Srv2    WIN 2023-03-8   Critical   INC3
1237        Srv3    WIN 2023-03-10  Critical

$csv1 = Import-Csv .\csv1.csv
$csv2 = Import-Csv .\csv2.csv
#csv2 | where-object { $csv1.Alert_ID -match $_.Alert_ID} | export-csv .\csv3.csv -notype

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)): `Import-Csv .\csv1.csv |Update-Object (Import-Csv .\csv2.csv) -on Alert_ID |Export-Csv .\Csv3.Csv -NoType` — iRon, Mar 10 '23 at 07:05

iRon · Accepted Answer · 2023-03-11T09:33:07.277

In case you don't want to use an external (Join-Object) module for this. You might also use the Group-Object cmdlet for this (which is probably slower):

$csv1 = ConvertFrom-Csv @'
Alert_ID, Server, Type, Timestamp,  Severity
    1234, Srv1,   WIN,  2023-03-10, Critical
    1235, Srv1,   Net,  2023-03-9,  Critical
    1236, Srv2,   WIN,  2023-03-8,  Critical
    1237, Srv3,   WIN,  2023-03-10, Critical
'@

$csv2 = ConvertFrom-Csv @'
Alert_ID, Server, Type, Timestamp,  Severity, Ticket
    1234, Srv1,   WIN,  2023-03-10, Critical, INC1
    1236, Srv2,   WIN,  2023-03-8,  Critical, INC3
'@

$csv1 + $csv2 |Group-Object -Property Alert_ID |Foreach-Object { @($_.Group)[-1] } |Format-Table

Alert_ID Server Type Timestamp  Severity Ticket
-------- ------ ---- ---------  -------- ------
1234     Srv1   WIN  2023-03-10 Critical INC1
1235     Srv1   Net  2023-03-9  Critical
1236     Srv2   WIN  2023-03-8  Critical INC3
1237     Srv3   WIN  2023-03-10 Critical

Explanation:

$csv2 + $csv1 adds the to objects list as one list
Group-Object -Property Alert_ID Groups the objects by the Alert-ID property
Foreach-Object { @($_.Group)[-1] } takes the last object in the group property list (presuming that Group-Object respects the order of the original object list, which is actually not defined in the help, see also: Question: Group-Object group order #19307)

Using the (JoinModule), this would be similar to: $Csv1 |Merge-Object $Csv2 -on Alert_ID

Based on the revised question:

$csv2 = ConvertFrom-Csv @'
Alert_ID, Server, Type, Timestamp,  Severity, Ticket
    1234, Srv1,   WIN,  2023-03-10, Critical, INC1
    1236, Srv2,   WIN,  2023-03-8,  Critical, INC3
    1238, Srv4,   NET,  2023-02-10, Critical, INC01
    1239, Srv5,   NET,  2023-02-20, Critical, INC02
'@

$csv1 + $csv2 |Where-Object { $csv1.Alert_ID -eq $_.Alert_ID } |
Group-Object -Property Alert_ID |Foreach-Object { @($_.Group)[-1] } |Format-Table

Alert_ID Server Type Timestamp  Severity Ticket
-------- ------ ---- ---------  -------- ------
1234     Srv1   WIN  2023-03-10 Critical INC1
1235     Srv1   Net  2023-03-9  Critical
1236     Srv2   WIN  2023-03-8  Critical INC3
1237     Srv3   WIN  2023-03-10 Critical

Explanation:

Where-Object { $csv1.Alert_ID -eq $_.Alert_ID } filters only the objects that are only listed in Csv1 based on the common comparison operators feature: when the input is a collection, the operator returns the elements of the collection that match the right-hand value of the expression.

Using the (JoinModule), this would be similar to: $Csv1 |Update-Object $Csv2 -on Alert_ID

Hi Ron.. i have made some changes to CSV2 which has more ticket reference but i need the output csv3 which has alert id on csv1 — Enigma, Mar 10 '23 at 08:41

Compare CSV file with extra Column data

1 Answers1