Is there a quicker method
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)
VB Code:
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
Re: Is there a quicker method
your "if" statements could be re-arranged a bit, not sure how much time it'll save tho..
VB Code:
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