Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 226
  • Last Modified:

How to optimize speed of IsDate, Cdate, and Splitting strings into arrays -- in a small VB6 project.

Okay, I have a program that runs through textfiles or csv to find the date and output weither the date is found or not. It uses the VB6 DatePicker control, which is located at the MS common controls 6.0-2.

Right now I have it so that it finds 'specific text' an textfile, and only gives THOSE lines with the text in them. However, there is also a date range I am now adding, which will find only specific dates too.

Using the code below works find to get the date from the line -- 'str1' does well...

Basicly it grabs the line, checks for valid dates, bookmarks the number it if it finds it in a specific place in the array so it can be processed faster 'next line' until the end of the file, and then makes checks to see if the date in the file is = to the date range I have specified.

However, the program goes from about 6 seconds without the date range filter to almost 30+ with a 300k lined file. Thus I do not think my coding is quite optimized.

Is there a better solution for this? I use CDate because the date that comes in can be 11/12/09, 11 12 09, Dec 11 09, etc.. Cdate basicly solves all this for me in a nice package.. Unless there is something 'better' then cdate, or a function 'made' manually..

Basicly I am wondering how to improve performance. I do know it will not be the 'same' speed without the date range filter, but I am wondering if I can use something other then arrays, or cdate/isdate.. Or by looking at the coding below, a way to increase performance minor/major.

Thanks!
Line Input #intFileHandle2, str1
               validline = 0
                                
                If Check1.Value = 1 Then
                    ReDim marray(0)
                    validon = Replace(str1, Chr(34), "")
                    validon = Replace(validon, "'", "")
                    marray = Split(str1, ",")
                    
                    If validfound = -1 Then
                        For i = 0 To UBound(marray)
                            If IsDate(marray(i)) Then
                                validfound = i
                            End If
                        Next
                    End If
                    
                    If validfound <> -1 Then
                        If IsDate(marray(validfound)) Then
                            currentdate = DTPicker1.Value
                            finddate = Format(CDate(marray(validfound)), "short date")
                            Do While currentdate <= DTPicker2.Value
                                If finddate = currentdate Then
                                    validline = 1
                                End If
                                currentdate = currentdate + 1
                            Loop
                        End If
                    End If
                End If
            'Do rest of stuff here with validline being equal to 1...

Open in new window

0
Valleriani
Asked:
Valleriani
  • 2
2 Solutions
 
Patrick MatthewsCommented:
You may want to exit out of the loops once your conditions are met.  Thus,

                        For i = 0 To UBound(marray)
                            If IsDate(marray(i)) Then
                                validfound = i
                            End If
                        Next

becomes:

                        For i = 0 To UBound(marray)
                            If IsDate(marray(i)) Then
                                validfound = i
                                Exit For
                            End If
                        Next

and:

                            Do While currentdate <= DTPicker2.Value
                                If finddate = currentdate Then
                                    validline = 1
                                End If
                                currentdate = currentdate + 1
                            Loop

becomes:

                            Do While currentdate <= DTPicker2.Value
                                If finddate = currentdate Then
                                    validline = 1
                                    Exit Do
                                End If
                                currentdate = currentdate + 1
                            Loop
0
 
VallerianiAuthor Commented:
Thank you for the reply!

Ah right I did miss though, however it unfort doesn't change much because most 'searched' aren't there, for example in a 300k file, maybe 10k of those are of 3-4 dates.

But still, every little bit helps! (I will credit you a bit when I get more solutions too :) )
0
 
VallerianiAuthor Commented:
To be honest, I believe a good portion may be the split and the array of it. Is there a better way to do this?
0
 
danaseamanCommented:
1. Read entire file and Split with VbCrLf
2. Move Check1 to outer loop
3. Remove redundant IsDate ouside Do Loop
4. Use Date var for finddate
5. validon is not being used in posted code


Option Explicit
 
Private Sub Process()
   Dim sArray()      As String
   Dim mArray()      As String
   Dim str1          As String
   Dim validon       As String
   Dim i             As Long
   Dim lLine         As Long
   Dim validline     As Long
   Dim validfound    As Long
   Dim currentdate   As Date
   Dim finddate      As Date
 
   sArray = FileToArray(App.Path & "\MyFile.csv")
   If Check1.Value = 1 Then
      For lLine = 0 To UBound(sArray)
         validline = 0
         str1 = sArray(lLine)
         validon = Replace(str1, Chr(34), "")
         validon = Replace(validon, "'", "")
         mArray = Split(str1, ",")
 
         If validfound = -1 Then
            For i = 0 To UBound(mArray)
               If IsDate(mArray(i)) Then
                  validfound = i
                  Exit For
               End If
            Next
         End If
 
         If validfound <> -1 Then
            'If IsDate(mArray(validfound)) Then
            currentdate = DTPicker2.Value
            finddate = CDate(mArray(validfound)) 'was Format(CDate(mArray(validfound)), "short date")
            Do While currentdate <= DTPicker2.Value
               If finddate = currentdate Then
                  validline = 1
                  Exit Do
               End If
               currentdate = currentdate + 1
            Loop
            'End If
         End If
 
         'Do rest of stuff here with validline being equal to 1...
      Next
   End If
End Sub
 
Private Function FileToArray(strPath As String)
   On Error GoTo ErrHandler
   Dim FF As Integer
 
   If Dir(strPath) = "" Then Exit Function
   FF = FreeFile
   Open strPath For Input As #FF
 
   FileToArray = Split(Input(LOF(FF), #FF), vbCrLf)
ErrHandler:
   Close #FF
End Function

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now