Need to read a csv file with vbs

Hi,

I'm trying to parse a csv file by using vb script. I would like to read employee names off of it and compare those values against our active directory. Please help by showing how the code should look like in the .vbs file. Thanks.
alateosAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
ee_autoConnect With a Mentor Commented:
Question PAQ'd, 500 points refunded, and stored in the solution database.
0
 
alateosAuthor Commented:
I need custom code that will take 2 fields from the csv file (employee, position) and compare these values to the active directory entries. If it finds someone whose title changed, then it should change that entry in the csv file.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
alateosAuthor Commented:
Why are we connecting to the jet driver? I simply need to parse a delimited csv file, read its contents, and compare to active directory.
0
 
hieloCommented:
>>Why are we connecting to the jet driver?
There are different "versions/implementations" of csv files (tab delimited,comma delimited, quotes comma quotes, delimited). The driver handles that.

From what you are describing, this might be more useful:
http://www.wisesoft.co.uk/scripts/vbscript_update_user_attributes_from_csv.aspx
0
 
rejoinderCommented:
Can you post a sample line from your CSV file please?
0
 
alateosAuthor Commented:
yes sure

Employee      Employee Name      Effective Date      Position Title      Detail Job Title      Employee Status      Most Recent Organization
10100      John Doe      9/21/2008      Pending      1      ACT      xxxxxx
0
 
rejoinderCommented:
Are those tabs between the fields?  I ask because EE might have changed your tabs to spaces when you posted your reply.
0
 
alateosAuthor Commented:
Yes they are tabs
0
 
rejoinderCommented:
In this example, the file is opened on line 6 - enter the file name here.
At line 45, the script reads a line from your file.
Line 48 splits the line into smaller string values based on tabs within the line.
If the name is found in AD, pull in some fields from AD and prepare them for comparisons or output.
You will have to know what fields you wish to compare against so the rest is in your hands.
'http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_23858406.html
 
Const ADS_UF_ACCOUNTDISABLE = 2
 
Set objFSO  = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile ("Q_23858406-Sample.CSV")
Set objOutP = objFSO.CreateTextFile("you-output-file.txt",True)
 
set objRootDSE   = GetObject("LDAP://RootDSE")
strBase          = "<LDAP://cn=Partitions," & objRootDSE.Get("ConfigurationNamingContext") & ">;"
strFilter        = "(&(objectcategory=crossRef)(systemFlags=3));"
strAttrs         = "name,trustParent,nCName,dnsRoot,distinguishedName;"
strScope         = "onelevel"
set objConn      = CreateObject("ADODB.Connection")
objConn.Provider = "ADsDSOObject"
objConn.Open "Active Directory Provider"
set objRS = objConn.Execute(strBase & strFilter & strAttrs & strScope)
objRS.MoveFirst
 
set arrDomainNames     = CreateObject("Scripting.Dictionary")
set dicDomainHierarchy = CreateObject("Scripting.Dictionary")
set dicDomainRoot      = CreateObject("Scripting.Dictionary")
 
while not objRS.EOF 
    dicDomainRoot.Add objRS.Fields("name").Value, objRS.Fields("nCName").Value
    if objRS.Fields("trustParent").Value <> "" then
        arrDomainNames.Add objRS.Fields("name").Value, 0
        set objDomainParent = GetObject("LDAP://" & objRS.Fields("trustParent").Value)
        dicDomainHierarchy.Add objRS.Fields("name").Value,objDomainParent.Get("name")
   else 
        arrDomainNames.Add objRS.Fields("name").Value, 1
   end if
   objRS.MoveNext
wend
 
Set adoCommand = CreateObject("ADODB.Command")
Set adoConnection = CreateObject("ADODB.Connection")
adoConnection.Provider = "ADsDSOObject"
adoConnection.Open "Active Directory Provider"
adoCommand.ActiveConnection = adoConnection
 
objFile.SkipLine
 
Do While Not objFile.AtEndOfStream
    strData = objFile.ReadLine
    if strData <> "" then
        for each strDomain in arrDomainNames
            arrData = Split(strData,vbTab)
            strSearchField = "(cn=*" & arrData(1) & "*)"
            strBase        = "<LDAP://" & strDomain & ">"
            strFilter      = "(&(objectCategory=user)(objectCategory=contact)" & strSearchField & ")"
            strAttributes  = "physicalDeliveryOfficeName,TelephoneNumber,description,Department,Title,cn,samAccountName,mail,Info,Mobile,company,streetAddress,l,st,postalCode,c,homePhone,manager,whenCreated,distinguishedName,userAccountControl,legacyExchangeDN,homeMDB,primaryGroupID,lastLogon"
            strQuery       = strBase & ";" & strFilter & ";" & strAttributes & ";subtree"
            
            adoCommand.CommandText = strQuery
            adoCommand.Properties("Page Size") = 100
            adoCommand.Properties("Timeout") = 30
            adoCommand.Properties("Cache Results") = False
            
            Set adoRecordset = adoCommand.Execute
            Do Until adoRecordset.EOF
                strphysicalDeliveryOfficeName = adoRecordset.Fields("physicalDeliveryOfficeName")
                strTelephoneNumber            = adoRecordset.Fields("TelephoneNumber")
                strdescription                = adoRecordset.Fields("description")
                strDepartment                 = adoRecordset.Fields("Department")
                strTitle                      = adoRecordset.Fields("Title")
                strcn                         = adoRecordset.Fields("cn")
                strsamAccountName             = adoRecordset.Fields("samAccountName")
                strmail                       = adoRecordset.Fields("mail")
                strInfo                       = adoRecordset.Fields("Info")
                strMobile                     = adoRecordset.Fields("Mobile")
                strcompany                    = adoRecordset.Fields("company")
                strstreetAddress              = adoRecordset.Fields("streetAddress")
                strl                          = adoRecordset.Fields("l")
                strst                         = adoRecordset.Fields("st")
                strpostalCode                 = adoRecordset.Fields("postalCode")
                strc                          = adoRecordset.Fields("c")
                strhomePhone                  = adoRecordset.Fields("homePhone")
                strmanager                    = adoRecordset.Fields("manager")
                strwhenCreated                = adoRecordset.Fields("whenCreated")
                strdistinguishedName          = adoRecordset.Fields("distinguishedName")
                struserAccountControl         = adoRecordset.Fields("userAccountControl")
                strlegacyExchangeDN           = adoRecordset.Fields("legacyExchangeDN")
                strhomeMDB                    = adoRecordset.Fields("homeMDB")
                strprimaryGroupID             = adoRecordset.Fields("primaryGroupID")
                strlastLogon                  = adoRecordset.Fields("lastLogon")
                
                ' Your code here...
                'objOutP.writeline "<--Use the objOutP.writeline command to send lines to an output file"
                'Example:
                
                objOutP.writeline strcn & vbTab & strwhenCreated & vbTab & strDepartment & vbTab & strTitle
              
                'In any event, you will know what fields correspond to what in your CSV file.
                
                adoRecordset.MoveNext
            loop
        next
    end if
Loop
objOutP.Close
wscript.echo "Done."

Open in new window

0
 
exx1976Commented:
If it's a CSV file, you could always just open it with excel and then use that..

VBS with Excel is exceedingly easy to write..
0
 
alateosAuthor Commented:
Rejoinder,

I will test your code and get back to you thanks.

0
 
rejoinderCommented:
OK, thanks.
0
 
alateosAuthor Commented:
admin please close this ... no solution was provided
0
 
hieloCommented:
>>admin please close this ... no solution was provided
Just accept your comment (ID: 25384732) as the solution. That way the question will remain in the PAQ db. The links on this thread will be useful to those wanting to parse a csv file.
0
All Courses

From novice to tech pro — start learning today.