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
Visual Basic Classic

Avatar of undefined
Last Comment
leonstryker

8/22/2022 - Mon
mvidas

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
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
leonstryker

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
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
petersz

ASKER
Leonstryker,
I'm very interested in learn to do it with ADO
ASKER CERTIFIED SOLUTION
leonstryker

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question