Solved

Need to read a csv file with vbs

Posted on 2008-10-29
17
556 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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 

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

Ready to get started with anonymous questions?

It's easy! Check out this step-by-step guide for asking an anonymous question on Experts Exchange.

Question has a verified solution.

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

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses
Course of the Month7 days, 23 hours left to enroll

617 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