We help IT Professionals succeed at work.

How can I import data into the ipPhone field of AD from an excel spreadsheet containing user names and ip phone number?

54ITStaff
54ITStaff asked
on
805 Views
Last Modified: 2013-07-19
Can someone instruct me on how to perfrom an import into AD from an excel spreadsheet?  I have to add the IP Phone numbers to 3000 users and am not really sure on how to go about it.  I've fumbled around a little with ldifde and dsmod, but I really can't seem to get anything to work correctly.  Any insights are appreciated.

I have an excel spreadsheet containing the lastname name and firstname of the users and their associated ip phone numbers.  I'd like to use the format "lastname, firstname" for any importing since the distinguished name from the system that I exported this spreadsheet from does not match the distinguished name of the system I will be importing to.

Thanks for all your help.

Will
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
Hi Will.

Is ipPhone a custom attribute?  I don't think I've used that before....what "data type" is that value? A string, or an Array?

Anyway, let's see if we can change that value for just one user first.

Set strDN to the full distinguished name of a user, and see what happens.

Regards,

Rob.
strDN = "CN=John Smith,OU=Users,OU=Sites,DC=domain,DC=com"
strIPPhone = "1300429329
Set objUser = GetObject("LDAP://" & strDN)
objUser.PutEx "ipPhone", strIPPhone
objUser.SetInfo
MsgBox "Done"

Open in new window

Author

Commented:
Hi Rob,

Thank you for your response.  ipPhone is not a custom attribute, it is defined in the users AD profile (maybe it's just defined as phone).

I really have no idea what you're asking me to do when you say "Set strDN to the full distinguished name of a user, and see what happens".  How and where do I run your script?  I wish I knew more about scripting!!
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
OK, yeah, I was just flicking through the ADUC user Properties screens, and saw ipPhone...sorry, my mistake, I was just being ignorant.....

So, you're user account.....does it have an IPPhone defined?

Try this script. You can make strIPPhone a value, or no value, and it will clear it or add it.

See if this works.....

Rob.
Set objADSysInfo = CreateObject("ADSystemInfo")
Set objUser = GetObject("LDAP://" & objADSysInfo.UserName)
Const ADS_PROPERTY_CLEAR = 1
Const ADS_PROPERTY_UPDATE = 2
Const ADS_PROPERTY_APPEND = 3
Const ADS_PROPERTY_DELETE = 4
'strIPPhone = "1300429329"
strIPPhone = ""
If strIPPhone = "" Then
	objUser.PutEx ADS_PROPERTY_CLEAR, "ipPhone", ""
	objUser.SetInfo
	MsgBox "ipPhone cleared."
Else
	objUser.Put "ipPhone", strIPPhone
	objUser.SetInfo
	MsgBox "ipPhone added"
End If

Open in new window

Author

Commented:
Basically what I'm trying to do is some type of merge.  I have an excel spreadsheet that contains my users and their associated ipphone numbers (two seperate columns in the excel doc).  I would like to somehow manipulate that data so that I can get the ipphone number from the excel spreadsheet to populate the ipPhone field for each of our 3000+ users.

I hope this clarifies things a little better.
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
OK, so your Excel sheet has two columns, NTLoginName, and ipPhone number?

Try this script.  It will create a change log as well.  It opens a CSV file called
UserIPPhones.csv
and log to
UserIPPhones_Change_Log.csv

Regards,

Rob.
strInputFile = Replace(WScript.ScriptFullName, WScript.ScriptName, "") & "UserIPPhones.csv"
strOutputFile = "UserIPPhones_Change_Log.csv"
 
Set objExcel = CreateObject("Excel.Application")
Const xlUp = -4162
objExcel.Visible = True
Set objWB = objExcel.Workbooks.Open(strInputFile, False, False)
Set objSheet = objWB.Sheets(1)
 
Const ADS_SCOPE_SUBTREE = 2
Const ADS_PROPERTY_CLEAR = 1
Const ADS_PROPERTY_UPDATE = 2
Const ADS_PROPERTY_APPEND = 3
Const ADS_PROPERTY_DELETE = 4
 
Set objConnection = CreateObject("ADODB.Connection")
Set objCommand =   CreateObject("ADODB.Command")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"
Set objCommand.ActiveConnection = objConnection
 
objCommand.Properties("Page Size") = 1000
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE
 
Set objRootDSE = GetObject("LDAP://RootDSE")
 
strDetails = """Login Name"",""Display Name"",""Old IPPhone"",""New IPPhone"""
 
For intRow = 2 To objSheet.Cells(65536, "A").End(xlUp).Row
	strLoginName = CStr(objSheet.Cells(intRow, "A").Value)
	strIPPhone = CStr(objSheet.Cells(intRow, "B").Value)
	
	objCommand.CommandText = _
	    "SELECT adsPath FROM 'LDAP://" & objRootDSE.Get("defaultNamingContext")& "' WHERE objectClass='person' AND objectCategory='user' AND samAccountName = '" & strLoginName & "'"
	Set objRecordSet = objCommand.Execute
	 
	objRecordSet.MoveFirst
	Do Until objRecordSet.EOF
		Set objUser = GetObject(objRecordset.Fields("adsPath").Value)
		boolUpdatePhone = False
		If CStr(objUser.ipPhone) <> strIPPhone Then
			strOldPhone = CStr(objUser.ipPhone)
			boolUpdatePhone = True
			If strIPPhone = "" Then
				objUser.PutEx ADS_PROPERTY_CLEAR, "ipPhone", 0
			Else
				objUser.ipPhone = strIPPhone
			End If
		End If
		If boolUpdatePhone = True Then
			strDetails = strDetails & VbCrLf & """" & CStr(objUser.samAccountName) & """,""" & CStr(objUser.CN) & """"
			strDetails = strDetails & ",""" & strOldPhone & """,""" & strIPPhone & """"
			objUser.SetInfo
			'MsgBox "telephoneNumber was updated for " & strLoginName
		Else
			'MsgBox "No details were updated for " & strLoginName
		End If
		objRecordSet.MoveNext
	Loop
Next
 
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objOutputFile = objFSO.CreateTextFile(strOutputFile, True)
objOutputFile.Write strDetails
objOutputFile.Close
Set objOutputFile = Nothing
Set objFSO = Nothing
 
objWB.Close False
objExcel.Quit
Set objExcel = Nothing
 
MsgBox "Finished exporting. Please see " & strOutputFile

Open in new window

Author

Commented:
I will try this and get back to you ASAP.  Thanks so much.

Author

Commented:
Rob,

The two columns in the Excel spreadsheet are"

column 1: "Lastname, Firstname" (there is a space after the Lastname, if that matters)
column 2: "ipPhone"

Currently there is no ipPhone data in AD.  The spreadsheet I created was from our phone system's AD which is different than our AD that users login to.  Their login names are not the same which is why I have to use something like "Lastname, Firstname" as a way to link the two together.  Will this script work in the environment I've described?  If not, can we modify your script to make it work?

I REALLY appreciate your help Rob!  Thank you.
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
OK, so I've now changed it to get Lastname and Firstname, and match those up.

If it happens to find two or more users with the same name, it won't update them, but it will tell you that there are duplicates, so you have an opportuntiy to fix that for the next time you run the script.

Also, the ChangeLog will not have an entry for a user where nothing was changed. For example, if the user already has 3124545 as their number, and the CSV file has the same, it will not be logged.

Regards,

Rob.
strInputFile = Replace(WScript.ScriptFullName, WScript.ScriptName, "") & "UserIPPhones.csv"
strOutputFile = "UserIPPhones_Change_Log.csv"
 
Set objExcel = CreateObject("Excel.Application")
Const xlUp = -4162
objExcel.Visible = True
Set objWB = objExcel.Workbooks.Open(strInputFile, False, False)
Set objSheet = objWB.Sheets(1)
 
Const ADS_SCOPE_SUBTREE = 2
Const ADS_PROPERTY_CLEAR = 1
Const ADS_PROPERTY_UPDATE = 2
Const ADS_PROPERTY_APPEND = 3
Const ADS_PROPERTY_DELETE = 4
 
Set objConnection = CreateObject("ADODB.Connection")
Set objCommand =   CreateObject("ADODB.Command")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"
Set objCommand.ActiveConnection = objConnection
 
objCommand.Properties("Page Size") = 1000
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE
 
Set objRootDSE = GetObject("LDAP://RootDSE")
 
strDetails = """Last Name"",""First Name"",""Display Name"",""Old IPPhone"",""New IPPhone"""
 
For intRow = 2 To objSheet.Cells(65536, "A").End(xlUp).Row
	strLastName = Trim(CStr(Split(objSheet.Cells(intRow, "A").Value, ",")(0)))
	strFirstName = Trim(CStr(Split(objSheet.Cells(intRow, "A").Value, ",")(1)))
	strIPPhone = CStr(objSheet.Cells(intRow, "B").Value)
	
	objCommand.CommandText = _
	    "SELECT adsPath FROM 'LDAP://" & objRootDSE.Get("defaultNamingContext")& "' WHERE objectClass='person' AND objectCategory='user' " & _
	    "AND sn = '" & strLastName & "' AND givenName = '" & strFirstName & "'"
	Set objRecordSet = objCommand.Execute
	
	intRecordCount = objRecordSet.RecordCount
	If intRecordCount > 1 Then
		MsgBox "There are " & intRecordCount & " users named " & strLastName & ", " & strFirstName & VbCrLf & "No update was made."
	Else
		If Not objRecordSet.BOF Then objRecordSet.MoveFirst
		Do Until objRecordSet.EOF
			Set objUser = GetObject(objRecordset.Fields("adsPath").Value)
			boolUpdatePhone = False
			If CStr(objUser.ipPhone) <> strIPPhone Then
				strOldPhone = CStr(objUser.ipPhone)
				boolUpdatePhone = True
				If strIPPhone = "" Then
					objUser.PutEx ADS_PROPERTY_CLEAR, "ipPhone", 0
				Else
					objUser.ipPhone = strIPPhone
				End If
			End If
			If boolUpdatePhone = True Then
				strDetails = strDetails & VbCrLf & """" & CStr(objUser.SN) & """,""" & CStr(objUser.givenName) & """,""" & _
					CStr(objUser.CN) & """,""" & strOldPhone & """,""" & strIPPhone & """"
				objUser.SetInfo
				'MsgBox "telephoneNumber was updated for " & strLoginName
			Else
				'MsgBox "No details were updated for " & strLoginName
			End If
			objRecordSet.MoveNext
		Loop
	End If
Next
 
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objOutputFile = objFSO.CreateTextFile(strOutputFile, True)
objOutputFile.Write strDetails
objOutputFile.Close
Set objOutputFile = Nothing
Set objFSO = Nothing
 
objWB.Close False
objExcel.Quit
Set objExcel = Nothing
 
MsgBox "Finished exporting. Please see " & strOutputFile

Open in new window

Author

Commented:
Thank you so much for all your help this far.... now for my (hopefully) last question to you before I give this a try:  How do I run this script?  I have never run a script on a Windows box  before.  Can you walk me through this?  Please!
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
Sure, no problem.  Firstly, are you running this from a machine, with Domain Admin rights, that has Microsoft Excel installed?  You will need that.  It does not have to be run from a domain controller.

OK, so the code above, highlight all of that, and press CTRL + C to copy it to the clipboard.
Click Start --> Run, type Notepad and press Enter
Press CTRL + V to paste the code.
Click File --> Save As
In the "Save as type" box, select "All Files"
In the "File name" box, type something like "ImportIPPhones.vbs", including the quotes at either end.
Click OK

Now, that file location where you just saved that file, browse there with Windows Explorer, and double-click it to run it.

It will show Excel for a few seconds, while it opens the spreadsheet to read the values.  When it's finished, it will have created a new CSV file called UserIPPhones_Change_Log.csv

Regards,

Rob.

Author

Commented:
So I can run this script as described above from my workstation as long as I am logged in as a user with Domain Admin rights?  Does the excel spreadsheet have to be in any particular location?  Perhaps in the directory where this script is saved?

Other than that, I think I understand what to do.  Thanks so much!
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
>> So I can run this script as described above from my workstation as long as I am logged in as a user with Domain Admin rights?

Absolutely!  Because the script uses the LDAP provider, as long as your workstation can communicate with the network, and the script is run from a user profile with Domain Admin rights, you'll be able to alter the user properties.

>>  Does the excel spreadsheet have to be in any particular location?  Perhaps in the directory where this script is saved?

Yes, when the first two lines are like this (change the second one to that below):
strInputFile = Replace(WScript.ScriptFullName, WScript.ScriptName, "") & "UserIPPhones.csv"
strOutputFile = Replace(WScript.ScriptFullName, WScript.ScriptName, "") & "UserIPPhones_Change_Log.csv"

But, if you want to specify your own location, locally:
strInputFile = "C:\Temp\UserIPPhones.csv"
strOutputFile = "C:\Temp\UserIPPhones_Change_Log.csv"

Or somewhere on the network:
strInputFile = "\\Server1\Share\UserIPPhones.csv"
strOutputFile = "\\Server1\Share\UserIPPhones_Change_Log.csv"

Regards,

Rob.

Author

Commented:
Great.  Let's see how if it works!

Author

Commented:
Rob,

Here's what I've done so far:

1.  I placed the script and the excel doc (useripphones.csv) locally in c:\script\
2.  I edited your script as noted here >>>>   strInputFile = "C:\script\useripphones.csv"
                                                                      strOutputFile = "C:\script\UserIPPhones_Change_Log.csv"

Now, when I run this script, I recieve the following error:
Windows Script Host
Script: C:\script\importipphones1.vbs
Line: 7
Char: 1
Error: 'C:\script\useripphones.csv' could not be found.  Check the spelling of the files, make sure that the file has not been renamed, moved or deleted.
Code: 800A03EC
Source: Microsoft Office Excel

What am I doing wrong?
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
Hi, please check the file name that you're intending to use....

At point 1 above, you mention C:\Script\UserIPPhones.csv, however, the error in the script mentions C:\Script\ImportIPPhones.csv

Please check that line 7 does actually match up to the physical file name....

Rob.

Author

Commented:
Rob,

UserIPPhones.csv is the excel spreadsheet containing the two columns.  ImportIPPhones.vbs is the name of the script.  It looks like the error is telling me that it can't fine the excel document?  I'm really not sure where to go from here.
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Man, I can't thank you enough for helping me with this...I keep forgetting you not on Chicago time!!

Both the script and the excel doc are located on the same computer, in the same directory.  Also, I will check for the file type extension.

Here goes.....

Author

Commented:
Rob,

I had to remove line 89, and then it worked like a charm!  You are awesome!  Thank you so much, I wish my brain was as big as yours!

Author

Commented:
Thank you so much Rob.  Please let me know if you don't recievie your points (I upped them to 500).
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
Line 89?

Did you copy in
Open In New Window
??  That's part of EE, not the code.....

Anyway, glad we got it working!  Was it the file extension?

Rob.

Author

Commented:
Yes, Open in New Window is what I deleted...  I actually did that?  Well, now you know why you're helping me :)

It works beautifully.  I've never actually been able to close an issue I started on this site, so I hope I did it correctly and you get your points.  I doubled them for you.  Let me know if not ok.  Thanks again Rob.

Will
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
Yep, you closed it fine.  It looks originally you tried to close it without allocating an answer, but then you accepted my comment, giving it a good grade ;-)

Rob.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.