×
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

Jobs

Saving Text from Email _Cliptext to Cursor
3

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.

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