VBS script to parse a csv, get data and move AD object

Question

I am quite new in vbs and I would like some of your help on this script.

Basically I need a script that will get my current computername, look into a csv file to get the new related computername and then use that new name to move the corresponding account in the AD to a new OU.

I already know how to get my current computername and how to move an object to a new OU, these are things I have already done, but I am really not confident about parsing the csv looking for the new computername based on my current one.

The new name is the value just after the current name in the csv file. Only separted by a coma.

Edit 1

I tried your solution but as stated in the comments I think there are some things that I don't get. I might misuse the recordset or do not know how to retrieve the information from it. Here is my full script so you can see what I am doing:

'Get the old/current computername
Set wshShell = WScript.CreateObject( "WScript.Shell" )
OldComputerName = wshShell.ExpandEnvironmentStrings( "%COMPUTERNAME%" )

'Parse the xml file to get the related new computername

Dim CONNECTION : Set CONNECTION = CreateObject("ADODB.CONNECTION")
Dim RECORDSET : Set RECORDSET = CreateObject("ADODB.RECORDSET")
CONNECTION.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\test\;Extended Properties=""text;HDR=YES;FMT=Delimited"""
RECORDSET.Open "SELECT NewComputerName FROM ComputerList.csv WHERE ComputerName = '& OldComputerName'", CONNECTION, 3, 3


 'Move the new computername in the target AD to a new OU
Dim NewComputerName
Dim OldLocation
NewComputerName = RECORDSET
OldLocation = "LDAP://CN=" & NewComputerName & ",OU=Staging,OU=Workstations,DC=contoso,DC=lab"
Set objNewOU = GetObject("LDAP://OU=Migration,OU=Workstations,DC=contoso,DC=lab")
Set objMoveComputer = objNewOU.MoveHere(OldLocation, vbNullString)

' It does not work as it said Error: Wrong number of arguments or invalid property assignment pour la ligne:
' OldLocation = "LDAP://CN=" & NewComputerName & ",OU=Staging,OU=Workstations,DC=contoso,DC=lab"

Thanks a lot for your help ! :)

How big is this CSV file going to be? Few dozen records or more? — Salman Arshad
– Salman Arshad, Commented Mar 12, 2012 at 10:34

Salman Arshad · Accepted Answer · 2012-03-12 17:25:00Z

0

You can use ADO to read CSV (and other delimited) files. The gory details are discussed in this article. Sample code for reading a simple CSV file using VBScript is as follows:

Note: the CSV file needs a header line in order for ADO to use column names:

ComputerName,NewComputerName
Computer #1,Other Computer #1
Computer #2,Other Computer #2
Computer #3,Other Computer #3

VBScript Code:

option explicit

dim CONNECTION : set CONNECTION = CreateObject("ADODB.CONNECTION")
dim RECORDSET : set RECORDSET = CreateObject("ADODB.RECORDSET")

CONNECTION.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Folder\Containing\CSV\File\;Extended Properties=""text;HDR=YES;FMT=Delimited"""

RECORDSET.Open "SELECT * FROM data.csv WHERE ComputerName = '" & OldComputerName & "'", CONNECTION, 3, 3

' //// For testing \\\\
WScript.Echo RECORDSET.Source
' \\\\ For testing ////

if RECORDSET.EOF then
    WScript.Echo "Record not found"
    WScript.Quit
else
    dim NewComputerName : NewComputerName = RECORDSET("NewComputerName") & ""
    WScript.Echo NewComputerName
end if

Note that this code might not work on 64-bit OS -- not directly. You must run 32-bit version of CScript/WScript like so:

%windir%\SysWoW64\cscript c:\Path\To\test.vbs

edited Mar 12, 2012 at 17:25

answered Mar 12, 2012 at 11:23

Salman Arshad

274k85 gold badges450 silver badges540 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Wawa41 Over a year ago

Hi and thanks for you reply. The file will be quite big yes, with around 800 entries It will be run on a 32bit version of XP so that should not be a problem. Is this script returning "Other Computer #1" when I give it the argument Computer #1 ?

Salman Arshad Over a year ago

I've edited my answer. Regarding your code (i) there seems to be a problem around string concatenation (&) operator (ii) the way you assigned a value to NewComputerName

Wawa41 · Accepted Answer · 2012-04-19 13:32:40Z

0

Ok the script works perfectly now thanks to your modifications ! :)

But I've got one more trouble, it seems that I cannot open the csv file in the FROM clause when this file have - in the name.

Like : CONTOSO-US-ComputerList.csv I get the error Syntax error in FROM clause

But when I use a file name without dashes there is no problem.

I know it is a detail but I have no choice than having a file with dashes in the name :/

Thanks again for your help :) Very much appreciated !!

EDIT:

Nevermind I found the solution thanks to the scriptingguys !

The request now looks like this:

strFile = "[CONTOSO-ComputerList.csv]" RECORDSET.Open "SELECT * FROM " & strFile & " WHERE ComputerName = '" & OldComputerName & "'", CONNECTION, 3, 3

edited Apr 19, 2012 at 13:32

answered Mar 13, 2012 at 9:28

Wawa41

611 gold badge1 silver badge9 bronze badges

1 Comment

Salman Arshad Over a year ago

Yup, [] should be used to enclose special characters in SQL queries. You might want to post this note as an edit to your question instead of as an answer.

Collectives™ on Stack Overflow

VBS script to parse a csv, get data and move AD object

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related