Powershell - Join items in an array

Question

I'm really sorry if this has been asked before. I have spent some time Googling the question but nothing seems to match my query.

I'm creating new user accounts from a CSV file which is fine, only group membership information is spread over 40 different cells. An example is as below:

USERNAME,Group1,Group2,Group3
User1,Exchange,Office
User2,Office,HTTP,FTP
User3,Office

At the moment the only way I can think of achieving this is:

IF ( $IMPORT.Group1 -ne $NULL ) { Add-AdGroupMember $IMPORT.Group1 -Member $IMPORT.Username }

If there were just a handful of cells I'd go with this, but since there's up to 40 it seems a bit of a long winded way to go about it. Is there an easier way to achieve this?

Thanks

Note to the original poster - if it was you that upvoted my answer, and it works for you, I'd appreciate it if you'd accept the answer - click on the check-mark. It shows in the summary that the question has been 'solved', and it give me more rep than just the upvote. — Jeff Zeitlin
– Jeff Zeitlin, Commented Feb 28, 2017 at 13:13
So you have 40 group columns in your CSV file? Are there other columns besides that or is it just username and X number of groups — Matt
– Matt, Commented Feb 28, 2017 at 13:33
It wasn't me that upvoted, but consider it done. Works like an absolute charm. Thank you so much! — Graham J
– Graham J, Commented Feb 28, 2017 at 13:33
@Matt - that's more-or-less how I interpreted it. Certainly, in my organization, I've seen users in that many groups... — Jeff Zeitlin
– Jeff Zeitlin, Commented Feb 28, 2017 at 13:34
I am asking the OP because Import-CSV would be fine for this but only if those 40 columns exist. The sample data and the explanation are different which is why I am asking. — Matt
– Matt, Commented Feb 28, 2017 at 13:35

Matt · Accepted Answer · 2017-02-28 19:30:18Z

4

I would more-or-less ignore the fact that this is a CSV, and parse it yourself:

$ListData = Get-Content -Path \\Fully\Qualified\File\Name\Of\File.csv
$ListData = $ListData[1..$(($ListData.Length)-1)] # Gets rid of header line
ForEach ($line in $ListData) {
    $words = $line -split ','
    $UserName = $words[0]
    $words = $words[1..$(($words.length)-1)] # Gets rid of username, leaves only groups
    ForEach ($group in $words) {
        Add-ADGroupMember -Identity $Group -Members $UserName
    }
}

It should be obvious what this is doing, but a quick explanation:

Line  1: Loads the data.
Line  2: Gets rid of the header line, leaving only the actual data
Line  3: For each line of the file,
Line  4:    Convert it into an array. This array will have the user name
           in the first slot (index 0), and the rest of the slots, 
           however many there are, will be the groups for the user to be in.
Line  5:    Extract the user name...
Line  6:    ...and delete it from the array, which now contains only groups
Line  7:    For each group,
Line  8:        Add the user to it.
Line  9:    (end group processing)
Line 10:(end processing the file)

edited Feb 28, 2017 at 19:30

Matt

47.1k9 gold badges90 silver badges125 bronze badges

answered Feb 28, 2017 at 12:56

Jeff Zeitlin

11k2 gold badges26 silver badges38 bronze badges

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

2 Comments

Matt Over a year ago

$words[1..$(($words.length)-1)] logic could be simplified as $words = $words | Select -Skip 1. Not sure of any really performance implications but that should be moot here.

Jeff Zeitlin Over a year ago

@Matt - I always forget about Select-Object -skip... thanks for that. Obviously, the same construct can be used in line 2.

Matt · Accepted Answer · 2017-02-28 19:18:56Z

1

If there were just a handful of cells I'd go with this, but since there's up to 40 it seems a bit of a long winded way to go about it. Is there an easier way to achieve this?

This could mean that you actually have a CSV with as many columns as the user with that largest number of groups. If that is the case Import-CSV would still be your friend.

$users = Import-CSV $path

# Determine all of the columns that contain Group values. 
$groupProperties = $users[0].Psobject.Properties | 
    Where-Object{$_.MemberType -eq "NoteProperty" -and $_.Name -like "Group*"} | 
    Select-Object -ExpandProperty Name

Foreach($user in $users){
    # Collect all the groups for this user into an array
    $groups = $groupProperties | ForEach-Object{$user.$_}

    # Add the user into each of the groups
    $groups | Foreach-object{Add-AdGroupMember $_ -Member $user.Username}
}

The trick is that we take the first row from the imported CSV data and check for all of the properties that start with "Group". No matter how many you have. Then we have a string array containing each of the group columns that can be used in a loop for each user.

1..40 | ForEach-Object{"Group$_"} would also work but you would have to hard code the potential number. Mine example will function just fine for a properly formed CSV file. If a row does not have 40 groups that is fine. PowerShell will populate those other groups columns with null which is not an issue down the line. The important thing about your file and my solution is that you need to have the header populated properly.

For each user we collect of the groups and save it to a variable $groups. The nulls will not get passed down the pipe so they do not need to be accounted for.

You don't need to save the $groups at all and can just connect the two commands together but it is easier to read and explain as its individual parts.

answered Feb 28, 2017 at 19:18

Matt

47.1k9 gold badges90 silver badges125 bronze badges

3 Comments

Jeff Zeitlin Over a year ago

I had to read through this several times to see what it's doing. Very nice! The only place I can see a problem is if the names turn out not to be "Group1", "Group2" ... etc. - and there's no way to know without the querent actually saying so one way or the other (as you pointed out in the other comment block, on the question).

Matt Over a year ago

@JeffZeitlin There are avenues around this. If the file only has the first column as user name and you assume the rest are groups then you could just do $users[0].Psobject.Properties | ?{$_.MemberType -eq "NoteProperty"} | select -ExpandProperty Name -Skip 1. that is dependant on username being the first column. If not a clause in the where that would exclude "username" would address that. It all depends on the source data.

Graham J Over a year ago

This is brilliant. Thank you. The example CSV I gave was meant to simplify the question but in a real world scenario was a little more complex than shown. Nevertheless both answers have helped get me where I need to be. The columns Groups begin from column 13 and are all static up to 43 even if not populated. So in @JeffZeitlin example I changed $words = $words[1..$(($words.length)-1)] into $words = $words[12..$(($words.length)-12)] and added $words - $words | ?{ $_ -notlike $null } Your answer Matt worked out the box. Thank you to both.

Collectives™ on Stack Overflow

Powershell - Join items in an array

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related