text parsing in a field

[ken]

Hi,
I wonder if someone can help me with a text parsing problem.

I have a form for a kind of an address book and I am looking for a quick method to input data into the fields.

In this form I have a memo field where I input the full details which I copy from an email msg and then I paste into this memo field.
Now from the memo field, which contains all the necessary data I have to select each item of interest (name, phone, fax, mobile, email) and then copy and paste them into the appropriate fields.
I would like, if possible, to parse the text from this memo field and extract at least the email addresses automatically and input them into the EMAIL field.
Usually there are at least 2 email addresses so the script
has to find them and put them in the fields EMAIL1 and EMAIL2. (I have provided 5 email fields. just in case)
I think this can be done with regular expressions.

Also it would be great if the script can parse also the telephone numbers and detect the country prefix and then fill in the appropriate COUNTRY field. I have in the database a COUNTRY_PREFIX field so the script can check the phone number prefix against the data from this field.
I can give you some sample data to better understand the case

------

Phone: +33 (1) 47 35 91 98
Fax: +33 (1) 47 35 92 96
email: [email protected]

-----

TEL: +972 - 9 - 8928300 FAX: + 972 - 9 - 8883643
MBL: +972 507 456741
EMail: [email protected]

------

Thanks a lot for your attention!
Regards,
Corneliu

[Stan Mathews]

RE: text parsing in a field

To make sure I understand your input structure I need to ask some questions.

Will phone numbers always have "Phone: " before them?
Phone: +33 (1) 47 35 91 98

Will fax numbers always have "Fax: " before them?
Fax: +33 (1) 47 35 92 96

Like wise for email?
email: [email protected]

A sample of your table would be helpful.

The COUNTRY field does not need to be filled in by the same mechanism that parses the phone numbers. You can design a lookup field rule that fills in the country field when the prefix field is populated. To do this you need a table of prefixes and corresponding country names.

[ken]

RE: text parsing in a field

Hi,
Thanks your prompt reply.

Unfortunatelly, I receive many emails from customers all over the world and their messages are not standardized at all.
Sometimes the numbers are precedeed with a prefix, sometimes not and the prefixes are not always the same.
I will give you some examples below

In general you can assume that the phone numbers have in front of them one of the foll prefixes (tel,phone,fax,mob)

Even if the computer can not guess all of them, it is still a big improvement if it can still guess most of them.
I can later correct them manually, if necessary.

TEL. +380487177757 FAX. +380487775858
Pers. e-mail: [email protected] Mob: +380976257227

-----

Tel: +9714 277 2633
Fax: +9714 277 2644
Email: [email protected]
Mob: +97150 776 4956 - SAMI

-----

MOB.002012 7067758 / FAX 00203 4207721

------

PHONE/FAX 55377230/55327798
MAIL [email protected]

-----

TEL: 886-2-27217731 (RESP) / 27772771 (DIR
FAX: 886-2-27117798/27117799
MOBILE: 886-935773324
E.MAIL: [email protected]

-----

I think parsing of email addresses is much simpler so i can begin with parsing of email addresses from the message and entering them in the appropriate fields.

Regards
Corneliu

[Stan Mathews]

RE: text parsing in a field

I think you are correct. It would be very tedious to try and parse out phone numbers when the expected formats are not known.

In parsing for email addresses, all we need to do is find a string of characters containing the "@" unless there is some other possibility I am not aware of.

Does the possibility exist that there will be more than one email address in the memo field? If there is do you want the first or last to be stored in the email address field?

[ken]

RE: text parsing in a field

yes, it is possible to have 1 up to 3 emails in a message.
but I have 5 email fields in my database EMAIL1, EMAIL2, ETC...

[ken]

RE: text parsing in a field

yes, it is possible to have 1 up to 3 emails in a message.
but I have 5 email fields in my database EMAIL1, EMAIL2, ETC...

I would preffer to parse the first email encountered, then to check for other possible emails and store them in the next fields EMAIL2, EMAIL3....

I would like the parsing process to start when I paste the
selected message in the memo field...

Regards,
Corneliu

[Stan Mathews]

RE: text parsing in a field

"I would like the parsing process to start when I paste the
selected message in the memo field... " - I am not aware of ane events that fire when data entry is pasted into a form field as the change does not take place until the field is exited. This sample parses the memo field when it is exited.

I am a attaching a sample of something like I think you want. If you do not have a table named "test" in your database you can add my table to your database and examine the form "memtest". The programming is behind the onchange event of the "mem" field on the form.

[ken]

RE: text parsing in a field

Thank you so much for your efforts. You are very close to the solution. I have tested with some real data and i noticed that it works when there are only email addresses in the selection. When the email is preceded by a phone number or other text it does not recognize the email address. After deleting the text in front of the email address it works again.
I will give you some samples that do not work in the present configuration, maybe you can work out a solution.
Anyway the program is very close to what I want it to be. Congratulations!


-----

phones: +90 216 4877234 - 3897745
fax: +90 216 4777241
e-mail : [email protected]
[email protected]
[email protected]

********

in the above example only the first email is recognized but it is trasferred in the 3-rd place (email3), the first 2 places remain empty.
After deleting the first line with phones, 2 emails are recognized but the first email is still empty.
After deleting of the line with fax, all 3 email are recognized....

-------

UNIVERSAL RING S.A.
Fax (562) 6397768
Tel (562) 6397733
Email [email protected]
Web http://www.unimart.cl

------

in the above example, nothing is recognized at first.
after deleting the first line, the email is transferred to 3-rd place.
after deleting line 2 (with fax), the email is transferred to 2-nd place
and after deleting the line with phones, the email is transferred at its place (email1)
I think the routine reserves places for the lines above the email address.

Maybe you find a solution to correct the situation.
Also maybe you can add the possibility to add also the web site address.

Thanks a lot for your efforts so far.

Regards,
Corneliu

[Steve Andrews]

RE: text parsing in a field

Corneliu,

Here is a variation on Stan's neat example. Notice that instead of going through loops to search for the "@" of email addresses or "http:" for web addresses, I utilized the filter_string() function. This function retrieves all lines of a crlf-delimited list which contain a certain substring.

I hope this helps.

Steve

[Stan Mathews]

RE: text parsing in a field

If you promise not to tell anyone that I really don't know what I'm doing but eventually come across a solution, you can try the attached script. It replaces the onchange event script for the field previously mentioned.

I did this with version 5 methods so it would be backward compatible. There are easier methods.... but this works.

[ken]

RE: text parsing in a field

Hi,

I have cheched hundred of messages and the most used prefixes for phone, fax and mobiles are as follows:

TEL. or PH: or PHN: or Tel : or Phone : or Tel: or T: or Ph. or PHONES.: or Telf. or PHN : or Direct: or LINE: or
TEL : or Direct : or Telefon: or TEL.: or TELP: or Tel)

FAX: or Fax : or F: or Fx. or FAX....: or FAX :
or Telefax: or fax. or FX: or FAX.: or Fax)

MOB: or CELL: or Gsm or MOBIL : or Mobile : or MB: or HP:
or Mob)

so for most purposes we can asume that if one line contains any of the above prefixes, the number that follows is phone, fax or mobile number.

Also you can asume that the first line from the selected text always contain the company name (COMPANY field)

In this way, it should be possible to parse the following fields:

COMPANY (always the first line of the text)
PHONE
FAX
MOBILE
EMAIL1
EMAIL2
EMAIL3
WEB_SITE

I am sure that you are able to implement it as you are already very close.

Thanks a lot for your efforts which are highly appreciated here.

Regards,
Corneliu

[Stan Mathews]

RE: text parsing in a field

Cool, never thought of that approach Steve.

[ken]

RE: text parsing in a field

Hi Steve

thanks a lot for your interest.
I have tested your program with some real life data and it works but at first a debugging windows shows up memtest : mem_on change and the pointer indicates: dim emails as C

after closing this debugging windows, I can see the email at its place

pls see also my earlier comment regarding the other fields.
COMPANY, PHONE, FAX, MOBILE

Thanks a lot for your efforts,

regards,
Corneliu

[Steve Andrews]

RE: text parsing in a field

'Sorry. I left the debug()'s in uncommented. Here it is again.

As for the other items to search for, you need to be careful to make sure that these substrings have the colon (or a space & colon) to follow them. Why? Because they may be found within the email & web adresses themselves.

For instance, if the person to contact is at ExxonMobile Oil, his/her email address may be "[email protected]". If you simply search for "mobile" you will get this email address which is not what you want.

Steve

[ken]

RE: text parsing in a field

yes it works but i noticed that if the email has some blank spaces in front of it then it is not parsed like in the foll example

E-MAIL: [email protected]
[email protected]
Yh ID: [email protected]

the second email is not parsed until I delete the spaces before it

regards,
Corneliu