script to convert this digest to FMP 5 database
script to convert this digest to FMP 5 database
- Subject: script to convert this digest to FMP 5 database
- From: Bill <email@hidden>
- Date: Wed, 22 Nov 2000 16:02:26 +0800
Hello,
It is a long message, be patience. Here's the process :
1. Using Netscape to receive email.
2. Save each digest as file.
3. Use FileMaker Pro 5 to open this file, it's converted to a database
with fields f1, f2, ... Each record represent one line in the digest,
most of the time, in cell "f1". But, sometime the line is break into
many cell ( from 2 cells to 11 cells ), save the converted database.
4. run the following script, in order to merge all data into cell 1
set theList to {}
tell application "FileMaker Pro"
tell database 1
tell current layout
set xx to (count of field)
set yy to (count of record)
repeat with i from 1 to yy
set end of theList to cells 1 thru xx of record i
set cell 1 of record i to theList as text
set theList to {}
end repeat
end tell
end tell
end tell
In fact, it's significantly faster when you run this script in
ScriptMaker, so it's worth the time to import it, then run by
ScriptMaker. Close the file, save it with name "getReady", put it on
desktop.
5. The final database is named "scriptDigest", an interim clone is named
"tempDigest", the final script's code below :
set messageLine to {}
set dateLine to {}
set blankLine to {}
set datelist to {}
set authorList to {}
set subjectList to {}
set bodyList to {}
tell application "FileMaker Pro"
open file "bee:desktop folder:tempDigest"
open file "bee:desktop folder:getReady"
tell database "getReady"
set xx to (count of record)
repeat with i from 1 to xx
if cell 1 of record i starts with "Message:" then
set end of messageLine to i
-- the reason of (i + 8 ) is :
-- each message header may contain date, from, subject, to
-- cc, Organization & Reply-To, each info occupy one line,
-- so, 7 plus 1, in order to get the blank line which
-- indicate the starting of message body
repeat with j from (i + 1) to (i + 8)
-- get record contains date & relevant record number
if cell 1 of record j starts with "Date:" then
set end of dateLine to j
set end of datelist to cell 1 of record j
end if
-- get record contains author
if cell 1 of record j starts with "From:" then
set end of authorList to cell 1 of record j
end if
-- get record contains subject
if cell 1 of record j starts with "Subject:" then
set end of subjectList to cell 1 of record j
end if
-- get record number of blank line
if cell 1 of record j = "" then
set end of blankLine to j
end if
-- to delete false record number in blankLine
-- those blank message or one line message
set yy to count of blankLine
repeat with k from 2 to yy
-- the character prior to 3 is "is less than or equal to"
if ((item k) of blankLine) - ((item (k - 1)) of blankLine) ? 3
then
set item k of blankLine to ""
end if
end repeat
set blankLine to integers of blankLine
end repeat
end if
end repeat
--get message body as list
set zz to (count of blankLine)
repeat with i from 1 to (zz - 1)
set end of bodyList to ((cell 1 of records ?
(item i of blankLine as integer) thru ?
((item (i + 1) of messageLine as integer) - 1)) as text)
end repeat
set end of bodyList to (cell 1 of records ?
(last item of blankLine as integer) thru -1) as text
close -- close database "Converted"
end tell
end tell
-- delete unwanted stuff in message body
set bodyTextList to {}
repeat with i in bodyList
set bodyText to i
-- item 1 in findItList is ASCII character 9
-- item 2 in findItList are ASCII character 9 & ASCII character 9
-- item 3 in findItList is message end mark in digest
-- the 4 to 8 in findItList are digest end mark in digest
set findItList to {" ", " ", "--__--__--",
"_______________________________________________", "applescript-users
mailing list", "email@hidden",
"
http://www.lists.apple.com/mailman/listinfo/applescript-users";, "End of
applescript-users Digest"}
set replaceItWithList to {return, return, "", "", "", "", "", ""}
set aa to count of findItList
repeat with i from 1 to aa
set findIt to item i of findItList as text
set replaceItWith to item i of replaceItWithList as text
searchReplace(bodyText, findIt, replaceItWith)
set bodyText to result
end repeat
set end of bodyTextList to bodyText
end repeat
-- handler to find & replace
on searchReplace(theText, SearchString, ReplaceString)
set oldDelims to AppleScript's text item delimiters
set AppleScript's text item delimiters to SearchString
set newText to text items of theText
set AppleScript's text item delimiters to ReplaceString
set newText to newText as text
set AppleScript's text item delimiters to oldDelims
return newText
end searchReplace
-- get author name
set authorNameList to {}
repeat with i in authorList
set end of authorNameList to text 7 thru -1 of i
end repeat
-- get subject
set subjectTopicList to {}
repeat with i in subjectList
set end of subjectTopicList to text 10 thru -1 of i
end repeat
-- create inDate in format dd/mm/yyyy
set inDateList to {}
repeat with i in datelist
-- reason to use offset of "200" is
-- this script is evolving from an early one which does it this way.
set d to (text ((offset of "200" in i) - 7) thru ((offset of "200" in
i) - 6) of i) as integer
set d to text -2 thru -1 of ("0" & d)
set m to text ((offset of "200" in i) - 4) thru ((offset of "200" in i)
- 2) of i
-- 3 space before Jan
(offset of m in " JanFebMarAprMayJunJulAugSepOctNovDec") div 3
set m to text -2 thru -1 of ("0" & result)
set y to text (offset of "200" in i) thru ((offset of "200" in i) + 3)
of i
set inDate to d & "/" & m & "/" & y
set end of inDateList to inDate
end repeat
set a to ""
set b to ""
set c to ""
set d to ""
set e to ""
-- in file "tempDigest", view by creation date in define fields window
-- keep in sequence
--"messageID", "subject","author", "inDate", "body"
set xx to (count of subjectList)
repeat with i from 1 to xx
set a to ""
set b to item i of subjectTopicList
set c to item i of authorNameList
set d to item i of inDateList
set e to item i of bodyTextList
tell application "FileMaker Pro"
tell database "tempDigest"
create new record at end with data {a, b, c, d, e}
end tell
end tell
end repeat
6. check the integrity of records, open the final database
"scriptDigest", import records from this interim file "tempDigest".
Thanks for everyone who contribute all the tricks & skills in this
mailing list. Well, is it possible to do step 1 - 3 by applescript? Any
help, please.
Thanks in advance
Best Wishes
Bill