none
Update a users AD information using a .csv RRS feed

  • Question

  • Hi All

    I have a .csv with the following column headers:

    User

    Description

    Manager

    MobilePhone

    HomeOffice

    Company

    I need to update a list of users which are in the csv with there attributes in AD, being a newbie for scripting I could do with a little help, I have so far the following (Not sure if this is in any way going to work)

    $Users = Import-CSV C:\Scripts\ADUPDATE\UT.csv

    foreach ($User in $Users)
    {
        Set-ADUser -Identity $User -Description $User.'title' -Manager $user.'manager' -MobilePhone $user.'mobile'  -HomeOffice $user.'I' -Department $user.'department' -Company $user'company'
    }

    Any help would be much appreciated

    Regards

    Stu

    Friday, January 13, 2017 5:46 PM

Answers

  • The headings in the csv file should be: User, Title, Manager,... not $User.User, $User.Title, $User.Manager, etc.

    Richard Mueller - MVP Enterprise Mobility (Identity and Access)

    I guess you don't want him to learn to use "help"...

    I think it is one of the first and most important lessons of PowerShell.  New users almost never learn to use it.


    \_(ツ)_/

    • Marked as answer by Stewart.N Monday, January 16, 2017 5:59 PM
    Monday, January 16, 2017 1:15 PM
  • Start over.  Create a new Excel spreadsheet with two records in it and then save it as a CSV.  YOU have probably damaged the file you are working on  which is very likely if you had extra quotes. 

    There is no reason for the distinguished name to be the only field that fails to apply.


    \_(ツ)_/

    • Marked as answer by Stewart.N Monday, January 16, 2017 5:58 PM
    Monday, January 16, 2017 5:43 PM

All replies

  • First, your script doesn't use your column header names. Your CSV doesn't have title, mobile, l, or department. Second, the header names should not be quoted. And $User won't identify the user. $User.user will if that field is the sAMAccountName or DN. Finally, $user'company' is missing a period. Should be $User.company.

    Richard Mueller - MVP Enterprise Mobility (Identity and Access)


    Friday, January 13, 2017 6:03 PM
    Moderator
  • Here is a quick a dirty way to apply a set of csv rows to a user. Be sure teh "Identity" column ise set to teh users Distinguished name:

    Import-Csv updateuser.csv |
    	ForEach-Object{
    		$splat = @{ }
    		$item = $_
    		$_.PsObject.Properties.Name | 
    			ForEach-Object{
    				$splat.Add($_, $item."$_") 
    			}
    		Set-AdUser @splat -WhatIf
    	}
    

    This will bind all columns to the command by name.  It allows you to easily test with a few columns and add and test until you have a good CSV file.


    \_(ツ)_/

    Friday, January 13, 2017 6:45 PM
  • I couldn't find my original so I rebuilt it:

    function Set-AdUserFromCsv{
    	[CmdletBinding(SupportsShouldProcess)]
    	Param(
    		[Parameter(Mandatory)]
    		$FilePath
    	)
    	
    	Process{
    		Import-Csv updateuser.csv |
    			ForEach-Object{
    				$splat = @{ }
    				$item = $_
    				$_.PsObject.Properties.Name | 
    					ForEach-Object{
    						$splat.Add($_, $item.$_) 
    					}
    				Set-AdUser @splat
    			}
        }
    }

    I supports WhatIf on the command.

    Set-AdUserFromCsv -FilePath .\updateuser.csv -WhatIf

    I believe there are other variations of this in the Gallery.


    \_(ツ)_/



    • Edited by jrv Friday, January 13, 2017 6:58 PM
    Friday, January 13, 2017 6:56 PM
  • Hi All

    Many thanks for your advise here so I have made the changes but it still doesn't update the AD account, probably doing something very silly which is down to the lack of PowerShell knowledge. The screenshot below is just a sample of the data, but I can confirm the column $user.user contains a list of users SAMaccountName

    Script:

    $Users = Import-CSV C:\Scripts\ADUPDATE\UT.csv

    foreach ($User in $Users)

    {

    Set-ADUser -Identity $User.user -Description $User.title -Manager $user.manager -MobilePhone $user.mobile  -HomeOffice $user.l -Department $user.department -Company $user.company

    }

    ScreenShot From .CSV ( Headers and Sample text)



    • Edited by Stewart.N Monday, January 16, 2017 11:54 AM
    Monday, January 16, 2017 11:51 AM
  • You need to start by creating a CSV file.  What you have is just an Excel spreadsheet with a bunch of PowerShell looking things in it.

    Start by looking up what a CSV is.

    Next you might take one of the tutorials on PowerShell so you can understand the answers given to your issue.


    \_(ツ)_/

    Monday, January 16, 2017 12:29 PM
  • the screenshot was taken from an export created as a .csv file, I have just changed the rows so not to show any personal information.

    thanks for the tip on taken a tutorial on PowerShell....



    • Edited by Stewart.N Monday, January 16, 2017 1:01 PM
    Monday, January 16, 2017 1:00 PM
  • The headings in the csv file should be: User, Title, Manager,... not $User.User, $User.Title, $User.Manager, etc.

    Richard Mueller - MVP Enterprise Mobility (Identity and Access)

    Monday, January 16, 2017 1:13 PM
    Moderator
  • The headings in the csv file should be: User, Title, Manager,... not $User.User, $User.Title, $User.Manager, etc.

    Richard Mueller - MVP Enterprise Mobility (Identity and Access)

    I guess you don't want him to learn to use "help"...

    I think it is one of the first and most important lessons of PowerShell.  New users almost never learn to use it.


    \_(ツ)_/

    • Marked as answer by Stewart.N Monday, January 16, 2017 5:59 PM
    Monday, January 16, 2017 1:15 PM
  • Richard

    Thanks for the info :) those heading where pre set from the export. I'll change these now

    JRV

    As per your suggestion, I'm currently watching some Powershell for beginners tutorials so hopefully I wont come back with any more silly questions.

    I do appreciate the help

    Regards

    Stu

    Monday, January 16, 2017 1:46 PM
  • An Export does not create those headings unless you wrote your own export and used those names as headings.

    Please read the help carefully and look up CSV with your search provider.


    \_(ツ)_/

    Monday, January 16, 2017 1:58 PM
  • Thanks for the info :) those heading where pre set from the export. I'll change these now

    If those really are the headings, and you could not change them, I would have to experiment how to handle them. Maybe refer to them similar to $User.[$user.title], or $user.[`$user.title], where ` is the escape character so that "$user" is read literally rather than attempting to evaluate a variable.

    Richard Mueller - MVP Enterprise Mobility (Identity and Access)

    Monday, January 16, 2017 2:01 PM
    Moderator
  • Wouldn't it make more sense to just fix the headings?

    If this is going to be done more than once then the source of the export should be fixed to prevent confusion and errors later.


    \_(ツ)_/


    • Edited by jrv Monday, January 16, 2017 2:04 PM
    Monday, January 16, 2017 2:03 PM
  • Hi Both

    thanks for the information and help thus far, I have the PowerShell script running almost perfectly, the only part that doesn't work is the managers field. No matter what I change either the script or the heading to be the managers field doesn't populate, no error message nothing.

    Any ideas?

    Stu

    Monday, January 16, 2017 3:13 PM
  • The managers field requires a DistinguishedName.


    \_(ツ)_/

    Monday, January 16, 2017 3:21 PM
  • ok so even with the DistingueshedName added it still doesn't work

    • Edited by Stewart.N Monday, January 16, 2017 3:37 PM
    Monday, January 16, 2017 3:36 PM
  • What does you current code look like.  It works correctly when I do it.


    \_(ツ)_/



    • Edited by jrv Monday, January 16, 2017 4:03 PM
    Monday, January 16, 2017 4:00 PM
  • In the csv distinguished names must be quoted, because the values have embedded commas. However, I don't think that is your problem.

    And the manager parameter of Set-ADUser actually accepts sAMAccountNames. A nice feature of the cmdlet.

    Edit: And if any value passed to a parameter of Set-ADUser has embedded spaces, it should be quoted in the Set-ADUser statement. This could be a DN or sAMAccountName value.


    Richard Mueller - MVP Enterprise Mobility (Identity and Access)


    Monday, January 16, 2017 4:08 PM
    Moderator
  • Hi

    The code looks as follows:

    All the other fields are population fine.

    Regards

    Stu

    Monday, January 16, 2017 4:34 PM
  • Check the CSV with notepad to be sure the manger field is in quotes.

    I just noticed too that Set-AdUserr can take samnames.


    \_(ツ)_/

    Monday, January 16, 2017 4:52 PM
  • opening with notepad displayed triple " before and after the manager DN. but when you look at the DN in the .csv its only showing 1 "

    anyway I changed the file in notepad to show just 1 " and the csv now show none and it doesn't work

    Regards

    Stu

    Monday, January 16, 2017 5:10 PM
  • Remove the quotes in Excel.  You don't want to use them there.  Save the file and look at it in notepad.  It should have a ,"...dn...",

    Are you beginning to see why I stressed learning about a CSV?  With the time you are wasting on guess work you could have become a master of CSV files.


    \_(ツ)_/

    Monday, January 16, 2017 5:35 PM
  • JRV

    I do understand and to be honest i'm looking at it right now..

    removing the " then opening the file in notepad show just a single " before and after

    But still doesn't work.

    Regards

    Stu

    Monday, January 16, 2017 5:39 PM
  • Start over.  Create a new Excel spreadsheet with two records in it and then save it as a CSV.  YOU have probably damaged the file you are working on  which is very likely if you had extra quotes. 

    There is no reason for the distinguished name to be the only field that fails to apply.


    \_(ツ)_/

    • Marked as answer by Stewart.N Monday, January 16, 2017 5:58 PM
    Monday, January 16, 2017 5:43 PM
  • Now it works, like you said just create a new .csv file and its now working nothing different to the previous .csv

    Weird

    Thank you so much for you assistance ( Both of you )

    Regards

    Stu

    PS: On my list tomorrow get some PowerShell Training in :)

    Monday, January 16, 2017 5:52 PM