Grep difference between two csv files with differences columns

Question

I have two files A.csv and B.csv, the two do not have identical column names, however the column "Name" and "Jobstream" respectively in the file A and B may contain identical values or not. My goal is to compare the two columns between them and generate a new C.csv file in order to continue to do other processing afterwards. Here is, what I did, but it does not work as I want, please, can you help me to make my script functional.

A.csv

ID,"Type","Name","Documentation"
79570056-ab4f-6969-8c58-a5bd5847a823,"TechnologyEvent","CLOTTRGAAJ","WAIT TRGFIC-CLOT190"
79570056-ab4f-6969-8c58-a5bd5847a824,"TechnologyEvent","CLOTTRGAAK","WAIT TRGFIC-CLOT191"
79570056-ab4f-6969-8c58-a5bd5847a831,"TechnologyEvent","COGNTRGAAA","WAIT TRGFIC-COGN130"
79570056-ab4f-6969-8c58-a5bd5847a842,"TechnologyEvent","FNETTRGAAC","trigger PFNET001 PRD"
79570056-ab4f-6969-8c58-a5bd5847a852,"TechnologyEvent","INFOTRGAAA","Flag envoi flux ACORD"
79570056-ab4f-6969-8c58-a5bd5847a730,"TechnologyInteraction","ACTO650Q","Interface 4.1 Fac (V2)"
79570056-ab4f-6969-8c58-a5bd5847a731,"TechnologyInteraction","ACTO666D","Transfert DUMP ACTO"
79570056-ab4f-6969-8c58-a5bd5847a732,"TechnologyInteraction","ACTO700M","Export FAC pour ACSA"
79570056-ab4f-6969-8c58-a5bd5847a733,"TechnologyInteraction","ACTO705Q","GÃ©nÃ©ration Fichiers IRIS"
79570056-ab4f-6969-8c58-a5bd5847a734,"TechnologyInteraction","ACTO800Q","Logs APP+IIS pour AGPC"
79570056-ab4f-6969-8c58-a5bd5847a742,"TechnologyInteraction","ACTO988Q","BCK-SYS-PRACEFACAPP04"
79570056-ab4f-6969-8c58-a5bd5847a743,"TechnologyInteraction","ACTO990Q","BCK-SYS-PRACEFACAPP03"
79570056-ab4f-6969-8c58-a5bd5847a744,"TechnologyInteraction","ACTO996Q","BCK-SYS-PRACEFACAPP02"
79570056-ab4f-6969-8c58-a5bd5847a745,"TechnologyInteraction","ACTO998Q","BCK-SYS-xxACEFACAPP01"

B.csv

Jobstream,"Jobstream Description","Op num","Job","Script or expected file(s)","Server","user","location","Job Description"
ACTO988Q,"BCK-SYS-PRACEFACAPP04","9","","technical","","","","Begin Of JobStream"
ACTO988Q-005S,"BCK-SYS-PRACEFACAPP04","40","PACTO0BP","PACTO-988Q-005S.KSH","SECLPRSSPSRVC02","lin-sb-axausr","F+ACTO-006","Lancement Sauvegarde"
ACTO990Q,"BCK-SYS-PRACEFACAPP03","9","","technical","","","","Begin Of JobStream"
ACTO990Q-005S,"BCK-SYS-PRACEFACAPP03","40","PACTO0BO","PACTO-990Q-005S.KSH","SELOPRSSPSRVC01","lin-sb-axausr","F+ACTO-013","Lancement Sauvegarde"
ACTO996Q,"BCK-SYS-PRACEFACAPP02","9","","technical","","","","Begin Of JobStream"
ACTO996Q-005S,"BCK-SYS-PRACEFACAPP02","40","PACTO0AB","PACTO-996Q-005S.KSH","SECLPRSSPSRVC02","lin-sb-axausr","F+ACTO-009","Lancement Sauvegarde"
ACTO998Q,"BCK-SYS-xxACEFACAPP01","9","","technical","","","","Begin Of JobStream"
ACTO998Q-005S,"BCK-SYS-xxACEFACAPP01","40","PACTO0AA","PACTO-998Q-005S.KSH","SELOPRSSPSRVC01","lin-sb-axausr","F+ACTO-001","Lancement Sauvegarde"
ADMR009Q,"EBX -> AGRE","9","","technical","","","","Begin Of JobStream"
ADMR-009Q-100T,"EBX -> AGRE","100","PADMR0DY","PADMR-009Q-100T.KSH","PRATFUDMGTW01","svcudmu","F+ADMR-002","UDM ADMR001 from EBX"
ADMR-009Q-101T,"EBX -> AGRE","101","PADMR0DZ","PADMR-009Q-101T.BAT","PRAXCAPP02","AXA-CESSIONS\SVC_SCHEDULING","F+ADMR-003","get latest RefData"
ADMR-009Q-110T,"EBX -> AGRE","110","PADMR0D2","PADMR-009Q-110T.KSH","PRATFUDMGTW01","svcudmu","F+ADMR-002","UDM ADMR002 from EBX"
ADMR-009Q-111T,"EBX -> AGRE","111","PADMR0D3","PADMR-009Q-111T.BAT","PRAXCAPP02","AXA-CESSIONS\SVC_SCHEDULING","F+ADMR-003","get latest Business data"
ADMR-009Q-120T,"EBX -> AGRE","120","PADMR0D6","PADMR-009Q-120T.KSH","PRATFUDMGTW01","svcudmu","F+ADMR-002","UDM ADMR003 from EBX"

$oldElementsCsv=Import-Csv $env:USERPROFILE\Desktop\A.csv -Delimiter ','  | Select-Object Name  # get array of just Name values
#get Jobstream in NewExtract_AGRe_TWS_ALL_20210607.csv
$NewExtract_AGRe_TWS_ALL_20200925Jobstream= Import-Csv $env:USERPROFILE\Archi\B.csv 

$output= @()
#Jobstream, Jobstream Description,Op num, Job,Script or expected file(s),Server, user, location, Job Description
ForEach ( $row in $oldElementsCsv){

$objMatch = $NewExtract_AGRe_TWS_ALL_20200925Jobstream | Where-Object { $_.Jobstream -notcontains $row.Name} 
$pattern='^(.*)-'
$pattern2='^(.*)_'
if(($objMatch.Jobstream  -notlike $pattern) ){

$output += New-Object PsObject -Property @{"ID"=[guid]::NewGuid().ToString(); "Type"="TechnologyInteraction"; "Name"= $objMatch.Jobstream; "Documentation"=$objMatch."Job Description"}   

}  elseif($objMatch.Jobstream  -like $pattern2) {

$output += New-Object PsObject -Property @{"ID"=[guid]::NewGuid().ToString(); "Type"="TechnologyInteraction"; "Name"=$objMatch.Jobstream; "Documentation"= $objMatch."Job Description"} 


} else{

$output += New-Object PsObject -Property @{"ID"=[guid]::NewGuid().ToString(); "Type"="TechnologyService"; "Name"= $objMatch.Jobstream; "Documentation"= $objMatch."Job Description"} 


}
}
$output |Select-Object -Property "ID","Type","Name","Documentation"| Export-Csv $env:USERPROFILE\Desktop\Archi\newElements.csv -NoTypeInformation -Encoding UTF8 
$output |Select-Object -Property "ID","Type","Name","Documentation"| Export-Csv $env:USERPROFILE\Desktop\C.csv -NoTypeInformation -Encoding UTF8

Have you tried using import-csv and compare-object (specifying the properties to compare)? — js2010, Sep 17 '21 at 13:15
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 A.csv |Join (Import-Csv B.csv) -On Name -Eq Jobstream` — iRon, Sep 17 '21 at 15:17
[Try to avoid using the increase assignment operator (`+=`) to create a collection](https://stackoverflow.com/a/60708579/1701026) as it might become very expensive. — iRon, Sep 17 '21 at 15:35

Grep difference between two csv files with differences columns

0 Answers0