Home > OS >  with Powershell how browse a csv file and link the elements according to their types?
with Powershell how browse a csv file and link the elements according to their types?

Time:10-07

I need to link the job to the each subjob with the "RealizationRelationship" and the relation between previous and next subjob there are a "TriggeringRelationship" : the job is of this format for example ACGN100Q while the subjobs that are attached are sequential and of this format: ACGN-100Q-000T;ACGN-100Q-010T;ACGN-100Q-020T;ACGN-100Q-030T

In my csv file the type of this job ACGN100Q is "TechnologyInteraction" while the subjobs are of type "TechnologyService". I am developing a script that allows me to say for example that the link between ACGN-100Q-000T and ACGN-100Q-010T is of type "TrigerringRelation" and the link between ACGN100Q and ACGN-100Q-000T is of type "RealizationRelation". I need help because I can't make the link.

And finally the relationship between job of type "TechnologyInteraction" and job of type "TechnologyEvent" is of type "TriggeringRelationship I have a beginning of answer in a previous post, but it turns out that had not understood the subject well, now what I am looking for is clear.

ID,"Type","Name","Documentation", "planification" 

eb214110-2b6a-48b2-ba5a-7c13dc3bba39,"TechnologyInteraction","ACGN100Q","Begin Of JobStream"
a46681e7-19a8-4fc5-b747-09679c15ff26,"TechnologyService","ACGN-100Q-000T","Transfert UDM (xACGN000)"
85761a09-1145-4037-a527-66a743def45f,"TechnologyService","ACGN-100Q-010T","move fichier REF to work"
27b126fb-c708-427d-b0a6-ce4747114ac4,"TechnologyService","ACGN-100Q-020T","w_read_account"
bb0c5e42-5fad-4bd9-8ee9-f41d0b824e82,"TechnologyService","ACGN-100Q-030T","w_read_referential"
0b8b76e3-62fa-4527-9f05-2eb4dbaa8e97,"TechnologyService","ACGN-100Q-040T","w_load_CompanyGroup"
1f487986-3cac-4af8-bda2-6400a1c71f48,"TechnologyService","ACGN-100Q-050T","w_load_Company"
40bec2d6-4545-48c2-acd5-089a05d4a723,"TechnologyInteraction","ADMR061Q","Begin Of JobStream", "23h30" 
14b2e8ce-cceb-4a72-a22b-1518ca582dfe,"TechnologyEvent","Create list files 23h30","ADMR-061Q-090T", "23h30" 
fe7d7052-a3e5-4cea-a987-567e29fdf685,"TechnologyEvent","w_F_REF08_BROKER 23h30","ADMR-061Q-100T",  "23h30" 
ddc3713b-57ec-4152-8dc7-01b8166c969a,"TechnologyEvent","w_F_REF08_CEDENT 23h30","ADMR-061Q-100T", "23h30" 

And I want to get a file that looks like this:

relation.csv

"ID","Type","Name","Documentation","Source","Target"

"New ID","RealizationRelationship","","","eb214110-2b6a-48b2-ba5a-7c13dc3bba39","a46681e7-19a8-4fc5-b747-09679c15ff26"
"New ID","RealizationRelationship","","","eb214110-2b6a-48b2-ba5a-7c13dc3bba39","85761a09-1145-4037-a527-66a743def45f"
"New ID","RealizationRelationship","","","eb214110-2b6a-48b2-ba5a-7c13dc3bba39","27b126fb-c708-427d-b0a6-ce4747114ac4"
"New ID","RealizationRelationship","","","eb214110-2b6a-48b2-ba5a-7c13dc3bba39","bb0c5e42-5fad-4bd9-8ee9-f41d0b824e82"
"New ID","RealizationRelationship","","","eb214110-2b6a-48b2-ba5a-7c13dc3bba39","0b8b76e3-62fa-4527-9f05-2eb4dbaa8e97"
"New ID","RealizationRelationship","","","eb214110-2b6a-48b2-ba5a-7c13dc3bba39","1f487986-3cac-4af8-bda2-6400a1c71f48"
"New ID","TriggeringRelationship","","","a46681e7-19a8-4fc5-b747-09679c15ff26","85761a09-1145-4037-a527-66a743def45f"
"New ID","TriggeringRelationship","","","85761a09-1145-4037-a527-66a743def45f","27b126fb-c708-427d-b0a6-ce4747114ac4"
"New ID","TriggeringRelationship","","","27b126fb-c708-427d-b0a6-ce4747114ac4","bb0c5e42-5fad-4bd9-8ee9-f41d0b824e82"
"New ID","TriggeringRelationship","","","bb0c5e42-5fad-4bd9-8ee9-f41d0b824e82","0b8b76e3-62fa-4527-9f05-2eb4dbaa8e97"
"New ID","TriggeringRelationship","","","0b8b76e3-62fa-4527-9f05-2eb4dbaa8e97","1f487986-3cac-4af8-bda2-6400a1c71f48"

"New ID","TriggeringRelationship","","","40bec2d6-4545-48c2-acd5-089a05d4a723","14b2e8ce-cceb-4a72-a22b-1518ca582dfe"
"New ID","TriggeringRelationship","","","40bec2d6-4545-48c2-acd5-089a05d4a723","fe7d7052-a3e5-4cea-a987-567e29fdf685"
"New ID","TriggeringRelationship","","","40bec2d6-4545-48c2-acd5-089a05d4a723","ddc3713b-57ec-4152-8dc7-01b8166c969a"
$result= @()
    
function linkedRelationCsvToElementsCsv{
 Write-Host "beginning linkedRelationCsvToElementsCsv"
$elements2Csv=Import-Csv $path\elements.csv

$jobTypes = @( "TechnologyInteraction");
$jobTypes2= @("TechnologyEvent");
$subTypes = @( "TechnologyService");

$previousItem = $null;

foreach( $item in $elements2Csv )
{
  
    if( $item.Type -in $jobTypes )
    {
        # start of a new job, but output  the link between TWS and Job name

          $result0=  new-object PSCustomObject -Property ([ordered] @{
            "ID"            = [guid]::NewGuid().ToString()
            "Type"          = "RealizationRelationship"
            "Name"          = "";
            "Documentation" = ""
            "Source"        ="ef2f510b-924b-439d-8720-0183c7294eb3"
            "Target"        = $item.ID
        });
        $result= $result   $result0
         
    }
     elseif( $item.Type -in $jobTypes2 )
    {
        # start of a new job, but output  the link between TWS and Job name

          $result3=  new-object PSCustomObject -Property ([ordered] @{
            "ID"            = [guid]::NewGuid().ToString()
            "Type"          = "RealizationRelationship"
            "Name"          = "";
            "Documentation" = ""
            "Source"        =$previousItem.ID
            "Target"        = $item.ID
        });
        $result= $result   $result3
         
    }
    elseif( $item.Type -in $subJobTypes)
    {
        # not a subjob type that we recognise
        throw "unrecognised subjob type '$($item.Type)' for subjob '$($item.ID)'";
    }
    elseif( $null -eq $previousItem )
    {
        # we've got a subjob, but there was no previous job or subjob
          throw "no preceding item for subjob '$($item.ID)'  with name: '$($item.Name)'";
       
    } 
    
    elseif( $previousItem.Type -in $jobTypes )
    {
     
        # this is the first subjob after the parent job
        
        $result1=  new-object PSCustomObject -Property ([ordered] @{
            "ID"            = [guid]::NewGuid().ToString()
            "Type"          = "TriggeringRelationship"
            "Name"          = ""
            "Documentation" = ""
            "Source"        = $previousItem.ID
            "Target"        = $item.ID
        });
        $result= $result   $result1
    }
  
    else
    {
        # the preceding item was a subjob as well
        $result2=  new-object PSCustomObject -Property ([ordered] @{
            "ID"            = [guid]::NewGuid().ToString()
            "Type"          = "TriggeringRelationship"
            "Name"          = ""
            "Documentation" = ""
            "Source"        = $previousItem.ID
            "Target"        = $item.ID
        });

        $result =$result   $result2
    }

    $previousItem = $item;
    #$elements2Csv
    Write-Host "previousItem value is '$($previousItem)'  and Item value is '$($item)'"
  
    }

$result |Select-Object -Property ID,"Type","Name","Documentation","Source","Target"| Export-Csv $path\relations.csv -NoTypeInformation -Encoding UTF8 
notepad $path\relations.csv 
   Write-Host "Ending  linkedRelationCsvToElementsCsv"
}linkedRelationCsvToElementsCsv # Call the function

CodePudding user response:

You could do regex matches on the Name and Documentation based on an extrapolation of the master record's Name field (split it in half, drop a hyphen in the middle, and stick it back together). That'll match master to child easily enough. Then you just want to group them by parent and link one to the next? That should be simple enough.

$Records=@'
ID,"Type","Name","Documentation", "planification" 

eb214110-2b6a-48b2-ba5a-7c13dc3bba39,"TechnologyInteraction","ACGN100Q","Begin Of JobStream"
a46681e7-19a8-4fc5-b747-09679c15ff26,"TechnologyService","ACGN-100Q-000T","Transfert UDM (xACGN000)"
85761a09-1145-4037-a527-66a743def45f,"TechnologyService","ACGN-100Q-010T","move fichier REF to work"
27b126fb-c708-427d-b0a6-ce4747114ac4,"TechnologyService","ACGN-100Q-020T","w_read_account"
bb0c5e42-5fad-4bd9-8ee9-f41d0b824e82,"TechnologyService","ACGN-100Q-030T","w_read_referential"
0b8b76e3-62fa-4527-9f05-2eb4dbaa8e97,"TechnologyService","ACGN-100Q-040T","w_load_CompanyGroup"
1f487986-3cac-4af8-bda2-6400a1c71f48,"TechnologyService","ACGN-100Q-050T","w_load_Company"
40bec2d6-4545-48c2-acd5-089a05d4a723,"TechnologyInteraction","ADMR061Q","Begin Of JobStream", "23h30" 
14b2e8ce-cceb-4a72-a22b-1518ca582dfe,"TechnologyEvent","Create list files 23h30","ADMR-061Q-090T", "23h30" 
fe7d7052-a3e5-4cea-a987-567e29fdf685,"TechnologyEvent","w_F_REF08_BROKER 23h30","ADMR-061Q-100T",  "23h30" 
ddc3713b-57ec-4152-8dc7-01b8166c969a,"TechnologyEvent","w_F_REF08_CEDENT 23h30","ADMR-061Q-100T", "23h30" 
'@ -split '[\r\n] '|ConvertFrom-Csv
$HT = [Ordered]@{}
$Records|%{$HT.Add($_.ID,$_)}
$Relations = ForEach($Master in $Records|?{$_.Name -notmatch '-' -and $_.Documentation -notmatch '-'}){
    $Child = $Master.Name -split '(?<=^.{4})' -join '-'
    $Children = $Records|?{$_.Name -match "(.*$child.*)" -or $_.Documentation -match "(.*$child.*)"}|%{
        [pscustomobject]@{
            "ID"            = [guid]::NewGuid().ToString()
            "Type"          = "RealizationRelationship"
            "Name"          = "";
            "Documentation" = ""
            "Source"        =$Master.ID
            "Target"        = $_.ID
        }
    }
    $Children
    $Children|Group Source|%{
        For($i=1;$i -lt $_.Group.Count;$i  ){
            [PSCustomObject]@{
                "ID"            = [guid]::NewGuid().ToString()
                "Type"          = "TriggeringRelationship"
                "Name"          = ""
                "Documentation" = ""
                "Source"        = $_.Group[$i-1].Target
                "Target"        = $_.Group[$i].Target
            }
        }
    }
}
$Relations|ft

That outputs:

ID                                   Type                    Name Documentation Source                               Target                              
--                                   ----                    ---- ------------- ------                               ------                              
9113f645-831a-4c86-9518-d8b0c95eae7a RealizationRelationship                    eb214110-2b6a-48b2-ba5a-7c13dc3bba39 a46681e7-19a8-4fc5-b747-09679c15ff26
db4436be-7dd7-4bba-8387-32ed97ed9885 RealizationRelationship                    eb214110-2b6a-48b2-ba5a-7c13dc3bba39 85761a09-1145-4037-a527-66a743def45f
94cc2eb4-63cf-4cae-a139-1628307e938b RealizationRelationship                    eb214110-2b6a-48b2-ba5a-7c13dc3bba39 27b126fb-c708-427d-b0a6-ce4747114ac4
c738adf7-dddb-45d3-aa99-ceb2113b969b RealizationRelationship                    eb214110-2b6a-48b2-ba5a-7c13dc3bba39 bb0c5e42-5fad-4bd9-8ee9-f41d0b824e82
11d94a45-2313-45b8-94d5-e5422bfc15c2 RealizationRelationship                    eb214110-2b6a-48b2-ba5a-7c13dc3bba39 0b8b76e3-62fa-4527-9f05-2eb4dbaa8e97
b1ff9e89-301d-4681-b6b5-d8418c41299d RealizationRelationship                    eb214110-2b6a-48b2-ba5a-7c13dc3bba39 1f487986-3cac-4af8-bda2-6400a1c71f48
25d4eb64-d580-40cd-9f7a-ff1c41217875 TriggeringRelationship                     a46681e7-19a8-4fc5-b747-09679c15ff26 85761a09-1145-4037-a527-66a743def45f
5a9f160e-5956-4a70-b8cb-ccfca764f772 TriggeringRelationship                     85761a09-1145-4037-a527-66a743def45f 27b126fb-c708-427d-b0a6-ce4747114ac4
c04434d3-1a22-47ba-a057-df430be0d5a9 TriggeringRelationship                     27b126fb-c708-427d-b0a6-ce4747114ac4 bb0c5e42-5fad-4bd9-8ee9-f41d0b824e82
b50754f9-145e-44a9-92ee-c1bceb8dea71 TriggeringRelationship                     bb0c5e42-5fad-4bd9-8ee9-f41d0b824e82 0b8b76e3-62fa-4527-9f05-2eb4dbaa8e97
0686e961-a90a-4f7f-a021-395b31157edf TriggeringRelationship                     0b8b76e3-62fa-4527-9f05-2eb4dbaa8e97 1f487986-3cac-4af8-bda2-6400a1c71f48
ae8b3626-859f-4426-b9f0-4ab4921a2ba0 RealizationRelationship                    40bec2d6-4545-48c2-acd5-089a05d4a723 14b2e8ce-cceb-4a72-a22b-1518ca582dfe
7acaaf00-bbc5-4944-993b-cdf6cafebc7b RealizationRelationship                    40bec2d6-4545-48c2-acd5-089a05d4a723 fe7d7052-a3e5-4cea-a987-567e29fdf685
5e8733ec-990c-408d-ab01-732cfcd7c76e RealizationRelationship                    40bec2d6-4545-48c2-acd5-089a05d4a723 ddc3713b-57ec-4152-8dc7-01b8166c969a
af4ba86f-3ab9-4996-a127-0ece6d1bca67 TriggeringRelationship                     14b2e8ce-cceb-4a72-a22b-1518ca582dfe fe7d7052-a3e5-4cea-a987-567e29fdf685
296447fc-3601-44ec-9f16-0907edcfffc1 TriggeringRelationship                     fe7d7052-a3e5-4cea-a987-567e29fdf685 ddc3713b-57ec-4152-8dc7-01b8166c969a
  • Related