Import users using a csv file

[betanet]

Hi All,
I am working in a test environment and am trying to import a group of users into AD on a server 2003 domain controller, I used a csv file created on a windows 2000 server and it won’t work, I keep getting the error about password complexity. So I created some user accounts on the 2003 server exported them to a csv file, deleted the accounts and tried to import them, the new error I get is “unwilling to perform” and “access to the attribute is not permitted because the attribute is owned by the SAM”
This is driving me crazy, does anyone have a csv file I could edit, that works.

Cheers,
Gerry

 

[DirtyPimp]

Hi Mate the first error password complexity is due to security settings in the Domain Security policy. Goto Administrative tools and then select Domain Security policy. then under account policies and then password policy..Disable password complexity.

the other error ur getting is due to try to add something that is controlled by SAM. i.e ObjectSid

 

[markdmac]

Here is a script I wrote that will create users for you from an Excel spreadsheet. You could import your CSV data and get it into the columns as I have specified int he script and it shoud do the trick for you.

You will need to run this froma machine with Excel and be logged in with admin rights. Make the spreadsheet be the only open Excel spreadsheet.

'==========================================================================
'
' NAME: createNewUsers.vbs
'
' AUTHOR: Mark D. MacLachlan , The Spider's Parlor
' URL: [URL unfurl="true"]http://www.thespidersparlor.com[/URL]
' DATE  : 04/21/2003
' COPYRIGHT 2003 The Spider's Parlor, All Rights Reserved
'
' COMMENT: 
' Creates users based on Excel spreadsheet information.
' Excel spreadsheet  formatting:
' Row 1 to have headings
' Column A (1) to have login
' Column B (2) to have first_name 
' Column C (3) to have last_name  
' Column D (4) to have phone      
' Column E (5) to have extension  
' Column F (6) to have fax        
' Column G (7) to have email      
' Column H (8) to have street     
' Column I (9) to have suite     
' Column J (10) to have city  
' Column K (11) to have state      
' Column L (12) to have zip    
'=====================================

set x = getobject(,"excel.application")
r = 2

Const c = "US"
Const co = "UNITED STATES"
'Change the following with your company info
Const company = "Company XYZ"
Const ou_name = "Users"

do until len(x.cells(r, 1).value) = 0

    login      = x.cells(r, 1).value
    first_name = x.cells(r, 2).value
    last_name  = x.cells(r, 3).value
    phone      = x.cells(r, 4).value
    extension  = x.cells(r, 5).value
    fax        = x.cells(r, 6).value
    email      = x.cells(r, 7).value
    street     = x.cells(r, 8).value
    suite      = x.cells(r, 9).value
    city       = x.cells(r, 10).value
    state      = x.cells(r, 11).value
    zip    =cstr(x.cells(r, 12).value)


    if len(first_name) = 0 then 
       first_name = " "
       fn=0 
    end if

    if len(last_name) = 0 then 
       last_name = " " 
       ln=0
    end if

    if len(phone) = 0 then  
       phone = " " 
    end if

    if len(extension) = 0 then 
       extension = " " 
    end if

    if len(fax) = 0 then 
       extension = " " 
    end if

    if len(email) = 0 then 
       email = " " 
    end if

    if len(street) = 0 then 
       street = " " 
    end if

    if len(suite) = 0 then 
       suite = " " 
    end if

    if len(city) = 0 then 
       city = " " 
    end if

    if len(state) = 0 then 
       state = " " 
    end if

    if len(zip) = 0 then 
       zip = " " 
    end if
  
  'Change the following with the proper LDAP path for your network.
    set o = getobject("LDAP://SERVER.XYZ.com.local/CN=Users,DC=XYZ,DC=com,DC=local")

    if err then
        x.cells(r, 15).value = err.number & ":  " & err.description
        err.Clear
    end if

            
	
    
    if fn + ln = 0 then 
       fullName = x.cells(r, 1).value 
    else 
       fullName = first_name & " " & last_name
    end if


	
    Set oUser = o.Create("user","CN=" & fullName)
    oUser.Put "sAMAccountName", login
    oUser.SetInfo
    oUser.Put "userPrincipalName", email
    oUser.SetInfo
    oUser.Put "c", c
    oUser.Put "co", co
    oUser.Put "company", company
    oUser.Put "displayName", first_name & " " & last_name
    oUser.Put "facsimileTelephoneNumber", fax
    oUser.Put "givenName", first_name
    oUser.SetInfo
    oUser.Put "l", "Phoenix"
    oUser.Put "mail", email
    oUser.Put "mailNickname", login
    'oUser.Put "name", first_name & " " & last_name
    oUser.Put "otherTelephone", "Ext. "& extension
    oUser.Put "postalCode", zip
    oUser.SetInfo
    oUser.Put "sn", last_name
    oUser.Put "st", state
    oUser.Put "streetAddress", street & vbCrLf & Suite
    oUser.Put "telephoneNumber", phone & "Ext. "& extension
    oUser.SetPassword login
    oUser.SetInfo
    oUser.AccountDisabled = False
    oUser.SetInfo
	

    If Err.Number <> 0 And Err.Number <> -2147019886 Then
        x.cells(r, 15).value = err.number & ":  " & "ID creation error"
    Else

    x.cells(r, 15).value = "created"
        end if


    r = r + 1
    

    set objOU = Nothing
    set oUser = Nothing    
    set o = nothing
Err.Clear
Loop


set x = nothing

msgbox "done"

I hope you find this post helpful. Please let me know if it was.

Regards,

Mark

 

[markdmac]

One more thing, please be sure to edit the LDPA path to make it match your environment.

I hope you find this post helpful. Please let me know if it was.

Regards,

Mark

 

[fuzzymallets]

Every time I try to use the script I get an error.
The error says
Script: *location of script file*
Line: 136
Char: 5
Error: The specified directory service attribute or value does not exist.
Code: 8007200A
Source: (null)

What am i doing wrong?

 

[martyh]

Install the tools from the 2003 CD (or DL from web) and then you can use the command line tools to create your users. There are a bunch of them; dsadd, dsget, dsquery etc.

I use Excel to create the command lines using LOOKUP (to select the OUs) and CONCATENATE so that all I do is put the username in a cell and Excel creates the command for me. Then cut and paste in a batch file and voila! tons of users faster than you can say GUI!

I also use xcacls.vbs to assign/check permissions. Here are some examples:

md \\HCSD-TRANS\Students$\sniderka

creates the home directory folder

RMTSHARE  \\HCSD-TRANS\Students$\sniderka=D:Students\sniderka

creates the share

dsadd user "CN=sniderka,ou=hs admin,ou=bldgadmin,ou=accounts,dc=hboe,dc=org" -hmdir \\HCSD-TRANS\Students$\sniderka -hmdrv H:

creates the user account and assign home dir

xcacls.vbs \\HCSD-TRANS\Students$\sniderka /g HCSD\sniderka:m HCSD\"Domain Admins":f /f /s /t

recursively removes any existing permissions and assign modify for user and full for Domain Admins

HTH




Marty
Network Admin
Hilliard Schools

 

[superjet]

CSVDe

http://www.computerperformance.co.uk/Logon/CSVDE_LDIFDE.htm

 

[markdmac]

Take a look at your data and at the script. That line you are getting the error on is a setinfo command which is where it is applying the data to ad. You will notice that there are only a few items being applied there as t e script does a setinfo every so often to aid in such troubleshooting.

Note that looking back at this script I had it hard coded to make the location Phoenix, for Arizona which is where I am. You'll need to tweak that.

I hope you find this post helpful. Please let me know if it was.

Regards,

Mark