Link to home
Start Free TrialLog in
Avatar of petersz
petersz

asked on

Search a CSV to return a record

Requirements:

Based on machine name search the csv based on the machine name and return the row.

This code stops right before the test machine.  and I need it to return the record.


      Path2 = "C:\temp\"
      
      Dim Fso,f,file,objLogFile
      Set objExcel = CreateObject("Excel.Application")
      Set objWorkbook = objExcel.Workbooks.Open _
    (Path2 & "\MachDB.csv")
   
      Set objFSO = CreateObject("Scripting.FileSystemObject")
      Set objFolder = objFSO.GetFolder(Path1)
      
      intRow = 2

      Do
          Wscript.Echo "MachineName: " & objExcel.Cells(intRow, 1).Value
          Wscript.Echo "Subnet: " & objExcel.Cells(intRow, 2).Value
          Wscript.Echo "BankProServerIP: " & objExcel.Cells(intRow, 3).Value
          intRow = intRow + 1
      Loop Until objExcel.Cells(intRow,1).Value = "machine4"

      objExcel.Workbooks.Close
      objExcel.Quit
Avatar of mvidas
mvidas
Flag of United States of America image

Hi petersz,

As an fyi, you are dim'ming FSO but using objFSO.  In any case, you don't need to use excel here, you can do it all using the file system object:

 Dim FSO, f, Path2, tempStr, MachName, tempArr()
 Path2 = "C:\temp\"
 Set FSO = CreateObject("Scripting.FileSystemObject")
 Set f = FSO.OpenTextFile(Path2 & "MachDB.csv")
 tempStr = ""
 MachName = "machine4"
 Do Until f.AtEndOfStream
  tempStr = f.ReadLine
  If LCase(Left(tempStr, Len(MachName))) = LCase(MachName) Then
   'tempStr now contains the line for that machine
   Exit Do
  End If
 Loop
 
 If Len(tempStr) > 0 Then 'if it found that machine name
  tempArr = Split(tempStr, ",")
  WScript.Echo "MachineName: " & tempArr(0)
  WScript.Echo "Subnet: " & tempArr(1)
  WScript.Echo "BankProServerIP: " & tempArr(2)
 End If

Matt
Avatar of petersz
petersz

ASKER

thanks for the quick response!!!!

I add some code to gather the machine name to query the csv file.  I do get a an error on line 21 Type Mismatch data is invalid.


Dim FSO, f, Path2, tempStr, MachName, tempArr()
 Path2 = "C:\temp\"
 Set WshShell = Wscript.CreateObject("Wscript.Shell")
 Set ProcessEnv = WshShell.Environment("Process")
 
 Set FSO = CreateObject("Scripting.FileSystemObject")
 Set f = FSO.OpenTextFile(Path2 & "MachDB.csv")

 MachName = ProcessEnv("ComputerName") 'This will get the Machine ID's
  tempStr = ""
 'MachName = "machine4" 'test machine data
 Do Until f.AtEndOfStream
  tempStr = f.ReadLine
  If LCase(Left(tempStr, Len(MachName))) = LCase(MachName) Then
   'tempStr now contains the line for that machine
   Exit Do
  End If
 Loop
 
 If Len(tempStr) > 0 Then 'if it found that machine name
  tempArr = Split(tempStr, ",")
  WScript.Echo "MachineName: " & tempArr(0)
  WScript.Echo "Subnet: " & tempArr(1)
  WScript.Echo "ServerIP: " & tempArr(2)
 End If
This maybe easier to do with an ADO connection to the file rather than using Excel to open it and looping through the cells.  If you are interested let me know and I will show you how.

Leon
Avatar of petersz

ASKER

Leonstryker,
I'm very interested in learn to do it with ADO
ASKER CERTIFIED SOLUTION
Avatar of leonstryker
leonstryker
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial