Replacing multiple instances of text file lines

[frateg8r]

I have text files that may have one, or multiple, instances of the following line groupings:

PG27FANU1022356                                                                 
PG2803110701                                                                   
PG30A0331202412002   2809

I have the following procedure (part of a larger module running a number of different validations in the text file) which looks to check whether the date in the PG30 record is correct or not and change it if not. The procedure works fine if there is only a single instance of the PG30 record; however, if there are multiple instances, none of them end up being changed.

            Set tso = ofs.OpenTextFile(sFilePath & sFileName)
            Do While Not tso.AtEndOfStream
            sLine = tso.ReadLine
            Dim sETA As String
            Dim sReplaceLine As String
            sETA = "04092024"
    
            If Left(sLine, 4) = "PG30" Then
                If sETA <> Mid(sLine, 6, 8) Then
                    sReplaceLine = Left(sLine, 5) & sETA & Right(sLine, 67)
                    Open sFilePath & sFileName For Output As #2
                    Print #2, Replace(Trim(strFileText), sLine, sReplaceLine);
                    Close #2
                    Print #1, "PG30 ETA Updated"
                Else
                    Print #1, "PG30 ETA Verified"
                End If
            End If
            Loop

I suspect the problem stems from the replacement text file being lost when the loop starts for the second instance of the PG30 record, since the original file is still open. I did try adding "tso.close" after the "Close #2" line, but that did not affect the outcome. If I killed the sub after that, then the replacement file was in place with the first instance corrected, but of course, not the second one.

How can I exit the loop and restart from the top (the "Set tso" line), so as to continue processing from the second instance of the PG30 record?

 

[rlv01]

It looks to me like you are trying to modify the same input file that you are reading, while you are still reading it - and that is a recipe for trouble. Instead use two files. One is the input file for reading that you don't modify, the other is the output file for writing that you do.
Not tested.

    Dim sETA As String          'don't declare variables inside a loop
    Dim sReplaceLine As String
    
    Set tso = ofs.OpenTextFile(sFilePath & sFileName)
    
    Open sFilePath & "Output.txt" For Output Access Write As #2
    
    Do While Not tso.AtEndOfStream
        sLine = tso.ReadLine
        
        sETA = "04092024"
        
        If Left(sLine, 4) = "PG30" Then
            If sETA <> Mid(sLine, 6, 8) Then
                sReplaceLine = Left(sLine, 5) & sETA & Right(sLine, 67)
                sLine = sReplaceLine
                Print #1, "PG30 ETA Updated"
            End If
        Else
            Print #1, "PG30 ETA Verified"
        End If
        Print #2, sLine
    Loop
    Close #2

Once the processing is complete, add code to (if you desire) to delete the original file and replace it with the new updated file.

 

[frateg8r]

It looks to me like you are trying to modify the same input file that you are reading, while you are still reading it - and that is a recipe for trouble. Instead use two files. One is the input file for reading that you don't modify, the other is the output file for writing that you do.
Not tested.

    Dim sETA As String          'don't declare variables inside a loop
    Dim sReplaceLine As String
   
    Set tso = ofs.OpenTextFile(sFilePath & sFileName)
   
    Open sFilePath & "Output.txt" For Output Access Write As #2
   
    Do While Not tso.AtEndOfStream
        sLine = tso.ReadLine
       
        sETA = "04092024"
       
        If Left(sLine, 4) = "PG30" Then
            If sETA <> Mid(sLine, 6, 8) Then
                sReplaceLine = Left(sLine, 5) & sETA & Right(sLine, 67)
                sLine = sReplaceLine
                Print #1, "PG30 ETA Updated"
            End If
        Else
            Print #1, "PG30 ETA Verified"
        End If
        Print #2, sLine
    Loop
    Close #2

Once the processing is complete, add code to (if you desire) to delete the original file and replace it with the new updated file.

Thank you! In retrospect, that's an obvious solution - guess I just spent too much time inside this code and needed a fresh set of eyes. Also taking your advice on declaring variables outside a loop - thanks for that.