Parse Message content and save as tab delimited file

Scripting Forums AppleScript | Mac OS X
#1

Hi,

I’m not a scripter but have been able to kludge things together in the past but this is simply beyond me at this point.

I’d like to create a rule in Apple Mail where the body text of individual incoming email messages is parsed and then exported to a tab delimited text file. I’m using the Mail App but could use Outlook or any suitable client.

The body text of the selected incoming messages look like this:

[i]BILL_NUMBER TRACE_NUMBER
123456 112233
789012 443322
345678 667788

Number of rows found: 3[/i]

The number of rows in the message could be any number from 1 to nn.

I need to save a text file that looks like this:

123456 112233
789012 443322
345678 667788

What this means is stripping out the column titles, changing the space between the columns to tabs, and stripping out the “Number of rows” line.

The filename for the saved file I’d like to have a short date and time (hhmm) for the file name… ie. 2016.03.17.0810.txt

From other script examples on this list I have been able to put together something that opens a selected message in mail, strips out the header and footer and then saves to the desktop… I can’t seem to get it to save to a directory without a permissions error (lots of posts about that). I also haven’t got the time into the file name, only the date. Finally, I don’t know how to loop the code so have duplicated code to find where the number of rows text is = 1 through n… I’d rather have that loop. In any event this really needs to be able to end up attached to an email rule. TextEdit may not be the route either but it is how I started on this.

I’ll attach the minimal code I have so far an hope for some great feedback!

Thanks!

set todaysDate to (current date)

set the fname to (((year of (current date)) * 10000) + ((month of (current date)) * 100) + (day of (current date))) as string

tell application "Mail"
	set selectedMessages to selection
	if (count of selectedMessages) is equal to 0 then
		display alert "No Messages Selected" message "Select the messages you want to collect before running this script."
	end if
	set theText to ""
	repeat with theMessage in selectedMessages
		set theText to theText & (content of theMessage) as string
	end repeat
end tell

on replaceText(find, replace, subject) -- from MacScripter
	set prevTIDs to text item delimiters of AppleScript
	set text item delimiters of AppleScript to find
	set subject to text items of subject
	
	set text item delimiters of AppleScript to replace
	set subject to "" & subject
	set text item delimiters of AppleScript to prevTIDs
	
	return subject
end replaceText

set theText to replaceText("BILL_NUMBER  TRACE_NUMBER  ", "", theText)
set theText to replaceText("
Number of rows found: 1
", "", theText)
set theText to replaceText("
Number of rows found: 2
", "", theText)
set theText to replaceText("
Number of rows found: 3
", "", theText)
set theText to replaceText("
Number of rows found: 4
", "", theText)
set theText to replaceText("
Number of rows found: 5
", "", theText)
set theText to replaceText("
Number of rows found: 6
", "", theText)
set theText to replaceText("
Number of rows found: 7
", "", theText)
set theText to replaceText("
Number of rows found: 8
", "", theText)
set theText to replaceText("
Number of rows found: 9
", "", theText)
set theText to replaceText("
Number of rows found: 10
", "", theText)
set theText to replaceText("
Number of rows found: 11
", "", theText)

tell application "TextEdit"
	activate
	set theDesktopPath to the path to the desktop folder as text
	set theDocument to make new document
	set text of theDocument to theText
	save document 1 in file (theDesktopPath & fname & ".txt")
end tell
#2

You may try to use :

set todaysDate to (current date)

tell (current date) to set fname to (((its year) * 10000 + (its month) * 100 + (its day)) as text) & "_" & text 2 thru -1 of ((1000000 + (its hours) * 10000 + (its minutes) * 100 + (its seconds)) as text)

tell application "Mail"
	set selectedMessages to selection
	if (count of selectedMessages) is equal to 0 then
		display alert "No Messages Selected" message "Select the messages you want to collect before running this script."
	end if
	set theText to ""
	repeat with theMessage in selectedMessages
		set theText to theText & (content of theMessage) as string
	end repeat
end tell



set theText to replaceText("BILL_NUMBER  TRACE_NUMBER  ", "", theText)
if theText contains return then
	set cr to return
else
	set cr to linefeed
end if
set fnd to 0
repeat 11 times
	set fnd to fnd + 1
	set theText to replaceText("
Number of rows found: " & fnd & cr, "", theText)
end repeat

set theDestinationPath to the path to the desktop folder as text # Or any path of your choice.

my writeto(theDestinationPath & fname & ".txt", theText, text, false)

on replaceText(find, replace, subject) -- from MacScripter
	set prevTIDs to text item delimiters of AppleScript
	set text item delimiters of AppleScript to find
	set subject to text items of subject
	
	set text item delimiters of AppleScript to replace
	set subject to "" & subject
	set text item delimiters of AppleScript to prevTIDs
	
	return subject
end replaceText

#=====
(*
Handler borrowed to Regulus6633 - http://macscripter.net/viewtopic.php?id=36861
*)
on writeto(targetFile, theData, dataType, apendData)
	-- targetFile is the path to the file you want to write
	-- theData is the data you want in the file.
	-- dataType is the data type of theData and it can be text, list, record etc.
	-- apendData is true to append theData to the end of the current contents of the file or false to overwrite it
	try
		set targetFile to targetFile as «class furl»
		set openFile to open for access targetFile with write permission
		if not apendData then set eof of openFile to 0
		write theData to openFile starting at eof as dataType
		close access openFile
		return true
	on error
		try
			close access targetFile
		end try
		return false
	end try
end writeto

#=====

Yvan KOENIG running El Capitan 10.11.3 in French (VALLAURIS, France) jeudi 17 mars 2016 17:41:03

#3

Hi,
the suggestions are working fine… I was able to increase the loop to somethng that will cover off most any instance with no issue AND have been able to get the file saving with an appropriate file name in a selected directory with no permissions issues… great!

The space between the two columns is still written as spaces instead of a . I may be able to work around that…

Now, how to convert it to something I can use with a mail rule to simply be run automatically upon receipt of any mail message… Thanks in advance for comments and thoughts?

B

#4

According to what you posted in your original message, it seems that the values aren’t separated by single spaces but by groups of 7 spaces.
As I’m not sure that it’s always the case I built a code able to deal with separators made of 10 thru 1 space characters.
The upper number is defined in a variable so yo may easily edit it if it’s not sufficient.

set todaysDate to (current date)

tell (current date) to set fname to (((its year) * 10000 + (its month) * 100 + (its day)) as text) & "_" & text 2 thru -1 of ((1000000 + (its hours) * 10000 + (its minutes) * 100 + (its seconds)) as text)

tell application "Mail"
	set selectedMessages to selection
	if (count of selectedMessages) is equal to 0 then
		display alert "No Messages Selected" message "Select the messages you want to collect before running this script."
	end if
	set theText to ""
	repeat with theMessage in selectedMessages
		set theText to theText & (content of theMessage) as string
	end repeat
end tell

set theText to replaceText("BILL_NUMBER  TRACE_NUMBER  ", "", theText)
set theParagraphs to paragraphs of theText
repeat with aParagraph in theParagraphs --paragraphs of theText
	if aParagraph starts with "Number of rows found: " then set contents of aParagraph to ""
end repeat
set theText to my recolle(theParagraphs, return)
repeat while theText contains return & return
	set theText to my replaceText(return & return, return, theText)
end repeat

repeat while theText contains space & return
	set theText to my replaceText(space & return, return, theText)
end repeat

# Builds a separator made of nb space characters
set nb to 10
set blanks to ""
repeat nb times
	set blanks to blanks & space
end repeat
repeat
	set theText to my replaceText(blanks, tab, theText)
	if blanks = " " then exit repeat
	set blanks to text 1 thru -2 of blanks
end repeat

set theDestinationPath to the path to the desktop folder as text # Or any path of your choice.

my writeto(theDestinationPath & fname & ".txt", theText, text, false)

on replaceText(find, replace, subject) -- from MacScripter
	set prevTIDs to text item delimiters of AppleScript
	set text item delimiters of AppleScript to find
	set subject to text items of subject
	
	set text item delimiters of AppleScript to replace
	set subject to "" & subject
	set text item delimiters of AppleScript to prevTIDs
	
	return subject
end replaceText

#=====
(*
Handler borrowed to Regulus6633 - http://macscripter.net/viewtopic.php?id=36861
*)
on writeto(targetFile, theData, dataType, apendData)
	-- targetFile is the path to the file you want to write
	-- theData is the data you want in the file.
	-- dataType is the data type of theData and it can be text, list, record etc.
	-- apendData is true to append theData to the end of the current contents of the file or false to overwrite it
	try
		set targetFile to targetFile as «class furl»
		set openFile to open for access targetFile with write permission
		if not apendData then set eof of openFile to 0
		write theData to openFile starting at eof as dataType
		close access openFile
		return true
	on error
		try
			close access targetFile
		end try
		return false
	end try
end writeto


#=====

on recolle(l, d)
	local oTIDs, t
	set {oTIDs, AppleScript's text item delimiters} to {AppleScript's text item delimiters, d}
	set t to l as text
	set AppleScript's text item delimiters to oTIDs
	return t
end recolle

#=====

It remove also the spaces which may exist at end of lines.

I don’t treat the rule question because I’m not at ease with this feature.

Yvan KOENIG running El Capitan 10.11.3 in French (VALLAURIS, France) jeudi 17 mars 2016 19:08:18