Feed a ADSI Script info from a CSV file.

  • Thread starter Thread starter James S. Borg
  • Start date Start date
J

James S. Borg

I am new to scripting, have been toying with them for 1 day, and have a
problem.

I am trying to update the telephoneNumber field for all user accounts. Doing
so by OU. I have exported the necessary information to a CSV file, using
CSVDE.exe, now I can edit the telephone field in a very convenient format.

I also created the following script:

Const ADS_PROPERTY_UPDATE = 2

Set objUser = GetObject _
("LDAP://CN=USERNAME,OU=ORGANIZATIONALUNIT,DC=Domain,DC=com")

objUser.Put "telephoneNumber", "EXT"

objUser.SetInfo

Now I want to feed this script the csv file, using the fields for CN and
telephoneNumber. Can anyone help me with this? Please keep in mind I am very
new to scripting and not hip on terms and common practices. I know it can be
done but without some direction it will probably take me too long to figure
it out, though I will continue trying and will post back any solution I
find.

Thank you for any assistance.
James Borg
 
James said:
I am new to scripting, have been toying with them for 1 day, and have a
problem.

I am trying to update the telephoneNumber field for all user accounts. Doing
so by OU. I have exported the necessary information to a CSV file, using
CSVDE.exe, now I can edit the telephone field in a very convenient format.

I also created the following script:

Const ADS_PROPERTY_UPDATE = 2

Set objUser = GetObject _
("LDAP://CN=USERNAME,OU=ORGANIZATIONALUNIT,DC=Domain,DC=com")

objUser.Put "telephoneNumber", "EXT"

objUser.SetInfo

Now I want to feed this script the csv file, using the fields for CN and
telephoneNumber. Can anyone help me with this? Please keep in mind I am very
new to scripting and not hip on terms and common practices. I know it can be
done but without some direction it will probably take me too long to figure
it out, though I will continue trying and will post back any solution I
find.
Click to expand...

OK using CSVDE we are exporting for each user only two attributes DN and
homePhone:

csvde -m -f users.csv -s DC1 -d "OU=lltest,dc=w2k3,dc=private" -r
"(objectClass=user)" -l DN,homePhone

Our file looks something like this:

DN,homePhone
"CN=Jan Kowalski,OU=LLTest,DC=w2k3,DC=private",75557555

we can remove first line with headers. Next, CSV is maybe not the best
choice becouse it is a little complicated to split file into separated
values - so I propose read this file into Excel and then save as TXT Tab
separated file.

Next, read the file:

<code>
Set oFs = CreateObject("Scripting.FileSystemObject")
Set oFile = oFs.OpenTextFile( strFilePath )
strUserAccounts = oFile.ReadAll()
</code>

strUserAccounts in this moment contains whole CSV file, and we have to
split it into separated lines

<code>
arrUserAccountsTmp = Split(strUserAccounts , vbCRLF)
</code>

Now arrUserAccountsTmp is an array, and every single element of this
array contains one line from original file. This line contains
attributes value separated with tabs, so in next step we read each
line, split it into separated values and modify telephone number value.

<code>

For each usrLine in arrUserAccountsTmp

'spliting the line on tabs
arrUser = Split (usrLine, vbTAB)

'DN is first attribute but base count for array starts with 0
strUserDN = arrUser(0)

'reading homePhone attribute from the CSV file
strUserPhone = arrUser(1) & "our value if needed"

'connect to user object using DN
Set objUser = GetObject("LDAP://" & strUserDN )

'write new phone number
objUser.Put "homePhone", strUserPhone
objUser.SetInfo

Set objUser = Nothing
Next

</code>

This is quickly written script tested on my VPC so it should work but
they may be some problems. maybe it isn't prettiest piece of script ever
made but it's a little late :(

And whole script code looks like this:

<code>

Set oFs = CreateObject("Scripting.FileSystemObject")
Set oFile = oFs.OpenTextFile( strFilePath )
strUserAccounts = oFile.ReadAll()

oFile.Close

arrUserAccountsTmp = Split(strUserAccounts , vbCRLF)

For each usrLine in arrUserAccountsTmp

'spliting the line on tabs
arrUser = Split (usrLine, vbTAB)

'DN is first attribute but base count for array starts with 0
strUserDN = arrUser(0)

'reading homePhone attribute from the CSV file
strUserPhone = arrUser(1) & "our value if needed"

'connect to user object using DN
Set objUser = GetObject("LDAP://" & strUserDN )

'write new phone number
objUser.Put "homePhone", strUserPhone
objUser.SetInfo

Set objUser = Nothing
Next

'Destroying objects

Set oFile = Nothing
Set oFS = Nothing

</code>
 
I have not implemented this yet and from your explanations I should be able
to do so without screwing up.
You have been very helpful thank you.
 
ptwilliams said:
Depending on the number of users (lots=big file size) though, you may find
it preferable to read from the text file a line at a time.

I usually read a line at a time into an array, and work from there.
Click to expand...

Good point Paul - mostly I work with files for ~1-5k users and read all file
at once. In most cases I'm modyfing these attributes directly in AD without
exporting them to the file but James prefer this way
 
I don't know how to read one line at a time. From what Tomasz says I
probably don't need to, my number of users is about 600.
I have run into a problem with the script however. It errors out with:

Line: 23
Char: 1
Error: The server is not operational
Code: 8007203A

So I located the line referenced and this is where it is supposed to be
pulling the information from the text file. Apparently it is not. I have
experimented and all I have so far is that if I take the line that is
supposed to be inserted from the text file and put it into the spot in the
script it is supposed to be inserted to it runs. However I then get an error
on Line: 17 Char: 1 Error: Subscript out of Range: '[number: 0]'. This is
supposed to be the user info from the text file, there is data there so I
don't know why it would fail here. My guess is that somewhere the code is
incorrectly referencing the text file. But where?

Now on line 20 in the quotation marks "Our Value if Needed", is this for me
to specify a value to enter for all users? Isn't it getting this value from
the text file?

The last section called destroying objects what is this for? Are two files
created to handle the text file and then they need to be closed or deleted
to prevent looping?

BTW The two of you seem pretty familiar with scripting, can you recommend a
book for a newbie like myself. Preferably one that focuses on ADSI
scripting. It seems like I can accomplish quite a bit if I knew what the
hell I was doing. On the other hand I could probably screw up quite a bit if
I am not cautious.

Thanks for any assistance.

Here is the script.

<code>
Set oFs = CreateObject("Scripting.FileSystemObject")

'Next Line has path of tab delimited text file, this file contains updated
phone values to be imported back into AD.
Set oFile = oFs.OpenTextFile("c:\downloads\test.txt")
strUserAccounts = oFile.ReadAll()

oFile.Close

arrUserAccountsTmp = Split(strUserAccounts , vbCRLF)

For each usrLine in arrUserAccountsTmp

'spliting the line on tabs
arrUser = Split (usrLine, vbTAB)

'DN is first attribute but base count for array starts with 0
strUserDN = arrUser(0)

'reading homePhone attribute from the CSV file
strUserPhone = arrUser(1) & "our value if needed"

'connect to user object using DN
Set objUser = GetObject("LDAP://" & strUserDN)

'write new phone number
objUser.Put "telephoneNumber", strUserPhone
objUser.SetInfo

Set objUser = Nothing
Next

'Destroying objects

Set oFile = Nothing
Set oFS = Nothing
</code>

Here are the contents of my text file:

"CN=_template1,OU=Testing,DC=Domain,DC=com" testing1
"CN=_template2,OU=Testing,DC=Domain,DC=com" testing2
"CN=_template3,OU=Testing,DC=Domain,DC=com" testing3
"CN=_template4,OU=Testing,DC=Domain,DC=com" testing4
 
James said:
I don't know how to read one line at a time. From what Tomasz says I
probably don't need to, my number of users is about 600.
I have run into a problem with the script however. It errors out with:

Line: 23
Char: 1
Error: The server is not operational
Code: 8007203A
Click to expand...


Line 23 in your script is probably the line when You are getting user
object based on text file DN data, something like that:

Set objUser = GetObject("LDAP://" & strUserDN)


and Your file looks like thath:
<quote>
"CN=_template1,OU=Testing,DC=Domain,DC=com" testing1
"CN=_template2,OU=Testing,DC=Domain,DC=com" testing2
"CN=_template3,OU=Testing,DC=Domain,DC=com" testing3
"CN=_template4,OU=Testing,DC=Domain,DC=com" testing4
</quote>
so if there is a TAB between firs and second field, after spliting it
You will get a DN as follows:
"CN=_template1,OU=Testing,DC=Domain,DC=com"

Notice, that the string will still contain quotation marks so Your full
line will look like this:

Set objUser =
GetObject("LDAP://""CN=_template1,OU=Testing,DC=Domain,DC=com")

and becouse of this You can connect thi this object - just get rid of
quotation mark from the text file (but be sure that You are using othe
characted like TAB as separator between fields).

Now on line 20 in the quotation marks "Our Value if Needed", is this for me
to specify a value to enter for all users? Isn't it getting this value from
the text file?
Click to expand...

If you don't need to modify value taken form file just get rid of this
part, this line can look like this:

strUserPhone = arrUser(1)

This was only an example .
The last section called destroying objects what is this for?
Click to expand...
Just to make some cleanup - this is my hapit to always destroy objects I
invoke.

Are two files
created to handle the text file and then they need to be closed or deleted
to prevent looping?
Click to expand...

No, this are two objects - one is FileSystemObject and second is handle
to file - If You will not do this by yourself nothing will go wrong, but
I like to cleanup after my code in memory :)
BTW The two of you seem pretty familiar with scripting, can you recommend a
book for a newbie like myself. Preferably one that focuses on ADSI
scripting. It seems like I can accomplish quite a bit if I knew what the
hell I was doing. On the other hand I could probably screw up quite a bit if
I am not cautious.
Click to expand...


This is really cool book:
http://www.rallenhome.com/books/adcookbook/toc.html

I like this too:
http://www.amazon.com/exec/obidos/ASIN/0735609314/002-3416938-8100051

here You will find a lot of resources:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnanchor/html/scriptinga.asp
http://www.winguides.com/scripting/
http://labmice.techtarget.com/scripting/WSH.htm


And here You will find a lot of examples and How to's:
http://www.microsoft.com/technet/scriptcenter/default.mspx
 
I removed made the changes you recommended now where the script is calling
the text file on line 17 I am receiving the following error:
Line: 17
Char:1
Error: Subscript out of range: '[number: 0]'

here is line 17
<code>
strUserDN = arrUser(0)
</code>
 
James said:
I removed made the changes you recommended now where the script is calling
the text file on line 17 I am receiving the following error:
Line: 17
Char:1
Error: Subscript out of range: '[number: 0]'

here is line 17
<code>
strUserDN = arrUser(0)
</code>
Click to expand...

What character is in Your text file between values:

"CN=_template4,OU=Testing,DC=Domain,DC=com" testing4
^^^ - this char

In my first post I was sugessting You to save file as "TAB Separated"
using Excel, and then in script I used vbTAB (which is constant for tab)
to split values

arrUser = Split (usrLine, vbTAB)

So, If there is no tab characters in the line split function will not
split it and arrUser will contain no fileds so calling arrUser(0) will
generate an error.
 
I saved the csv file as a tab delimited text file. Here is how it looks now
after the previous changes you recommended:
<data>
CN=_template1,OU=Testing,DC=Domain,DC=com Phone1
CN=_template2,OU=Testing,DC=Domain,DC=com Phone2
CN=_template3,OU=Testing,DC=Domain,DC=com Phone3
CN=_template4,OU=Testing,DC=Domain,DC=com Phone4
</data>
There is a tab between the CN value and the telephoneNumber value. As you
can see I removed the quotation marks. Now I am not sure if you want me to
remove the commas and replace them with tabs as well, if that's the case
then It would be very time consuming. If that is the case perhaps I could
modify the CSVDE utility to pull this information out as separately.


Tomasz Onyszko said:
James said:
I removed made the changes you recommended now where the script is
calling the text file on line 17 I am receiving the following error:
Line: 17
Char:1
Error: Subscript out of range: '[number: 0]'

here is line 17
<code>
strUserDN = arrUser(0)
</code>
Click to expand...

What character is in Your text file between values:

"CN=_template4,OU=Testing,DC=Domain,DC=com" testing4
^^^ - this char

In my first post I was sugessting You to save file as "TAB Separated"
using Excel, and then in script I used vbTAB (which is constant for tab)
to split values

arrUser = Split (usrLine, vbTAB)

So, If there is no tab characters in the line split function will not
split it and arrUser will contain no fileds so calling arrUser(0) will
generate an error.
Click to expand...
 
I removed the commas and replaced them with a tab to test now I receive the
following error:
Line: 23
Char: 1
Error: An operations error occurred
Code: 80072020
Source: (null)

Here is line 22 and 23 of the code:
<code>

'connect to user object using DN
Set objUser = GetObject("LDAP://" & strUserDN )

</code>
Please let me know if I was correct in removing the commas and replacing
them with the tab.

James S. Borg said:
I saved the csv file as a tab delimited text file. Here is how it looks
now after the previous changes you recommended:
<data>
CN=_template1,OU=Testing,DC=Domain,DC=com Phone1
CN=_template2,OU=Testing,DC=Domain,DC=com Phone2
CN=_template3,OU=Testing,DC=Domain,DC=com Phone3
CN=_template4,OU=Testing,DC=Domain,DC=com Phone4
</data>
There is a tab between the CN value and the telephoneNumber value. As you
can see I removed the quotation marks. Now I am not sure if you want me to
remove the commas and replace them with tabs as well, if that's the case
then It would be very time consuming. If that is the case perhaps I could
modify the CSVDE utility to pull this information out as separately.


Tomasz Onyszko said:
James said:
I removed made the changes you recommended now where the script is
calling the text file on line 17 I am receiving the following error:
Line: 17
Char:1
Error: Subscript out of range: '[number: 0]'

here is line 17
<code>
strUserDN = arrUser(0)
</code>
Click to expand...

What character is in Your text file between values:

"CN=_template4,OU=Testing,DC=Domain,DC=com" testing4
^^^ - this char

In my first post I was sugessting You to save file as "TAB Separated"
using Excel, and then in script I used vbTAB (which is constant for tab)
to split values

arrUser = Split (usrLine, vbTAB)

So, If there is no tab characters in the line split function will not
split it and arrUser will contain no fileds so calling arrUser(0) will
generate an error.
Click to expand...
Click to expand...
 
James said:
I saved the csv file as a tab delimited text file. Here is how it looks now
after the previous changes you recommended:
<data>
CN=_template1,OU=Testing,DC=Domain,DC=com Phone1
CN=_template2,OU=Testing,DC=Domain,DC=com Phone2
CN=_template3,OU=Testing,DC=Domain,DC=com Phone3
CN=_template4,OU=Testing,DC=Domain,DC=com Phone4
</data>
There is a tab between the CN value and the telephoneNumber value. As you
can see I removed the quotation marks. Now I am not sure if you want me to
Click to expand...

No, this should work just perfect - with Your data and script (script
code once again quoted below) on my VPC it works smooth.
then It would be very time consuming. If that is the case perhaps I
could
modify the CSVDE utility to pull this information out as separately.
Click to expand...

next time don't export data with CSVDE but modify them directly in AD
with a script.

OK, I'm going sleep now - will be back here in few hours - will be glad
to see your post with feedback


<quote>
Set oFs = CreateObject("Scripting.FileSystemObject")
Set oFile = oFs.OpenTextFile( "user.txt" )
strUserAccounts = oFile.ReadAll()

oFile.Close

arrUserAccountsTmp = Split(strUserAccounts , vbCRLF)

For each usrLine in arrUserAccountsTmp

arrUser = Split (usrLine, vbTab)

'DN is first attribute but base count for array starts with 0
strUserDN = arrUser(0)
wscript.echo arrUser(0)

'reading homePhone attribute from the CSV file
strUserPhone = arrUser(1)

'connect to user object using DN
Set objUser = GetObject("LDAP://" & strUserDN )

'write new phone number
objUser.Put "homePhone", strUserPhone
objUser.SetInfo

Set objUser = Nothing
Next

'Destroying objects

Set oFile = Nothing
Set oFS = Nothing
</quote>
 
Tomasz
I never checked to see if the telephoneNumber field was changed on the user
accounts, it has. So this script is running and doing what it is supposed to
be doing. However I still receive the error message referring to the
following code:

<code>
strUserDN = arrUser(0)
</code>

I completely copied your code, the only changes I made was the user prompt
and changed homePhone to telephoneNumber. The code errors either way, but
performs the changes as instructed.

<error>
Line: 14
Char: 1
Error: Subscript out of range: '[number: 0]'
Code: 800A0009
</error>

I could accept the error and still use the script but I would really like to
know why it still gives me the error. I have run this from a Windows XP
Professional Workstation with Domain Admin Credentials and on a server
logged in as a domain admin. Still the problem persists, but the script
performs the operation.

Do you program for a living? This is tremendously frustrating.
 
James S. Borg said:
Tomasz
I never checked to see if the telephoneNumber field was
changed on the user
accounts, it has. So this script is running and doing what it
is supposed to
be doing. However I still receive the error message referring
to the
following code:

&lt;code&gt;
strUserDN = arrUser(0)
&lt;/code&gt;

I completely copied your code, the only changes I made was the
user prompt
and changed homePhone to telephoneNumber. The code errors
either way, but
performs the changes as instructed.

<error>
Line: 14
Char: 1
Error: Subscript out of range: '[number: 0]'
Code: 800A0009
</error>

I could accept the error and still use the script but I would
really like to
know why it still gives me the error. I have run this from a
Windows XP
Professional Workstation with Domain Admin Credentials and on
a server
logged in as a domain admin. Still the problem persists, but
the script
performs the operation.

Do you program for a living? This is tremendously frustrating.



message
James S. Borg wrote:
Click to expand...
&nbsp;>> I saved the csv file as a tab delimited text file.
Here is how it looks
&nbsp;>> now after the previous changes you recommended:
&nbsp;>> &lt;data&gt;
&nbsp;>> CN=_template1,OU=Testing,DC=Domain,DC=com Phone1
&nbsp;>> CN=_template2,OU=Testing,DC=Domain,DC=com Phone2
&nbsp;>> CN=_template3,OU=Testing,DC=Domain,DC=com Phone3
&nbsp;>> CN=_template4,OU=Testing,DC=Domain,DC=com Phone4
&nbsp;>> &lt;/data&gt;
&nbsp;>> There is a tab between the CN value and the
telephoneNumber value. As you
&nbsp;>> can see I removed the quotation marks. Now I am not
sure if you want me
&nbsp;>> to
No, this should work just perfect - with Your data and script (script code
once again quoted below) on my VPC it works smooth.
Click to expand...
&nbsp;> >then It would be very time consuming. If that is the
case perhaps I could
&nbsp;> >modify the CSVDE utility to pull this information out
as separately.
next time don't export data with CSVDE but modify them directly in AD with
a script.

OK, I'm going sleep now - will be back here in few hours - will be glad to
see your post with feedback


&lt;quote&gt;
Set oFs = CreateObject("Scripting.FileSystemObject")
Set oFile = oFs.OpenTextFile( "user.txt" )
strUserAccounts = oFile.ReadAll()

oFile.Close

arrUserAccountsTmp = Split(strUserAccounts , vbCRLF)

For each usrLine in arrUserAccountsTmp

arrUser = Split (usrLine, vbTab)

'DN is first attribute but base count for array starts with 0
strUserDN = arrUser(0)
wscript.echo arrUser(0)

'reading homePhone attribute from the CSV file
strUserPhone = arrUser(1)

'connect to user object using DN
Set objUser = GetObject("LDAP://" & strUserDN )

'write new phone number
objUser.Put "homePhone", strUserPhone
objUser.SetInfo

Set objUser = Nothing
Next

'Destroying objects

Set oFile = Nothing
Set oFS = Nothing
&lt;/quote&gt;
Click to expand...
Click to expand...

Hi,

I do this on a regular basis, but I use VBScript instead. I have never
had much luck with the csvde.exe. There is also a great tool called
ADModify. I have heard it is great. My scripts are here:
http://www.sd61.bc.ca/windows2000

http://www.gotdotnet.com/workspaces/workspace.aspx?id=f5cbbfa9-e46b-4a7a-8ed8-3e44523f32e2

Cheers,

Lara
 
James said:
Tomasz
I never checked to see if the telephoneNumber field was changed on the user
accounts, it has. So this script is running and doing what it is supposed to
Click to expand...

cool :)

(...)
<error>
Line: 14
Char: 1
Error: Subscript out of range: '[number: 0]'
Code: 800A0009
</error>

I could accept the error and still use the script but I would really like to
know why it still gives me the error. I have run this from a Windows XP
Click to expand...


(...)


This is pretty simple - error is not in a code but in a source file :) -
your file looks something like this:
<quote>
line1
line2

</quote>

Remar this one additional empty line :) so the script is breaking up in
first iteration file on the vbCRLF character, which is end of line, so
the last line from the file will be ... yes, empty - and when You are
trying next to split is on the vbTAB character it does nothing, then
when You are trying to call this empty line it gives You an error -
subscript out of range.

IF Your file will not have empty line at the end it will not generate an
error, or just add additional if check to be sure thath You are not
trying to do something with empty line

<code>
For each usrLine in arrUserAccountsTmp
if usrLine <> "" Then

... code to make user changes ...

Ned if
Next
Do you program for a living? This is tremendously frustrating.
Click to expand...

No, I'm just a sys admin :)
 
Absolutely correct there was a blank line. You are good.
You have been a tremendous help.

Thank you

Tomasz Onyszko said:
James said:
Tomasz
I never checked to see if the telephoneNumber field was changed on the
user accounts, it has. So this script is running and doing what it is
supposed to
Click to expand...

cool :)

(...)
<error>
Line: 14
Char: 1
Error: Subscript out of range: '[number: 0]'
Code: 800A0009
</error>

I could accept the error and still use the script but I would really like
to know why it still gives me the error. I have run this from a Windows
XP
Click to expand...


(...)


This is pretty simple - error is not in a code but in a source file :) -
your file looks something like this:
<quote>
line1
line2

</quote>

Remar this one additional empty line :) so the script is breaking up in
first iteration file on the vbCRLF character, which is end of line, so the
last line from the file will be ... yes, empty - and when You are trying
next to split is on the vbTAB character it does nothing, then when You are
trying to call this empty line it gives You an error - subscript out of
range.

IF Your file will not have empty line at the end it will not generate an
error, or just add additional if check to be sure thath You are not trying
to do something with empty line

<code>
For each usrLine in arrUserAccountsTmp
if usrLine <> "" Then

... code to make user changes ...

Ned if
Next
Do you program for a living? This is tremendously frustrating.
Click to expand...

No, I'm just a sys admin :)
Click to expand...
 
Back
Top