Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Need to read a csv file with vbs

Posted on 2008-10-29
17
Medium Priority
?
561 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 4
  • +2
17 Comments
 
LVL 82

Expert Comment

by:hielo
ID: 22832588
0
 

Author Comment

by:alateos
ID: 22832668
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
ID: 22832672
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:alateos
ID: 22832702
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
ID: 22833086
>>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
ID: 22834105
Can you post a sample line from your CSV file please?
0
 

Author Comment

by:alateos
ID: 22834268
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
ID: 22834327
Are those tabs between the fields?  I ask because EE might have changed your tabs to spaces when you posted your reply.
0
 

Author Comment

by:alateos
ID: 22834386
Yes they are tabs
0
 
LVL 14

Expert Comment

by:rejoinder
ID: 22834609
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
ID: 22837806
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
ID: 22849731
Rejoinder,

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

0
 
LVL 14

Expert Comment

by:rejoinder
ID: 22851617
OK, thanks.
0
 

Author Comment

by:alateos
ID: 25384732
admin please close this ... no solution was provided
0
 
LVL 82

Expert Comment

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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses

715 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