Is there a quicker method

[Richyrich]

Hi was wondering if anyone could look over the following process and give any suggestions for speeding it up, using VBA in Access 97, I am open a file reading it line by line, for each line that is read I am formating the data and writing to a new file. The process takes about 20 minutes (100/150MB file)

Open "C:\PostPay\file" & strType & lngProcessID & ".txt" For Input As #1
Open "C:\PostPay\fileResult" & strType & lngProcessID & ".txt" For Output As #2
 
 
Do Until EOF(1)
  Line Input #1, strFile
  
  'Ignore the header
  If Not (InStr(1, strFile, "|") > 0) Then
  
    vntArray = Split(strFile, ",")
    
    For intCount = 0 To 16
      'Edit the File
 
      If intCount = 0 Then
      
        Select Case Mid$((vntArray(intCount)), 2, 3)
        
          Case "GRA"
            'Grand Total - MUST ALWAYS INCLUDE OR ABORT PROCCESS
            strFile = Trim$(Mid$(vntArray(intCount), 2, 11))
            blnGrandTotal = True
            
          Case "EH_"
            'Guiding only
            strFile = Trim$(Mid$(vntArray(intCount), 2, 9))
          Case Else
            'Exclude spaces and ".rcd"
            
            strFile = Trim$(Mid$(vntArray(intCount), 2, InStr(1, vntArray(intCount), ".rcd") - 2))
      End Select
      
      Else
        strFile = strFile & "," & Trim$(vntArray(intCount))
      End If
    Next
    
    Print #2, strFile & "," & dtmDate & "," & lngProcessID
    
  End If
Loop
 
Close #1
Close #2

Any help much appreciated

[Richyrich]

I am presuming that I have followed the most efficient way for doing this?

[darre1]

there are a couple of things i can see that would speed up "processing" very slightly, but won't make much different overall. the time it takes is really just because of the fact that you are going through a 150Mb file line by line.

can't really do much about that.

20mins doesn't sound so bad to me, considering anyway

[Richyrich]

what are the couple of things?

[Si_the_geek]

your "if" statements could be re-arranged a bit, not sure how much time it'll save tho..

Open "C:\PostPay\file" & strType & lngProcessID & ".txt" For Input As #1
Open "C:\PostPay\fileResult" & strType & lngProcessID & ".txt" For Output As #2
 
strFile = "|"    'assuming the header bit doesn't repeat!
Do Until EOF(1) Or InStr(1, strFile, "|") = 0
  Line Input #1, strFile
Loop
 
Do Until EOF(1)
  Line Input #1, strFile
  
    vntArray = Split(strFile, ",")
    
 
        Select Case Mid$((vntArray(0)), 2, 3)
        
          Case "GRA"
            'Grand Total - MUST ALWAYS INCLUDE OR ABORT PROCCESS
            strFile = Trim$(Mid$(vntArray(0), 2, 11))
            blnGrandTotal = True
            
          Case "EH_"
            'Guiding only
            strFile = Trim$(Mid$(vntArray(0), 2, 9))
          Case Else
            'Exclude spaces and ".rcd"
            
            strFile = Trim$(Mid$(vntArray(0), 2, InStr(1, vntArray(0), ".rcd") - 2))
      End Select
 
    For intCount = 1 To 16
      'Edit the File
 
        strFile = strFile & "," & Trim$(vntArray(intCount))
    Next
    
    Print #2, strFile & "," & dtmDate & "," & lngProcessID
    
Loop
 
Close #1
Close #2