Solved

Need to read a csv file with vbs

Posted on 2008-10-29
17
509 Views
Last Modified: 2009-11-18
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.
0
Comment
Question by:alateos
  • 6
  • 4
  • 4
  • +2
17 Comments
 
LVL 82

Expert Comment

by:hielo
Comment Utility
0
 

Author Comment

by:alateos
Comment Utility
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
 
LVL 82

Expert Comment

by:hielo
Comment Utility
0
 

Author Comment

by:alateos
Comment Utility
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
 
LVL 82

Expert Comment

by:hielo
Comment Utility
>>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
 
LVL 14

Expert Comment

by:rejoinder
Comment Utility
Can you post a sample line from your CSV file please?
0
 

Author Comment

by:alateos
Comment Utility
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
 
LVL 14

Expert Comment

by:rejoinder
Comment Utility
Are those tabs between the fields?  I ask because EE might have changed your tabs to spaces when you posted your reply.
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:alateos
Comment Utility
Yes they are tabs
0
 
LVL 14

Expert Comment

by:rejoinder
Comment Utility
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
 
LVL 18

Expert Comment

by:exx1976
Comment Utility
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
 

Author Comment

by:alateos
Comment Utility
Rejoinder,

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

0
 
LVL 14

Expert Comment

by:rejoinder
Comment Utility
OK, thanks.
0
 

Author Comment

by:alateos
Comment Utility
admin please close this ... no solution was provided
0
 
LVL 82

Expert Comment

by:hielo
Comment Utility
>>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
 

Accepted Solution

by:
ee_auto earned 0 total points
Comment Utility
Question PAQ'd, 500 points refunded, and stored in the solution database.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now