Import AD Info in Bulk

mwaskosky · 2021-05-17T13:38:04.000Z

Hi All. I am very new to PowerShell. What I am trying to accomplish is to get bulk information updated in AD from other sources. I have a CSV spreadsheet formatted below:

User	UserprincipalName	Name	GivenName	Surname	Enabled	DisplayName	Path	City	Company	State	StreetAddress	OfficePhone	MobilePhone	EmailAddress	Title	Department	Discription
ttestADSYNC	ttestADSYNC@test.com	Test TEST	Test	TEST	TRUE	Test T. TEST	CN=Test T. TEST,OU=test,DC=,DC=com	Test City	TEST	TT	5 Test. 31 W	999-999-9999	999-999-9999	ttestADSYNC@test.com	TEST - TEST ACCOUNT	IT	Test City

Below is my script:

### Update AD Users
# Import active directory module for running AD cmdlets
Import-Module activedirectory

#Store the data from ADUsers.csv in the $ADUsers variable
$ADUsers = Import-csv C:\Users\mwaskosky\Downloads\Projects\UpdateADusers5-7-2021importtest.csv

#Loop through each row containing user details in the excel file
foreach ($User in $ADUsers)
{
#Read user data from each field in each row and assign the data to a variable as below

### -Remove - Add - Replace - Clear @{Attribute1LDAPDisplayName=value1, value2, ...; Attribute2LDAPDisplayName=value1, value2, ...; AttributeNLDAPDisplayName=value1, value2, ...}
### [-Replace <Hashtable>]
Set-ADUser -Replace `
-Name "$($User.Givenname)$($User.Surname)"`
-GivenName $($User.GivenName) `
-Surname $($User.Surname) `
-DisplayName "$($User.DisplayName)" `
-Path $($User.Path) `
-City $($User.City) `
-Company $($User.Company) `
-State $($User.State) `
-StreetAddress $($User.streetaddress) `
-OfficePhone $($User.OfficePhone) `
-MobliePhone $($User.MobilePhone) `
-EmailAddress $($User.email) `
-Title $($User.jobtitle) `
-Department $($User.Department) `
-Discription $($User.Discription) `
}

I am not sure what do do next I have been working on this for a long time now and I have tried many different things. And I think that I am close.

### Update AD Users
# Import active directory module for running AD cmdlets
Import-Module activedirectory
 
#Store the data from ADUsers.csv in the $ADUsers variable
$ADUsers = Import-csv C:\Users\mwaskosky\Downloads\Projects\UpdateADusers5-7-2021importtest.csv

#Loop through each row containing user details in the CSV file 
foreach ($User in $ADUsers)
{
    #Read user data from each field in each row and assign the data to a variable as below

    
   ### -Remove - Add - Replace - Clear @{Attribute1LDAPDisplayName=value1, value2, ...; Attribute2LDAPDisplayName=value1, value2, ...; AttributeNLDAPDisplayName=value1, value2, ...}
   ### [-Replace <Hashtable>]
 Set-ADUser -Replace `
            -Name "$($User.Firstname)$($User.Lastname)"`
            -GivenName $($User.GivenName) `
            -Surname $($User.Surname) `
            -DisplayName "$($User.DisplayName)" `
            -Path $($User.Path) `
            -City $($User.City) `
            -Company $($User.Company) `
            -State $($User.State) `
            -StreetAddress $($User.streetaddress) `
            -OfficePhone $($User.OfficePhone) `
            -MobliePhone $($User.MobilePhone) `
            -EmailAddress $($User.email) `
            -Title $($User.jobtitle) `
            -Department $($User.Department) `
            -Discription $($User.Discription) `
   }
```

I am getting the error:
Set-ADUser : Cannot bind parameter ‘Replace’. Cannot convert the “-Name” value of type “System.String” to type “System.Collections.Hashtable”. At C:\Users\mwaskosky\Documents\PowerShell PS1\RMS\UpdateAD User Accounts.ps1:16 char:13 + Set-ADUser -Replace ` + ~~~~~~~~ + CategoryInfo : InvalidArgument: ( [Set-ADUser], ParameterBindingException + FullyQualifiedErrorId : CannotConvertArgumentNoMessage,Microsoft.ActiveDirectory.Management.Commands.SetADUser

Any help would be much appreciated. What I am trying to do is to import just information into existing AD accounts.

Set-ADUser : Cannot bind parameter 'Replace'. Cannot convert the "-Name" value of type "System.String" to type "System.Collections.Hashtable".
At C:\Users\mwaskosky\Documents\PowerShell PS1\RMS\UpdateAD User Accounts.ps1:16 char:13
+  Set-ADUser -Replace `
+             ~~~~~~~~
    + CategoryInfo          : InvalidArgument: (:) [Set-ADUser], ParameterBindingException
    + FullyQualifiedErrorId : CannotConvertArgumentNoMessage,Microsoft.ActiveDirectory.Management.Commands.SetADUser

Neally · 2021-05-17T13:44:08.000Z

If you post code, please use the ‘Insert Code’ button. Please and thank you!

PLEASE READ BEFORE POSTING! Read if you're new to the PowerShell forum! Programming & Development

Hi, and welcome to the PowerShell forum! Don’t apologize for being a “noob” or “newbie” or “n00b.” There’s just no need – nobody will think you’re stupid, and the forums are all about asking questions. Just ask! Use a descriptive subject. Don’t say “Need help” or “PowerShell Help”, actually summarize what the problem is. It helps the rest of us keep track of which problem is which. Don’t post massive scripts. We’re all volunteers and we don’t have time to read all that, nor will we copy…

mwaskosky · 2021-05-17T13:52:43.000Z

Thanks. I updated the post.

Neally · 2021-05-17T13:56:15.000Z

mwaskosky:

I have a CSV/excel spreadsheet formatted below:

ADUsers UserprincipalName Name GivenName Surname Enabled DisplayName Path City Company State StreetAddress OfficePhone MobilePhone EmailAddress Title Department Discription

ttestADSYNC ttestADSYNC@rmseq.com Test TEST Test TEST TRUE Test T. TEST CN=Test T. TEST,OU=test,DC=,DC=com Test City TEST TT 5 Test. 31 W 999-999-9999 999-999-9999 ttestADSYNC@test.com TEST - TEST ACCOUNT IT Test City

if it is delimited with a pipe as you have it ( | ) that’s not a valid CSV file and you have to tell ‘import-csv’ to use pipe ( | ) as a delimited.
CSV stands for comma separated values and should be more like so

ADUsers,UserprincipalName,Name,GivenName,Surname
ttestADSYNC,ttestADSYNC@rmseq.com,Test TEST,Test,TEST

mwaskosky · 2021-05-17T14:03:35.000Z

Sorry @alexw - my file is not a CSV. my bad. It is an excel spreadsheet.

Neally · 2021-05-17T14:06:59.000Z

mwaskosky:

My file is not a CSV. my bad. It is an excel spreadsheet.

No need to tag me if I’m in the thread.

PowerShell can not read excel by default.

So you take it out of Excel and then export it to a valid CSV ?

In your input file you have

|GivenName|Surname|

but in the script you

"$($User.Firstname)$($User.Lastname)"`

that’s not matching.

What you have in your input (CSV) needs to match the column name.
So it should be more like

"$($User.GivenName)$($User.SurName)"

mwaskosky · 2021-05-17T14:20:19.000Z

Thanks I will make it a CSV. And make the changes that are needed for the script and let you know.

Neally · 2021-05-17T14:30:02.000Z

mwaskosky:

Set-ADUser -Replace -Name "$($User.Firstname)$($User.Lastname)"
-GivenName $($User.GivenName) -Surname $($User.Surname)
-DisplayName “$($User.DisplayName)” -Path $($User.Path)
-City $($User.City) `

Also, I’m a bit confused, you want to replace / update ALL those attributes?

mwaskosky · 2021-05-17T15:05:19.000Z

Yes. I just came into this organization and they have not been keeping AD current. And it does not talk to any other systems. And I was brought it to get the company up to the 21st century. I am used to relying on Network Admins to do a lot of this bulk admin. But I am only the second IT person and we need to get this AD talking with Azure AD and that is my goal is to export info from Azure AD import it then run a AD Sync server then it will put data from AD out to Azure AD.

mwaskosky · 2021-05-17T15:12:05.000Z

I just changed my CSV file I also changed the first column to the name user since that is what PS is calling I think.

Getting the same error: Set-ADUser : Cannot bind parameter ‘Replace’. Cannot convert the “-Name” value of type “System.String” to type “System.Collections.Hashtable”.
At C:\Users\mwaskosky\Documents\PowerShell PS1\RMS\UpdateAD User Accounts.ps1:16 char:13

Set-ADUser -Replace `
CategoryInfo : InvalidArgument: ( [Set-ADUser], ParameterBindingException
FullyQualifiedErrorId : CannotConvertArgumentNoMessage,Microsoft.ActiveDirectory.Management.Commands.SetADUser

Neally · 2021-05-17T15:31:40.000Z

mwaskosky:

get this AD talking with Azure AD and that is my goal is to export info from Azure AD import it then run a AD Sync server then it will put data from AD out to Azure AD

you know there is a tool to sync that up for you?

https://www.microsoft.com/en-us/download/details.aspx?id=47594

Neally · 2021-05-17T15:32:04.000Z

How does your CSV look like now?

mwaskosky · 2021-05-17T15:58:11.000Z

I thought the tool only pushed info from AD to Azure. Does it go both ways?

mwaskosky · 2021-05-17T16:00:14.000Z

CSV looks like:

User UserprincipalName Name GivenName Surname Enabled DisplayName Path City Company State StreetAddress OfficePhone MobilePhone EmailAddress Title Department Discription ttestADSYNC ttestADSYNC@TEST.com Test TEST Test TEST TRUE Test T. TEST CN=Test T. TEST,OU=TEST,DC=TEST,DC=com TEST TESTlocation MN 5 HTEST. W31 W (999) 999-9999 (999) 999-9999 ttestADSYNC@TEST.com TEST - TEST ACCOUNT IT Savage

Neally · 2021-05-17T16:02:49.000Z

mwaskosky:

CSV looks like:

User UserprincipalName Name GivenName Surname Enabled DisplayName Path City Company State StreetAddress OfficePhone MobilePhone EmailAddress Title Department Discription ttestADSYNC ttestADSYNC@TEST.com Test TEST Test TEST TRUE Test T. TEST CN=Test T. TEST,OU=TEST,DC=TEST,DC=com TEST TESTlocation MN 5 HTEST. W31 W (999) 999-9999 (999) 999-9999 ttestADSYNC@TEST.com TEST - TEST ACCOUNT IT Savage

there is still no commas? that’s not a CSV when?

Neally · 2021-05-17T16:07:54.000Z

mwaskosky:

I thought the tool only pushed info from AD to Azure. Does it go both ways?

it does not, but im confused why you have more info in Azure than on prem? Where they setup in Azure but never updated onprem?

just seems like an odd setup.

Neally · 2021-05-17T16:19:25.000Z

a few additional notes:

you have typos in the attributes ( Description vs Discription)
the ‘PATH’ is referring to an OU, so you want to update / move the object in a new OU?
there is no ‘set-aduser path’ you’d have to use ‘move-adobject’
the ‘name’ is pre-calculated as well, you’d have to use ‘rename-adobject’

the rest should be fine, as long as your input CSV if right.

Import-Module activedirectory

$ADUsers = Import-csv "C:\Users\mwaskosky\Downloads\Projects\UpdateADusers5-7-2021importtest.csv"

foreach ($User in $ADUsers) {
    $AD_User = Get-aduser $user 
    $newProperties = @{
        GivenName     = $($User.GivenName) 
        Surname       = $($User.Surname) 
        DisplayName   = "$($User.DisplayName)" 
        City          = $($User.City) 
        Company       = $($User.Company) 
        State         = $($User.State) 
        StreetAddress = $($User.streetaddress) 
        OfficePhone   = $($User.OfficePhone) 
        MobilePhone   = $($User.MobilePhone) 
        EmailAddress  = $($User.email) 
        Title         = $($User.jobtitle) 
        Department    = $($User.Department) 
        Description   = $($User.Discription) 
    }
    Set-ADUser $AD_User @newProperties 
    Rename-ADObject -NewName "$($User.Firstname)$($User.Lastname)"

    Get-Aduser -filter "name -eq '$("$($User.Firstname)$($User.Lastname)")" |
    Move-ADObject -TargetPath $User.Path
}

mwaskosky · 2021-05-18T10:16:00.000Z

Yeah I thought the same thing. Who set this up and why is there more info in Azure than in AD. They did this backwards and now I get to fix it. That is why I need the script. I have over 300 records to update. then more to create/import which is going to be a separate script.

supaplex-starwind · 2021-05-24T12:23:32.000Z

Also, try checking this guide https://www.hyper-v.io/creating-bulk-user-accounts-ad-via-powershell/ that covers the bulk import of AD data from a CSV as well as correct CSV format and other nuances and details. It is exceptionally well documented and may help you improve your Powershell skills.

mwaskosky · 2021-05-24T18:13:57.000Z

I have looked over many helpful things and I am still getting an error:

### Update AD Users
# Import active directory module for running AD cmdlets
Import-Module activedirectory
Install-Module ImportExcel
 
#Store the data from ADUsers.csv in the $ADUsers variable
$ADUsers = Import-csv "C:\Users\mwaskosky\Downloads\Projects\UpdateADusers5-7-2021importtest.csv"

### Loop through each row containing user details in the CSV file 
### Read user data from each field in each row and assign the data to a variable as below
### -Remove - Add - Replace - Clear @{Attribute1LDAPDisplayName=value1, value2, ...; Attribute2LDAPDisplayName=value1, value2, ...; AttributeNLDAPDisplayName=value1, value2, ...}
### [-Replace <Hashtable>]
foreach ($User in $ADUsers) {
    $AD_User = Set-ADuser ###$User.SamAccountName 
    $newProperties = @{
        GivenName     = $($User.GivenName) 
        Surname       = $($User.Surname) 
        DisplayName   = "$($User.DisplayName)" 
        City          = $($User.City) 
        Company       = $($User.Company) 
        State         = $($User.State) 
        StreetAddress = $($User.streetaddress) 
        OfficePhone   = $($User.OfficePhone) 
        MobilePhone   = $($User.MobilePhone) 
        EmailAddress  = $($User.email) 
        Title         = $($User.jobtitle) 
        Department    = $($User.Department) 
        Description   = $($User.Discription) 
    }
    ###Get-ADUser -Identity $User | Set-ADuser -GivenName $GivenName
    ###Get-ADUser -Identity $User | Set-ADuser -Surname $Surname
    ###Get-ADUser -Identity $User | Set-ADuser -DisplayName $DisplayName
    ###Get-ADUser -Identity $User | Set-ADuser -City $City
    ###Get-ADUser -Identity $User | Set-ADuser -Company $Company
    ###Get-ADUser -Identity $User | Set-ADuser -State $State
    ###Get-ADUser -Identity $User | Set-ADuser -StreetAddress $StreetAddress
    ###Get-ADUser -Identity $User | Set-ADuser -OfficePhone $OfficePhone
    ###Get-ADUser -Identity $User | Set-ADuser -MobilePhone $MobilePhone
    ###Get-ADUser -Identity $User | Set-ADuser -EmailAddress $EmailAddress
    ###Get-ADUser -Identity $User | Set-ADuser -Title $Title
    ###Get-ADUser -Identity $User | Set-ADuser -Department $Department
    ###Get-ADUser -Identity $User | Set-ADuser -Description $Description
}

Error I am receiving is: PS C:\Windows\system32> C:\Users\mwaskosky\OneDrive - Road Machinery & Supplies Co Inc\Documents\PowerShell PS1\RMS\UpdateAD User Accounts.ps1
Set-ADUser : Parameter set cannot be resolved using the specified named parameters.
At C:\Users\mwaskosky\OneDrive - Road Machinery & Supplies Co Inc\Documents\PowerShell PS1\RMS\UpdateAD User Accounts.ps1:14 char:16

$AD_User = Set-ADuser ###$User.SamAccountName
CategoryInfo : InvalidArgument: ( [Set-ADUser], ParameterBindingException
FullyQualifiedErrorId : AmbiguousParameterSet,Microsoft.ActiveDirectory.Management.Commands.SetADUser

PS C:\Windows\system32>