?
Solved

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

Posted on 2009-05-16
4
Medium Priority
?
218 Views
Last Modified: 2013-12-26
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
Comment
Question by:Valleriani
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 93

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 400 total points
ID: 24402128
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
 
LVL 7

Author Comment

by:Valleriani
ID: 24402222
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
 
LVL 7

Author Comment

by:Valleriani
ID: 24402229
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
 
LVL 22

Accepted Solution

by:
danaseaman earned 1600 total points
ID: 24402579
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Suggested Courses
Course of the Month7 days, 21 hours left to enroll

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question