Reading and storing data

[jalverson]

This is related to financial audited statements. I want to write a macro or vb code to scan a document looking for keywords. When the keyword is found, all of the data after the keyword is stored in a field until the next keyword is found.

Here is an example. The original document is a text document. I create an access table to hold the specific data. Then, I run the vb code or macro to scan the text document. When the code finds the keyword NAME, it imports the data after the title into the name field. Then it finds the keyword ADDRESS and imports the data that follows into the address field. Next, it might find the keyword company background and imports the text that follows into a memo field. The code would continue until the entire document is scanned and imported into the related data fields.

Does anyone have suggestions on how to handle this challenge. Thanks for your responses.

Jeff

 

[gromit]

Hi Jeff -

Sounds a little like the approach by compilers to "tokenize" a program (convert the lines of a program into language tokens that are recognized by the compiler as instructions).

I would store the tokens in a table or array and use InStr to see if there are any matches. You could do a first pass to identify all instances of each token and their relative locations, then write them to a temporary table. You could then reopen the table and sort the locations, so that you are now parsing in order. You would know each sequential position and could snip the string into parts.

I think the real challenge would be how to differentiate tokens from legitimate data. E.g. if STATE is a keyword, what about a company called Tri-State Financial Services? Error checking and trapping would be key.

Am I telling you anything that you don't already know?? Post back if I can be of more help.

- gromit

 

[jalverson]

Thanks for the response. It will take me some time to do some testing based on your recommendation. The first business hurdle is to determine the correct application. I have already stated that I don't think Access is the right tool because the data will quickly exceed the 2 gig limitation. I think we should be using a sql database on a sql server. Then, we could build the front end user screens using Access forms but then insert the data into the sql database.

You may not see any response for a couple of weeks, but I will post back if I have additional questions. Thanks again for your help. This forum has been great for obtaining quick solutions.

Jeff

 

[reclusivemonkey]

Jeff,

VBA is very limited when it comes to pattern matching. I would think something like Perl (Pratical Extraction and Reporting Language) would be a much better tool for the job.