locked
Importing AD User Properties from a CSV file - help! :-) RRS feed

  • Question

  • Hello,

    Please forgive me, as my scripting knowledge is very small - however I know in my head what it is I need the script to achieve. I have been tasked with getting all of the information for users in Active Directory (around 1000 users) into a CSV file (which I've managed) which is then going to be sent round to department heads for them to update the CSV file with the correct details for their staff. Once I receive the CSV files back, I want to populate AD with all the updated information. Please note this is for existing AD users, and not new ones.

    I have managed to piece together the following command which gets me the information I need into a CSV file:

    Get-ADUser -Filter * -SearchBase "OU=LiveUsers,DC=DomainName,DC=local" -properties  samAccountName, description, telephoneNumber, MobilePhone, PostalCode, Title, Department  | select GivenName, surname, samAccountName, description, telephoneNumber, MobilePhone, PostalCode, Title, Department | export-csv -path c:\output.csv -NoTypeInformation -Encoding "UTF8"

    What I now need to be able to do is import the updated data back into active directory, but I'm really stuck. I've read through article after article and I'm really struggling to create something. Also, I don't want to do it wrong and incorrectly modify 1000 objects in AD and have no job in the morning :)

    Thanks a lot in advance.

    Lee


    • Edited by leej024 Monday, October 13, 2014 10:07 AM
    • Moved by Bill_Stewart Monday, December 1, 2014 7:12 PM This is not "scripts on demand"
    Monday, October 13, 2014 10:06 AM

All replies

  • Hi, I think something along the lines of this should work, I haven't tested it though:

    Import-csv C:\output.csv | % {
    
    $user = $_.SamAccountName
    
    $props =  @{
    
    GivenName = $_.GivenName
    Sn = $_.Surname
    Description = $_.Description
    TelephoneNumber = $_.telephoneNumber
    Mobile = $_.MobilePhone
    PostalCode = $_.PostalCode
    Title = $_.Title
    Department = $_.Department
    }
    
    
    get-aduser $user | set-aduser -Replace $props
    
    
    }

    • Edited by Braham20 Monday, October 13, 2014 11:59 AM
    Monday, October 13, 2014 11:34 AM
  • If you have 1000's of users, and you export attributes to a csv, then edit the csv file, then use the csv to update the users, you will be updating 1000's of users when perhaps only 10 users have had only 2 attributes changed. I guess that's OK, but in the script to update users, I would consider checking if the existing value of each attribute matches the value specified in the csv, then updating only if the value differ. Either that, or somehow make sure the csv only includes values that should be updated. This would mean eliminating rows corresponding to users that have no updates, and leaving columns blank where no change is needed. Then the code to update users only updates values where the column is not blank. In the past, in such cases, if I want to null a value, I entered a special string in the csv (or spreadsheet) meaning to remove any existing value.

    I know this sounds like more work, but such a script would be more robust. I actually have an elaborate PowerShell script to update users in bulk from an Excel spreadsheet in the script gallery:

    https://gallery.technet.microsoft.com/Update-AD-Users-in-Bulk-ca0068f1

    In this case I used the string ".delete" to indicate that any existing value for the attribute is to be removed. As noted in the script description, one field of the spreadsheet (or the csv) must be able to uniquely identify the user. This should be either distinguishedName or sAMAccountName. You probably should not modify either of these values, but only use them to find the user.

    You should also be able to find other scripts in the gallery for updating users in bulk, some from csv files.


    Richard Mueller - MVP Directory Services

    Monday, October 13, 2014 3:11 PM