join Variable A item “User” with Variable B “email”

[Bungee_2690 Participant Topics: 2 Replies: 8 Points: 42Rank: Member]

Hi,

Asking for help on a simple issue.

I have two variables and I would like to combine data from each and then output the results into a CSV.

Can some one please point me in the right direction on how best to do this.

For example,

Variable A, has the following items “User”, “Delegate”, “Status”

Variable B, has the items “group”, “type”, “role”…”email”

I would like to join  Variable A item “User”  with Variable B “email”

If there is a match I would like to create a new variable which combines these items “User”, “Delegate”, “Status” and “group”, “role”…”email”

I’m just scratching my head on the best way of doing this, and advice is welcome.

Thanks

[kvprasoon Senior Moderator Topics: 9 Replies: 1449 Points: 5,428Rank: Community MVP]

To combine, you should have some element in common to ensure the right combination. It should be mostly user or email. Do you have anything common between A and B ?

[Bungee_2690 Participant Topics: 2 Replies: 8 Points: 42Rank: Member]

Thanks for getting back, yes the email address is the same in both Variable A and Variable B.

for example – Variable A (User: [email protected])  = Variable B (email : [email protected])

Here is an out put from Variable A :

User Delegate

— ———

User: [email protected] Show 0 Delegates
User: [email protected] Show 1 Delegate
Delegate: [email protected]
Status: accepted

User: [email protected] Show 2 Delegates
Delegate: [email protected] (1/2)
Status: accepted

Delegate: [email protected] (2/2)
Status: accepted

User: [email protected] Show 0 Delegates

Here is an out put from Variable B :

group : [email protected]
type : USER
role : OWNER
status : ACTIVE
email : [email protected]

group : [email protected]
type : USER
role : MANAGER
status : ACTIVE
email : [email protected]

group : [email protected]
type : USER
role : MANAGER
status : ACTIVE
email : [email protected]

group : [email protected]
type : USER
role : OWNER
status : ACTIVE
email : [email protected]

[Doug Maurer Participant Topics: 9 Replies: 706 Points: 2,837Rank: Community Hero]

Note even after changing the temp.com to test.com in Variable B data, only two entries from Variable A match entries from Variable B.

PowerShell

$variableA = @' User,Delegate,Status user.one@test.com,, user.two@test.com,user.three@test.com,accepted user.four@test.com,user.five@test.com,accepted user.six@test.com,,accepted user.seven@test.com,, '@ | ConvertFrom-Csv $variableB = @' group,type,role,status,email staff@temp.com,USER,OWNER,ACTIVE,user.seven@test.com staff@temp.com,USER,MANAGER,ACTIVE,user.three@test.com staff@temp.com,USER,MANAGER,ACTIVE,user.one@test.com staff@temp.com,USER,OWNER,ACTIVE,user.five@test.com '@ | ConvertFrom-Csv $combined = foreach($entry in $variableA) { $variableB | where email -eq $entry.user | ForEach-Object { [PSCustomObject]@{ User = $entry.user Delegate = $entry.Delegate Status = $entry.Status Group = $_.group Role = $_.role Email = $_.email } } }

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30

$variableA = @' User,Delegate,Status [email protected],, [email protected],[email protected],accepted [email protected],[email protected],accepted [email protected],,accepted [email protected],, '@ | ConvertFrom-Csv   $variableB = @' group,type,role,status,email [email protected],USER,OWNER,ACTIVE,[email protected] [email protected],USER,MANAGER,ACTIVE,[email protected] [email protected],USER,MANAGER,ACTIVE,[email protected] [email protected],USER,OWNER,ACTIVE,[email protected] '@ | ConvertFrom-Csv   $combined = foreach($entry in $variableA) {     $variableB | where email -eq $entry.user | ForEach-Object {         [PSCustomObject]@{             User     = $entry.user             Delegate = $entry.Delegate             Status   = $entry.Status             Group    = $_.group             Role     = $_.role             Email    = $_.email         }     } }

[Bungee_2690 Participant Topics: 2 Replies: 8 Points: 42Rank: Member]

Many thanks for this, I think im on the right track.

Is it possible to contact you directly to get further help on this…? If it’s not possible – not to worry.

Regards

 

 

[Author]

Posts