locked
Importing attribute from a CSV to AD for multiple users RRS feed

  • Question

  • Hi All,

    I have searched around for an answer to this and tried it on our test domain with a test CSV file, but can't get it to work. So I apologize if the answer is out there, but I'm unable to get this resolved. What I have is a CSV file with two columns at the top "USER_NAME" and "ExtensionAttribute1". Below the two columns are data for many users, the USER_NAME is their sAMAccountName and the ExtensionAttribute1 is a value (PIN #) that I want to go in their ExtensionAttribute1 attribute. I am using Import-CSV. I created 5 test accounts on our test domain so I could test this. I'm trying to run this from the active directory powershell module.

    I can run import-CSV C:\testimport.csv and get the output of the file fine. Then I run this next powershell command and just get a prompt like this and nothing else, <<

    Import-CSV C:\Testimport.csv | foreach{
    Get-ADUser -Filter {(sAMAccountName -eq $_.USER_NAME) | Set-ADUser -extensionAttribute1 $_.extensionAttribute1

    Do I need to run part of this command first and then the second part of the command after? Or can I run it all at once, but I'm messing something up? I ask this because when I run the command, like I said, I get this "<<" but no errors. Any help would be greatly appreciated, I can provide any more information that you may need.

    Thanks!

    -Scott

    Sunday, January 12, 2014 3:56 AM

Answers

  • I uploaded a script to the Script Center because I was constantly having to do that
    http://gallery.technet.microsoft.com/Update-User-attributes-bef81480
    • Proposed as answer by Yan Li_ Tuesday, January 14, 2014 2:57 AM
    • Marked as answer by AnnaWY Thursday, January 23, 2014 6:28 AM
    Sunday, January 12, 2014 4:56 AM
  • Scott,

    You can paste that in Powershell ISE and save it, or you can paste it in notepad and save it as 'something.ps1'

    What you will ensure that you can do is run unsigned scripts.  By default, Powershell will not run scripts that aren't digitally signed by a trusted authority.

    To Get Around That:

    - Open run Powershell as Administrator.

    - Run this command: get-executionpolicy

    - If the results are 'Restricted', run this command 'set-executionpolicy bypass' then press enter to confirm.

    (If you are not going to regularly write and run your own scripts on this server, you can change it back after you run my script. More info @ http://technet.microsoft.com/en-us/library/ee176961.aspx

    To explain what the script is reading.

    1. Import-csv 'yourfile.csv' | foreach-object ({ -- This line is grabbing a file that contains columns

    2. $SAM = $_.samaccountname -- I'm just defining a new varibable to use that ensures it's in the right column.  It's not completely necessary, but it helps me understand what I'm doing in the execution line.

    3. $attrib = same thing as #2.

    4. write-host "Working on $sam..." -nonewline  -- All this is doing is writing to the screen what line it's on so you can following along.  I like to see what is happening.

    5. get-aduser $sam -- is using the variable I set before.  So whatever line it is on, it will be using the column 'samaccountname'

    6. set-aduser -replace @{extenstionattribute1="$attrib"} --This is the command that was failing you before.  Not all attributes can be set like you were trying before.  For example, '-description "IT Guy' or '-smartcardlogonrequired $true'

    7. })    - Closes the loop.

    I hope all this makes sense.  If you have previously copied my script, go regrab it again.  I am about to change it.  Line 2 needs to come AFTER line 3.  You can't use '$sam' before defining what '$sam' is :/    

    Oops.  Minor details.  Let me know if you have anymore questions.


    Chris Ream

    • Proposed as answer by Yan Li_ Tuesday, January 14, 2014 2:57 AM
    • Marked as answer by AnnaWY Thursday, January 23, 2014 6:28 AM
    Sunday, January 12, 2014 3:01 PM
  • Since the Set-ADUser cmdlet accepts sAMAccountNames to identify users, there is no need to use Get-ADUser. I would use

    Set-ADUser $_.User_Name @Replace{extensionAttribute1 = $_.extensionAttribute1}

    in the ForEach.


    Richard Mueller - MVP Directory Services

    • Proposed as answer by Yan Li_ Tuesday, January 14, 2014 2:57 AM
    • Marked as answer by AnnaWY Thursday, January 23, 2014 6:28 AM
    Sunday, January 12, 2014 7:02 PM

All replies

  • Your answer should work...I think you are missing the closing brace for the filter parameter's argument:
    Import-CSV C:\Testimport.csv | foreach{
    Get-ADUser -Filter {(sAMAccountName -eq $_.USER_NAME)} | Set-ADUser -extensionAttribute1 $_.extensionAttribute1
    What is the ExtensionAttribute1 ? Is it literally the same or you just mentioned a dummy attribute ?
    Coz there is no -ExtensionAttribute1 parameter for Set-ADUser.
    So I guess you will have to use the -add or -replace parameters....You can find more help by issuing Help Set-ADUser -online from your PowerShell console.

    Hope this helps

    Knowledge is Power{Shell}.


    • Edited by DexterPOSH Sunday, January 12, 2014 4:11 AM
    Sunday, January 12, 2014 4:03 AM
  • Thanks a lot for the info! I tried it with the closing brace and I'm getting the same thing, just a "<<" and nothing. I check the attributes and nothing is populated. Do I need to run the Import-CSV C:\TestImport.CSV | foreach{ before I run the other command, or can I run the whole command at once under the active directory module for powershell? Thanks again for the help!
    Sunday, January 12, 2014 4:14 AM
  • THIS HAS BEEN CORRECTED. 

    ##########################

    $thelist = import-csv c:\testimport.csv | foreach-object ({

    $sam = $_.USER_NAME

    $attrib = $_.extensionAttribute1

    write-host "Processing $sam..." -nonewline

    get-aduser $sam | set-aduser -replace @{extensionAttribute1="$attrib"}

    write-host "Done!" -foregroundcolor Green })

    ##############################################

    It'll be pretty.  And work.



    Chris Ream




    • Proposed as answer by Christopher Ream Sunday, January 12, 2014 4:51 AM
    • Edited by Christopher Ream Sunday, January 12, 2014 3:02 PM Corrected script order
    • Unproposed as answer by Yan Li_ Tuesday, January 14, 2014 2:57 AM
    • Proposed as answer by Yan Li_ Tuesday, January 14, 2014 2:57 AM
    Sunday, January 12, 2014 4:51 AM
  • Christopher's solution should work for you.

    Try it and let us know.


    Knowledge is Power{Shell}.


    • Edited by DexterPOSH Sunday, January 12, 2014 4:54 AM
    Sunday, January 12, 2014 4:53 AM
  • I uploaded a script to the Script Center because I was constantly having to do that
    http://gallery.technet.microsoft.com/Update-User-attributes-bef81480
    • Proposed as answer by Yan Li_ Tuesday, January 14, 2014 2:57 AM
    • Marked as answer by AnnaWY Thursday, January 23, 2014 6:28 AM
    Sunday, January 12, 2014 4:56 AM
  • Thanks Brad for sharing it.

    That is a cool solution.... :)


    Knowledge is Power{Shell}.

    Sunday, January 12, 2014 5:01 AM
  • Hey Chris. Thanks for the info. I will give this a try when I get a chance. A couple questions, which might be stupid, but can I run this from an AD module powershell console all in one line. Or do I need to run single parts at once, or do I need to put it into a powershell script and run it?

    Also, just for my curiosity, I'm looking at this and I'm curious, does powershell know that "$SAM"=sAMAccountName? 

    Thanks again!

    -Scott

    Sunday, January 12, 2014 7:44 AM
  • Sorry I didn't see part of your question. ExtensionAttribute1 is literally the exact name of the attribute in AD. It will be used to have PIN# for each of the users in the CSV file. Thanks.
    Sunday, January 12, 2014 7:46 AM
  • Scott,

    You can paste that in Powershell ISE and save it, or you can paste it in notepad and save it as 'something.ps1'

    What you will ensure that you can do is run unsigned scripts.  By default, Powershell will not run scripts that aren't digitally signed by a trusted authority.

    To Get Around That:

    - Open run Powershell as Administrator.

    - Run this command: get-executionpolicy

    - If the results are 'Restricted', run this command 'set-executionpolicy bypass' then press enter to confirm.

    (If you are not going to regularly write and run your own scripts on this server, you can change it back after you run my script. More info @ http://technet.microsoft.com/en-us/library/ee176961.aspx

    To explain what the script is reading.

    1. Import-csv 'yourfile.csv' | foreach-object ({ -- This line is grabbing a file that contains columns

    2. $SAM = $_.samaccountname -- I'm just defining a new varibable to use that ensures it's in the right column.  It's not completely necessary, but it helps me understand what I'm doing in the execution line.

    3. $attrib = same thing as #2.

    4. write-host "Working on $sam..." -nonewline  -- All this is doing is writing to the screen what line it's on so you can following along.  I like to see what is happening.

    5. get-aduser $sam -- is using the variable I set before.  So whatever line it is on, it will be using the column 'samaccountname'

    6. set-aduser -replace @{extenstionattribute1="$attrib"} --This is the command that was failing you before.  Not all attributes can be set like you were trying before.  For example, '-description "IT Guy' or '-smartcardlogonrequired $true'

    7. })    - Closes the loop.

    I hope all this makes sense.  If you have previously copied my script, go regrab it again.  I am about to change it.  Line 2 needs to come AFTER line 3.  You can't use '$sam' before defining what '$sam' is :/    

    Oops.  Minor details.  Let me know if you have anymore questions.


    Chris Ream

    • Proposed as answer by Yan Li_ Tuesday, January 14, 2014 2:57 AM
    • Marked as answer by AnnaWY Thursday, January 23, 2014 6:28 AM
    Sunday, January 12, 2014 3:01 PM
  • Since the Set-ADUser cmdlet accepts sAMAccountNames to identify users, there is no need to use Get-ADUser. I would use

    Set-ADUser $_.User_Name @Replace{extensionAttribute1 = $_.extensionAttribute1}

    in the ForEach.


    Richard Mueller - MVP Directory Services

    • Proposed as answer by Yan Li_ Tuesday, January 14, 2014 2:57 AM
    • Marked as answer by AnnaWY Thursday, January 23, 2014 6:28 AM
    Sunday, January 12, 2014 7:02 PM
  • True. 30 ways to skin a cat. Lol.

    Chris Ream

    Sunday, January 12, 2014 9:29 PM
  • Hi,

    Just checking in to see if the suggestions were helpful. Please let us know if you would like further assistance.

    TechNet Subscriber Support

    If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.


    Regards, Yan Li

    Tuesday, January 14, 2014 2:58 AM
  • Hello

    You should not store any security information (like pin code or password) in AD attributes because all users can read them.

    In your example you forgot close brace.

    If you see << in console, then you should check all brackets and qiotes.

    Good luck)

    Tuesday, January 14, 2014 8:24 AM
  • Sorry for the late reply guy's. I used Chris's recommendation and it worked great. I appreciate everyone's feedback and help! Kolomiets, thank you for explaining why I was seeing that. That will come in handy.Have a great day everyone:).
    Thursday, January 23, 2014 6:00 PM