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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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
ee_autoCommented:
Question PAQ'd, 500 points refunded, and stored in the solution database.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.