Parsing emails to a CSV file

Hello

So I am not that technical, but the tech guy left, so now this little project is in my lap… lucky me! :slight_smile:

There was a simple form survey set up on our website with the form results emailed to a specific account. There are about 24 variables in the survey email. Is there a way that I can write an AppleScript that can parse the variables and their contents into a .csv file so we can see all the results in Excel? For example, this is what the results email looks like:

I wish they hadn’t set it up this way, but unfortunately they did and now we have 600 emails to get though. I have them in Entourage, but I also imported them into Mail and exported an .mbox file in case any of these were easier to work with than others. I know a little about scripting as I have done it in the past, but I don’t know AppleScript and don’t even know where to begin or if this can even be done?

Any help appreciated!

Tiffany

Hi Tiffany,

try this, it creates tab delimited files on desktop with a file name including current date and the sender, which can be opened in Excel.
The script assumes that the emails contain only lines like in your sample text and works as attachment to a Mail rule

using terms from application "Mail"
	on perform mail action with messages theMessages for rule theRule
		repeat with eachMessage in theMessages
			tell application "Mail"
				tell eachMessage
					set theSender to (extract name from sender)
					set theDate to date received
					set theContents to content as string
				end tell
			end tell
			set theDate to short date string of theDate
			set TempFile to ((path to desktop) as string) & theSender & "_" & theDate
			make_TextFile(theContents, TempFile)
		end repeat
	end perform mail action with messages
end using terms from

on make_TextFile(theText, targetFile)
	set {nList, vList} to {{}, {}}
	set {TID, text item delimiters} to {text item delimiters, ": "}
	repeat with i in (get paragraphs of theText)
		if contents of i is not "" then
			set {end of nList, end of vList} to text items of i
		end if
	end repeat
	set text item delimiters to tab
	set nList to nList as string
	set vList to vList as string
	set text item delimiters to TID
	write_to_disk from (nList & return & vList) into targetFile
end make_TextFile

on write_to_disk from theData into theTarget
	try
		set ff to open for access file theTarget with write permission
		write theData to ff
		close access ff
		return true
	on error
		try
			close access file theTarget
		end try
		return false
	end try
end write_to_disk