?
Solved

VBScript - Nested loop issue

Posted on 2011-02-10
5
Medium Priority
?
702 Views
Last Modified: 2012-05-11
I have written a script to read file A and for each line return the characters before the first comma and set this to string1 and then search file B for lines that start with string1 and then write any lines found to File3 before moving on to read the second line of file A to start over but I can't get it to work correctly as it seems to only work for the first line read in step one and I don't know why.

Any help much appreciated.
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objF1 = objFSO.OpenTextFile(strFile1,1,0)
Set objF2 = objFSO.OpenTextFile(strFile2,1,0)
Set objF3 = objFSO.CreateTextFile(strFile3)
objF3.close
Set objF3 = objFSO.OpenTextFile(strFile3,2)

Do Until objF1.AtEndOfStream
   strLine1 = objF1.ReadLine
   Length = inStr(strLine1, ",")
   strName = Left(strLine1,Length)
   Do Until objF2.AtEndOfStream
      strLine2 = objF2.ReadLine
      If inStr(strLine2, strName) = 1 Then
         objF3.Writeline strLine2
      End If
   Loop

Loop

Open in new window

0
Comment
Question by:zeetec
[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
  • 3
  • 2
5 Comments
 
LVL 65

Expert Comment

by:RobSampson
ID: 34869331
Hi, you need to close and re-open file 2 so that the cursor returns to the start of the file.

Regards,

Rob.
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objF1 = objFSO.OpenTextFile(strFile1,1,0)
Set objF3 = objFSO.CreateTextFile(strFile3)
objF3.close
Set objF3 = objFSO.OpenTextFile(strFile3,2)

Do Until objF1.AtEndOfStream
   strLine1 = objF1.ReadLine
   Length = inStr(strLine1, ",")
   strName = Left(strLine1,Length)
   Set objF2 = objFSO.OpenTextFile(strFile2,1,0)
   Do Until objF2.AtEndOfStream
      strLine2 = objF2.ReadLine
      If inStr(strLine2, strName) = 1 Then
         objF3.Writeline strLine2
      End If
   Loop
   objF2.Close
Loop

Open in new window

0
 
LVL 65

Expert Comment

by:RobSampson
ID: 34869731
You also don't need to close and re-open file3 for writing.  When you use CreateTextFile, you automatically create a new file for writing to.

Rob.
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objF1 = objFSO.OpenTextFile(strFile1,1,False)
Set objF3 = objFSO.CreateTextFile(strFile3,True)

Do Until objF1.AtEndOfStream
   strLine1 = objF1.ReadLine
   Length = inStr(strLine1, ",")
   strName = Left(strLine1,Length)
   Set objF2 = objFSO.OpenTextFile(strFile2,1,0)
   Do Until objF2.AtEndOfStream
      strLine2 = objF2.ReadLine
      If inStr(strLine2, strName) = 1 Then
         objF3.Writeline strLine2
      End If
   Loop
   objF2.Close
Loop

Open in new window

0
 

Author Comment

by:zeetec
ID: 34869745
I had tried opening and closing the text file inside the first loop previously but for some reason that code results in every line from File2 being written to File3 and I can't figure out why. Thanks for the tip about the createtextfile too.
0
 
LVL 65

Accepted Solution

by:
RobSampson earned 2000 total points
ID: 34869770
It shouldn't do that...perhaps try this:

Rob.
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objF1 = objFSO.OpenTextFile(strFile1,1,False)
Set objF3 = objFSO.CreateTextFile(strFile3,True)

Do Until objF1.AtEndOfStream
   strLine1 = objF1.ReadLine
   Length = inStr(strLine1, ",")
   strName = Left(strLine1,Length)
   Set objF2 = objFSO.OpenTextFile(strFile2,1,0)
   Do Until objF2.AtEndOfStream
      strLine2 = objF2.ReadLine
      If Left(strLine2, Len(strName)) = strName Then
         objF3.Writeline strLine2
      End If
   Loop
   objF2.Close
Loop

Open in new window

0
 

Author Comment

by:zeetec
ID: 34869888
Ok. I worked out what the issue was. There was (and will always be) a blank line at the end of file1 which was causing it to match every line from file2 so I just needed to account for that with an if statement. Thanks for your help. Points will be duely be awarded to you.

Zeetec
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objF1 = objFSO.OpenTextFile(strFile1,1,False)
Set objF3 = objFSO.CreateTextFile(strFile3,True)

Do Until objF1.AtEndOfStream
   strLine1 = objF1.ReadLine
   Length = inStr(strLine1, ",")
   If Length = 0 Then
      Exit Do
   End If
   strName = Left(strLine1,Length)
   Set objF2 = objFSO.OpenTextFile(strFile2,1,0)
   Do Until objF2.AtEndOfStream
      strLine2 = objF2.ReadLine
      If Left(strLine2, Len(strName)) = strName Then
         objF3.Writeline strLine2
      End If
   Loop
   objF2.Close
Loop

Open in new window

0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

766 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