Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Saving Text from Email _Cliptext to Cursor 8

Status
Not open for further replies.

David Higgs

Programmer
May 6, 2012
392
GB
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.
 
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 [Smile]ing

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.
 
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
 
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:
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

Clipboard01_zanr0i.png
 
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
 
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
 
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.
 
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.
 
Atlopes said:
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.
 
Mike Lewis said:
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.
 
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 [Smile]ing

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.
 
David said:
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?
 



Atlopes said:
Can you give an example, please?

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

Regards,

David.

Recreational user of VFP.
 
David,

Change the pattern to

Code:
m.RegExp.Pattern = "^\s*(\d{2}\/\d{2}\/\d{4})\s+(\d+)\s+([A-Z0-9\/]+)\s+(\w+)\s*(.*)$"
 
Atlopes said:
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.
 
David said:
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.
 
Atlopes said:
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.
 
This pattern?
Code:
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:
			INSERT INTO DataStore ;
				VALUES (CTOD(m.Match.Submatches.Item(0)), ;
					VAL(m.Match.Submatches.Item(1)), ;
					m.Match.Submatches.Item(2), ;
					[highlight #FCE94F]IIF(m.Match.Submatches.Item(3) == "N/A", "", m.Match.Submatches.Item(3)), ;[/highlight]
					m.Match.Submatches.Item(4))
 
Alopes said:
This pattern?

That did the trick!

Alopes said:
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.
 
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top