Users to groups based on Excel

Question

Working on a little project that is supposed to place ADusers in ADgroups based on Excel-file.

Got lost in the *.Split-maze and I am sure there is an easy solution I cannot see just now.

USERNAME      : PROG-USER-01
USERGROUP-01  : X
USERGROUP-02  :
USERGROUP-03  : X
USERGROUP-04  :

SUITE-ADMINS  :

USERNAME      : PROG-USER-02
USERGROUP-01  :
USERGROUP-02  :
USERGROUP-03  :
USERGROUP-04  : X

SUITE-ADMINS  :

Code:

$ExcelPlaceUsers = @(Import-Excel -Path "C:\users_and_relations.xlsx" -WorksheetName 'USERGROUP')
$ePUsernames = $ExcelPlaceUsers.USERNAME

foreach ($ePuser in $ePUsernames){
    #$ePuser #USERNAMES
    $targetCell = $ExcelPlaceUsers.$($i)[$ePuser]

    if ($targetCell -eq 'x') {
                #Output
    }
}

Wanted output:

PROG-USER-01
USERGROUP-01
USERGROUP-03

PROG-USER-02
USERGROUP-04

Possible duplicate : https://stackoverflow.com/q/55854654/4961700 — Solar Mike, Apr 26 '19 at 08:19

score 1 · Accepted Answer · answered Apr 26 '19 at 09:24

We could loop over the object returned by Import-Excel (I'm using the following module: https://www.powershellgallery.com/packages/ImportExcel/5.4.0) Same code will work with a CSV file using the command Import-CSV. This won't require an extra module to be installed.

Change the Write-Output to whatever you want to do with the values.

$ExcelPlaceUsers = Import-Excel .\test.xlsx -NoHeader

Foreach ($Username in $ExcelPlaceUsers) {
    if ($Username.P2 -and $Username.P2 -ne 'X') {
        Write-Output ' '
        Write-Output $Username.P2
    } elseif ($Username.P2 -eq 'X') {
        Write-Output $Username.P1
    }
}

Output will be:

PROG-USER-01
USERGROUP-01
USERGROUP-03

PROG-USER-02
USERGROUP-04

possible to make it loop? need the code to be more dynamic, lots of users :) — Freshman, Apr 27 '19 at 09:39
Appears to work fine when I add more users to the list in the same format. Printscreen: http://prntscr.com/ni57ba — Michael B., Apr 29 '19 at 06:29

Users to groups based on Excel

1 Answers1