none
Creating user accounts from an excel spreadsheet question

    Question

  • I have a question.  The article here:  http://msdn.microsoft.com/en-us/library/ms974568.aspx  which discusses creating AD user accounts from an Excel spreadsheet - this appears to be only supported on a Windows 2000/2003 AD server and NOT a Windows 2008 AD server.  Is this correct?  If this script is not supported on Windows 2008 - can anyone point me in the right direction for direction on creating a script that is supported on Windows 2008?

    I have 200 users sitting in a spreadsheet that I need to get into AD, and I really don't want to input them one by one.  I have a lot of info on the users too (first/last name, location, phone #, dept, manager, email, phone ext, secondary phone, title, address, city/state/zip, cell phone).

    Thanks
    Thursday, February 18, 2010 7:17 PM

Answers

  • Steve
     
    Using Notepad Copy the text below here into a file and save as NEWUSERS.PS1
     
    It's a very wordy script mostly to try and give you a feel about how variables are accessed in Powershell etc etc.

    It requires

    Rights on the Domain in question to Create users
    QUEST ACTIVEROLES Cmdlets
    Powershell (any version)


    The NEW-QADUSER is one line.   And yes, you can Create Home folders in Powershell as well :)
     
    BE REALLY CAREFUL the first time.  I have Sun Virtual Box (*free*) and Hyper-V Server 2008R2 to setup test environments and try my scripts first to be safe.  Virtualization is free, Data Recovery isn't.
     
    Cheers and let me know how it goes ...
     
    Sean
    The Energized Tech
     
    ----------------------------------

    Steve's Sample CSV file is formatted as follows

    FIRSTNAME,LASTNAME,MI,USERNAME,TITLE,LOCATION,ROLE,DEPARTMENT,EMAIL,EXT,MOBILE,ADDRESS,CITY,STATE,ZIP,COUNTRY,IMMEDIATESUPERVISOR

    BOB,JONES,,BJONES,CLERICAL,01-MANTECA,CLERICAL-FT,ACCOUNTING,bjones@test.com,1166,,555 Main Street,SomeCity,CA,90210,USA,BGATES 

    ------------------------------ Cut Below Here NEWUSER.PS1 ----------------------------
    #
    # This script ASSUMES a Domain called "mydomain.local" You must edit appropriate spots to reflect the
    # CORRECT domain name in your environment.  Also ensure and test and test and test and TEST AGAIN
    # in a TEST domain vs production before unleashing the script
    #
    #
    # Get a temporary password for the users.  If you don't the accounts will create no problem but
    # they will be "Disabled" accounts.
    #
    $PASSWORD=READ-HOST 'Enter Temporary User Password-' -assecurestring
    #
    # IMPORT CSV LIST OF USER NAMES and Store them into a Variable
    #
    $LIST=IMPORT-CSV C:\USERLIST.CSV
    #
    # Go through EACH item in the list (Header row is treated as variable names by default)
    #
    FOREACH ($USER in $LIST) {
    #
    # I could just Assign straight in from the Cmdlet but I'm putting them into
    # Individual variables so you can see how the data is
    # referenced (Check your CSV headers, those are the exact names
    # but Not case sensitive
    #
    $Firstname=$USER.FIRSTNAME
    $Lastname=$USER.LASTNAME
    $Middleinitial=$USER.MIDDLEINITIAL
    $USERNAME=$USER.USERNAME
    #
    # Now here's where Powershell takes off. I COULD manually type in the Username or have Powershell
    # Generate it for me.  Cool eh?
    # so the above line COULD be
    # $FIRSTLETTER=($FIRSTNAME+' ').Substring(0,1).Trimend()
    # $USERNAME=$FIRSTLETTER+$LASTNAME
    #
    # Also lines starting with a # are comments and don't do anything other than
    # Read information
    #
    $TITLE=$USER.TITLE
    $LOCATION=$USER.LOCATION
    $ROLE=$USER.ROLE
    $DEPARTMENT=$USER.DEPARTMENT
    $Email=$USER.EMAIL
    $EXTENSION=$USER.EXTENSION
    $MOBILE=$USER.MOBILE
    $ADDRESS=$USER.ADDRESS
    $CITY=$USER.CITY
    $STATE=$USER.STATE
    $ZIP=$USER.ZIP
    $COUNTRY=$USER.COUNTRY
    $IMMEDIATESUPERVISOR=$USER.IMMEDIATESUPERVISOR
    #
    #
    # Now there are other bits needed like your Domain
    # But you don't need to put all that into the CSV, Just let
    # Powershell figure it out :)
    #
    #
    $domain='@mydomain.local'
    #
    $ALIAS=$Username
    $UPN=$Username+$domain
    #
    $DISPLAYNAME=$FIRSTNAME+" "+$LASTNAME
    $Company='Our Company'
    $Phone='209-555-1212 '+$EXTENSION
    $PostalZip=$ZIP
    $StateProv=$STATE
    $Address=$ADDRESS
    $Web='www.somewebsitename.com'
    $StateProv=$STATE
    $Office=$LOCATION
    $Description=$TITLE
    $JobTitle=$ROLE
    $Department=$DEPARTMENT
    $Fax=''
    #
    #
    # SAM USERID cannot be greater than 20 characters - Legacy
    #
    #
    $SAM=$UserID
    $Sam=(($Sam+'                    ').Substring(0,20)).Trimend()
    #
    #
    #
    # Now the fun stuff... Make those users!
    #
    #
    #
    NEW-QADUSER -ParentContainer 'mydomain.local/Imported' -Name $DISPLAYNAME -UserPassword $PASSWORD -City $CITY -Company $COMPANY -Department $DEPARTMENT -email $EMAIL -FAX $FAX -Firstname $FIRSTNAME -Lastname $LASTNAME -Mobilephone $MOBILEPHONE -Office $OFFICE -Phonenumber $PHONENUMBER -Postalcode $POSTALZIP -samaccountname $SAM -StateorProvince $StateProv -StreetAddress $Address -Title $JOBTITLE -UserPrincipalName $UPN -webpage $WEB -Description $DESCRIPTION -displayname $DISPLAYNAME
    #
    #
    #
    }
    Friday, February 19, 2010 10:52 PM

All replies

  • Easiest way I would use is with Powershell and the Quest Active Directory Cmdlets (Free to download).  When that you can save that spreadsheet as a CSV file.  I did a 100+ user import and it took all of a few minutes to do once done.

    The beautiful part as well is the imported users are disabled by Default if you don't assign a password.

    Example

    If your headers per column are like this example....

    FIRSTNAME,LASTNAME,DEPT,SAM,LoginID,
    JOHN,SMITH,ACCOUNTING,jsmith,jsmith@abc.com
    MARY,JONES,RECEPTION,mjones,mjones@abc.com
    JERRY,ICECREAMGUY,BOSS,jicecreamguy,jicecreamguy@abc.com

    And the name of the file is called USERS.CSV

    In Powershell with Quest it would be

    # Prompt for Password to assign to users temporarily
    $PASSWORD=READ-HOST 'Enter Temporary Password' -Assecurestring

    $USERLIST=$IMPORT-CSV C:\IMPORT\USERS.CSV

    Foreach ($USER in $USERLIST)
    {
    $NAME=$USER.FIRSTNAME+" "+$USER.LASTNAME

    NEW-QADUSER -ParentContainer 'Users' -Name $NAME -UserPassword $PASSWORD -Company 'ABC Company' -Department $User.DEPT -Firstname $User.FIRSTNAME -Lastname $User.LASTNAME -Samaccountname $SAM -UserPrincipalName $User.LoginID
    }

    This is a really bad example and quickly done, if you have a sample row header from the Excel, I can get you a script that will get the job done no problem :)

    Sean
    The Energized Tech

    Thursday, February 18, 2010 7:59 PM
  • If the VBScript program works with 2000/2003 AD, there is no reason why it would not work with 2008 or 2008 R2. The article you linked was written in 2004 before there was any knowledge of 2008. I made a quick glance at the code and saw nothing of concern.

    I also have a sample VBScript program to create users in bulk from a spreadsheet linked here:

    http://www.rlmueller.net/CreateUsers.htm

    Richard Mueller
    MVP ADSI
    Friday, February 19, 2010 2:29 AM
    Moderator
  • Sean,

    Thanks for the info and offer to help out.  I have a spreadsheet - not in the format you described above, but I can easily put it in the format.  I have other fields as well, such as title, location, role, department, email, phone #'s, address, city, state, zip, country, and immediate supervisor (who reports to).

    I wonder how we could incorporate that too?

    You can email me here directly:  steve02a AT hotmail.com

    I totally appreciate your help with this..

    Thanks

    Steve
    Friday, February 19, 2010 6:51 PM
  • Steve
     
    Using Notepad Copy the text below here into a file and save as NEWUSERS.PS1
     
    It's a very wordy script mostly to try and give you a feel about how variables are accessed in Powershell etc etc.

    It requires

    Rights on the Domain in question to Create users
    QUEST ACTIVEROLES Cmdlets
    Powershell (any version)


    The NEW-QADUSER is one line.   And yes, you can Create Home folders in Powershell as well :)
     
    BE REALLY CAREFUL the first time.  I have Sun Virtual Box (*free*) and Hyper-V Server 2008R2 to setup test environments and try my scripts first to be safe.  Virtualization is free, Data Recovery isn't.
     
    Cheers and let me know how it goes ...
     
    Sean
    The Energized Tech
     
    ----------------------------------

    Steve's Sample CSV file is formatted as follows

    FIRSTNAME,LASTNAME,MI,USERNAME,TITLE,LOCATION,ROLE,DEPARTMENT,EMAIL,EXT,MOBILE,ADDRESS,CITY,STATE,ZIP,COUNTRY,IMMEDIATESUPERVISOR

    BOB,JONES,,BJONES,CLERICAL,01-MANTECA,CLERICAL-FT,ACCOUNTING,bjones@test.com,1166,,555 Main Street,SomeCity,CA,90210,USA,BGATES 

    ------------------------------ Cut Below Here NEWUSER.PS1 ----------------------------
    #
    # This script ASSUMES a Domain called "mydomain.local" You must edit appropriate spots to reflect the
    # CORRECT domain name in your environment.  Also ensure and test and test and test and TEST AGAIN
    # in a TEST domain vs production before unleashing the script
    #
    #
    # Get a temporary password for the users.  If you don't the accounts will create no problem but
    # they will be "Disabled" accounts.
    #
    $PASSWORD=READ-HOST 'Enter Temporary User Password-' -assecurestring
    #
    # IMPORT CSV LIST OF USER NAMES and Store them into a Variable
    #
    $LIST=IMPORT-CSV C:\USERLIST.CSV
    #
    # Go through EACH item in the list (Header row is treated as variable names by default)
    #
    FOREACH ($USER in $LIST) {
    #
    # I could just Assign straight in from the Cmdlet but I'm putting them into
    # Individual variables so you can see how the data is
    # referenced (Check your CSV headers, those are the exact names
    # but Not case sensitive
    #
    $Firstname=$USER.FIRSTNAME
    $Lastname=$USER.LASTNAME
    $Middleinitial=$USER.MIDDLEINITIAL
    $USERNAME=$USER.USERNAME
    #
    # Now here's where Powershell takes off. I COULD manually type in the Username or have Powershell
    # Generate it for me.  Cool eh?
    # so the above line COULD be
    # $FIRSTLETTER=($FIRSTNAME+' ').Substring(0,1).Trimend()
    # $USERNAME=$FIRSTLETTER+$LASTNAME
    #
    # Also lines starting with a # are comments and don't do anything other than
    # Read information
    #
    $TITLE=$USER.TITLE
    $LOCATION=$USER.LOCATION
    $ROLE=$USER.ROLE
    $DEPARTMENT=$USER.DEPARTMENT
    $Email=$USER.EMAIL
    $EXTENSION=$USER.EXTENSION
    $MOBILE=$USER.MOBILE
    $ADDRESS=$USER.ADDRESS
    $CITY=$USER.CITY
    $STATE=$USER.STATE
    $ZIP=$USER.ZIP
    $COUNTRY=$USER.COUNTRY
    $IMMEDIATESUPERVISOR=$USER.IMMEDIATESUPERVISOR
    #
    #
    # Now there are other bits needed like your Domain
    # But you don't need to put all that into the CSV, Just let
    # Powershell figure it out :)
    #
    #
    $domain='@mydomain.local'
    #
    $ALIAS=$Username
    $UPN=$Username+$domain
    #
    $DISPLAYNAME=$FIRSTNAME+" "+$LASTNAME
    $Company='Our Company'
    $Phone='209-555-1212 '+$EXTENSION
    $PostalZip=$ZIP
    $StateProv=$STATE
    $Address=$ADDRESS
    $Web='www.somewebsitename.com'
    $StateProv=$STATE
    $Office=$LOCATION
    $Description=$TITLE
    $JobTitle=$ROLE
    $Department=$DEPARTMENT
    $Fax=''
    #
    #
    # SAM USERID cannot be greater than 20 characters - Legacy
    #
    #
    $SAM=$UserID
    $Sam=(($Sam+'                    ').Substring(0,20)).Trimend()
    #
    #
    #
    # Now the fun stuff... Make those users!
    #
    #
    #
    NEW-QADUSER -ParentContainer 'mydomain.local/Imported' -Name $DISPLAYNAME -UserPassword $PASSWORD -City $CITY -Company $COMPANY -Department $DEPARTMENT -email $EMAIL -FAX $FAX -Firstname $FIRSTNAME -Lastname $LASTNAME -Mobilephone $MOBILEPHONE -Office $OFFICE -Phonenumber $PHONENUMBER -Postalcode $POSTALZIP -samaccountname $SAM -StateorProvince $StateProv -StreetAddress $Address -Title $JOBTITLE -UserPrincipalName $UPN -webpage $WEB -Description $DESCRIPTION -displayname $DISPLAYNAME
    #
    #
    #
    }
    Friday, February 19, 2010 10:52 PM
  • How do you incorporate the "middle initial" in this script assuming you have the MI field in the .CSV file?

    Does the SAM field stand for the login username?

     

    Thanks

    Kwek Tee

    Thursday, July 07, 2011 4:49 PM
  • The SAM field assigns a value to the sAMAccountName attribute in AD, which is the "pre-Windows 2000 logon" name of the user. This shows up on the "Account" tab in ADUC, and is sometimes referred to as the loginID or username. It must be unique in the domain. The Name (Common Name of the user) must only be unique in the OU.

    The middle initial (the "Initials" field on the "General" tab of ADUC) corresponds to the initials attribute in AD. The alias in the AD modules (Set-ADUser) for this is "initials". I don't know about the Quest cmdlets, but you can use Get-Help to find out.

     


    Richard Mueller - MVP Directory Services
    Thursday, July 07, 2011 7:18 PM
    Moderator
  • trying to run this, i keep getting:

    New-QADUser : The server is unwilling to process the request. (Exception from HRESULT: 0x80072035)

    All it will do is create the account with the name from the sample csv and create it disabled - it doesn't import any of the other address type information

    Friday, September 21, 2012 4:56 PM
  • Hi,

    This question has been marked as answered for more than a year. If you still need help, please start a new question.

    Bill

    Friday, September 21, 2012 5:33 PM
    Moderator