csv parsing with vbscript (Full Version)

All Forums >> [Scripting Technologies] >> VB Script



Message

ssalhoff -> csv parsing with vbscript (4/29/2008 10:41:04 AM)

Hello,

I was wondering if anyone had any suggestions on what would be the best way to read one field on each line of a csv file and search down through each line in the file to locate a match.  Once I find a match I have to grab 3 fields from that line and write them to the original line.  I would say that this has me pulling my hair out but I keep it shaved down really short to prevent that.[;)]  Any suggestions would be great.

Thanks,
Steve



akaplan -> RE: csv parsing with vbscript (4/29/2008 11:13:20 AM)

If you are comfortable with ODBC, you can open the CSV file and treat it with a database.  Some code I have for reading CSV is here:

Option Explicit

Const HKEY_LOCAL_MACHINE = &H800000
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H0001

'Optional CSV is default
Dim strComputer, objReg, strKeyPath, strValueName, strValue
strComputer = "."
Set objReg=GetObject("winmgmts:\\" & strComputer & _
   "\root\default:StdRegProv")
strKeyPath = "SOFTWARE\Microsoft\Jet\4.0\Engines\Text"
strValueName = "Format"
strValue = "CSVDelimited"
'strValue = "TABDelimited"?
objReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue
'End optional stuff

'CSV read begins
Dim OConn, oRS, strPathtoTextFile, strFileName
Set oConn = CreateObject("ADODB.Connection")
Set oRS = CreateObject("ADODB.Recordset")


'Example data
strFileName = "MyCSVFile.csv"

Dim scriptpath
'get path name, ending in \
scriptpath = Left(Wscript.ScriptFullName, InStrRev(Wscript.ScriptFullName, "\"))
strPathtoTextFile = scriptpath

WScript.Echo scriptpath
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
         "Data Source=" & strPathtoTextFile & ";" & _
         "Extended Properties=""text;HDR=YES;FMT=Delimited"""

oRS.Open "SELECT * FROM ["& strFileName & "]", _
         oConn, adOpenStatic, adLockOptimistic, adCmdText

Do Until oRS.EOF
    Wscript.Echo "Server: " & oRS.Fields.Item("Server")
    oRS.MoveNext
Loop



ssalhoff -> RE: csv parsing with vbscript (4/29/2008 3:11:42 PM)

Well that got me started, thank you.  Now however I am running into issues defining the logic to sort it and get the additional fields.  Here is waht I was trying to put together however It is telling me that the Object doesn't support this property or method.  Any thoughts?

Option Explicit

Const HKEY_LOCAL_MACHINE = &H800000
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H0001

'Optional CSV is default
Dim strComputer, objReg, strKeyPath, strValueName, strValue
strComputer = "."
Set objReg=GetObject("winmgmts:\\" & strComputer & _
  "\root\default:StdRegProv")
strKeyPath = "SOFTWARE\Microsoft\Jet\4.0\Engines\Text"
strValueName = "Format"
strValue = "CSVDelimited"
'strValue = "TABDelimited"?
objReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue
'End optional stuff

'CSV read begins
Dim OConn, oRS, strPathtoTextFile, strFileName
Set oConn = CreateObject("ADODB.Connection")
Set oRS = CreateObject("ADODB.Recordset")


'Example data
strFileName = "AD_Users-disabled.csv"

Dim scriptpath
'get path name, ending in \
scriptpath = Left(Wscript.ScriptFullName, InStrRev(Wscript.ScriptFullName, "\"))
strPathtoTextFile = scriptpath

WScript.Echo scriptpath
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & strPathtoTextFile & ";" & _
        "Extended Properties=""text;HDR=YES;FMT=Delimited"""

oRS.Open "SELECT * FROM ["& strFileName & "]", _
        oConn, adOpenStatic, adLockOptimistic, adCmdText

Dim string1, string2, Item
Do Until oRS.EOF
   Wscript.Echo "" & oRS.Fields.Item("manager")
       set string1 = oRS.Fields.Item("manager")
       For Each string1 In oRS.Fields.Item("manager")
            If oRS.Fields.Item("manager") = "" then
               exit For
            end If
        Next
            Do Until oRs.EOF
                 Set string2 = oRS.Fields.Item("distinguishedName")
                 For Each String2 In oRS.Fields.Item("distinguishedName")
                      IF StrComp(string1,string2, vbTextCompare) = 0 then
                      oRS.MoveNext
                      Elseif StrComp(string1,string2, vbTextCompare) = 1 then
                      Wscript.echo "match"
                      oRS.MoveNext
                      End if
                 next
            Loop

   oRS.MoveNext
Loop



akaplan -> RE: csv parsing with vbscript (4/30/2008 8:13:06 AM)

set string1 = oRS.Fields.Item("manager")
You can only set objects.  Try instead : string1 = oRS.Fields.Item("manager")

Alan



ssalhoff -> RE: csv parsing with vbscript (5/1/2008 10:27:40 AM)

Hi Alan,
I made the change that you suggested but I am still getting the same result.  It is still failing at the For Each statement.

Option Explicit

Const HKEY_LOCAL_MACHINE = &H800000
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H0001

'Optional CSV is default
Dim strComputer, objReg, strKeyPath, strValueName, strValue
strComputer = "."
Set objReg=GetObject("winmgmts:\\" & strComputer & _
  "\root\default:StdRegProv")
strKeyPath = "SOFTWARE\Microsoft\Jet\4.0\Engines\Text"
strValueName = "Format"
strValue = "CSVDelimited"
'strValue = "TABDelimited"?
objReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue
'End optional stuff

'CSV read begins
Dim OConn, oRS, strPathtoTextFile, strFileName
Set oConn = CreateObject("ADODB.Connection")
Set oRS = CreateObject("ADODB.Recordset")


'Example data
strFileName = "AD_Users-disabled.csv"

Dim scriptpath
'get path name, ending in \
scriptpath = Left(Wscript.ScriptFullName, InStrRev(Wscript.ScriptFullName, "\"))
strPathtoTextFile = scriptpath

WScript.Echo scriptpath
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & strPathtoTextFile & ";" & _
        "Extended Properties=""text;HDR=YES;FMT=Delimited"""

oRS.Open "SELECT * FROM ["& strFileName & "]", _
        oConn, adOpenStatic, adLockOptimistic, adCmdText


Dim string1, string2, Item
Do Until oRS.EOF
  Wscript.Echo "" & oRS.Fields.Item("manager")
      string1 = oRS.Fields.Item("manager").value
      For Each string1 In oRS.Fields.Item("manager")
           If oRS.Fields.Item("manager") = "" then
              exit For
           end If
       Next
           Do Until oRs.EOF
                string2 = oRS.Fields.Item("distinguishedName").value
                For Each String2 In oRS.Fields.Item("distinguishedName")
                     IF StrComp(string1,string2, vbTextCompare) = 0 then
                     oRS.MoveNext
                     Elseif StrComp(string1,string2, vbTextCompare) = 1 then
                     Wscript.echo "match"
                     oRS.MoveNext
                     End if
                next
           Loop

  oRS.MoveNext



mseely -> RE: csv parsing with vbscript (5/4/2008 5:11:10 PM)

What does a sample line or two of the CSV file look like?

From a quick look at the script, I'd say get rid of the 'For Each' statements.
Since the value of each field in a csv is a string (and not an array or a collection), there is nothing for the 'For Each' to work on.



ssalhoff -> RE: csv parsing with vbscript (5/6/2008 9:00:24 AM)

Hi, yes it seems to be hanging up on the for each statements.  Here is a bit of sample data.

distinguishedName,sAMAccountName,mail,employeeID,employeeType,sn,givenName,displayName,title,l,department,departmentNumber,telephoneNumber,mobile,facsimileTelephoneNumber,employeePhoto,physicalDeliveryOfficeName,manager,ManagerName,userAccountControl,Account Status,helpdesk-executive,helpdesk-critical,
"CN=User1 Name,OU=Users,OU=Integration,DC=domain,DC=net",user1,user1@domain.net,1234,Employee,Name,User1,User1 Name,,City-A,Security,4694,1 2125551212,,,https://pics.domain.net/Pic/EmployeePhotos/User1.JPG,room123,"CN=User7 Name,OU=Americas,OU=Users,OU=Corp,DC=domain,DC=net",User7 Name,700,TRUE,FALSE,FALSE,
"CN=User2 Name,OU=AsiaPac,OU=Users,OU=Eng,DC=domain,DC=net",user2,user2@domain.net,1345,Employee,Name,User2,User2 Name,,City-b,Ops,5203,1 2125551212,,,https://pics.domain.net/Pic/EmployeePhotos/User2.JPG,room234,"CN=User 10 Name,OU=Americas,OU=Users,OU=Eng,DC=domain,DC=net",User10 Name,700,TRUE,FALSE,FALSE,
"CN=User3 Name,OU=AsiaPac,OU=Users,OU=Eng,DC=domain,DC=net",user3,user3@domain.net,1456,Employee,Name,User3,User3 Name,,City-b,Finance,3672,1 2125551212,1 2125551212,,https://pics.domain.net/Pic/EmployeePhotos/User3.JPG,room345,"CN=User4 Name,OU=Americas,OU=Users,OU=Corp,DC=domain,DC=net",User4 Name,700,TRUE,FALSE,FALSE,
"CN=User4 Name,OU=Americas,OU=Users,OU=Corp,DC=domain,DC=net",user4,user4@domain.net,1567,Employee,Name,User4,User4 Name,,City-C,Hardware,7623,1 2125551212,,,https://pics.domain.net/Pic/EmployeePhotos/User4.JPG,room456,"CN=User7 Name,OU=Americas,OU=Users,OU=Corp,DC=domain,DC=net",User7 Name,700,TRUE,FALSE,FALSE,
"CN=User5 Name Akkaya,OU=EMEA,OU=Users,OU=Corp,DC=domain,DC=net",user5,user5@domain.net,1678,Employee,Name,User5,User5 Name,,City-D,Field Sales,5400,1 2125551212,1 2125551212,1 2125551212,https://pics.domain.net/Pic/EmployeePhotos/User5.JPG,room567,"CN=User 10 Name,OU=Americas,OU=Users,OU=Eng,DC=domain,DC=net",User10 Name,700,TRUE,FALSE,FALSE,
"CN=User6 Name,OU=EMEA,OU=Users,OU=Corp,DC=domain,DC=net",user6,user6@domain.net,1789,Employee,Name,User6,User6 Name,,City-e,Sales,6445,1 2125551212,1 2125551212,,https://pics.domain.net/Pic/EmployeePhotos/User6.JPG,room678,"CN=User 10 Name,OU=Americas,OU=Users,OU=Eng,DC=domain,DC=net",User10 Name,700,TRUE,FALSE,FALSE,
"CN=User7 Name,OU=Americas,OU=Users,OU=Corp,DC=domain,DC=net",user7,user7@domain.net,1890,Employee,Name,User7,User7 Name,,City-C,Ops,9202,1 2125551212,,,https://pics.domain.net/Pic/EmployeePhotos/User7.JPG,room789,"CN=User4 Name,OU=Americas,OU=Users,OU=Corp,DC=domain,DC=net",User4 Name,700,TRUE,FALSE,FALSE,
"CN=User8 Name,OU=Americas,OU=Users,OU=Eng,DC=domain,DC=net",user8,user8@domain.net,1901,Contractor,Name,User8,User8 Name,,City-A,Security,7665,,,,https://pics.domain.net/Pic/EmployeePhotos/User8.JPG,room890,"CN=User4 Name,OU=Americas,OU=Users,OU=Corp,DC=domain,DC=net",User4 Name,700,TRUE,FALSE,FALSE,
"CN=User 9 Name,OU=Americas,OU=Users,OU=Eng,DC=domain,DC=net",user9,user9@domain.net,1012,Employee,Name,User9,User9 Name,,City-A,HR,2708,1 2125551212,,,https://pics.domain.net/Pic/EmployeePhotos/User9.JPG,room901,"CN=User7 Name,OU=Americas,OU=Users,OU=Corp,DC=domain,DC=net",User7 Name,700,TRUE,FALSE,FALSE,
"CN=User 10 Name,OU=Americas,OU=Users,OU=Eng,DC=domain,DC=net",user10,user10@domain.net,1123,Employee,Name,User10,User10 Name,,City-A,Software,1695,1 2125551212,,,https://pics.domain.net/Pic/EmployeePhotos/User10.JPG,room012,"CN=User7 Name,OU=Americas,OU=Users,OU=Corp,DC=domain,DC=net",User7 Name,700,TRUE,FALSE,FALSE



mseely -> RE: csv parsing with vbscript (5/7/2008 2:37:18 AM)

is this what is being attempted ?

Find the manager for each user
Find the info for the manager and place it on the line for the user

for example -
manager for 'User1 Name,OU=Users,OU=Integration,DC=domain,DC=net'  is 'User7 Name,OU=Users,OU=Integration,DC=domain,DC=net'
The mail for user7 is 'user7@domain.net' - so place 'user7@domain.net' at the end of the line for 'user1'






vrodrigues -> RE: csv parsing with vbscript (5/7/2008 8:00:36 PM)

quote:

 
Option Explicit

Const HKEY_LOCAL_MACHINE = &H800000
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H0001

'Optional CSV is default
Dim strComputer, objReg, strKeyPath, strValueName, strValue
strComputer = "."
Set objReg=GetObject("winmgmts:\\" & strComputer & _
  "\root\default:StdRegProv")
strKeyPath = "SOFTWARE\Microsoft\Jet\4.0\Engines\Text"
strValueName = "Format"
strValue = "CSVDelimited"
'strValue = "TABDelimited"?
objReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue
'End optional stuff

'CSV read begins
Dim OConn, oRS, strPathtoTextFile, strFileName
Set oConn = CreateObject("ADODB.Connection")
Set oRS = CreateObject("ADODB.Recordset")


'Example data
strFileName = "MyCSVFile.csv"

Dim scriptpath
'get path name, ending in \
scriptpath = Left(Wscript.ScriptFullName, InStrRev(Wscript.ScriptFullName, "\"))
strPathtoTextFile = scriptpath

WScript.Echo scriptpath
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & strPathtoTextFile & ";" & _
        "Extended Properties=""text;HDR=YES;FMT=Delimited"""

oRS.Open "SELECT * FROM ["& strFileName & "]", _
        oConn, adOpenStatic, adLockOptimistic, adCmdText

Do Until oRS.EOF
   Wscript.Echo "Server: " & oRS.Fields.Item("Server")
   oRS.MoveNext
Loop





Alan,
Very nice. I never thought of parsing csv file in this manner.
Thanks.
Vic



mseely -> RE: csv parsing with vbscript (5/7/2008 11:05:38 PM)

Let me know if this is what you are trying for. 

Mark

---


Option Explicit
Const HKEY_LOCAL_MACHINE = &H800000
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H0001
'Optional CSV is default
Dim strComputer, objReg, strKeyPath, strValueName, strValue
Dim string3, string4
strComputer = "."
Set objReg=GetObject("winmgmts:\\" & strComputer & _
"\root\default:StdRegProv")
strKeyPath = "SOFTWARE\Microsoft\Jet\4.0\Engines\Text"
strValueName = "Format"
strValue = "CSVDelimited"
'strValue = "TABDelimited"?
objReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue
'End optional stuff
'CSV read begins
Dim OConn, oRS, strPathtoTextFile, strFileName , ors2
Set oConn = CreateObject("ADODB.Connection")
Set oRS = CreateObject("ADODB.Recordset")
Set oRS2 = CreateObject("ADODB.Recordset")

'Example data
strFileName = "AD_Users-disabled.csv"
Dim scriptpath
'get path name, ending in \
scriptpath = Left(Wscript.ScriptFullName, InStrRev(Wscript.ScriptFullName, "\"))
strPathtoTextFile = scriptpath

oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
       "Data Source=" & strPathtoTextFile & ";" & _
       "Extended Properties=""text;HDR=YES;FMT=Delimited"""
'' Everything above here is same
oRS.Open "SELECT usrfile.*,mgrfile.mail FROM ["& strFileName & "] usrfile left join ["& strFileName & "] mgrfile on usrfile.manager=mgrfile.distinguishedname", _
       oConn, adOpenStatic, adLockOptimistic, adCmdText
'' Do a left join between the file and itself, joining using 'manager' and 'distinguishedname'
Dim oFld,szLine,szFileStr
szLine = ""
For each oFld in oRS.Fields
szLine=szLine & "," & oFld.Name
Next
If Left(szLine,1) = "," Then szline = Mid(szLine,2)
szFileStr = szLine
''  Recreating the header line, with the fields from the manager

Do Until oRS.EOF
   szLine = ""
   For each oFld in oRS.Fields
       If InStr(oFld.value,",") Then
          szLine=szLine & "," & Chr(34) & oFld.value & Chr(34)
       Else
          szLine=szLine & "," & oFld.value
       End If
   Next
   If Left(szLine,1) = "," Then szline = Mid(szLine,2)
   szFileStr = szFileStr & vbcrlf & szLine
   oRS.MoveNext
Loop
''  Loop through the results recreating each Record line to include the manager information.
''  If a Field value has a ',' in it then need to place quotes around the field.
oRs.Close
oConn.Close


Dim ofso,outfile
Set ofso = CreateObject("Scripting.FileSystemObject")
Set outfile = ofso.CreateTextFile(ofso.BuildPath(scriptpath , "new-" & strFileName))
outfile.Write szFileStr
outfile.close
''  Create a new csv file, and place the newly parsed data into it.



Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.25