×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Saving Text from Email _Cliptext to Cursor
8

Saving Text from Email _Cliptext to Cursor

Saving Text from Email _Cliptext to Cursor

(OP)
I receive an Email containing Data to update a Database.

The format of the Email is:

CODE -->

Date          Number       Callsign Area  Notes
01/01/2020    52931        M9TEK    SP83
05/01/2020    52932        GW9TIP   TL14  Any additional information here 

Although the Date and Number Fields are constant width, the others can be of variable length. Another complication is that the number of spaces between each Field can also vary in different Emails.

The Email Data is copied to the Clipboard (_Cliptext). I envisaged using STREXTRACT to populate the Cursor Fields, but how can I deal with the variable spaces?

Any help would be most appreciated.



Regards,

David.

Recreational user of VFP.

RE: Saving Text from Email _Cliptext to Cursor

How about:

CODE

**Date          Number       Callsign Area  Notes
**01/01/2020    52931        M9TEK    SP83
**05/01/2020    52932        GW9TIP   TL14  Any additional information here
**1234567890123456789012345678901234567890
FOR I = 2 TO MEMLINES(_CLIPTEXT)
	m.STRING = MLINE(_CLIPTEXT,I)
	m.DATE = CTOD(LEFT(m.STRING,20))
	m.NUMERIC = VAL(SUBSTR(m.STRING,11,9))
	m.STRING = RIGHT(m.STRING,20,255)
	m.CALLSIGN = GETWORDNUM(m.STRING,1)
	m.AREA = GETWORDNUM(m.STRING,2)
	m.NOTES = ALLTRIM(RIGHT(m.STRING,21,255))
	** do something with that...
NEXT 

Regards

Griff
Keep Smileing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you.

RE: Saving Text from Email _Cliptext to Cursor

Outlook can be automated to get the body and attachments.

And if you didn't format this text data that way, but it xomes in that way, that's SDF, with fixed width columns. I understand you said the spacing can differ, but the line with captions is giving the spacing away when you determine the positions of first letters of each caption. The only difficulty would be captions with multiple words.

One example is too little information to give advice, but so far that's my observations. It could also be tabs, which changes counting positions depending on either tab length or tab positions. If it's tabs the look will depend on which editor you use, but the separation of the columns then can be very easy, even if the data doesn't align visually.

Where does this come from? Is this how a telephony system reports incoming calls? Is there anything in the settings that would enable this to be output as CSV with commas or semicolons instead?

Bye, Olaf.

Olaf Doschke Software Engineering
https://www.doschke.name

RE: Saving Text from Email _Cliptext to Cursor

David,

Since you're receiving the data by email and can't rely on a regular format, you can look for patterns in the text and try to fetch the lines that follow it, assuming these may hold significant data.

This approach use Regular Expressions. By using it, you can copy the entire email and the program will try to identify the lines that match your requirements.

Let's say you receive this email:

CODE -->

Dear David,

Lorem ipsum dolor sit amet, consectetur adipiscing elit. Duis rhoncus ante eu pellentesque rutrum. Nam sit amet scelerisque
arcu. Maecenas suscipit tincidunt dui id aliquam. Aenean lobortis ullamcorper euismod. Suspendisse at sapien rhoncus,
ultrices lacus imperdiet, porttitor lectus. Maecenas ut urna elementum, ullamcorper magna vitae, fermentum tortor. Quisque
dui mi, lacinia in mollis sed, imperdiet a leo. In in laoreet ante. Aliquam tortor ligula, efficitur ut erat quis, imperdiet
rutrum turpis. Nunc ultrices euismod nibh, in faucibus mauris. Nunc rutrum mauris diam, eget tempus lorem suscipit non. In
malesuada risus vel erat fringilla, sed aliquam risus sodales.

Mauris in elit est. Curabitur vulputate mauris ut mauris suscipit tincidunt. Pellentesque libero justo, lacinia a lobortis
sed, imperdiet eu leo. Quisque pretium hendrerit erat ut tincidunt. Vestibulum id elit non augue consectetur venenatis. Nunc
sagittis bibendum mattis. Maecenas ultricies interdum sapien, in tincidunt odio bibendum non. Etiam pretium, velit aliquet
imperdiet cursus, eros tortor fermentum quam, eget volutpat risus ante eu justo. Nunc nibh leo, consectetur quis semper vitae,
elementum vel tortor.

Date          Number       Callsign Area  Notes
01/01/2020    52931        M9TEK    SP83
05/01/2020    52932        GW9TIP   TL14  Any additional information here

Best regards 

Copying its contents into the clipboard and running this program

CODE --> VFP

LOCAL Source AS String

m.Source = _Cliptext

SET DATE TO DMY
SET SEPARATOR TO "/"

CREATE CURSOR DataStore (Date Date, Number Int, Callsign Varchar(20), Area Varchar(20), Notes Memo)

LOCAL ARRAY Lines(1)
LOCAL LineIndex AS Integer

LOCAL RegExp AS VBScript.RegExp

m.RegExp = CREATEOBJECT("VBScript.RegExp")
m.RegExp.Pattern = "^\s*(\d{2}\/\d{2}\/\d{4})\s+(\d+)\s+(\w+)\s+(\w+)\s*(.*)$"

FOR m.LineIndex = 1 TO ALINES(m.Lines, m.Source)

	m.Matches = m.RegExp.Execute(m.Lines(m.LineIndex))

	IF m.Matches.Count = 1
	
		m.Match = m.Matches.Item(0)

		IF m.Match.Submatches.Count = 5

			INSERT INTO DataStore ;
				VALUES (CTOD(m.Match.Submatches.Item(0)), ;
					VAL(m.Match.Submatches.Item(1)), ;
					m.Match.Submatches.Item(2), ;
					m.Match.Submatches.Item(3), ;
					m.Match.Submatches.Item(4))

		ENDIF

	ENDIF

ENDFOR

BROWSE 

will result in this


RE: Saving Text from Email _Cliptext to Cursor

You could probably build a solution involving the use of GETWORDCOUNT() and GETWORDNUM(). The point is that those functions consider a string of an arbitrary number of spaces as a word delimiter. So, in your example, the date, number, calls sign and area would all be separate words, regardless of the number of spaces between them. It's true that wouldn't apply to your Notes field, but as that is always the last field on the line, it should be fairly easy to deal with that.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: Saving Text from Email _Cliptext to Cursor

So, to amplify my suggestion:

CODE -->

lcLine = <the next line of text>
lcDate =  GETWORDNUM(lcLine, 1)
lcNumber = GETWORDNUM(lcLine, 2)
lcCall =  GETWORDNUM(lcLine, 3)
lcArea =  GETWORDNUM(lcLine, 4)
lnPos = AT(lcArea, lcLine) + LEN(lcArea)
lcNotes =  substr(lcLine, lnPos) 
Or something along those lines.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: Saving Text from Email _Cliptext to Cursor

Do you have any control over the email that is sent? Maybe they would be nice enough to attach a CSV? Seems like a harmless request anyway.

RE: Saving Text from Email _Cliptext to Cursor

(OP)
Thank you all for your replies.

I will carry out some tests with some Historic Data and see how each Program works.

The information / Data received is from a Club to allow members to update their records as they wish. My information is kept in a VFP Database, others my choose Excel, Word or whatever. I would not expect the Data Originator to comply with my Data Format, I am just grateful to receive the information.

• Data has no Headers, I provided them to show which filed the data is stored in.
• Date & Number Fields are fixed Length the remainder can vary in length on each line.
• Spaces between Fields (Words) are consistent in each Email, however they can very between Emails.

Regards,

David.

Recreational user of VFP.

RE: Saving Text from Email _Cliptext to Cursor

(OP)

Quote (Atlopes)

Since you're receiving the data by email and can't rely on a regular format, you can look for patterns in the text and try to fetch the lines that follow it, assuming these may hold significant data.

This approach use Regular Expressions. By using it, you can copy the entire email and the program will try to identify the lines that match your requirements.

I need to read up on the VBScript.RegExp to see what this does.

All appears to be working ok on initial tests with one exception, if a Line Entry contains a “/” then the Line is not Inserted into the Cursor.

Regards,

David.

Recreational user of VFP.

RE: Saving Text from Email _Cliptext to Cursor

(OP)

Quote (Mike Lewis)

You could probably build a solution involving the use of GETWORDCOUNT() and GETWORDNUM(). The point is that those functions consider a string of an arbitrary number of spaces as a word delimiter.

Mike,

You quite rightly point out that the Data is basically "Words" separated by a number of spaces and it is the varying spaces that is the issue to overcome.

I'll take a look at GETWORDCOUNT() and GETWORDNUM(). "Notes" can be assumed to be any Data after "Area"

Regards,

David.

Recreational user of VFP.

RE: Saving Text from Email _Cliptext to Cursor

This would do better then

CODE

m.CLIPTEXT = STRTRAN(STRTRAN(_CLIPTEXT,"  "," "),"  "," ")
FOR I = 1 TO MEMLINES(m.CLIPTEXT)
	m.STRING = MLINE(m.CLIPTEXT,I)
	m.DATE = CTOD(GETWORDNUM(m.STRING,1))
	m.NUMERIC = VAL(GETWORDNUM(m.STRING,2))
	m.CALLSIGN = GETWORDNUM(m.STRING,3)
	m.AREA = GETWORDNUM(m.STRING,4)
	m.ALLBARNOTES = DTOC(m.DATE)+" "+STR(m.NUMERIC,5,0)+" "+m.CALLSIGN+" "+m.AREA
	m.POS = AT(m.ALLBARNOTES , m.STRING) + LEN(m.ALLBARNOTES )
	m.NOTES =  SUBSTR(m.STRING, m.POS)


	** do something with that...


NEXT 

Regards

Griff
Keep Smileing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you.

RE: Saving Text from Email _Cliptext to Cursor

Quote (David)

All appears to be working ok on initial tests with one exception, if a Line Entry contains a “/” then the Line is not Inserted into the Cursor.

Can you give an example, please?

RE: Saving Text from Email _Cliptext to Cursor

(OP)



Quote (Atlopes)

Can you give an example, please?

27/02/2018 22177 G9RRF/7B4KET NZ13 Any-additional-text-here

Regards,

David.

Recreational user of VFP.

RE: Saving Text from Email _Cliptext to Cursor

David,

Change the pattern to

CODE --> VFP

m.RegExp.Pattern = "^\s*(\d{2}\/\d{2}\/\d{4})\s+(\d+)\s+([A-Z0-9\/]+)\s+(\w+)\s*(.*)$" 

RE: Saving Text from Email _Cliptext to Cursor

(OP)

Quote (Atlopes)

Change the pattern to

m.RegExp.Pattern = "^\s*(\d{2}\/\d{2}\/\d{4})\s+(\d+)\s+([A-Z0-9\/]+)\s+(\w+)\s*(.*)$"

That worked; thank you very much for the quick response.

I'll spend some time compiling some Historic Data and see if I can break it!

Regards,

David.

Recreational user of VFP.

RE: Saving Text from Email _Cliptext to Cursor

Quote (David)

I'll spend some time compiling some Historic Data and see if I can break it!

Great, it will be an exercise on Regular Expressions, then.

RE: Saving Text from Email _Cliptext to Cursor

(OP)

Quote (Atlopes)

Great, it will be an exercise on Regular Expressions, then.

Yes, I need to understand the Regular Expressions code as I may decide to alter the code so that if "Area" contains N/A "Area" is left Blank/Empty.

Another observation, if "Area" contains N/A the /A part is shown in "Notes".

e.g 05/11/2019 22873 ZA0FAW N/A additional text here

Regards,

David.

Recreational user of VFP.

RE: Saving Text from Email _Cliptext to Cursor

This pattern?

CODE --> VFP

m.RegExp.Pattern = "^\s*(\d{2}\/\d{2}\/\d{4})\s+(\d+)\s+([A-Z0-9\/]+)\s+([A-Z0-9\/]+)\s*(.*)$" 

Plus, deal with N/A semantics when you store the data

CODE --> VFP

INSERT INTO DataStore ;
				VALUES (CTOD(m.Match.Submatches.Item(0)), ;
					VAL(m.Match.Submatches.Item(1)), ;
					m.Match.Submatches.Item(2), ;
					IIF(m.Match.Submatches.Item(3) == "N/A", "", m.Match.Submatches.Item(3)), ;
					m.Match.Submatches.Item(4)) 

RE: Saving Text from Email _Cliptext to Cursor

(OP)

Quote (Alopes)

This pattern?

That did the trick!

Quote (Alopes)

Plus, deal with N/A semantics when you store the data

Thank you for that, although I've used similar code before, I wasn't sure how to incorporate it in your code.

I'll continue with testing of the Historic Data although I think all options have been covered.

Regards,

David.

Recreational user of VFP.

RE: Saving Text from Email _Cliptext to Cursor

(OP)
Atlopes

I've completed initial tests using some Historic and fictional Data; everything is working ok. I will now add the Code to my project. Many thanks for providing a working example it was much appreciated.

Other contributors

Although I've chosen Atlopes option for my application, I do appreciate your contributions. Having started this thread with a narrow view due to lack of knowledge, your contributions have given me other VFP Commands etc to pursue which will help me expand my knowledge base.

Thanks to you all.

Regards,

David.

Recreational user of VFP.

RE: Saving Text from Email _Cliptext to Cursor

(OP)
I have made a few alterations to the original REGEX Code so that it reflects some changes made to the format in the information I receive from a Third Party.

CODE -->

(\d{2}\/\d{2}\/\d{4})\s+(\d+)\s+([A-Z0-9\/]+)\s+([A-Z0-9\/]+)\s*(.*)$" 

The latest code I use is:

CODE -->

m.RegExp.Pattern = "^\s*(\d{1,}\/\d{1,}\/\d{2,})\s+(\d+)\s+([A-Z0-9\/]+)\s+([A-Z0-9\/]+)\s*(.*)$" 

I would like to change ([A-Z0-9\/]+) in the above code to accept e.g. SX86 or a Blank String.

Any help would be most appreciated.

Regards,

David.

Recreational user of VFP.

RE: Saving Text from Email _Cliptext to Cursor

You have two places with ([A-Z0-9\/]+) and SX86 does not tell me which one you could mean.

So what column number (match number) should be allowed to be blank alternatively to a match of its usual expression?

Can you give an example line of data you'd like to match? That would be the simplest way to tell us.

Bye, Olaf.



Olaf Doschke Software Engineering
https://www.doschke.name

RE: Saving Text from Email _Cliptext to Cursor

David, assuming that a Blank String cannot be followed by notes and it's only possible for the Area column:

CODE --> VFP

m.RegExp.Pattern = "^\s*(\d{1,}\/\d{1,}\/\d{2,})\s+(\d+)\s+([A-Z0-9\/]+)(\s+([A-Z0-9\/]+)\s*(.*))?\s*$" 

Please note that the grouping of the expression changes, therefore the Area column now receives data from the Submatch(4), and Notes from Submatch(5).

RE: Saving Text from Email _Cliptext to Cursor

If you mean the area code, my idea would be to give blank as the alternative match of the area code:

CODE

m.RegExp.Pattern = "^\s*(\d{1,}\/\d{1,}\/\d{2,})\s+(\d+)\s+([A-Z0-9\/]+)\s+([A-Z0-9\/]+|\s+)\s+(.*)$" 

Match count, grouping/numbering stays the same.

Bye, Olaf.

Olaf Doschke Software Engineering
https://www.doschke.name

RE: Saving Text from Email _Cliptext to Cursor

(OP)
atlopes,

Your revised code did not Import any Data.

I wasn't sure how to modify the Submatches, here is my latest code prior to any modifications. Submatch 3 replaces an Area Code of "N/A" with " " (4 Spaces)

CODE -->

IF m.Match.Submatches.Count = 5

			INSERT INTO cNewBooks ;
				VALUES (CTOD(m.Match.Submatches.Item(0)), ;
						 VAL(m.Match.Submatches.Item(1)), ;
					     	 m.Match.Submatches.Item(2), ;
					     	 IIF(m.Match.Submatches.Item(3) == "N/A","    ", m.Match.Submatches.Item(3)), ;	
					   	 	 m.Match.Submatches.Item(4))					   	 	 				   	 	 					   	 	 				   	 	 				   	 	 					   	 	 
					   	 	 
		ENDIF 

Please be advised, I no longer use the Notes Field so although the Notes are still being received it is ignored.

Sample Data

Date Number Callsign Area Notes
01/01/2020 52931 M9TEK SP83
05/01/2020 52932 GW9TIP TL14 Any additional information here
06/06/2020 62014 MM9ATK

NOTE As previously, the number of spaces between fields can vary.

I would like to change the RegExp which currently accepts 3 or 4chr Area Codes to accept a Blank Area Code. See last entry in above Sample Data.

Regards,

David.

Recreational user of VFP.

RE: Saving Text from Email _Cliptext to Cursor

(OP)
Olaf,

Your code only imported entries that had 3 spaces between Callsign & Area. All other entries with 1 or 2 spaces were ignored.

Regards,

David.

Recreational user of VFP.

RE: Saving Text from Email _Cliptext to Cursor

David,

You're not taking into account that now there is a different number of groups in the regular expression, and they may differ depending on the incoming data.

My expression gets all your cases.

CODE --> VFP

LOCAL Source AS String

TEXT TO m.Source NOSHOW
01/01/2020 52931 M9TEK SP83
05/01/2020 52932 GW9TIP TL14 Any additional information here
06/06/2020 62014 MM9ATK
ENDTEXT

SET DATE TO DMY
SET SEPARATOR TO "/"

CREATE CURSOR DataStore (Date Date, Number Int, Callsign Varchar(20), Area Varchar(20), Notes Memo)

LOCAL ARRAY Lines(1)
LOCAL LineIndex AS Integer

LOCAL RegExp AS VBScript.RegExp

m.RegExp = CREATEOBJECT("VBScript.RegExp")
m.RegExp.Pattern = "^\s*(\d{1,}\/\d{1,}\/\d{2,})\s+(\d+)\s+([A-Z0-9\/]+)(\s+([A-Z0-9\/]+)\s*(.*))?\s*$"

FOR m.LineIndex = 1 TO ALINES(m.Lines, m.Source)

	m.Matches = m.RegExp.Execute(m.Lines(m.LineIndex))

	IF m.Matches.Count = 1
	
		m.Match = m.Matches.Item(0)

		IF m.Match.Submatches.Count >= 3

			INSERT INTO DataStore ;
				VALUES (CTOD(m.Match.Submatches.Item(0)), ;
					VAL(m.Match.Submatches.Item(1)), ;
					m.Match.Submatches.Item(2), ;
					IIF(m.Match.Submatches.Item(4) == "N/A", "", NVL(m.Match.Submatches.Item(4), "")), ;
					NVL(m.Match.Submatches.Item(5), ""))

		ENDIF

	ENDIF

ENDFOR

BROWSE 

RE: Saving Text from Email _Cliptext to Cursor

Correcting my previous post: the number of groups in the regular expression being six, the collection of sub-matches will always be six for a matched expression.

RE: Saving Text from Email _Cliptext to Cursor

Then you have to allow some s* where you had s+ before, as you need to not just allow an intermediate blank, but an early end of line.

I put together this and it extracts 5 times in the sample. You should try one without Area and still a note, even though you don't store them anymore, it should still match the pattern and not be skipped.

CODE

clear

* pattern for Date, Number, Callsign, Area, Notes
Text To lcMail NoShow
Dear David,

Lorem ipsum dolor sit amet, consectetur adipiscing elit. Duis rhoncus ante eu pellentesque rutrum. Nam sit amet scelerisque
arcu. Maecenas suscipit tincidunt dui id aliquam. Aenean lobortis ullamcorper euismod. Suspendisse at sapien rhoncus,
ultrices lacus imperdiet, porttitor lectus. Maecenas ut urna elementum, ullamcorper magna vitae, fermentum tortor. Quisque
dui mi, lacinia in mollis sed, imperdiet a leo. In in laoreet ante. Aliquam tortor ligula, efficitur ut erat quis, imperdiet
rutrum turpis. Nunc ultrices euismod nibh, in faucibus mauris. Nunc rutrum mauris diam, eget tempus lorem suscipit non. In
malesuada risus vel erat fringilla, sed aliquam risus sodales.

01/01/2020 52931 M9TEK SP83
05/01/2020 52932 GW9TIP TL14 Any additional information here
06/06/2020 62014 MM9ATK
EndText

m.RegExp = CREATEOBJECT("VBScript.RegExp")
m.RegExp.Pattern = "^\s*(\d{1,}\/\d{1,}\/\d{2,})\s+(\d+)\s+([A-Z0-9\/]+)\s*([A-Z0-9\/]+|\s*)\s*(.*)$" 

FOR m.LineIndex = 1 TO ALINES(paLines, m.lcMail)
   m.Matches = m.RegExp.Execute(m.paLines(m.LineIndex))
   IF m.Matches.Count = 1
      m.Match = m.Matches.Item(0)
      IF m.Match.Submatches.Count >0
         ? m.paLines(m.LineIndex)
         ? 'has '+Transform(m.Match.Submatches.Count)+' items:'
         For lnCount = 1 to m.Match.Submatches.Count
             ? m.Match.Submatches.Item(lnCount-1)
         EndFor 
         ? '---'
      ENDIF
   ENDIF
ENDFOR 

Bye, Olaf.

Olaf Doschke Software Engineering
https://www.doschke.name

RE: Saving Text from Email _Cliptext to Cursor

(OP)

Quote (atlopes)

You're not taking into account that now there is a different number of groups in the regular expression, and they may differ depending on the incoming data.

Although I've done a little studying on RegExp I need to study your code a little further. I managed to modify your original code to accept dates in dd/mm/yyyy or d/m/yy formats. Small steps!

Quote (atlopes)

My expression gets all your cases.

It does indeed, many thanks for your help. I've inserted the relevant parts of your code into my application and all is working well. The latest data I received today identified another issue in that the callsign field could contain a persons name which contains a period (.) instead of a callsign. eg BANKS.A, Banks.D or Trump.D

Anyway, once again, thank you very much for you help, much appreciated.

Regards,

David.

Recreational user of VFP.

RE: Saving Text from Email _Cliptext to Cursor

(OP)

Quote (Olaf)

Then you have to allow some s* where you had s+ before, as you need to not just allow an intermediate blank, but an early end of line.

Thank you for your code. I'm compiling a list of the RegExp's and comparing the differences in my learning process, so your input is most appreciated. I break the code down into each field and work out what each piece of code does. I knew of the " | " char to match a pattern or an empty string, but couldn't quite get it to work.

Regards,

David.

Recreational user of VFP.

RE: Saving Text from Email _Cliptext to Cursor

Quote (David)

The latest data I received today identified another issue in that the callsign field could contain a persons name which contains a period (.) instead of a callsign. eg BANKS.A, Banks.D or Trump.D

This,

CODE --> VFP

m.RegExp.Pattern = "^\s*(\d{1,}\/\d{1,}\/\d{2,})\s+(\d+)\s+([A-Z0-9\/]+|[A-Za-z]+\.[A-Z])(\s+([A-Z0-9\/]+)\s*(.*))?\s*$" 

will accept a name in the form you described instead of a regular call sign.

RE: Saving Text from Email _Cliptext to Cursor

(OP)

Quote (atlopes)

This Code will accept a name in the form you described instead of a regular call sign.

Thank you so much, you have been a great help to me, not only in this thread but also previous threads where I have had issues with importing and modifying data. Much appreciated.

I have documented all of the RegExp variations I've used and will break them down so that I can learn from it.

Regards,

David.

Recreational user of VFP.

RE: Saving Text from Email _Cliptext to Cursor

Another idea to keep the patterns simpler (also simpler to maintain and understand) is to test them separately and look out for rows where all patterns match as full matches.

It doesn't pay to strip it down to testing all the single item patterns, especially as CALLSIGN and AREA have the same. You'd introduce the task of puzzling together which is what again, but still this can be a nice recipe to "unstir" the soup in two passes instead of figuring out the one regexp for all items.

Atlopes has made that a fine single pattern and I don't mean to degrade this because of its necessary aftermath, but here's the idea in general to easier put together what you need:

CODE

#Define cSTART '^'
#Define cEND '$'

#Define cDATE '\d{1,}\/\d{1,}\/\d{2,}'
#Define cNUMBER '\d+'
#Define cSPACES '\s+'
#Define cOPTIONALSPACES '\s*'
#Define cUPPERALPHANUMERICSLASHES '[A-Z0-9\/]+'
#Define cNAME '[A-Za-z]+\.[A-Z]'
#Define cCALLSIGN cUPPERALPHANUMERICSLASHES+'|'+cNAME
#Define cAREA '[A-Z0-9\/]{4}'

#Define cANYTHING '.*'

TEXT To lcMail NoShow
Dear David,

Lorem ipsum dolor sit amet, consectetur adipiscing elit. Duis rhoncus ante eu pellentesque rutrum. Nam sit amet scelerisque
arcu. Maecenas suscipit tincidunt dui id aliquam. Aenean lobortis ullamcorper euismod. Suspendisse at sapien rhoncus,
ultrices lacus imperdiet, porttitor lectus. Maecenas ut urna elementum, ullamcorper magna vitae, fermentum tortor. Quisque
dui mi, lacinia in mollis sed, imperdiet a leo. In in laoreet ante. Aliquam tortor ligula, efficitur ut erat quis, imperdiet
rutrum turpis. Nunc ultrices euismod nibh, in faucibus mauris. Nunc rutrum mauris diam, eget tempus lorem suscipit non. In
malesuada risus vel erat fringilla, sed aliquam risus sodales.

01/01/2020 52931 M9TEK SP83
05/01/2020 52932 GW9TIP TL14 Any additional information here
06/06/2020 62014 MM9ATK
13/13/0823 12345 Trump.D SP83 Bulls**t
13/13/0823 12345 Trump.D      F*** ***s
05/06/2020 345789 .................
ENDTEXT

m.RegExp = Createobject("VBScript.RegExp")

* extract Date, Number, Callsign, Area
For m.LineIndex = 1 To Alines(paLines, m.lcMail)
   * PASS 1, detecting DATE and NUMBER
   m.RegExp.Pattern = cSTART + cOPTIONALSPACES + '('+cDATE + ')' + cSPACES + '(' + cNUMBER + ')' + cANYTHING + cEND
   m.Matches = m.RegExp.Execute(m.paLines(m.LineIndex))
   If m.Matches.Count = 1
      m.Match = m.Matches.Item(0)
      If m.Match.Submatches.Count = 2
         lcDate   = m.Match.Submatches.Item(0)
         * You could veryfy valid date here easier than in regexp by trying to cast to date.
         lcNumber = m.Match.Submatches.Item(1)
      Endif
      * remove Date and number for PASS 2 matching
      m.paLines(m.LineIndex) = Strtran(m.paLines(m.LineIndex),lcDate,'',1,1,2)
      m.paLines(m.LineIndex) = Strtran(m.paLines(m.LineIndex),lcNumber,'',1,1,2)

      * PASS 2, CALLSIGN and AREA
      m.RegExp.Pattern = cSTART + cOPTIONALSPACES + '(' + cCALLSIGN + ')' + cSPACES + '(' + cAREA + '|' + cSPACES+ ')' + cANYTHING + cEND
      m.Matches = m.RegExp.Execute(m.paLines(m.LineIndex))
      If m.Matches.Count = 1
         m.Match = m.Matches.Item(0)
         If m.Match.Submatches.Count = 2
            lcCallsign = m.Match.Submatches.Item(0)
            lcArea     = m.Match.Submatches.Item(1)
            ? 'Date:',lcDate, 'Number:',lcNumber,'Callsign:', lcCallsign, 'Area:',lcArea
            * Here you have a full record and can insert it.

            Insert Into DataStore ; && (date, number,. callsigm, area, note)
            Values (Ctod(lcDate), ;
               VAL(lcNumber), ;
               lcCasllsign, ;
               IIF(lcArea == 'N/A', '', Nvl(lcArea, '')), ;
               '')
         Endif
      Else
         * You might do something with Date and Number here anyway.
         * For example, store it with the rest of the information in m.paLines(m.LineIndex) for a Memo for later manual or extended regex parsing
         ? 'Only date and number:', lcDate, lcNumber
      Endif
   Endif
Endfor 

It obviously is more code, but perhaps easier to understand. I intentionally didn't put the brackets defining submatches in the constants, because the single expressions can be combined, like it's the case for CALLSIGN and AREA, which each have 2 alternative patterns commbined.

You may want to mend the SQL-Insert, if you don't store the note anymore. What I did there with the constant '' as the note was necessary because the way it is the insert needs values for all fields, including '' for the non-parsed note.

I could say something about dismissed ELSE branches, but the poor guy who has to read everything I write to get angry about all this Bulls**t will already be angry enough.

Bye, Olaf.

Olaf Doschke Software Engineering
https://www.doschke.name

RE: Saving Text from Email _Cliptext to Cursor

(OP)
Hello Olaf,

Thank you for your explanation on RegExp and for your comments in each stage of the code. That will be useful to help me better understand how it all works.

It’s interesting that you make use of #Define, it was my intention to further expand my VFP knowledge by taking a look at #Define. Your code has shown me how useful it is.

To get the code to work, I created a Cursor DataStore and removed the extra “s” from lcCasllsign and all ran as expected. Did I pass the Test?

As you say a little more code but a little easier to understand, which is the main thing. I’ll study your code further at my leisure.

I did notice the unnecessary reference to Bulls**t on the other thread; I’d sooner read ten of your threads than that rubbish.

Regards,

David.

Recreational user of VFP.

RE: Saving Text from Email _Cliptext to Cursor

Thanks, David.

And take your time with going through this, it's still quite convoluted.

In short, the idea of the #DEFINEs is to have the simple single patterns you put together to larger patterns including the definition of where sub-matches are to be found in the normal round brackets. Therefore you find the highest level composition within the code, not within the definitions. For example the extension of the previous area pattern to alternatively also be blank as '(' + cAREA + '|' + cSPACES+ ')'.

And the defines are just simple constants here. Placeholders, which give the regexp a name. If you want to change a definition you can now do it at that place of definitions, and you may add your repository of expressions here.

Regarding constants, the usual name convention is to use all UPPERCASE names for them, because when you finally put constants into include files and their definition becomes "invisible" that way, unless you open the .h include file in parallel, it would not become apparent they are constants when written in normal lower or mixed case. I prefixed them with c, because DATE, NUMBER, and SPACE are reserved words and color the code wrong unintentionally.

Anyway, that part could also be done with variables. There's just no reason to set variables to values that never change, well, at least not within one run, but over time you need to cover more cases like the 'N/A' surely added the need to allow slashes.

Bye, Olaf.

Olaf Doschke Software Engineering
https://www.doschke.name

RE: Saving Text from Email _Cliptext to Cursor

2

Late to the conversation.. just to add a reminder.. in VFPX ( https://github.com/VFPX/VFPRegExTool )
you can find a Regex tool I shared to check as you type your regular expressions.

You can test against your sample text and get the matches directly highlited
in editor and the match list.








Marco Plaza
@nfoxProject
https://www.github.com/nftools

RE: Saving Text from Email _Cliptext to Cursor

Great tool. I'd like to see a building block concept in there, too. Also naming the groups and submatches would be helpful. Just don't have the time right now.

And a good reminder why I don't like \w for word characters, as I rarely have digits in words, and underscores, too. Otherwise a great shortener for [A-Za-z0-9_]. You can of course live with a little watering of matches when they never happen or you can spot them in aftermath. Allowing slash to also see N/A as callsign or area in David's case also is such watering, if you only want to allow slashes in the context of N/A or n/a and no other combination of letters and digits with slashes. And a date expression rejecting a 31st of a month not having 31 days? Possible, because its proven regexp are turning complete, which means you could program with them theoretically. (see for example https://stackoverflow.com/questions/8647893/regula...).

Refining patterns and make them more strict makes them lengthier, though, and less readable, but naming could turn this to the better, even if names are longer than the expressions, they are better human readable/understandable/maintainable.

Anyway, all this doesn't defy my first two words. Great tool.

Bye, Olaf.

Olaf Doschke Software Engineering
https://www.doschke.name

RE: Saving Text from Email _Cliptext to Cursor

(OP)

Quote (mplaza)

Late to the conversation.. just to add a reminder.. in VFPX ( https://github.com/VFPX/VFPRegExTool )
you can find a Regex tool I shared to check as you type your regular expressions.

Marco,

Thank you very much for sharing the link to your VFPRegExpTool Utility.

I have just downloaded the utility and can see that it will be a very useful tool in learning how to compile RegExp.

I've only just downloaded the utility so I need to check if there is a description of the various colours used to display the results.

Thanks again for sharing a great utility.

Regards,

David.

Recreational user of VFP.

RE: Saving Text from Email _Cliptext to Cursor

Thanks for your feedback..

I'm sure you'll find it as useful as I do!

Marco Plaza
@nfoxProject
https://www.github.com/nftools

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close