get text from memo field

[DaveM]

Hi all.

I did a search to no avail, but I need to know how to get text into another field or a variable from a memo field. The text may be an email, first name, last name or other usually prefaced by a lead like eMAIL:. I can't figure out how to search and extract the few words that are important to my project.

If it helps, I get leads off the internet from varied sources that format the body of the emails different ways so lines won't work.

some look like this:

Name: John Davenport
Address: 1 Not Available
City: Ocala
State: FL
Zip: 34352
Distance: Customer is approx. 36 mi. from you
E-mail: [email protected]
Phone: 354-804-6889

Others may look like this:

First Name: jonnnar
Last Name: crebese
Address: N/A
City State Zip: perry,fl 32377
Telephone: 850 589 7191
EMail: [email protected]

There are many other formats as well.

Any help is appreciated!

Dave Mason

[Stan Mathews]

Re: get text from memo field

I can't figure out how to search and extract the few words that are important to my project.

What few words might those be? If you can define the things you want then someone can probably help construct a means of getting the data. If there are no common characteristics then no one can help.

For example, is there some type of name you want extracted always preceded by

name:

whether it be

name:
first name:
last name:

If this is so, if the data is plain name: as in

name: john doe

are you sure there aren't any instances of

name: Doe, John

Complex task when you consider all the possible variations.

[DaveM]

Re: get text from memo field

Stan,

Thanks.

Might help if you read my query first? I thought I made it quite clear.

I need to extract several things and put them into fields on a form. I need a way to search for several keys which may be different on different emails in. I gave examples of those.

Dave

[DaveM]

specific memo field tex to variable

Hi All!

Yes, I searched the database.

I need to extract text like:

Name: John Doe

Email: [email protected]

Phone: 352-352-2015

from a memo filed on a form to variables. I would want only the actual name, actual email and actual phone number extracted. I know how to do what I need after that.

Surely there is a ready-made function(s) to do this, but I have not found it/them.

Thanks all who help!

Dave Mason

[Al Buchholz]

Re: get text from memo field

I did a search of help for 'string'

Found stringscanner

Haven't used it. Does it do what you want?

Otherwise look at extract_string()

Many others found under character functions in the help system.

[Stan Mathews]

Re: get text from memo field

Stan,

Thanks.

Might help if you read my query first? I thought I made it quite clear.

I need to extract several things and put them into fields on a form. I need a way to search for several keys which may be different on different emails in. I gave examples of those.

Dave

I've re-read the post and I see nothing which definitively tells me which data you wanted. When I see words included such as

The text may be an email, first name, last name or other usually prefaced by a lead like eMAIL

I am at a loss as to how to give solid advice. I was merely pointing out that you must be able to say exactly what you want. That, in turn, can be translated into a code sequence to deliver the desired result.

Hope Al's suggestion gets you underway. Good luck.

[DaveM]

Re: get text from memo field

Stan,

I remember you from the past few years.

Dave

[DaveM]

Re: get text from memo field

Al,

Thanks! I did not see those. I will try the scanner to get a point (if it will) and then extract the line behind it or the whole line.

Dave

[G Gabriel]

Re: get text from memo field

Dave:
1-If you are in a set, and if you are searching a memo field in a child table, the syntax is:
flatenquery(rtf_field_to_text("table-"field"),"search_string"))

2-If you are in the parent table, or simply searching a single table, the syntax is:
contains(rtf_field_to_text("table-"field"),"search_string"))

[DaveM]

Re: get text from memo field

I think I need to explain more!

I have to find about 6 different things within a memo field. An example would be:

name: John Doe - where I need to find the line and copy the name "John Doe" to another field or variable on the same form. I can disect it after that with no problem into the fields First and Last.

email: [email protected] - where I need to find the line and copy "[email protected]" to a field or variable on the same form.

phone: or Main phone: 352 302 2015 - where I need to copy 352 302 2015 and place it in my phone field on the same form.

There are other lines/words I need to copy also.

I use alpha to copy my emails for work where people are going online to search for cars and I have several companies that send the emails with this information (formatted all kinds of ways) to me. I then formulate a response within Alpha 7 and return a price quote to them. I currently do a lot of copy and paste to get the needed information into the proper fields so I can send a good response. I have about 3600 records in this database and associated tables. The memo and other fields are all in one table(master).

I have been using Alpha since about 1990 and am not a novice. I like most have problems sometimes.

Dave

[Tom Cone Jr]

Re: get text from memo field

Dave, I fear you're in for a rough ride without some delimiters in the text. How will your script know where a target string begins and ends? Some names have one word, some 3 or 4. Some have punctuation, other's don't. How will the script know where the phone number string begins and ends. Some will have spaces, some hyphens, some parentheses without spaces. The combinations possible with raw text are mind boggling.

If it were me and the text of the email were available to me, I'd insert field delimiters manually before moving the text into a memo field. Then my script could parse the text in the memo reliably.

My 2 cents.

-- tom

[DaveM]

Re: get text from memo field

Tom,

Long time since I read your sinical rhetoric. Nice to hear from you! You still here in FL?

All the texts I am to searc for begin with a telling word like "name:", "First" or "email:" and the line is blank with only the appropriate text after that. If I can find "email", I can then copy all the text after. That should help me, I think.

I can break it down easily after I get it.

Dave Mason

[G Gabriel]

Re: get text from memo field

Dave:
The expression I sent you does just that: it will locate the text you are looking for within the memo field. As to copy/paste, you would have to do that part manually or add another script.

For example, if you are looking for a sub-text "name: John Doe" and since you do not know the name beforehand, you would look just for "name" which will be followed by "John Doe", then you would, manually, copy "John Doe" to the desired field.

If you wish to automate the process where the script will find the the sub-text that follows "name" and copy it to a field, it's possible by finding the location of name in the RTF and then copy from that point forward. Problem is, How much forward? I am afraid, this part is probably impossible to do with a script for several reasons, not the least of which is the fact that names come in all shapes, forms and lengths, but the fact that you might have "name" followed by a space, a colon, maybe a space & a colon or any other format. Not only the script wouldn't know where the name ends, it won't know where it begins. I am afraid that part has to be done manually unless you are willing to write an extensive script that ignores spaces, colon, semicolon, coma etc which will tell the script where to begin copying, and even if you do that, I do not see how you could tell it where to stop copying. There is a good reason why databases are made of fields. If what you wish to accomplish is a fully automated process that runs down the memos, pluck the pertinent information and store it in the pertinent fields, I am afraid that falls under "Artificial Intelligence" which I have heard about and have been waiting for, for the last 25 years and it has yet to arrive.

Nothing in your question suggests being novice by the way!

[G Gabriel]

Re: get text from memo field

I guess, I was writing my reply at the same time Tom was writing his and did not have a chance to read Tom's reply beforehand. That happens in this board particularly with slow-typists like myself.

[Tom Cone Jr]

Re: get text from memo field

Dave,

If each target word is in a line by itself, and if each target word always begins two characters after the colon, and if each line ends with a crlf pair, I think your goal is attainable. Do you have some sample memo field data you can post, or compose for testing?

-- tom

ps? sinical? And, yes. I'm still in L.A. (Lutz Area).

-- tom