Excel crashes while macro processes large text file

[RitaG]

Hey,

I have a macro which performs a search operation in a text file. The code accesses the text file (without actually opening it) and searches for a particular search text (provided by the user) by going through the entire text file line by line.

The macro runs fine for a text file of size aound a 100KB. The problem comes when the user provides a file of 4MB or more. The excel hangs, or sometimes just plain crashes.

The whole purpose of this macro is to search through large volumes of data (in a single text file). So when it crashes... well lets just say its a big problem.

Is there any way to prevent this ?? Any help would be highly appreciated. I'm really lost on as to how to go about this problem....

Also posted here

[snb]

And so am I if you do not explain which method you use to read the file.
There's a plethora of methods in VBA.
The 'line by line' approach doesn't sound very suited for your purpose.

[RitaG]

And here is the code.

Sub ExtractMessage_Click()
Dim wkst_home As Worksheet
Dim FSO As FileSystemObject
Dim Rs As Recordset
Dim MC As Connection
Dim blnFlag_NotFound As Boolean

Set MC = New ADODB.Connection
Set Rs = New Recordset

Set wkst_home = ThisWorkbook.Worksheets("Home")
Set FSO = CreateObject("Scripting.FileSystemObject")
Myworkbook = Application.ThisWorkbook.FullName
MC.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                      "Data Source=" & Myworkbook & ";" & _
                      "Extended Properties=Excel 12.0;" & _
                      "Persist Security Info=False"
                      
If wkst_home.ListObjects.Count = 0 Then
    Exit Sub
End If

With wkst_home.ListObjects(1)
    Set oLo = wkst_home.ListObjects(1)
    arrAddress = Split(Trim(oLo.Range.Address), "$")
    straddress = arrAddress(1) & arrAddress(2) & arrAddress(3) & arrAddress(4)
    rngtestcasenum = "Home" & "$" & straddress
    strSQL = "Select * from [" & rngtestcasenum & "]"
End With

Rs.Open strSQL, MC, adOpenStatic, adUseClient

' this will allow the user to select which text file to search
Call Browse
gff_location = objfiledirectory
arrFil = Split(gff_location, "\")
sFileName = arrFil(UBound(arrFil))

Application.ScreenUpdating = False
If FSO.FileExists(gff_location) Then
    If LCase(Right(sFileName, 4)) = ".txt" Then
        blnFlag_NotFound = False
        
        With Rs
            Do While Not (.EOF)
                i = 1
                Worksheets.Add(after:=Worksheets("Home")).Name = "Extracted Messages"
                Set wkst_Out = ThisWorkbook.Worksheets("Extracted Messages")
    
                ' will write all the values of a particular row into one string
                ' if there be any empty values in the table then those will not be read
                ' this string is then made into an array
                    strColVals = ""
                    For j = 0 To intTblFlds - 1
                        If IsNull(Rs.Fields(j).Value) = False Then
                            If strColVals = "" Then
                                strColVals = Rs.Fields(j).Value
                            Else
                                strColVals = strColVals & ";" & Rs.Fields(j).Value
                            End If
                        End If
                    Next
                
                    arrstrColVals = Split(Trim(strColVals), ";")
    
                    Set ts = FSO.OpenTextFile(gff_location)
                    Do While Not ts.AtEndOfStream
                        strLine = ts.ReadLine
                        If strLine = "" Then
                            If Not ts.AtEndOfStream Then
                                Do
                                    If Not ts.AtEndOfStream Then
                                       strLine = ts.ReadLine
                                    Else
                                        Exit Sub
                                    End If
                                Loop Until strLine <> ""
                            End If
                        End If
                        blnFlag_NotFound = False
                        If InStr(1, Trim(strLine), "{") > 0 Then
                            Sum = Sum + 1
                            wkst_Out.Cells(5, i).Value = strLine
                            Do
                                If Not ts.AtEndOfStream Then
                                    a = ts.ReadLine
                                    wkst_Out.Cells(5, i).Value = wkst_Out.Cells(5, i).Value & Chr(10) & a
                                End If
                            Loop Until a = "-}"
                            k = 0
                            Do
                                If InStr(1, Trim(wkst_Out.Cells(5, i).Value), Trim(arrstrColVals(k))) < 1 Then
                                    intInvalidCount = intInvalidCount + 1
                                    wkst_Out.Cells(5, i).Value = ""
                                    blnFlag_NotFound = True
                                    Exit Do
                                End If
                                k = k + 1
                            Loop Until UBound(arrstrColVals) < k ' - 1
                                
                            If blnFlag_NotFound = False Then
                                intValidCount = intValidCount + 1
                                i = i + 1
                            End If
                        Else
                            Do
                                If Not ts.AtEndOfStream Then
                                    a = ts.ReadLine
                                End If
                            Loop Until a = "-}"
                        End If
                    Loop
                    Rs.MoveNext
                    Application.ScreenUpdating = True
                   End If
                Loop
                Rs.Close
        End With

    Else
        MsgBox "Please Select a Text File to Extract"
    End If   
End If
MsgBox "Message Extraction Operation completed successfully."
MC.Close
End Sub

I hope now its a bit more clearer...

[RitaG]

The text files contains sections of lines, starting with "{" and ending with "-}".
The user provides search criteria in the "Home" sheet. The user enters the search texts in a table in the "Home" sheet. (can be more than one search text).

The macro needs to work like this : It has to locate those bunches of lines in the text file which starts with "{" and ends with "-}". Then the code has to check whether all the search texts are present in this set of lines. If yes, then it should be written to the sheet.If even one search text is not found then the entire set should not be written to the sheet.


e.g we have a text file which has these lines :
{12345678
1:abc
2:ddef
3:xyz
-}
{12345689
1:abc
2:jklm
3:ijk
-}
{98765543
1:abc
2:qwerty
3:xyz
-}

the user enters the search criteira as
search text 1-> 1:abc
search text 2-> 3:xyz

so the tool has to write the matching sets into a separate excel sheet. The following will get written

{12345678 {98765543
1:abc 1:abc
2:ddef 2:qwerty
3:xyz 3:xyz
-} -}

[Kyle123]

A 4MB text file is huge and opening it and looping through it line by line will always be slow and you're opening and parsing it for every record in your recordset that's mega slow - at least read it once and hold it in a variable, then look through that.

Also, you shouldn't be using sql like that, there's a known bug when executing sql on the workbook it is called from - which won't really help things either.

[RitaG]

And what should I hold in a variable ?

If you meant the set of lines between "{" and "-}", then how do you store multiple lines into a variable ??
Or if you meant each record of a recordet then again how do you do that ?

I'm only just learning VB coding in excel so please bear with my ignorance...

[RitaG]

I dont know how else to work this

Any suggestions ??

[Kyle123]

You'd store them as a string, read them in once and same the results as a string, or even better as an array so you could filter it to extract the info that you're looking for.

I wouldn't have called your code beginners

I think, conceptually, I'd do it like this:

I'd read each complete "object" into a single line string:
{12345678
1:abc
2:ddef
3:xyz
-}
Would become something like:
{12345678;1:abc;2:ddef;3:xyz;-}

I'd then save these single line strings into a single dimensional array.

I'd then use the Filter() function on each search parameter to get each of the matching objects.

I'd then compare the filtered arrays for objects that occur in both arrays, these would be the objects that match your search parameters

[RitaG]

Wont there be a limit to how many characters one can put into a string variable ? What if the number of characters between "{" and "-}" exceed that ?

[Kyle123]

Well how many characters are between {}?

I've only had the issue with string length once and that was on a txt file > 100MB - I tried to read the whole file in as a text string, so it's generally not an issue

[RitaG]

okay... I'll have to try this.. and i have never worked with filters in VBA so I'l have to get back to you on this.
Will this approach solve the problem of excel crashing while processing >4MB files?

[Kyle123]

At a guess it's not necessarily crashing, it is just taking so long to run the code it is unresponsive.

This approach should speed things up, filters are very easy

[jeffreybrown]

@RitaG,

I'm afraid your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

[Kyle123]

Where's it posted Jeff?

[RitaG]

@jeffreybrown
Sorry for the that ! I have edited my first post as suggested.

@Kyle123
Thank you so much !! I ran the 4MB text file and the code executed it in seconds ! Filters work like a dream. Thanks again