Load text file into memory for searching?

[Guest]

Is it possible to load a text file into memeory and perform binary searches
to increase speed?

 

[NickHK]

Check out the Open statement in the VBA Help: Their example of
Open "TESTFILE" For Binary Access Write As #1

However, if this is connected with your 1 ~ 5 million row text file, you
will probably have to do it in chunks.

NickHK

 

[Guest]

It is. Because right now, I have something that searches through the text
line by line with textCompare and it is quite slow (averaging about 3-4
seconds per search). Will the Binary method help in speeding things up?

I remember a friend once told me that if you load a text file into memory
and perform binary search, the maximum amount of time is equal to Log(N) vs
if you read it line by line its maximum time is N. I'm new to programming so
if someone can enlighten me on this, it'll be much appreciated.

 

[Dave Peterson]

Maybe you could look for your keys in the text file and create a new (much, much
smaller) text file based on what you find.

 

[NickHK]

I still think you should be looking at a database approach with that much
data; after all that is what they are designed for and I doubt any code you
can write will be optomized to the level of a DB manager.
IIRC, MySQL and SQLite support full text searches. With the help of a stored
procedure, even on a few million rows, you should get results pretty quick.

Just my 2c..

NickHK

 

[Guest]

I also agree. A database is the ultimate goal. However porting to that system
is not possible at the moment due to many constraints.

So searching through the text file is the best thing I have at the moment.
Unless there is a way to automatically port the contents in the text file
(which is mostly columnized data) to a database like Access?

 

[Guest]

That is essentially what I am doing right now. I am searching through the big
file to pull out sections into another text file. For example, I had 13 keys
I was searching and it returned me another text file with 13 paragraphs That
takes about 2 minutes because it literally runs through the whole text file
13 times. What I like to know is if there's any way of speeding that process
up.


Thanks.

 

[Dave Peterson]

Can you loop through the file once, but loop through the keys?

do until eof
read a record
for each key
check it, write it (a combined file or new file for each key???)
next key
loop

 

[NickHK]

Easy; link or import. Either way the data is available to Access.
However, data integrity may depend on what you mean by "mostly columnized
data".

NickHK

 

[Guest]

Ok I figured out how to import the text file into Access, and it looks
alright in terms for columns. Now how would I be able to search 2 key words,
and copy all the rows in between those 2 key words into excel?

 

[NickHK]

Use a query/SQL. If you are not familiar with SQL, this is the basic
concept:

Assuming you have some key defined, maybe in a field called ID
- Get the first word:
SELECT ID FROM YourTable WHERE TextField="KeyWord1" >> ID1

- Get the second word:
SELECT ID FROM YourTable WHERE TextField="KeyWord2" >> ID2

SELECT * FROM YourTable WHERE (ID>=ID1) AND (ID<=ID2)

You can combine all this this into a single statement, but understand the
logical steps first.

NickHK

 

[Guest]

So do I use VB to program this SQL or do I do this logic in the design Query
in Access?

 

[NickHK]

Either.
If they is a lot of processing to be done, it is probably better to get the
DB Engine to do that for you, then return only the records you want.
Look into using ADO.

NickHK