Help - I need faster way to edit a LARGE text file 2

[kskinne]

I have the following code in a VB project that loops through each record in a text file and edit each record as necessary, in a new text file:

  Open myOldFileName For Input As #1
  Open myNewFileName For Output As #2
  Do Until VBA.EOF(1)
    Line Input #1, str1
    If Left(str1, 2) = "11" Then
      If (Mid(str1, 30, 8) = "71277570" Or Mid(str1, 30, 8) = "71277571" Or Mid(str1, 30, 8) = "71277572" Or Mid(str1, 30, 8) = "71277573" Or Mid(str1, 30, 8) = "71277574") Then
        Print #2, Application.WorksheetFunction.Replace(str1, 41, 4, "9999")
      Else
        Print #2, str1
      End If
    Else
      Print #2, str1
    End If
  Loop

  Close #2
  Close #1

The code works as intended however it runs very slowly, because most of the text files are 300,000+ records in length. It usually takes over three minutes to run and I have a fast machine. And the problem is this has to be done to not just one but many files.

Is there a better way to accomplish the same thing, only faster?

Thanks,
Kevin

 

[gmmastros]

That's cool. I thought it was important to highlight the differences between both replaces. I wouldn't want your data to get mangled.

I'm glad you got this problem sorted out.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sxschech]

Hi,

I found your thread and seems like it gave me an idea on how to solve my issue from thread Change Date Format in fixed width text file
thread329-1575753.

I modified one of the examples provided and tried running it from Access, but it didn't work, so pasted it into excel and it worked from there. Anyway, the issue now is that I learned that I need to search for more than one case and as I'm not too familiar with the code, wasn't sure how to write it. Also I'm sure there is a more concise/efficient way to write the code than what I have done.

I set up the code to replace Months at the following position:

Replace(str1, 106, 3, "001")

Now it turns out I need to do the same for another position:

Replace(str1, 379, 3, "001")

Of course, this leads to the possibility that there may be more positions down the road that need replacing.

How can I set up the loop so that I don't have to run the code twice, once for col 106 and again for col 379?

In the code the commented 'str1 contains the data from the test text file CopyPTea.txt

Also, if I would like to allow the user to provide the file input and output names via a prompt or a cell in the excel file, how is that accomplished?

Here is the code:

Public Sub replaceit2()
'Replace 3 char Month to 2 digits with a leading 0
' Ex: JAN --> 001
'     DEC --> 012
' from tek-tips thread222-1575530

Dim str1 As String

'Uncomment or copy str1 data into the text file for testing purpose

    'str1 = "Test-Taker                                        
Example                                             M01Jan1980+44 
123456789       123 Example 
Lane                                                   
Example City                            EX1 1EX         
GBRGBRpltsupport@pearson.com                            
PLT23200678713Sep200913Sep2011Y          8584799082679090908790  
B08L19118Sep2009"

    Dim Application As Excel.Application
    Set Application = New Excel.Application
    
    
    With Application.WorksheetFunction
        Open "\\discimageserver\textfiles\COPYPTEA.txt" For Input As #1
        Open "\\discimageserver\textfiles\PTEATEST.txt" For Output As #2
        
        Do Until VBA.EOF(1)
            Line Input #1, str1
'        MsgBox .Replace(str1, 106, 3, "001")
'        MsgBox Replace$(str1, Mid$(str1, 106, 3), "001")
            Select Case Mid$(str1, 106, 3)
                Case "Jan"
                    Print #2, .Replace(str1, 106, 3, "001")

[red]'*** Need to Add this to each Month's the code ***                     
                    Print #2, .Replace(str1, 379, 3, "001") 
'***  *** [/red]
                Case "Feb"
                    Print #2, .Replace(str1, 106, 3, "002")
                    Print #2, .Replace(str1, 379, 3, "002")
                Case "Mar"
                    Print #2, .Replace(str1, 106, 3, "003")
                Case "Apr"
                    Print #2, .Replace(str1, 106, 3, "004")
                Case "May"
                    Print #2, .Replace(str1, 106, 3, "005")
                Case "Jun"
                    Print #2, .Replace(str1, 106, 3, "006")
                Case "Jul"
                    Print #2, .Replace(str1, 106, 3, "007")
                Case "Aug"
                    Print #2, .Replace(str1, 106, 3, "008")
                Case "Sep"
                    Print #2, .Replace(str1, 106, 3, "009")
                Case "Oct"
                    Print #2, .Replace(str1, 106, 3, "010")
                Case "Nov"
                    Print #2, .Replace(str1, 106, 3, "011")
                Case "Dec"
                    Print #2, .Replace(str1, 106, 3, "012")
            Case Else
                Print #2, str1
            End Select
        Loop
    End With
    Close #2
    Close #1

End Sub

 

[gmmastros]

How can I set up the loop so that I don't have to run the code twice, once for col 106 and again for col 379?

I would suggest that instead of printing directly to the file in each case statement, you simply modify the string in each case, and print at the end. Like this:

    With Application.WorksheetFunction
        Open "\\discimageserver\textfiles\COPYPTEA.txt" For Input As #1
        Open "\\discimageserver\textfiles\PTEATEST.txt" For Output As #2
        
        Do Until VBA.EOF(1)
            Line Input #1, str1
'        MsgBox .Replace(str1, 106, 3, "001")
'        MsgBox Replace$(str1, Mid$(str1, 106, 3), "001")
            Select Case Mid$(str1, 106, 3)
                Case "Jan"
                    str1 = .Replace(str1, 106, 3, "001")

                    Print #2, .Replace(str1, 379, 3, "001")
                Case "Feb"
                    str1 = .Replace(str1, 106, 3, "002")
                ' More case statements here
            End Select

            Select Case Mid$(str1, 379, 3)
                Case "Jan"
                    str1 = .Replace(str1, 379, 3, "001")
                Case "Feb"
                    str1 = .Replace(str1, 379, 3, "002")
                ' More case statements here
            End Select

            Print #2, str1

        Loop
    End With

Basically, you are pulling str1 from the file. The data for that line is put in to the str1 variable. You can modify the variable all day long, in as many ways as you want. What you cannot do is write it to the file multiple times without messing up your data.

Does this make sense?

Also, if I would like to allow the user to provide the file input and output names via a prompt or a cell in the excel file, how is that accomplished?

I have no idea. I can't remember the last time I used Excel.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[strongm]

This is rather highjacking the thread. Please start a new one for your question...

Also I'm sure there is a more concise/efficient way

I'd say so ...

Now it turns out I need to do the same for another position:

Replace(str1, 379, 3, "001")

Of course, this leads to the possibility that there may be more positions down the road that need replacing.

When starting the other thread, can you answer the following: do you need to modify every date you find in the line to match the new date template, or only specific ones? str1 in your example, for instance, actually has [blue]4[/blue] dates in it

 

[sxschech]

Apologies for "hijacking". Not my intent. Wasn't sure the etiquette in this regard as the thread seemed to be related and didn't want to cause a problem. I will post as a new thread.

 

[sxschech]

After reading George's post, seems like he answered the question sufficiently, so not sure I need to post a new thread at this time.

 

[strongm]

Well ... because, depending on the answer to my question in my previous post, we might be able to do away with the Case statement and multiple Replace statements altogether (and thus make it runnable in more or less any VBA host), that's why ...

 

[gmmastros]

sxschech, when strongm suggest that there may be a better method, it's in your best interest to take full advantage of that.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sxschech]

OK, will post. Thanks again.