I have two csv files, my goal is to generate a new csv file: for that, I must first
- check in the file relation.csv if the values of the column "ID" (file joined.csv) are not present.
- check if the "Type" column is "TechnologyInteraction" or "TechnologyEvent" in the joined.csv file.
- finally, if ID is not present and the type column is "TechnologyInteraction" or "TechnologyEvent", I change the values of the source and target columns.
My code runs but it takes too much time to generate the expected file. It turns out that the size of the joined.csv (247 kB) and relation.csv (456 kB) files is not as large. How to make the processing fast?
function updateArchiWithImportData{
process{
#Deletion of lines not starting with PAXC in the Extract_AGRe_TWS_ALL_20210607.csv file
$ElementCsv=Import-csv $env:USERPROFILE\Desktop\Archi\Extract_AGRe_TWS_ALL_20210607.csv -Delimiter ';' | Where-Object{ $_.Jobstream -match '^PAXC' }
#Loop through all the record in the CSV file and Extracting jobsets
$NewExtract_AGRe_TWS_ALL_20200925= ForEach($Entry in $ElementCsv){
if ($Entry."Script or expected file(s)" -ilike 'technical') {
$Entry."Jobstream"=$Entry."Jobstream" -replace '^(?:PAXC)?(.+?)','$1'
}else {
$Entry."Jobstream" = $Entry.'Script or expected file(s)' -replace '^(?:P|A|X|C)?(.+?)(\.(?:BAT|KSH)|$)','$1' -replace '^(WEBX|DWHS|COGN|CLOT|CLAI|BTRE|BISI|BDDO|ARXL|AGSO|AGPC|ACTO|FNET|ARX|AGS|INF|CLA|MEM|SWA|REX)-','$1'
}
$Entry
}
# Export Extract_AGRe_TWS_ALL_20210607.csv in new Desktop\Archi\NewExtract_AGRe_TWS_ALL_20210607.csv file
$NewExtract_AGRe_TWS_ALL_20200925 | Export-Csv $env:USERPROFILE\Desktop\Archi\NewExtract_AGRe_TWS_ALL_20210607.csv -NoTypeInformation -Encoding UTF8
#End of the generation of the csv $env:USERPROFILE\Desktop\Archi\NewExtract_AGRe_TWS_ALL_20210607.csv file, start of the jobstream grep search in the $env:USERPROFILE\Desktop\Archi\elements.csv file and the csv X file.
Write-Host "End of the generation of the csv $env:USERPROFILE\Desktop\Archi\NewExtract_AGRe_TWS_ALL_20210607.csv file"
Write-Host "start grep 'jobstream' in the elements.csv file and the csv $env:USERPROFILE\Desktop\Archi\NewExtract_AGRe_TWS_ALL_20210607.csv file."
#Now compare two files and update the elements.csv file
#get Name in old elements.csv
$oldElementsCsv=Import-Csv $env:USERPROFILE\Desktop\Archi\elements.csv -Delimiter ','
#get Jobstream in NewExtract_AGRe_TWS_ALL_20210607.csv
$NewExtract_AGRe_TWS_ALL_20200925Jobstream= Import-Csv $env:USERPROFILE\Desktop\Archi\NewExtract_AGRe_TWS_ALL_20210607.csv -Delimiter ','
#Output progress:
$output= @()
ForEach ( $row in $NewExtract_AGRe_TWS_ALL_20200925Jobstream){
$objMatch = $oldElementsCsv | Where-Object { $_.Name -ne $row."Jobstream"}
$pattern='^(.*)-'
$pattern2='^(.*)_'
if(($row."Jobstream" -notmatch $pattern) ){
$output += New-Object PsObject -Property @{ID=[guid]::NewGuid().ToString(); Type="TechnologyInteraction"; Name= $row."Jobstream"; Documentation= $row."Job Description"}
Write-Warning "Found new Jobstream : $($row."Jobstream")"
Write-Warning "Found new Jobstream Description : $($row."Job Description")"
} elseif($row."Jobstream" -match $pattern2) {
$output += New-Object PsObject -Property @{ID=[guid]::NewGuid().ToString(); Type="TechnologyInteraction"; Name= $row.Jobstream; Documentation= $row."Job Description"}
Write-Warning "New Jobstream : $($row."Jobstream")"
Write-Warning "New Jobstream Description : $($row."Job Description")"
} else{
$output += New-Object PsObject -Property @{ID=[guid]::NewGuid().ToString(); Type="TechnologyService"; Name= $row.Jobstream; Documentation= $row."Job Description"}
Write-Warning "New Jobstream : $($row."Jobstream")"
Write-Warning "New Jobstream Description : $($row."Job Description")"
}
}
$output |Select-Object -Property ID,"Type","Name","Documentation"| Export-Csv $env:USERPROFILE\Desktop\Archi\newElements.csv -NoTypeInformation -Encoding UTF8
#newElements.csv a is new elements.csv generated from NewExtract_AGRe_TWS_ALL_20210607.csv
#open new File:
notepad $env:USERPROFILE\Desktop\Archi\newElements.csv
}
}
updateArchiWithImportData # Call the function
function MergeTwoCsv {
$oldElementsCsv=Import-Csv $env:USERPROFILE\Desktop\Archi\elements.csv
$newElements= Import-Csv $env:USERPROFILE\Desktop\Archi\newElements.csv
$merged= $oldElementsCsv + $newElements
$merged |Select-Object -Property * -Unique | Export-Csv $env:USERPROFILE\Desktop\Archi\joined.csv -NoTypeInformation -Encoding UTF8
#Joined.csv is a combination of elements.csv and newElements.csv
notepad $env:USERPROFILE\Desktop\Archi\joined.csv
}
MergeTwoCsv # Call MergeTwoCsv the function
$result= @()
function linkedRelationCsvToElementsCsv{
#relation.csv headers are ID,"Type","Name","Documentation","Source","Target"
#$oldElementsCsv headers are ID,"Type","Name","Documentation"
$relationCsv=Import-CSV $env:USERPROFILE\Desktop\Archi\relations.csv -Delimiter ','
$joinedCsv=Import-CSV $env:USERPROFILE\Desktop\Archi\joined.csv -Delimiter ','
# Check if ID in $oldElementsCsv is presente in $relationCsv
ForEach ($line in $joinedCsv){
$checkID= $relationCsv | Where-Object {
($_.Source -ne $line.ID) -or ($_.Target -ne $line.ID)
}
if(($line.Type -eq 'TechnologyInteraction') -or ($line.Type -eq 'TechnologyEvent') ){
Write-Warning " the source and target columns in relation file not contains the Id of joined file, case type TechnologyInteraction founded "
# if the job is of type "TechnologyInteraction" or "TechnologyEvent", we link it to the TWS id's(TechnologyCollaboration,ef2f510b-924b-439d-8720-0183c7294eb3) in archi.
$newArrayResult= New-Object PsObject -Property @{ID=[guid]::NewGuid().ToString(); Type=$line.Type; Name=$line.Name; Documentation=$line.Documentation; Source="ef2f510b-924b-439d-8720-0183c7294eb3"; Target=$line.ID}
$result = $result + $newArrayResult
}else{
Write-Warning " the source and target columns in relation file contains the Id of joined file. "
$newArrayResult2 = New-Object PsObject -Property @{ID=$relationCsv.ID; Type=$relationCsv.Type;Name=$relationCsv.Name;Documentation=$relationCsv.Documentation; Source=$relationCsv.Source; Target=$relationCsv.Target}
$result = $result + $newArrayResult2
}
}
$result |Select-Object -Property ID,"Type","Name","Documentation","Source","Target"| Export-Csv $env:USERPROFILE\Desktop\Archi\newRelation.csv -NoTypeInformation -Encoding UTF8
notepad $env:USERPROFILE\Desktop\Archi\newRelation.csv
}linkedRelationCsvToElementsCsv # Call the function
I show you some of the contents of the two files. joined.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"
And relation.csv is:
ID,"Type","Name","Documentation","Source","Target"
id-babd7a16753d45c7ab7c9d692149b7ad,"RealizationRelationship","","","ef2f510b-924b-439d-8720-0183c7294eb3","79570056-ab4f-6969-8c58-a5bd5847a823"
id-bdc22e5077524384a7fe6f24e39117a9,"RealizationRelationship","","","79570056-ab4f-6969-8c58-a5bd5847a875","b57a5fff-9dd2-4cm1-9141-04c042f49431"
id-bdca26a3328b401091b671daba6c2692,"RealizationRelationship","","","ef2f510b-924b-439d-8720-0183c7294eb3","id-e78ac811853048f094682a6fd607d7de"
id-be0fbcbe3f2043eca42ed19ac8728b72,"RealizationRelationship","","","79570056-ab4f-6969-8c58-a5bd5847a874","b57a5fff-9dd2-4cm1-9141-04c042f49418"
id-be853ce889ff474cbabd07e481502388,"RealizationRelationship","","","79570056-ab4f-6969-8c58-a5bd5847a874","b57a5fff-9dd2-4cm1-9141-04c042f49411"
id-bef3d4040cc048279871935178349013,"RealizationRelationship","","","ef2f510b-924b-439d-8720-0183c7294eb3","79570056-ab4f-6969-8c58-a5bd5847a807"
id-bf3c421cce83454589c94dab4b7f0a28,"RealizationRelationship","","","79570056-ab4f-6969-8c58-a5bd5847a839","b57a5fff-9dd2-4cm1-9141-04c042f49326"
id-bf5513d5eb6540f0899d22d5d62ae5cb,"RealizationRelationship","","","79570056-ab4f-6969-8c58-a5bd5847a840","b57a5fff-9dd2-4cm1-9141-04c042f49334"
