Import Telephone Numbers from Excel for multiple AD User Accounts

[Dublin73]

Hi, we've migrated hundreds of users from an NT domain into 2003 AD. I have a spreadsheet ( .csv file, as opposed to .xls ) with five columns in it. The first column has a title of ""givenName" (in Column A, Row 1) and includes the first name of all of our users underneath it. The second column is titled "telephoneNumber" with the telephone extensions of all our users underneath it, the third column is titled "description", the fourth is titled "sn" for surname, and the fifth is the "DN" for each user. DN as in, CN=username,OU=Users,DC=domain,DC=com

I've extracted all of this information using the Microsoft CSVDE utility.

What we're trying to do is populate the AD User Account properties in a specific OU for the following....

"First name:"
"Last name:"
"Description"
"Telephone number:"
"E-Mail"

The "Display Name" for all of the users is already filled in. I've attempted to do this by borrowing a script that I've copied and pasted from this post...

thread329-1087357

I can get the following populated...

"First name:"
"Last name:"
"E-Mail"

but cannot populate the Telephone Number or Description fields. Here is the version of the script that I have edited.

Option Explicit
Dim oContainer, InfoFile, FileSystem, SourceFile, sLine(), aLine,DN
Dim DC, DC1, DC2, OU, i, oUser2, oCont2, aLine1

'Prompt to get AD domain name to make an LDAP connection to
DN = InputBox ("Please enter AD Domain name")

'split the domain name at the period, so I can feed the LDAP query correctly
DC = Split(DN, ".",-1,1)
DC1 = DC(0)
DC2 = DC(1)

'verify the user typed in the correct domain name, going to add some more prompts to this later
MsgBox ("The domain name you entered is " & DC1 & "." & DC2)

'Get the name of the OU the user wants to modify users objects for. all user objects in the OU
'will be modified
OU = InputBox ("Please enter the User container OU that you wish to modify")

'line 20 Let user verify the entered the correct OU
MsgBox ("All user objects in the " & OU & " OU will be modified")

'Make LDAP connection to the domain and OU specified above
Set oContainer=GetObject("LDAP://OU=" & OU &",DC=" & DC1 & ",DC=" & DC2)

EnumerateUsers oContainer 'This sub works fine, it puts the correct data where I want it
AddInfo oContainer 'This is the sub I am having problems With

Sub EnumerateUsers(oCont)
'This sub gets the UserDisplay attribute in AD, splits it and populates the First Name
'Last Name, e-mail address attrib for the user object. Also does the AD logon name and domain
'name
Dim oUser, UserCN, UserDisplay, givenName, sn, telephoneNumber, description, aLine, fInit, GoNext
For Each oUser In oCont
Select Case LCase(oUser.Class)
Case "user"


If Not IsEmpty(oUser.cn) Then
UserCN = oUser.cn
End If

If Not IsEmpty(oUser.DisplayName) Then
UserDisplay = oUser.DisplayName
aLine = split(UserDisplay, " ",-1,1)
givenName = aLine(0)
sn = aLine(1)
End If

fInit = Mid(givenName,1,1)


If IsEmpty (oUser.Mail) Then
oUser.Put "mail", LCase(givenName) & "." & LCase(sn) & "@mydomain.com"
End If

If IsEmpty (oUser.GivenName) Then
oUser.Put "GivenName", givenName
End If

If IsEmpty (oUser.sn) Then
oUser.Put "sn", sn
End If

If IsEmpty (oUser.UserPrincipalName) Then
oUser.Put "UserPrincipalName", UserCN & "@domain.com"
End If

oUser.SetInfo

EnumerateUsers oUser
'This sub should read a .csv file, put the attributes in an array, then go through the users in the
'OU and add the values from the array
End Select
Next
End Sub


Sub AddInfo(oCont)

Dim FileSystem,SourceFile,sLine,aLine1,givenName,telephoneNumber,description,sn,i,oUser
Set FileSystem = WScript.CreateObject("Scripting.FileSystemObject")
Set SourceFile = FileSystem.OpenTextFile("C:\csvdeexport.csv",1)

i=1
For Each oUser In oCont
Select Case LCase(oUser.Class)
Case "user"

if SourceFile.atEndOfStream then
wscript.echo "Line: " & i & vbcrlf & "Prematured end, not enough data to process."
else
sLine=SourceFile.ReadLine
aLine1 = Split(sLine, ",",-1,1)
if ubound(aLine1)<>6 then

givenName = aLine1(0)
telephoneNumber = aLine1(1)
description = aLine1(2)

If IsEmpty (oUser.telephoneNumber) Then
oUser.Put "telephoneNumber", telephoneNumber
End If

If IsEmpty (oUser.description) Then
oUser.Put "description", description
End If

oUser.SetInfo
end if
i=i+1
end if
case else
'do nothing

End Select
Next
SourceFile.close
set SourceFile=nothing
set FileSystem=nothing

End Sub

Rather than filling in the Telephone Number and Description for the individual users, the script enters the titles of both of these columns for the users. In other words in the User Account properties under telephone, I don't have their 4 digit extension, I have the column title "telephone". The same applies for description. How do I tell the script to say, here's the user's name, now populate the user's telephone number with the corresponding number in the "telephone" column in the csvdeexport.csv file?

any takers on this one?

 

[mrmovie]

have the top row of you excel spreadsheet have the actual real LDAP/WinNT property you want to the column to contain.

have your script first read the top row of the spreadsheet into a dictionary or array.

you can then read the row for each user and if the value <> "" for the cell, check the cell column number for which property you are updating (by querying the dictionary) then you can issue a Put(dicObject.Item(columnindex")) to update the user object

this means you code with never need to change if you want to update/mod a new LDAP property, all you need to do is add a new column with the header matching the name of the property

 

[Dublin73]

Thanks mrmovie! The top row of the spreadsheet has the actual LDAP properties:

givenName, telephoneNumber, description, sn and DN.

You mention putting the top row of the spreadsheet into an array. I thought that this section of the script was putting the top row of the spreadsheet into an array...

'This sub should read a .csv file, put the attributes in an array, then go through the users in the
'OU and add the values from the array
End Select
Next
End Sub


Sub AddInfo(oCont)

Dim FileSystem,SourceFile,sLine,aLine1,givenName,telephoneNumber,description,sn,i,oUser
Set FileSystem = WScript.CreateObject("Scripting.FileSystemObject")
Set SourceFile = FileSystem.OpenTextFile("C:\csvdeexport.csv",1)

Do I have this section incorrect?

 

[mrmovie]

Set dicTopLine = CreateObject("Scripting.Dictionary")

Do While Not objTS.AtEndOfStream
strTemp = objTS.ReadLine
i = i + 1
If i = 1 Then
aArray = Split(strTemp)
For Each aElement In aArray
dicTopLine.Add dicTopLine.Count + 1, aElement
Next
Else
aArray = Split(strTemp)
For j = 1 To UBound(aArray)
oUser.Put dicTopLine.Item(j), aArray(j)
Next
End If
Loop


you might want some defense in there and you might something more clever to get round properties which dont like being assigned to strings but I was just putting forward an idea to make your code dynamic....