Importing data from Excel workbooks

PowerShell Help
1

Hello,

PS Newbie here. Just wanted help in the PS excel reading workflow.

I want to do the below tasks using an excel file where I have two sheets.

  • Create some user-lists
  • Add Users to user-lists

First sheet has UserLists
image

Second sheet has users to be added for each user-lists
image

I wrote the below crude code to do this task. I am able to create the user-list in my integreated document management software but I am not sure how to add users to the user-lists.

$sheet1 = Import-Excel "C:\Users\Me\Desktop\UserList.xlsx" -WorkSheetName "UserList"

foreach($row in $sheet1.rows)
{
    $input1 = @{
               UserList = $row.Name
               UserListDescription = $row.description
               }

#Create User List [This part works when run separately]

$userlists = New-PWUserListByName @input1

#Importing Second Sheet [Not sure if it works inside the loop]
$sheet2 = Import-Excel "C:\Users\Me\Desktop\UserList.xlsx" -WorkSheetName "AddUsers"

        foreach($column in $sheet2.columns)
        {
#Find Users by Name and then Add Users to corresponding User List [**Need Help Here**]
        Get-PWUsersByMatch -UserName $column | Add-PWMemberToUserList -UserList $column

        }
}
2

You like to make it harder than actually needed, huh? … :wink:

As we do not know the document management software and the according cmdlets you will have to explain what these cmdlets expect as input.

If you used Import-Excel you already have a list. You don’t have to make it a list again.

The format of your “AddUsers” sheet is wrong. It should be somethingf like this:

"Userlist","Member"
"UserList1","Person1,Person2,Person3,Person5"
"Userlist2","Person12,Person22,Person32,Person52"
"Userlist3","Person4,Person6,Person8,Person3258"
1 Like
3

Yeah, my format for AddUsers sheet was wrong. Thanks for correcting this stupid mistake. :grin:

I’m creating the list again because I need to use them for “UserList” and “UserListDescription” parameters (@input1)

#Create User List 
$userlists = New-PWUserListByName @input1
#Example
$userlists = New-PWUserListByName -UserList 'UserList1' -UserListDescription 'Creating a new userlist1'

Now, the second part of the task needs to get the newly created user-lists and add corresponding users to them.

Below syntax can be used for the purpose.

$FirstValue is the list containing the User Names for the User List in $SecondValue

Get-PWUsersByMatch -UserName $FirstValue | Add-PWMemberToUserList -UserList $SecondValue

#Example
Get-PWUsersByMatch -UserName 'Person1' , 'Person2' | Add-PWMemberToUserList -UserList 'UserList1'

Just need to loop over this syntax.

4

Again … as we don’t know the cmdlets like New-PWUserListByName, Get-PWUsersByMatch and Add-PWMemberToUserList we’re probably unable to help you really.

Do you have a link to their documentation?

If a cmdlet is named New-PWUserListByName … with "… List … " in it I’d expect you can provide an array of users. So it shouldn’t be necessary to use a loop and provide the users one by one.

5

I added the only examples found in the documentation in my last reply. Hope it helps.

6

hmmm … me too … :wink:

If I got it right that should be all you need …

For your first sheet:

$sheet1 = Import-Excel 'C:\Users\Me\Desktop\UserList.xlsx' -WorkSheetName 'UserList'
foreach ($row in $sheet1) {
    New-PWUserListByName -UserList $row.Name -UserListDescription $row.Description
}

… and for the second sheet if it is like I recommended in my first answer …

$sheet2 = Import-Excel 'C:\Users\Me\Desktop\UserList.xlsx' -WorkSheetName 'AddUsers'
foreach ($row in $sheet2) {
    $UserList = $row.Member -split ','
    Get-PWUsersByMatch -UserName $UserList | 
        Add-PWMemberToUserList -UserList $row.UserList
}
1 Like
7

Thank a lot.

It’s working perfectly fine. Just a missing piece, the members in red markup are not added to the user-list. If I keep them in a single cell (“Person1,Person2,Person3”) separated by a comma as you mentioned, I got some error “Cannot find user”.

image

8

Ah … we have to turn the long string into a list/array of single users. I corrected my code suggestion above. Try it now.

9

Thanks a ton, man. It’s working now.