Solved

Need to read a csv file with vbs

Posted on 2008-10-29
17
538 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
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
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…
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…

828 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