Exporting emails - what are the options

DEVONthink Tips
1

I have to export 650 emails either in a form that is able to be read by a PC or, failing that, as an index to PDF files of each of the emails.

I have visited the index issue before with Christian providing a script that worked fine then but no longer appears to. Maybe it is the type of emails as the script gives me interminable lines including hexadecimal codes, subject matter split by “%” and so on. I have tried to clean out what I regard as non relevant characters but the emails end up splitting their dates, authors etc.

Yet, every email, regardless of source, displays properly in DevonThink Pro Office.

This cannot be an isolated issue - there must be a way to preferably (and relatively easily) export the emails without having to run them into other applications and then exporting them into a format that a PC will recognise.

Having created PDFs of each of the emails, I would settle for a scrpt that allows me to index the emails.

2

Have you tried selecting emails and converting them to RTF?

Selecting them and using Data > Create Table of Contents to get the listing you’re looking for?

Opening a Support ticket and attaching one or two of the misbehaving .eml files so Support can evaluate the problem better?

Perhaps if you posted the script here again it could be debugged?

3

Korn

Thanks for the reply. Apologies for the delay in replying to you.

The creation of a Table of Contents provides a workaround and it may suffice.

The script that Christian previously provided was:

– Customizable Metadata Export
– Created by Christian Grunenberg on Fri Jun 06 2014.
– Copyright © 2010-2014. All rights reserved.

property pSeparator : “;”

property pKind : true
property pDateCreated : true
property pURL : true
property pTags : true
property pComments : true
property pAnnotations : true
property pAuthor : true
property pRecipient : true

tell application id “DNtp”
try
set theSelection to the selection
if theSelection is {} then error “Please select some documents.”
set theFile to choose file name default name “Export.csv”

	show progress indicator "Exporting..."
	
	set theCSV to my prepareCSV("Name", pSeparator)
	if (pKind) then set theCSV to theCSV & my prepareCSV("Kind", pSeparator)
	if (pDateCreated) then set theCSV to theCSV & my prepareCSV("Date Created", pSeparator)
	if (pAuthor) then set theCSV to theCSV & my prepareCSV("Author", pSeparator)
	if (pRecipient) then set theCSV to theCSV & my prepareCSV("Recipient", pSeparator)
	if (pURL) then set theCSV to theCSV & my prepareCSV("URL", pSeparator)
	if (pTags) then set theCSV to theCSV & my prepareCSV("Tags", pSeparator)
	if (pComments) then set theCSV to theCSV & my prepareCSV("Comments", pSeparator)
	if (pAnnotations) then set theCSV to theCSV & my prepareCSV("Annotations", return)
	
	set theCSV to theCSV & my createCSV(theSelection)
	set thePath to POSIX path of theFile
	if thePath does not end with ".csv" then set thePath to thePath & ".csv"
	
	do shell script "echo " & quoted form of theCSV & ">" & quoted form of thePath
	
	hide progress indicator
on error error_message number error_number
	hide progress indicator
	if the error_number is not -128 then display alert "DEVONthink Pro" message error_message as warning
end try

end tell

on createCSV(theseRecords)
local this_record, this_csv, this_name, this_URL, this_metadata, this_author, this_recipient, this_annotation, theseTags
tell application id “DNtp”
set this_csv to “”
repeat with this_record in theseRecords
set this_name to name of this_record as string
set this_csv to this_csv & my prepareCSV(this_name, pSeparator)
if (pKind) then set this_csv to this_csv & my prepareCSV(kind of this_record as string, pSeparator)

		if (pDateCreated) then set this_csv to this_csv & my prepareCSV(creation date of this_record as string, pSeparator)
		if (pAuthor or pRecipient) then
			set this_metadata to meta data of this_record
			if (pAuthor) then
				try
					set this_author to |kMDItemAuthors| of this_metadata
				on error
					try
						set this_author to |kMDItemAuthorEmailAddresses| of this_metadata
					on error
						set this_author to ""
					end try
				end try
				if exists this_author then
					set this_csv to this_csv & my prepareCSV(this_author, pSeparator)
				else
					set this_csv to this_csv & my prepareCSV("", pSeparator)
				end if
			end if
			
			if (pRecipient) then
				try
					set this_recipient to |kMDItemRecipients| of this_metadata
				on error
					try
						set this_recipient to |kMDItemRecipientEmailAddresses| of this_metadata
					on error
						set this_recipient to ""
					end try
				end try
				if exists this_recipient then
					set this_csv to this_csv & my prepareCSV(this_recipient, pSeparator)
				else
					set this_csv to this_csv & my prepareCSV("", pSeparator)
				end if
			end if
		end if
		
		set this_URL to URL of this_record as string
		if this_URL begins with "x-devonthink-item://" then
			if (pURL) then set this_csv to this_csv & my prepareCSV("", pSeparator)
			set this_URL to (characters 21 thru -1 of this_URL) as string
		else
			if (pURL) then set this_csv to this_csv & my prepareCSV(this_URL, pSeparator)
			set this_URL to ""
		end if
		
		if (pTags) then
			set {od, text item delimiters of AppleScript} to {text item delimiters of AppleScript, return}
			set theseTags to (tags of this_record) as string
			set text item delimiters of AppleScript to od
			set this_csv to this_csv & my prepareCSV(theseTags, pSeparator)
		end if
		
		if (pComments) then set this_csv to this_csv & my prepareCSV(comment of this_record as string, pSeparator)
		
		if (pAnnotations) then
			if this_URL is not "" then
				set this_annotation to plain text of (get record with uuid this_URL)
			else
				set this_annotation to ""
			end if
			if exists this_annotation then
				set this_csv to this_csv & my prepareCSV(this_annotation, return)
			else
				set this_csv to this_csv & my prepareCSV("", return)
			end if
		end if
		
		if type of this_record is group or type of this_record is feed then
			step progress indicator this_name
			set this_csv to this_csv & my createCSV(children of this_record)
		end if
	end repeat
end tell
return this_csv

end createCSV

on prepareCSV(theString, theSeparator)
try
if theString contains “”" then
local od
set {od, text item delimiters of AppleScript} to {text item delimiters of AppleScript, “”"}
set theString to text items of theString
set text item delimiters of AppleScript to “”""
set theString to “” & theString
set text item delimiters of AppleScript to od
end if
on error
set theString to “”
end try
return “”" & theString & “”" & theSeparator
end prepareCSV

4

Over here, the script you posted above works exactly as designed. (The script you posted is a variation of the script “Export Metadata as CSV”, which is available from the DEVONthink Support Assistant.)

The script you posted produces a comma-separated table (.csv) that could be read by Excel or Numbers or other program. The script looks at any selection of files of any kind and produces a table that, optionally, contains columns listing the Kind, Date Created, URL, Tags, Spotlight Comments, Annotations, Author, and Recipient of the selected files. The values for all of those columns should be plain text. The CSV file that the script produces should be plain text. And plain text is the result I get over here. As mentioned, if it is not working then perhaps the files it is attempting to read are somehow corrupted and opening a ticket with Support and attaching a sample of the files you mentioned would be the best course of action since it is a bad idea to post personal data here.

The script you posted does not export emails. It exports a table of information about email files and/or any other file kind against which you run the script.

5

I am confused as to what your actual need on the PC is. You mentioned you created PDFs of the emails. These would be cross-platform compatible. What am I missing here?

6

Let me reply to both posts.

The script used to work perfectly for me when I wanted to create a chronological list of emails. I set up an Excel formula for separately extracting the date and time into separate cells. On this occasion, the export to Excel was producing a semi colon in single quotes (’;’) as the delimiter. I tried to used both ‘;’ and ; as delimiters on importing into another Excel worksheet, without success. I will revisit the export again but I tried it several times without success - even to the extent of just importing small blocks of emails as overall there are 650.

Yes, I have preserved the emails as PDFs and also as RTFDs but that is only part of it. Both formats date the file on the day it is created so it is useless to provide a chronological folder of the emails. I did create a Table of Contents which allowed me to list the emails by subject in date order and the hyperlinking of the Table of Contents works well to open particular emails on my Mac. However, I expect that it will not work on a PC so the Table of Contents will not be inter-active but it is better than nothing.

The background to this is that I am a lawyer and I am trying to provide my file in a case to another lawyer who is PC based. The chronological sequence of events is particularly important.

7

The penny dropped.

Now I see why you wrote

and I image what you are seeing looks like this


mailto:xyzzy@dreamhost.com?subject=Re:%20North%20House%20Jonquil%20Plantings&in-reply-to=%3C20140808023210.EEDBF6F8102@echoecho.dreamhost.com%3E

This is an example of a so-called “URL-encoded” address. In my case, with some exports, the email was imported with the URL-encoded version of the address embedded in the URL field of that email in DEVONthink. The script is merely exporting what it sees. URL-encoding does things like changing spaces to %20 – which is the hex code for a space. %3C and %3E are hex codes for the left and right caret characters, respectively.

As I said, the script is merely exporting the encoded text in the metadata in your database. The script is not broken. But, if you don’t like the encoding, you would have to modify the script to unencode the data. Here is an example of what you could do.

(Sorry, I’m not volunteering. Nowadays I only write scripts for folks that I’m also interested in using myself. Too many nasty PMs come my way, otherwise.)

8

Thanks Korm

I will see what I can do although AppleScript is not a comfortable discpline for me (I do intend to try to master it, someday).

In this part of the world, volunteers are accepted as just that - we do not lambast them for their efforts!