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]

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
-} -}

[RitaG]

And here is the code.

Please Login or Register  to view this content.

I hope now its a bit more clearer...

[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]

I dont know how else to work this

Any suggestions ??

[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...

[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