I am creating a script that proccesses an monthly email file I get into importable data for FileMaker. . .
The file starts out like this:
Gift Date Donor ID Amount Donor Name
--------- -------- ------ --------------- 10-JAN-2002 ##### $$ Doe, Jane 14-JAN-2002 ##### $$$ Foobar Foo Co. 15-JAN-2002 ##### $$$ Doe, Jane 17-JAN-2002 ##### $$$ Doe, John 23-JAN-2002 ##### $$$ Fooey, Foobar 29-JAN-2002 ##### $$$ Doe, John
The goal is to get this to a comma delimited text file, which I have done using Tex-Edit & Applescript. My last challenge is that date format. Any ideas how I can change it to mm/dd/yyyy?
So far this is what I’ve attempted and it is messy:
repeat with x from 1 to (number of lines)
search window 1 looking for "-JAN-"
cut
type text "/" ---------requires Sigma's scripting additions
type larrow holding down command
paste
type text "/"
end repeat
The search would happen for each month eg. -FEB-, -MAR-, etc. As you can see my problem is with using the left arrow key and that repeat function. I’ll shut up now and listen. . . Thanks
: I am creating a script that proccesses an monthly email file I
: get into importable data for FileMaker. . .
: The file starts out like this: Gift Date Donor ID Amount Donor
: Name
: --------- -------- ------ --------------- 10-JAN-2002 ##### $$
: Doe, Jane 14-JAN-2002 ##### $$$ Foobar Foo Co. 15-JAN-2002
: ##### $$$ Doe, Jane 17-JAN-2002 ##### $$$ Doe, John
: 23-JAN-2002 ##### $$$ Fooey, Foobar 29-JAN-2002 ##### $$$ Doe,
: John
: The goal is to get this to a comma delimited text file, which I
: have done using Tex-Edit & Applescript. My last challenge
: is that date format. Any ideas how I can change it to
: mm/dd/yyyy?
This solution uses Akua Sweets to format the date. If you don’t want to use a scripting addition there are vanilla approaches that accomplish the same thing.
set theText to "--------- -------- ------ --------------- 10-JAN-2002 ##### $$ Doe, Jane 14-JAN-2002 ##### $$$ Foobar Foo
Co. 15-JAN-2002 ##### $$$ Doe, Jane 17-JAN-2002 ##### $$$ Doe, John 23-JAN-2002 ##### $$$
Fooey, Foobar 29-JAN-2002 ##### $$$ Doe, John"
set {od, AppleScript's text item delimiters} to ¬
{AppleScript's text item delimiters, {" "}}
set textItems to text items of theText
repeat with anItem in textItems
try
set theDate to date anItem
set theDate to the clock from theDate using form "%m/%d/%c%y"
set contents of anItem to theDate
end try
end repeat
set theText to (textItems as text)
set AppleScript's text item delimiters to od
theText
--> "--------- -------- ------ --------------- 01/10/2002 ##### $$ Doe, Jane 01/14/2002 ##### $$$ Foobar Foo Co. 01/15/2002 ##### $$$ Doe, Jane 01/17/2002 ##### $$$ Doe, John 01/23/2002 ##### $$$ Fooey, Foobar 01/29/2002 ##### $$$ Doe, John"
Marc K. Myers
Marc@AppleScriptsToGo.com
AppleScriptsToGo
4020 W.220th St.
Fairview Park, OH 44126
(440) 331-1074
[2/9/02 2:14:35 AM]
Not a ‘vanilla’ approach but a different approach would be to note that “10-JAN-2002” is easily coerced to a date - not by ‘as date’ but by 'date “10-JAN-2002” → date class. Any date manipulators (from a simple ‘day of’, ‘month of’, etc upwards) then become usable.
Andreas
Another thought.
It occurred to me that if you have your computer’s global date format set to the format you want to end up with then just adding that one word ‘date’ should do the whole job for you with no additional code required!
Andreas
: Not a ‘vanilla’ approach but a different approach would be to
: note that “10-JAN-2002” is easily coerced to a date
: - not by ’ as date ’ but by ’ date “10-JAN-2002”
: → date class. Any date manipulators (from a simple ‘day
: of’, ‘month of’, etc upwards) then become usable.
That’s exactly what I did in this thread in
http://bbs.applescript.net/cgi-bin/webbbs_config.pl?read=9051
I broke the text into items delimited by spaces and then tried each one as a date. If the text could be made into a date I converted it to a “mm/dd/ccyy” format.
Marc K. Myers
Marc@AppleScriptsToGo.com
AppleScriptsToGo
4020 W.220th St.
Fairview Park, OH 44126
(440) 331-1074
[2/9/02 9:17:13 PM]
Just to clarify that the initial text looks like this:
10-JAN-2002 ##### $$ Doe, Jane
14-JAN-2002##### $$$ Foobar Foo Co.
15-JAN-2002 ##### $$$ Doe, Jane
17-JAN-2002 ##### $$$ Doe, John
23-JAN-2002 ##### $$$ Fooey, Foobar
29-JAN-2002 ##### $$$ Doe, John
Marc thanks for your help. I don’t think that will work for me though because that text changes every month so I would have to change the script every month, also eg:
set theText to “--------- -------- ------ ---------------
10-JAN-2002 ##### $$ Doe, Jane
14-JAN-2002 ##### $$$ Foobar Foo
15-JAN-2002 ##### $$$ Doe, Jane
17-JAN-2002 ##### $$$ Doe, John
23-JAN-2002 ##### $$$ Fooey, Foobar
29-JAN-2002 ##### $$$ Doe, John”
Are there any FAQ’s about using the text delimeter function? I keep seeing it on the BBS and don’t really understand why or how it works. . . Thanks If anyone has any suggestions about those vanilla approaches that would be appreciated. . .
Oops! I should have read your code instead of just nodding at it, shouldn’t I? Your code is actually very, very good - as usual. I suppose where I ended up boils down to simply leaving out your ‘Akua’ line:
try
set theDate to date anItem
-- set theDate to the clock from theDate using form "%m/%d/%c%y"
set contents of anItem to theDate
on error
end try
…and relying on the System’s designated date formatting to give the desired result. Actually I can’t really see any significant difference but if I had to choose I would opt for your method with Akua as being ‘cleaner’ - the “12.00 a.m.”'s and possibly also weekdays are totally spurious. You have also given a beautiful answer to any objection to the use of the osax.
A detail: if “Jan” is for any reason preferrable to “01” a tiny change to your ‘Akua’ line does it (if the System date format is appropriate):
using system form "%e"
I’ll tell my optician/optometrist about this - it was his fault!
Andreas
: Marc thanks for your help. I don’t think that will work for me
: though because that text changes every month so I would have
: to change the script every month, also eg: set theText to
: "--------- -------- ------ --------------- [snip]
I imagine Marc was using that line for demonstration purposes, not suggesting you actually do it that way.
You’ll need to make your own arrangements for passing the required string into Marc’s handler and for putting the result back in place.
: Are there any FAQ’s about using the text delimeter function? I
: keep seeing it on the BBS and don’t really understand why or
: how it works. . . Thanks If anyone has any suggestions about
: those vanilla approaches that would be appreciated. . .
See p210-211 of the AppleScript Language Guide for starters.
Meantime, here’s a very simple handler that’ll do what you want.
on transposeDayMonth(dateString, theDelimiter)
set oldTID to AppleScript's text item delimiters
set AppleScript's text item delimiters to theDelimiter
tell dateString to set newString to its second text item & theDelimiter & its first text item & theDelimiter & its third text item
set AppleScript's text item delimiters to oldTID
newString
end transposeDayMonth
transposeDayMonth("10-JAN-2002", "-")
--> "JAN-10-2002"
It doesn’t do any validation/error handling, but I’m assuming it’s not going to be fed weird values. It wouldn’t be hard to add this stuff if needed, though it’s faster and simpler without.
has
Hey, Marc, I’ve just realised that
[b]set[/b] [color=#CC0000][b]theDay[/b][/color] [b]to[/b] ([color=#008011][b]text[/b][/color] -2 [b]thru[/b] -1 [b]of[/b] ("0" & ([color=#008011][b]day[/b][/color] [b]of[/b] [color=#CC0000][b]theDate[/b][/color])))
is very slick! I’ll be searching out applets tomorrow to get rid of some [color=#CC0000]pad/color routines.
See - I can read when I try.
Andreas
I just realized that my idea isn’t as good as I thought it was. There are a lot of character strings besides dates that will resolve as dates if you do this:
try
set theDate to date theString
end try
Any number in the text would be transformed into a date. My bad!
Marc K. Myers
Marc@AppleScriptsToGo.com
AppleScriptsToGo
4020 W.220th St.
Fairview Park, OH 44126
(440) 331-1074
: Thanks If anyone has any suggestions about
: those vanilla approaches that would be appreciated. . .
In my original script I used Akua Sweets to format the date:
set theDate to the clock from theDate using form "%m/%d/%c%y"
To change a date value into a string in the form “mm/dd/yyyy” with vanilla AppleScript try this:
property monthList : {January, February, March, April, May, June, July, August, September, October, November, December}
set someDate to (current date) - (100 * days) – this is just to get a date to play with
set dateString to dateConvert(someDate)
on dateConvert(theDate)
set theDay to (text -2 thru -1 of (“0” & (day of theDate)))
set theYear to year of theDate as text
set theMonth to month of theDate
repeat with i from 1 to 12
if theMonth is item i of monthList then
set theMonth to (text -2 thru -1 of (“0” & i))
exit repeat
end if
end repeat
return theMonth & “/” & theDay & “/” & theYear
end dateConvert
Marc K. Myers
Marc@AppleScriptsToGo.com
AppleScriptsToGo
4020 W.220th St.
Fairview Park, OH 44126
(440) 331-1074
[2/9/02 3:37:55 PM]
: This shouldn’t be a problem when you know exactly what sort of
: data is being fed to it - checking for malformed strings would
: need to be a separate task.
: Though I’m still not sure what the advantage of turning a string
: into a date to turn it back into a string again is. Or have I
: missed something?
: has
The idea was to use a “try…end try” block to identify dates as opposed to other space delimited strings in an unstructured block of text. I didn’t realize until later that a string representing a number from 1 to the number of days in the current month would resolve as that date of the current month. A zero would resolve as the current date.
Marc K. Myers
Marc@AppleScriptsToGo.com
AppleScriptsToGo
4020 W.220th St.
Fairview Park, OH 44126
(440) 331-1074
[2/10/02 6:04:16 PM]
: Marc - in theory you are of course right but in practise there
: isn’t a problem if the given input format doesn’t vary. It
: ain’t bust , but if you want peace of mind then surely the
: overhead would only be very slight for testing the text item
: for the presence of, say, “-***-”.
: has - are you perhaps ignoring the manipulations (as, akua,
: whatever) possible with a date?
Heh, hope not - I already wrote two date formatting libraries that beat the tar out of Akua’s date formatting function, so it’s old hat to me…
But in this case you don’t need anything so complex as going from string to date to string again - a simple transposition will do.( I’m assuming that it’s just a matter of getting dates into a format that FMP can parse when it imports the comma-delimited table.)
Actually, I had another look through the thread and realised what Marc was doing: he was going through the entire string testing to see what would turn into a date and what wouldn’t. Which was a reasonable thing to do since Dan’s original message made it look as if the table was actually one big unordered string. Except Dan then posted a clarification showing that it is an ordered table, therefore you don’t need anything so complex after all - you only need to pick out the date string as the first lump of text in each table row, massage it a bit, then put it back. Sorted.
Cheers,
has
: Another thought.
: It occurred to me that if you have your computer’s global date
: format set to the format you want to end up with then just
: adding that one word “date” should do the whole job for you
: with no additional code required!
Not a very robust solution, however. Users shouldn’t have to resort to such remote tinkering just to get AS to do what they want it to do.
AS’s date class is brilliant in places, but feels terribly unfinished in others. Its habit of relying on machine-specific settings is particularly disconcerting - it’s a shame there isn’t a built-in function in AS for specifying date formatting when converting dates to/from strings.
has
: I just realized that my idea isn’t as good as I thought it was.
: There are a lot of character strings besides dates that will
This shouldn’t be a problem when you know exactly what sort of data is being fed to it - checking for malformed strings would need to be a separate task.
Though I’m still not sure what the advantage of turning a string into a date to turn it back into a string again is. Or have I missed something?
has
This is beginning to remind me of admonitions at school to “be sure to read the question properly”. Dan, qua examiner, didn’t make too good a job of setting the question in the first place, did you Dan?
Is the object of the exercise here to code something very specifically for Dan’s second posting? Should the code cater for some possible 'Ah but what if’s? Or not? Or such as what, Dan?
In the final analysis I can’t see any tangible difference time-wise, length-of-code-wise or any-other-wise between text massage or date massage. I don’t see text-to-date-to-text as a misdemeanour - it could end up as a really elegant way of making the whole thing ‘zing and zip’ speedily and safely along. The date routine, as Marc points out, needs to guard against the ’ date " 0 I’m not a date" ’ case, but a text manipulation would also need safeguards unless Dan could give an absolute guarantee that never everÉ
The secret that I haven’t divulged yet is… Have you guessed? It’s that I like my massages whatever way they come!
Andreas
Drat, I forgot…
My apologies, has - I didn’t mean to tread on your toes or to be patronising, but I didn’t know anything of your pedigree - which sounds impressive.
Andreas
Marc - in theory you are of course right but in practise there isn’t a problem if the given input format doesn’t vary. It ain’t bust, but if you want peace of mind then surely the overhead would only be very slight for testing the text item for the presence of, say, ‘-***-’.
has… are you perhaps ignoring the manipulations (as, akua, whatever) possible with a date?
Andreas
: This is beginning to remind me of admonitions at school to ‘be
: sure to read the question properly’. Dan, qua examiner, didn’t
: make too good a job of setting the question in the first
: place, did you Dan?
Given that you need to write html to post anything beyond plain-n-simple text on this forum, I find it hard to criticise folks when their messages come out looking hokey (hey, it even diced yours;). (Though IIRC, Dan’s text did talk about comma-delimted tables, which was a bit of a clue.)
: In the final analysis I can’t see any tangible difference
: time-wise, length-of-code-wise or any-other-wise between text
: massage or date massage.
This’ll depend on how many times you have to perform the operation. If there’s only a few lines in the table, it won’t make a big difference overall. If there’s dozens or hundreds or more lines, it’ll soon start to add up. The TID-based transposition is very fast due to its simplicity. Marc’s method, if given the entire table, will test every entry in that table, deliberately failing on most of them (the ‘try’ block); it also uses a coercion and an osax call. All these things take time. If you iterate over each line in the table, take the first item of each line, swap it about a bit and put it back, you’re doing a lot less work, and doing it more simply and efficiently.
Design good algorithms in AS is an art unto itself, but the general rule of: “speed, features, safety - pick any two” still holds.
: The date routine, as Marc points out, needs to guard
: against the ’ date ’ 0 I’m not a date’ ’ case,
Well, the iterate-over-everything approach certainly does need more work, since ‘date someString’ can turn some non-date stuff into dates as well. Iterating over the first item in each row of a structured table doesn’t, since you know what you’re getting each time.
If there’s a possibility of invalid data being entered into the table before it’s sent, or the table getting corrupted in transit, or… etc, then you need to do extra validation - which isn’t hard to do as long as you know what should be there. (I’d guess that any validation will be getting done in FMP at import time, btw. If you do it in AS then it’s more code to write and will run slower, so you don’t want to do it there unless you need to.)
: The secret that I haven’t divulged yet is Have you guessed? It’s
: that I like my massages whatever way they come !
[groaaaan…] 
has
: Heh, hope not - I already wrote two date formatting libraries
: that beat the tar out of Akua’s date formatting function, so
: it’s old hat to me…
So where could I get a copy of this library, please.
Graham.