Create AD users from a CSV file 2

[IanPike]

Can anybody give me direction for creating new AD users from a CSV file

 

[markdmac]

Can you use Excel instead?

If so, this will do the trick:

'==========================================================================
'
' NAME: createNewUsers.vbs
'
' AUTHOR: Mark D. MacLachlan , The Spider's Parlor
' URL:

http://www.thespidersparlor.com

' 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

 

[mrmovie]

If you had the 1st row specifying your field names then you could shorten things a little, having column 1 being the username

For i = 2 To Rows.Count
Set oUser = o.Create("user","CN=" & Cells(i,1).Value)
For j = 2 To Columns.Count
oUser.Put Cells(1,j).Value, Cells(i,j).Value
Next
oUser.SetInfo
Set oUser = Nothing
Next

 

[markdmac]

Mr. Movie, BRILLIANT SUGGESTION! A star for you!

I've just started modifying my script to include your suggestion. Only problem with it is it makes it a bit more difficult for the error checking. (like making sure all fields were filled in). Still I like it and am going to continue working on this to incorporate the error checking I feel is necessary.

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

Regards,

Mark

 

[mrmovie]

thanks markdmac, it start chruning through my mind about checking if the oUser got created. its all very well me posting something shorter buts its not much use if it doesnt do the error checking as well, thanks to the 'option explicit and on error resume next' post it seems to be preventing me from sleeping

 

[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?

 

[mrmovie]

which line is 136?

 

[markdmac]

Line 136 is a setinfo. You need to look at the three lines above it and be sure that what is being set has data.

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

Regards,

Mark

 

[fuzzymallets]

Thanks for all the help. You people are great.

I have one more question.

Is there some way using this script to skip accounts that already exist? I have got the script to work, but I have some account that already exist. So when I run the script it goes through until there is a account that exist and then stops.

 

[markdmac]

You could add an On Error Resume Next statement at the top of the script. It would then ignore the creation of an account that already exists and would still apply the rest of the settings that you are specifying in the spreadsheet. Handy for updating info.

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

Regards,

Mark

 

[fuzzymallets]

Thank you very much. Everything worked great.