• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 684
  • Last Modified:

Script Read data form excel and update the Active Directory 2003

Dear,

I have a case in a project. We have Active Directory windows 2003, I export all users to excel sheet to update their information. Now the data ready to be imported in the active directory. We are looking for a script that will read from the excel sheet and apply the change (modified user information such as Department, Manager & etc) in the active directory depending in the user name (Log-in name).

Regards,
0
devdept
Asked:
devdept
1 Solution
 
Joseph DalyCommented:
This is possible. What I did was take my excel sheet with all the info and concatenate each entry on the last free cell. I used the exclamation mark as a delimiter. So something like

username!displayname!lastname!firstname etc and save that to a text file.

The script below parses the entry for username and then updates AD using the attributes listed.
for /F "delims=! tokens=1,2,3,4,5,6,7,8" %%h IN (filename.txt) Do dsquery user -samid "%%h" | dsmod user -display "%%i" -ln "%%j" -fn "%%k" -title "%%l" -office "%%m" -desc "%%l" -empid "%%o

Open in new window

0
 
Joseph DalyCommented:
So basicallly one line of my text file refferenced in that script looked like this.

The entries are as follows
1. user account name (samid)
2. Display name
3. Last name
4. First name
5. Title
6. Location
7. Department
8. Employee id
fakeuser!User, Fake!User!Fake!Beancounter!Boston!Information Technology!9999

Open in new window

0
 
Joseph DalyCommented:
I hope this helps. If we were in person I could explain and show you better than typing but if you have any questions or problems just ask I will try my best. I know this script works as I had to do the same project as you.
0
Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

 
Ron MalmsteadInformation Services ManagerCommented:
I use excel all of the time to "concatenate", strings to run a script against multiple computers.
The excel formula is "=concatenate"

So if you have a list of the users, and all of the information you want to update in seperate columns, this is very do-able.  
You will need to install win2k3 support tools...

Using msexcel, you can "concatenate" strings to construct a batch file to fix everyone's information by using DSQUERY.exe and DSMOD.exe.
Below is an example of how to change a users department in a command line using dsquery and dsmod..

dsquery user -name "bill wilcop" | dsmod user -dept "FL company"

I've attached an excel file as an example of what i'm talking about...

When you have it all ready....you copy the entire finished command line column into notepad and save it as a batch file...put a "pause" as the last line.....and run the batch file

concatenatescripting.xls
0
 
devdeptAuthor Commented:
Dear All,
Thanks for your replay, i attached excel which have my information. I need a help to make Script or any way to apply the information in the active directory
 

Example.xls
0
 
Ron MalmsteadInformation Services ManagerCommented:
Look at the excel file you sent me... I added the formula for you.


Example-1-.xls
0
 
Ron MalmsteadInformation Services ManagerCommented:
dsmod user /?

-upn <UPN>              Sets the UPN value to <UPN>.
-fn <FirstName>         Sets user first name to <FirstName>.
-mi <Initial>           Sets user middle initial to <Initial>.
-ln <LastName>          Sets user last name to <LastName>.
-display <DisplayName>  Sets user display name to <DisplayName>.
-empid <EmployeeID>     Sets user employee ID to <EmployeeID>.
-pwd {<Password> | *}   Resets user password to <Password>. If *, then
                        you are prompted for a password.
-desc <Description>     Sets user description to <Description>.
-office <Office>        Sets user office location to <Office>.
-tel <Phone#>           Sets user telephone# to <Phone#>.
-email <Email>          Sets user e-mail address to <Email>.
-hometel <HomePhone#>   Sets user home phone# to <HomePhone#>.
-pager <Pager#>         Sets user pager# to <Pager#>.
-mobile <CellPhone#>    Sets user mobile# to <CellPhone#>.
-fax <Fax#>             Sets user fax# to <Fax#>.
-iptel <IPPhone#>       Sets user IP phone# to <IPPhone#>.
-webpg <WebPage>        Sets user web page URL to <WebPage>.
-title <Title>          Sets user title to <Title>.
-dept <Department>      Sets user department to <Department>.
-company <Company>      Sets user company info to <Company>.
-mgr <Manager>          Sets user's manager to <Manager>.
-hmdir <HomeDir>        Sets user home directory to <HomeDir>. If this is
                        UNC path, then a drive letter to be mapped to
                        this path must also be specified through -hmdrv.
-hmdrv <DriveLtr>:      Sets user home drive letter to <DriveLtr>:
-profile <ProfilePath>  Sets user's profile path to <ProfilePath>.
-loscr <ScriptPath>     Sets user's logon script path to <ScriptPath>.
-mustchpwd {yes | no}   Sets whether the user must change his password (yes)
                        or not (no) at his next logon.
-canchpwd {yes | no}    Sets whether the user can change his password (yes)
                        or not (no). This setting should be "yes"
                        if the -mustchpwd setting is "yes".
-reversiblepwd {yes | no}
                        Sets whether the user password should be stored using
                        reversible encryption (yes) or not (no).
-pwdneverexpires {yes | no}
                        Sets whether the user's password never expires (yes)
                        or not (no).
-acctexpires <NumDays>  Sets user account to expire in <NumDays> days from
                        today. A value of 0 sets expiration at the end of
                        today.
                        A positive value sets expiration in the future.
                        A negative value sets expiration in the past.
                        A string value of "never" sets the account
                        to never expire.
-disabled {yes | no}    Sets whether the user account is disabled (yes)
                        or not (no).
{-s <Server> | -d <Domain>}
                        -s <Server> connects to the domain controller (DC)
                        with name <Server>.
                        -d <Domain> connects to a DC in domain <Domain>.
                        Default: a DC in the logon domain.
-u <UserName>           Connect as <UserName>. Default: the logged in user.
                        User name can be: user name, domain\user name,
                        or user principal name (UPN).
-p <Password>           Password for the user <UserName>. If * then prompt
                        for password.
-c                      Continuous operation mode. Reports errors but
                        continues with next object in argument list
                        when multiple target objects are specified.
                        Without this option, the command exits on the
                        first error.
-q                      Quiet mode: suppress all output to standard output.
{-uc | -uco | -uci}     -uc Specifies that input from or output to pipe is
                        formatted in Unicode.
                        -uco Specifies that output to pipe or file is
                        formatted in Unicode.
                        -uci Specifies that input from pipe or file is
                        formatted in Unicode.
0
 
exx1976Commented:
I don't know what language you were looking to do this in, but here's a basic example of some vbs code that takes the SAMAccountName from the first cell, the Manager name from the second cell, and the Department from the third cell and updates AD.  It can easily be expanded to include other AD fields.  It begins looking for data on row 2, assuming row 1 is a header record.

HTH,
exx



Set oNetwork = CreateObject("wscript.network")
Domain = oNetwork.UserDomain
xlsname = "path to my spreadsheet.xls"
Set oExcelApp = CreateObject("Excel.Application")
Set oWorkbook = oExcelApp.Workbooks.Open(xlsname)
Set oWorksheet = oWorkbook.Worksheets(1)
oWorksheet.Activate
oExcelApp.Visible = False
iCounter = 2
bEmpty = False
Do Until bEmpty = True    
   strCell = oWorksheet.Cells(iCounter, 1)
   If strCell = "" Then
        bEmpty = True
   Else
    	EditUser oWorksheet.Cells(iCounter, 1), oWorksheet.Cells(iCounter, 2), oWorksheet.Cells(iCounter, 3)
	iCounter = iCounter + 1
   End If
Loop
oExcelApp.Quit
WScript.Echo("Done!")
 
Sub EditUser(SAMAccountName, Manager, Department)
	UserADSPath = GetObjectDN(SAMAccountName, Domain)
         If UserADSPath <> "?" Then
	       Set oUser = GetObject(UserADSPath)
	       oUser.Manager = Manager
	       oUser.Department = Department
	       oUser.SetInfo()
         Else
                WScript.Echo("User " & SAMAccountName & " not found")
         End If
End Sub
 
Function GetObjectDN(strObject, strDomain)
	On Error Resume Next : Err.Clear
	Set objNameTranslate = CreateObject("NameTranslate")
	objNameTranslate.Init 3, ""
	objNameTranslate.Set 3, strdomain & "\" & strObject
	strObjectDN = objNameTranslate.Get(1)
	If Err.Number <> 0 Then
		strObjectDN = "?"
	End If
	Set objNameTranslate = Nothing
	On Error Goto 0
	GetObjectDN = strObjectDN
End Function

Open in new window

0
 
musickmannCommented:
I suppose I'll have to open a new question...but I have a very similar problem to what exx1976: seems to have solved, but I'm having some difficulty making the script work for my situation, I get "Wrong number of arguments or invalid property assignemnt : 'EditUser'"

I need to change user passwords based on a list in excel, here's what I tried to do, don't have much experience with VB....so I'm sure it's all kinds of messed up.

If I need to open a new question for this just let me know.
Set oNetwork = CreateObject("wscript.network")
Domain = oNetwork.UserDomain
xlsname = "c:\ehs.xls"
Set oExcelApp = CreateObject("Excel.Application")
Set oWorkbook = oExcelApp.Workbooks.Open(xlsname)
Set oWorksheet = oWorkbook.Worksheets(1)
oWorksheet.Activate
oExcelApp.Visible = False
iCounter = 2
bEmpty = False
Do Until bEmpty = True    
   strCell = oWorksheet.Cells(iCounter, 1)
   If strCell = "" Then
        bEmpty = True
   Else
    	EditUser oWorksheet.Cells(iCounter, 1), oWorksheet.Cells(iCounter, 2), oWorksheet.Cells(iCounter, 3)
	iCounter = iCounter + 1
   End If
Loop
oExcelApp.Quit
WScript.Echo("Done!")
 
Sub EditUser(SAMAccountName, Password)
	UserADSPath = GetObjectDN(SAMAccountName, student.lakeschools.local)
         If UserADSPath <> "?" Then
	       Set oUser = GetObject(UserADSPath)
	       oUser.Password = Password
	       oUser.SetInfo()
         Else
                WScript.Echo("User " & SAMAccountName & " not found")
         End If
End Sub
 
Function GetObjectDN(strObject, strDomain)
	On Error Resume Next : Err.Clear
	Set objNameTranslate = CreateObject("NameTranslate")
	objNameTranslate.Init 3, ""
	objNameTranslate.Set 3, strdomain & "\" & strObject
	strObjectDN = objNameTranslate.Get(1)
	If Err.Number <> 0 Then
		strObjectDN = "?"
	End If
	Set objNameTranslate = Nothing
	On Error Goto 0
	GetObjectDN = strObjectDN
End Function

Open in new window

0
 
askpcguy909Commented:
If all you want to do is change user passwords just use the DSMOD user command and combine it with a For /F script.  There are plenty of examples online about using FOR /F with a text file that contains two parameters.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now