How do I populate a column in a PowerShell array from a Groups column in a CSV by searching for a matching Name of a corresponding user?

Question

I have a PowerShell script that works up to a certain point that imports two CSV files, CSV #1 and CSV #2, and creates a new array ($content3) with input from both CSV files. I need to populate the third column (Roles) of the new array with information from CSV #2 (ApprovedGroups) that has a corresponding Name that has a match in CSV #1 (realName). The following code is a sort of facsimile of the data I am actually working with, and the names are fictitious. The script runs, but I can't get the last logic loop to work properly. I have been working on the last logic loop for a while now, and have been running into a brick wall. Does anyone have any advice?

PowerShell Script:

# Import CSV files
$content1 = Import-Csv -Path "C:\Users\blah\Desktop\Test\test1.csv"
$content2 =  Import-Csv -Path "C:\Users\blah\Desktop\Test\test2.csv"

# Print existing CSV arrays
$content1
$content2

#create new array
$content3 =@()

# Populate new array with initial information and values
$i = 0
while ($i -lt $content1.Length) {
$content3 += @([PSCustomObject]@{UserName = $content1[$i].username; Name = $content1[$i].realName; Roles = ""})
$i++
}

# Look for matching name from $content3 in $content2, and then populate the corresponding Groups column in $content3 with the corresponding ApprovedGroups in $content2
# Does not currently work
$j = 0
foreach ($row in $content3)  {
while ($j -lt $content3.Length) {
if ($row[$j].Name -match $content2.Name) {$row[$j].Roles -eq $content2.ApprovedGroups ; break}
else {break}
$j++
} }

# Print new array thus far
$content3

CSV #1 ($content1):

userName       realName
--------       --------
BDupree        Dupree,Bob
BDupree_admin  Dupree,Bob
BDupree_backup Dupree,Bob
SSmith         Smith,Susan
SSmith_admin   Smith,Susan
SSmith_backup  Smith,Susan
TCarver        Carver,Tom
TCarver_admin  Carver,Tom
TCarver_backup Carver,Tom
JBoyer         Boyer,Joan
HDega          Dega,Hector

CSV #2 ($content2):

Name        ApprovedGroups
----        --------------
Dupree,Bob  Users,Administrators,Backup Operators
Smith,Susan Users,Administrators,Backup Operators
Carver,Tom  Users,Administrators,Backup Operators
Boyer,Joan  Users
Dega,Hector Users

Initial Results:

UserName       Name        Roles
--------       ----        ------
BDupree        Dupree,Bob
BDupree_admin  Dupree,Bob
BDupree_backup Dupree,Bob
SSmith         Smith,Susan
SSmith_admin   Smith,Susan
SSmith_backup  Smith,Susan
TCarver        Carver,Tom
TCarver_admin  Carver,Tom
TCarver_backup Carver,Tom
JBoyer         Boyer,Joan
HDega          Dega,Hector

Desired Results (please note the below items are to help with doing an audit on all of the approved roles (groups) for a specific person, who may have more than one account the roles (groups) are spread over):

UserName       Name        Roles
--------       ----        ------
BDupree        Dupree,Bob  Users,Administrators,Backup Operators
BDupree_admin  Dupree,Bob  Users,Administrators,Backup Operators
BDupree_backup Dupree,Bob  Users,Administrators,Backup Operators
SSmith         Smith,Susan Users,Administrators,Backup Operators
SSmith_admin   Smith,Susan Users,Administrators,Backup Operators
SSmith_backup  Smith,Susan Users,Administrators,Backup Operators
TCarver        Carver,Tom  Users,Administrators,Backup Operators
TCarver_admin  Carver,Tom  Users,Administrators,Backup Operators
TCarver_backup Carver,Tom  Users,Administrators,Backup Operators
JBoyer         Boyer,Joan  Users
HDega          Dega,Hector Users

UPDATE1: Running the following code did not produce the desired results:

$content1 = Import-Csv -Path "C:\Users\blah\Desktop\Test\test1.csv"
$content2 =  Import-Csv -Path "C:\Users\blah\Desktop\Test\test2.csv"

# create hash table to be used as a mapping table
$nameToIDMap = @{}

# add each row from the csv to the mapping table - use realName as key, userName as value
$content1 |ForEach-Object {
  $nameToIDMap[$_.realName] = $_.userName
}

$content2 |Select-Object @{Name='UserName';Expression={$nameToIDMap[$_.Name]}},Name,@{Name='Roles';Expression='ApprovedGroups'}

$content2 | ForEach-Object {
  [pscustomobject]@{
    UserName = $nameToIDMap[$_.Name]
    RealName = $_.Name
    Roles = $_.ApprovedGroups
  }
} |Export-Csv "C:\Users\blah\Desktop\Test\test3.csv" -NoTypeInformation

Running it resulted in the following, which is closer to what I want to do but it is not quite there:

UserName       Name        Roles
--------       ----        -----
BDupree_backup Dupree,Bob  Users,Administrators,Backup Operators
SSmith_backup  Smith,Susan Users,Administrators,Backup Operators
TCarver_backup Carver,Tom  Users,Administrators,Backup Operators
JBoyer         Boyer,Joan  Users
HDega          Dega,Hector Users

What I want to do is:

UserName       Name        Roles
--------       ----        ------
BDupree        Dupree,Bob  Users,Administrators,Backup Operators
BDupree_admin  Dupree,Bob  Users,Administrators,Backup Operators
BDupree_backup Dupree,Bob  Users,Administrators,Backup Operators
SSmith         Smith,Susan Users,Administrators,Backup Operators
SSmith_admin   Smith,Susan Users,Administrators,Backup Operators
SSmith_backup  Smith,Susan Users,Administrators,Backup Operators
TCarver        Carver,Tom  Users,Administrators,Backup Operators
TCarver_admin  Carver,Tom  Users,Administrators,Backup Operators
TCarver_backup Carver,Tom  Users,Administrators,Backup Operators
JBoyer         Boyer,Joan  Users
HDega          Dega,Hector Users

I am currently trying the following code to get closer to what I want but I currently getting an error that I am working on troubleshooting:

$content1 = Import-Csv -Path "C:\Users\blah\Desktop\Test\test1.csv"
$content2 = Import-Csv -Path "C:\Users\blah\Desktop\Test\test2.csv"

# create hash table to be used as a mapping table
$nameToGroupsMap = @{}

# add each row from the csv #2 to the mapping table - use Name as key, ApprovedGroups as value
$content2 |ForEach-Object {
  $nameToGroupsMap[$_.Name] = $_.ApprovedGroups
}

$content1 | ForEach-Object {
  [pscustomobject]@{
    UserName = $_.userName
    RealName = $_.realName
    Roles = $nameToGroupsMap[$_.Name]
  }
} | Export-Csv "C:\Users\blah\Desktop\Test\test3.csv" -NoTypeInformation

The error I am getting is:

Index operation failed; the array index evaluated to null.
At line:2 char:3
+   [pscustomobject]@{
+   ~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : NullArrayIndex

This is a quite common question, see e.g.: In Powershell, what's the best way to join two tables into one?. In case you do not want to reinvent the wheel and go through all the (performance) pitfalls you might use this Join-Object Module: $content1 | Join $content2 -on RealName -eq Name — iRon
– iRon, Commented Aug 18 at 6:14
Your tool looks very useful, but unfortunately I do not have the ability to use third-party modules for the task I am working on. Thank you for the additional information, I have been looking through much of it. — CyberUn1c0rn
– CyberUn1c0rn, Commented Aug 18 at 18:38
Thank you for your feedback. "unfortunately I do not have the ability to use third-party modules", technically you could simply copy the content of the [Join.psm1 ](github.com/iRon7/Join-Object/blob/master/Join.psm1) into a Join.ps1 file and dot-source (. .\Join.ps1) that. Company policy wise, I would say: please support my request to Add a Join-Object cmdlet to the standard PowerShell equipment — iRon
– iRon, Commented Aug 19 at 5:45

Mathias R. Jessen · Accepted Answer · 2025-08-18 19:02:23Z

3

Start by building a reverse mapping of Real Name -> List of User IDs from the data contained in the first CSV file.

For this you can use a hashtable:

# create hash table to be used as a mapping table
$nameToIDsMap = @{}

# add each row from the csv to the mapping table - use realName as key, userName as value
Import-Csv path\to\csv1.csv |ForEach-Object {
  $nameToIDsMap[$_.realName] += @($_.userName)
}

Now we can use $nameToIDsMap as a lookup table for the user's possible accounts, and create a new object for each:

Import-Csv path\to\csv2.csv |ForEach-Object {
  $personRecord = $_
  $nameToIDsMap[$personRecord.Name] |ForEach-Object {
    [pscustomobject]@{
      UserName = $_
      RealName = $personRecord.Name
      Roles = $personRecord.ApprovedGroups
    }
  }
} |Export-Csv path\to\csv3.csv -NoTypeInformation

edited Aug 18 at 19:02

answered Aug 18 at 9:50

Mathias R. Jessen

178k13 gold badges175 silver badges234 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

CyberUn1c0rn Aug 18 at 15:46

Thank you for your feedback! It is greatly appreciated. The code does not list all of the accounts, only the BDupree_backup, SSmith_backup, TCarver_backup, JBoyer, and HDega accounts. Is it possible to list all of the accounts as specified in the last code snippet in the question using the same method: BDupree, BDupree_admin, BDupree_backup, SSMith, SSmith_admin, SSmith_backup, TCarver, TCarver_admin, TCarver_backup, JBoyer, and HDega?

CyberUn1c0rn Aug 18 at 18:39

Your code is closer to where I want to get, but I need all the usernames to be featured. I tried some more code and added to my initial post.

Mathias R. Jessen Aug 18 at 19:02

@CyberUn1c0rn my apologies, I didn't notice the first list wasn't a 1:1 mapping - I've update the answer :)

Santiago Squarzon Aug 18 at 19:09

Can use Import-Csv path\to\csv1.csv | Group-Object realName -AsHashtable too

CyberUn1c0rn Aug 18 at 19:32

@Mathias R. Jessen thank you so much! The updated code works perfectly. Thank you!! This was driving me nuts. I have some additional strengthening I need to do in PowerShell and will keep working on that.

|

CyberUn1c0rn · Accepted Answer · 2025-08-18 19:37:01Z

The following code worked for me (Thank you, @Mathias R. Jessen !)

$content1 = Import-Csv -Path "C:\Users\blah\Desktop\Test\test1.csv"
$content2 =  Import-Csv -Path "C:\Users\blah\Desktop\Test\test2.csv"

# create hash table to be used as a mapping table
$nameToIDsMap = @{}

# add each row from the csv to the mapping table - use realName as key, userName as value
$content1 | ForEach-Object {
  $nameToIDsMap[$_.realName] += @($_.userName)
}

$content2 | ForEach-Object {
  $personRecord = $_
  $nameToIDsMap[$personRecord.Name] |ForEach-Object {
    [pscustomobject]@{
      UserName = $_
      RealName = $personRecord.Name
      Roles = $personRecord.ApprovedGroups
    }
  }
} | Export-Csv "C:\Users\blah\Desktop\Test\test3.csv" -NoTypeInformation

Johann · Accepted Answer · 2025-08-18 20:22:15Z

I think you're mixing up a foreach-loop and a while-loop. What you actually want to do is take every line of content3, compare its name property to the name property in every item in content2 and set the roles property of content3, if they match. You are iterating through the items in content3 with the foreach-loop, and then you re-iterate through them with the while loop. $j -lt $content3.length should be $j -lt $content2.length. You also have to set the line number in content2 you are interested in, so $content2.Name should be $content2[$j].Name.

Moreover, set the delimiter for the csv files to ";" to be sure they're parsed correctly.

Finally - I'm not quite sure - but I think you should create the Custom Object first and then add it to content3.

I've modified your code a bit, and it produces the desired output now, I think:

# Import CSV files
$content1 = Import-Csv -Path .\test1.csv -delimiter ";"
$content2 =  Import-Csv -Path .\test2.csv -delimiter ";"

# Print existing CSV arrays
# $content1
# $content2

#create new array
$content3 =@()

# Populate new array with initial information and values
$i = 0
while ($i -lt $content1.count) {
    $obj = [PSCustomObject]@{UserName = $content1[$i].username; Name = $content1[$i].realName; Roles = ""}
    $content3 += $obj
    $i++
}

# Look for matching name from $content3 in $content2, and then populate the corresponding Groups column in $content3 with the corresponding ApprovedGroups in $content2
# Does not currently work
$j = 0
foreach ($row in $content3)  {
    while ($j -lt $content2.count) {
        if ($row.Name -match $content2[$j].Name) {$row.Roles = $content2[$j].ApprovedGroups ; break}
        $j++
    } 
}

# Print new array thus far
$content3

Greetings

Johann

This solution worked as well, thank you, @Johann ! I appreciate the tips and will make sure to implement your advice in future scripts.

Collectives™ on Stack Overflow

How do I populate a column in a PowerShell array from a Groups column in a CSV by searching for a matching Name of a corresponding user?

3 Answers 3

6 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related