Another parcing question

[Frederick Wilson]

Hello all,

I am provided a CSV txt file from an Oracle DBS. I import it into Access
so I can perform reports and other actions. (Don't ask why).

One of the txt fields has the following 'type' of information

"User: Wilson Frederick opened Facility: RM101 at 8/2/2003 05:44:11"

what I want to do is have "Wilson Frederick" in a column and "RM101" in
a column.

I think the priciple is to find everything between "User: " and "
opened". As well as everything between "Facility: " and " at".

I found this one the net
http://www.mvps.org/access/strings/str0003.htm

I think that article is going down a similar road but I am not sure how
to apply it to my situation.

Any ideas?

Thanks,
--
Frederick Wilson

_____________________________________
for multimedia design services visit
http://www.legalanimatics.com

 

[Tom Ellison]

Dear Frederick:

To do what you want will require a couple of skills.

You will need to code the function in VBA. Use the built in InStr()
function to search for the keys you mentioned ("User:", "opened", "at").
The values returned by these searches may require you to offset from there
to the desired substring which you would put in the query results.

You may encounter significant difficulties. Do users always include a
colon? Is it always followed by two spaces? As you develop the function,
your query skills will be needed. Display the original string and all the
results of parsing it. Use this to carefully test what you've written.
Solve as many problems as you can. And constantly ask yourself whether the
user entry was sufficiently consistent to be worth the effort of parsing it
this way.

Tom Ellison

 

[Frederick Wilson]

Dear Frederick:

To do what you want will require a couple of skills.

You will need to code the function in VBA. Use the built in InStr()
function to search for the keys you mentioned ("User:", "opened", "at").
The values returned by these searches may require you to offset from there
to the desired substring which you would put in the query results.

You may encounter significant difficulties. Do users always include a
colon? Is it always followed by two spaces? As you develop the function,
your query skills will be needed. Display the original string and all the
results of parsing it. Use this to carefully test what you've written.
Solve as many problems as you can. And constantly ask yourself whether the
user entry was sufficiently consistent to be worth the effort of parsing it
this way.

Tom Ellison

AHHHH so the Key is InStr(). I will surely check that out tomorrow.
Thanks, that will surely get me started.

Ah yea, "User" is always suceeded by a colon and one space.

Thanks again,
Fred

--
Frederick Wilson

_____________________________________
for multimedia design services visit
http://www.legalanimatics.com

 

[John Nurick]

Hi Frederick,

I tend to use regular expressions for this sort of parsing: it saves an
awful lot of fiddling with InStr() and Mid() and so on. If you use the
rgxExtract() function that I recently posted at
http://www.j.nurick.dial.pipex.com/Code/vbRegex/rgxExtract.htm ,
you can use this pattern to extract everything between "User: " and "
opened " , and between "Facility: " and " at":

"User:\s+(.+)\s+opened\s+Facility:\s+(.+)\s+at"

rgxExtract() works in Access queries. An expression like this will
return the user name:

rgxExtract([FieldName],
"User:\s+(.+)\s+opened\s+Facility:\s+(.+)\s+at", 0)

Change the 0 to 1 to return the second piece of data, the facility.

 

[Frederick Wilson]

Dear Frederick:

To do what you want will require a couple of skills.

You will need to code the function in VBA. Use the built in InStr()
function to search for the keys you mentioned ("User:", "opened", "at").
The values returned by these searches may require you to offset from there
to the desired substring which you would put in the query results.

You may encounter significant difficulties. Do users always include a
colon? Is it always followed by two spaces? As you develop the function,
your query skills will be needed. Display the original string and all the
results of parsing it. Use this to carefully test what you've written.
Solve as many problems as you can. And constantly ask yourself whether the
user entry was sufficiently consistent to be worth the effort of parsing it
this way.

Tom Ellison

TOM,

That InStr() WORKED GREAT!

Thank you so much,
Fred


--
Frederick Wilson

_____________________________________
for multimedia design services visit
http://www.legalanimatics.com