Add 500 user to AD from Exel 5

[CRAIG2432]

Hello.

I have a Win 2003 R2 server and i need to add 500 users. At the moment the users do not exist exept on a Exel spreadsheet.

Is there an easy way of importing them?

Many thanks

 

[porkchopexpress]

You need to look at CSVDE

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

 

[markdmac]

The code for this is not very hard. You can easily modify this to support adding additional information. Use ADSIEdit to verify property names.

Some common ones are:
"company" = company
"displayName" = first_name & " " & last_name
"facsimileTelephoneNumber" = fax
"givenName" = first_name
"l" = city
"mail" = email
"mailNickname" = login
"name" = first_name & " " & last_name
"otherTelephone" = "Ext. "& extension
"postalCode" = zip
"sn" = last_name
"st" = state
"streetAddress" = street
"telephoneNumber" = phone

Make sure that you use passwords that meet your domains complexity requirements. "Abcd1234" as a default often works great, have the users change after first login.

If you use Exchange then you can also add additional SMTP addresses with the proxyAddresses property. That one is an array so you need to use PutEx instead of Put.

[green]'==========================================================================
'
' NAME: SimpleCreateUsersFromExcel.vbs
'
' AUTHOR: Mark D. MacLachlan , The Spider's Parlor
' URL: [URL unfurl="true"]http://www.thespidersparlor.com[/URL]
' DATE  : 7/1/2007
' COPYRIGHT (c) 2007 All Rights Reserved
'
' COMMENT: 
'
'    THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF
'    ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED To
'    THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
'    PARTICULAR PURPOSE.
'
'    IN NO EVENT SHALL THE SPIDER'S PARLOR AND/OR ITS RESPECTIVE SUPPLIERS 
'    BE LIABLE FOR ANY SPECIAL, INDIRECT OR CONSEQUENTIAL DAMAGES OR ANY
'    DAMAGES WHATSOEVER RESULTING FROM LOSS OF USE, DATA OR PROFITS,
'    WHETHER IN AN ACTION OF CONTRACT, NEGLIGENCE OR OTHER TORTIOUS
'    ACTION, ARISING OUT OF OR IN CONNECTION WITH THE USE OR PERFORMANCE
'    OF THIS CODE OR INFORMATION.
'
'==========================================================================

'Bind to the open Excel spreadsheet[/green]
Set xl = GetObject(,"Excel.Application")
[green]'Assume the Excel spreadsheet has a header row[/green]
xlRow = 2
[green]
'Get the AD Domain info  Example: DC=SPIDERSPARLOR,DC=COM[/green]
Set oRootDSE = GetObject("LDAP://RootDSE")
oDomain = oRootDSE.Get("DefaultNamingContext")
[green]
'Specify container or OU to create new accounts in (Default container shown below)[/green]
Set objOU = GetObject("LDAP://CN=Users," & oDomain)
[green]
'Now read from Excel and create the users[/green]
do until len(xl.cells(xlRow, 1).value) = 0
    login      = xl.cells(xlRow, 1).value
    first_name = xl.cells(xlRow, 2).value
    last_name  = xl.cells(xlRow, 3).value
    password   = xl.cells(xlRow, 4).value
    
    Set objUser = objOU.Create("User", "cn=" & login)
	objUser.Put "sAMAccountName", login
	objUser.SetInfo
	oUser.Put "givenName", first_name
	oUser.Put "sn", last_name
	objUser.SetInfo
	oUser.SetPassword password
	oUser.SetInfo
	oUser.AccountDisabled = False
	oUser.SetInfo[green]
	'Now require user to change the password[/green]
	objUser.Put "pwdLastSet", 0
	objUser.SetInfo
	[green]'increment the row number[/green] 
	xlRow = xlRow + 1
loop

I hope you find this post helpful.

Regards,

Mark

Check out my scripting solutions at

http://www.thespidersparlor.com/vbscript

Work SMARTER not HARDER. The Spider's Parlor's Admin Script Pack is a collection of Administrative scripts designed to make IT Administration easier! Save time, get more work done, get the Admin Script Pack.

 

[tfg13]

I know I have seen something like this before, Mark. Didn't someone ask for this about a year ago?

BTW, the star is on it's way.

PCE, CSVDE looks interesting. Thanks for the link, and your star is on it's way too....

 

[58sniper]

csvde is really nice (not discounting Mark's suggestion). I recently used it to create nearly 500 accounts for a client. I was then able to modify the same .csv file and dump it to a batch file and use dsmod to set the passwords as well.

Pat Richard, MCSE MCSA:Messaging CNA
Microsoft Exchange MVP

 

[CRAIG2432]

Thanks People..

I realy meen it. For someone how is just starting out in the world of IT I have posted quite a few question hear and you always point me in the right direction. Many thanks again.

When I get home this evening I will be making a donation to Tek Tips.

Regards

Craig

 

[markdmac]

tfg13, yes, I tend to post variations of this every once in a while.

I hope you find this post helpful.

Regards,

Mark

Check out my scripting solutions at

http://www.thespidersparlor.com/vbscript

Work SMARTER not HARDER. The Spider's Parlor's Admin Script Pack is a collection of Administrative scripts designed to make IT Administration easier! Save time, get more work done, get the Admin Script Pack.

 

[markdmac]

Pat, what I like about doing this with vbscript over csvde is not only can I set the password, but I can also add functionality to create user directories, set permissions on the folder and share them. Along with that, if my spreadsheet includes a managers email address, I can fire off an email to them letting them know their request was completed. With vbscript I can essentially create an entire workflow process.

Furthermore, I can have the vbscript flush the Excel spreadsheet or archive it. I can then schedule the script to run daily, looking for new requests. I then only need to update the Excel spreadsheet (or have others do that).

For those more advanced with scripting, a SQL database can be used as the source instead of Excel, making this a more enterprise ready solution.

I know you are a fan of scripting Pat, so the above is presented more for the benefit of Craig.


I hope you find this post helpful.

Regards,

Mark

Check out my scripting solutions at

http://www.thespidersparlor.com/vbscript

Work SMARTER not HARDER. The Spider's Parlor's Admin Script Pack is a collection of Administrative scripts designed to make IT Administration easier! Save time, get more work done, get the Admin Script Pack.

 

[porkchopexpress]

Nice one Mark.

 

[lemapas]

I need to do the same thing but i have my users on a .mdb file , someone have an idea to create these users on one shot ?

Thanks.

Pascal.

 

[markdmac]

Connecting to a MDB file is not very hard via vbscript, just some extra steps.

Const adOpenStatic = 3
Const adLockOptimistic = 3

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open _
    "Provider = Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source = 'C:\Scripts\Test.mdb'" 

sqlQuery = "Select * from Table1"
objRecordSet.Open sqlQuery , _
objConnection, adOpenStatic, adLockOptimistic


Do Until objRecordset.EOF
    UserName = objRecordset("UserName")
    FirstName = objRecordset("FirstName")
    LastName = objRecordset("LastName")
    Password = objRecordset("Password")
    
    Wscript.Echo UserName
    WScript.Echo FirstName
    WScript.Echo LastName
    WScript.Echo Password

    Wscript.Echo
    objRecordset.MoveNext
Loop

I would however suggest you go a simpler way. Copy and paste your data from access to Excell. From withing table view do Ctrl+A switch to Excel and Ctrl+V.

I hope you find this post helpful.

Regards,

Mark

Check out my scripting solutions at

http://www.thespidersparlor.com/vbscript

Work SMARTER not HARDER. The Spider's Parlor's Admin Script Pack is a collection of Administrative scripts designed to make IT Administration easier! Save time, get more work done, get the Admin Script Pack.

 

[lemapas]

Bonjour Mark,

Thanks for your help.
i will try it for sure.

Pascal.

 

[bickyatbickyz]

Is it neccessary to install microsoft excel on server or i can create spreadsheet on diff machine and just use the file here in the server.

 

[markdmac]

There are ways to open an Excel spreadsheet and read from it as a data source without having Excel installed, however support for that is discontinued so I suggest avoiding it. Instead what is easy to do is save your Excel spreadsheet as a CSV file.

You can then read the text line by line from the CSV and use the Split function to break each line up into an array that can then be used in the above posted code.

Instead of referencing something like:
login = xl.cells(xlRow, 1).value
first_name = xl.cells(xlRow, 2).value
last_name = xl.cells(xlRow, 3).value
password = xl.cells(xlRow, 4).value

You would be doing something like this:

On Error Resume Next

'open the file system object
Set oFSO = CreateObject("Scripting.FileSystemObject")
set WSHShell = wscript.createObject("wscript.shell")
'open the data file
Set oTextStream = oFSO.OpenTextFile("sourcefile.csv")
'make an array from the data file
DataList = Split(oTextStream.ReadAll, vbNewLine)
'close the data file
oTextStream.Close

For Each Line In DataList
    LineArray = Split(Line,";")
    login = LineArray(0)
    first_name = LineArray(1)
    last_name  = LineArray(2)
    password   = LineArray(3)

[green]   'Worker process code goes here.[/green]
Next

I hope you find this post helpful.

Regards,

Mark

Check out my scripting solutions at

http://www.thespidersparlor.com/vbscript

Work SMARTER not HARDER. The Spider's Parlor's Admin Script Pack is a collection of Administrative scripts designed to make IT Administration easier! Save time, get more work done, get the Admin Script Pack.

 

[fasolaomotomilola]

Thanks mark,

I dont know scripting at all.

Can you show me how to practically apply this script.
Should i save it as a batch file??


Thank you.

 

[markdmac]

save the script in notepad as a text file. Give the file a VBS extension.

I hope you find this post helpful.

Regards,

Mark

Check out my scripting solutions at

http://www.thespidersparlor.com/vbscript

Work SMARTER not HARDER. The Spider's Parlor's Admin Script Pack is a collection of Administrative scripts designed to make IT Administration easier! Save time, get more work done, get the Admin Script Pack.

 

[fasolaomotomilola]

Thanks for your prompt response.

Where do I locate the excel sheet, the script?.

Thank you

 

[markdmac]

Does not matter. Only open the one excel file and execute the script on the same computer. The script will find the running Excel process and work from there.

I hope you find this post helpful.

Regards,

Mark

Check out my scripting solutions at

http://www.thespidersparlor.com/vbscript

Work SMARTER not HARDER. The Spider's Parlor's Admin Script Pack is a collection of Administrative scripts designed to make IT Administration easier! Save time, get more work done, get the Admin Script Pack.