Solved

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

Posted on 2009-05-16
4
203 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
  • 2
4 Comments
 
LVL 92

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 100 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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 400 total points
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
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…
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…

772 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now