(1) The date field was complete, but did not allow for expected sorting in Excel. I dug in the Entourage dictionary, the applescript guide, and the Apple webpage to find a way to get the date and time without the day of the week. My hack is in the revised script below. Is there a better way of doing it?It's pretty good, Jim. Some of what both you and Barry did with dates won't work pre-OS 9, but that's not your worry, I guess. (Pre-OS 9 'mytime as string' won't work. But you can get exactly the same via 'date string of mytime & " " time string of mytime'. But your solution below won't work there since you can't get 'month as string' . there are ways to do it, however.)
Excel understands dates without day-of-the-week as dates, as you say. if you needed to separate the date from the time into separate columns, I have a script that will do that, but you can probably see how to do it too (time string to another tab field).
Barry added unnecessary quotes, as you say. You could do a test for
(2) The recipient display names ended up with a lot of extra double quotes. If there was no display name then there was an empty set of double quotes. If the display name already had quotes (single or double) around them then they ended up with two sets of quotes at the front and back. I also ran into a strange occurrence — the first displayed name did not have any leading quotes, even if they were in the original display name and it was followed by no quotes if there were none in the original display name and two quotes otherwise. The second and third recipient always showed two sets of quotes before and after the displayed name if the original display name contained quotes and one set of quotes before and after otherwise.
For item (2), I decided that I only needed the display name (unless there was none — and then I used the address) for both the sender and the recipients. See my additions below.
if recipName starts with "\"\"" an recipname ends with "\"\" then set recipname to text 2 thru -2 of recipname
to get rid of the ones that already exist, if you wish.
One thing I should alert you: Excel barfs completely when you try to import a text file that contains email addresses surrounded by < >. I don't know why. It's a real bug. it forces the entire text file into one single, unmodifiable cell cell and also strips out the email addresses! Did you actually try your script with any of these? If so, I wonder how you did it? What I did was to insert « and » (that's option-\ and shift-option-\) instead of < and >. That works fine. then, in Excel, do a Replace All for each of « and » by < and >. it's easy enough to so manually , but you could include all the Excel stuff at the end of the script if ypu wished. this is how:
At the end of the script, after the 'end tell', instead of the last three lines, do this (assuming you used « and » higher up)
set AppleScript's text item delimiters to {return}
set theText to summaryList as string
set AppleScript's text item delimiters to oldTIDs
set filePath to (path to desktop as string) & "Entourage Message Records.txt"
set f to open for access file filepath with write permission
set eof f to 0
write theText to f
close access f
tell application "Microsoft Excel"
Activate
OpenText filePath with TabDelimiter
set ur to UsedRange of ActiveSheet
AutoFit every Column of ur
Replace ur What "«" ReplaceWith "<"
Replace ur What "»" ReplaceWith ">"
display dialog "All done!" with icon 1
end tell
----------------------------
This will import the thing into Excel and fix the email addresses, and adjust the column widths too. it will save it as a text file, but you can simply save it as an Excel Workbook yourself, or script it. (One thing: it will replace any previous text file you've made by the script with the same name that's still on your desktop and still a text file. So either save it as an .xls file or move it somewhere else before running the script the next time.)
You did it perfectly.
I have no real background in scripting so I kind of use the bruit force method. I am always interested in learning more, so let me know if there is a more elegant way to accomplish what I did.
One other thing I did once I got into Excel to make the sorting more useful there was to remove the “RE: “ and “FW: “ leaders on replies and forwards. It would be nice to remove them from the subject line while creating the list if it is easy. Since the links are lost in the mbox file, at least being able to sort the list by subject is helpful.
That's easy enough:
if theSub starts with "Re: " or theSub starts with "FW: " then
try
set theSub to text 5 thru -1 of theSub
on error -- subject was blank
set theSub to «no subject»
end try
end if
AppleScript is not case-sensitive here, so it will work also with "RE: " and "Fw: ", but not with "Fwd: ". I leave it to you to figure out how to include that one if you wish (it's rather more complicated).
--
Paul Berkowitz
