csv parsing with vbscript

myITforum.com Community Forum

Home Forums Blogs Live Support chat Search Articles Wiki FAQ Email Lists Register Login My Profile Inbox Address Book My Subscription My Forums

Photo Gallery Member List Search Calendars FAQ Ticket List Log Out

All Forums RSS Feed Subscription:

csv parsing with vbscript

View related threads: (in this forum | in all forums)

Logged in as: Guest

Printable Version

All Forums >> [Scripting Technologies] >> VB Script >> csv parsing with vbscript

Page: [1]

Message

<< Older Topic Newer Topic >>

csv parsing with vbscript - 4/29/2008 10:41:04 AM

ssalhoff

Posts: 4
Score: 0
Joined: 4/29/2008
Status: offline

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

Post #: 1

RE: csv parsing with vbscript - 4/29/2008 11:13:20 AM 1 votes

akaplan

Posts: 168
Score: 20
Joined: 4/22/2003
From: North Carolina
Status: offline

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

(in reply to ssalhoff)

Post #: 2

RE: csv parsing with vbscript - 4/29/2008 3:11:42 PM

ssalhoff

Posts: 4
Score: 0
Joined: 4/29/2008
Status: offline

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

(in reply to akaplan)

Post #: 3

RE: csv parsing with vbscript - 4/30/2008 8:13:06 AM

akaplan

Posts: 168
Score: 20
Joined: 4/22/2003
From: North Carolina
Status: offline

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

Alan

(in reply to ssalhoff)

Post #: 4

RE: csv parsing with vbscript - 5/1/2008 10:27:40 AM

ssalhoff

Posts: 4
Score: 0
Joined: 4/29/2008
Status: offline

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

(in reply to akaplan)

Post #: 5

RE: csv parsing with vbscript - 5/4/2008 5:11:10 PM

mseely

Posts: 33
Score: 1
Joined: 5/4/2008
Status: offline

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.

(in reply to ssalhoff)

Post #: 6

RE: csv parsing with vbscript - 5/6/2008 9:00:24 AM

ssalhoff

Posts: 4
Score: 0
Joined: 4/29/2008
Status: offline

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

(in reply to mseely)

Post #: 7

RE: csv parsing with vbscript - 5/7/2008 2:37:18 AM

mseely

Posts: 33
Score: 1
Joined: 5/4/2008
Status: offline

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'

(in reply to ssalhoff)

Post #: 8

RE: csv parsing with vbscript - 5/7/2008 8:00:36 PM

vrodrigues

Posts: 56
Score: 7
Joined: 6/8/2001
Status: offline

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

(in reply to akaplan)

Post #: 9

RE: csv parsing with vbscript - 5/7/2008 11:05:38 PM

mseely

Posts: 33
Score: 1
Joined: 5/4/2008
Status: offline

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.

(in reply to vrodrigues)

Post #: 10