Following Folder Action script I use for publishing a spreadsheet to my website via FTP. All this to be able to import it in my iPhone with Simon Wigzells CSV Touch app (http://web.me.com/simon_wigzell/Ozymandias/Home.html). But you can adapt it to suit your specific needs.
It works for one document, not yet for multiple documents (dragged into the action folder).
(*
PublishCSV.applescript
Created by J. van Oostrum in may 2009.
Copyright 2009 Chaos Geordend. All rights reserved.
Publish a spreadsheet as comma separated document on a website via FTP
e.g. for use in combination with Simon Wigzell's 'CSV Touch' application
2008.10.06 New script
2008.11.09 Made this a folder action script
2009.04.27 Adjusted for use with iWorks '09 Numbers in OS X 10.5.6
TODO
Enable multiple file to be uploaded (sequencenumber and/or datetimestamp)
*)
-- Adjust these properties to suit your setup
property prptyHost : "[url=ftp://ftp.yourftphost.org]ftp.yourftphost.org[/url]" -- your website FTP host URL
property prptyUserid : "youruserid@something.org" -- your userid
property prptyPassword : "password" -- your password
property prptyDestinationFile : "/pathto/document#.csv" -- path to the destination file on your website host
--
property prptyToken : "#" -- token in destinationfile that will be replaced with the sequencenumber
property prptySeparatorChar : "," -- character used to separate the values with (usually a comma or semicolon)
-- More or less fixed values, declared here as global
property prptyTempFileCSV : "tmp.csv" -- name of the temporary FTP sourcefile
property prptyTempFileTXT : "tmp.txt" -- temporary file (with text filetype)
property prptyFileTypeList : {"numbers"} -- filetype(s) of files to be processed
property t : false -- set to true for testing this script in Scripteditor
-- TESTRUN
if (t) then
set thisItem to choose file with prompt "Choose a document"
set thisFolder to my parentPath(thisItem)
set prptyTempFileTXT to (thisFolder & prptyTempFileTXT) as text
copy my replace(prptyDestinationFile, prptyToken, "") to myDestinationFile -- no sequencenumber
my doCheckDocument(thisItem)
set myDocumentData to my getSelectedValues()
my doSaveAsCSV(theDocumentData, thisFolder)
my doFTP(thisFolder, myDestinationFile)
my doRemove(thisItem)
my doRemove((thisFolder & prptyTempFileCSV) as text)
return
end if
-- END TESTRUN
--
-- Folder action
--
on adding folder items to thisFolder after receiving addedItems
local myDocumentData
local myDestinationFile
copy "" to myDocumentData
copy "" to myDestinationFile
--get the name of the folder
tell application "Finder"
set thisFolderName to the name of thisFolder
end tell
repeat with i from 1 to number of items in addedItems
set thisItem to item i of addedItems
set itemInfo to the info for thisItem
if name extension of itemInfo is in prptyFileTypeList then -- process Numbers' documents only
my doCheckDocument(thisItem)
set myDocumentData to my getSelectedValues()
my doSaveAsCSV(myDocumentData, thisFolder)
if i > 1 then
copy my replace(prptyDestinationFile, prptyToken, i as string) as text to myDestinationFile -- sequence number if more than one file is transferred
else
copy my replace(prptyDestinationFile, prptyToken, "") as text to myDestinationFile
end if
--display dialog ("dest: " & myDestinationFile)
my doFTP(thisFolder, myDestinationFile)
my doRemove(thisItem)
my doRemove((thisFolder & prptyTempFileCSV) as text)
end if
end repeat
my doCloseNumbers()
tell application "Finder"
close folder thisFolder
end tell
end adding folder items to
--
-- check whether the document contains a table
--
on doCheckDocument(theDocument)
tell application "Numbers"
open (theDocument as alias)
if not (exists first document) then
display dialog "There is no document opened" buttons {"Cancel"} default button 1
end if
tell first document
tell first sheet
if not (exists table 1) then
display dialog "The document contains no table" buttons {"Cancel"} default button 1
end if
end tell
end tell
end tell
end doCheckDocument
--
-- Close the Numbers application
--
on doCloseNumbers()
tell application "Numbers"
close every document saving no
quit
end tell
end doCloseNumbers
--
-- return the value of all cells of the documents' table
--
on getSelectedValues()
local myValues
local myRowValuesAsList
local mySeparatorChar
say "Start copying data"
copy "" to myValues
tell application "Numbers"
tell first document
tell first sheet
tell first table
-- select all tablecells
set selection range to range (name of first cell of first row & ":" & name of last cell of last row as string)
set myRowValuesAsList to {}
-- fetch all values for every tablerow
repeat with r from 1 to the count of rows
set mySeparatorChar to ""
copy value of every cell in row r as list to myRowValuesAsList
repeat with cellValue in myRowValuesAsList
if cellValue as text = "0,0" then
copy myValues & mySeparatorChar & quote ¬
& " " & quote to myValues -- put space in empty cell (as it otherwise gets value 0,0 assigned)
else
copy myValues & mySeparatorChar & quote ¬
& my replace(cellValue as text, "\"", "\"\"") & quote to myValues -- keep quotes
end if
if mySeparatorChar = "" then copy prptySeparatorChar to mySeparatorChar
end repeat
copy myValues & return to myValues -- terminate row values with carriage return character
end repeat
say "Values were selected"
end tell
end tell
end tell
end tell
return myValues
end getSelectedValues
--
-- transfer the .csv document to website
--
on doFTP(theFolder, theDestinationFile)
local myTempFile
local myDestinationFile
local myRC
local lf
say "and transfer file to website with FTP"
set lf to ASCII character 10 -- linefeed character
copy the POSIX path of file ((theFolder & prptyTempFileCSV) as text) to myTempFile
copy theDestinationFile to myDestinationFile
-- File Transer Protocol script
-- Binary transfer is used to preserve file characteristics (e.g. newline chars)
set s to ¬
"HOST=" & the quoted form of (prptyHost as text) & "; " & ¬
"USER=" & the quoted form of (prptyUserid as text) & "; " & ¬
"PASSWD=" & the quoted form of (prptyPassword as text) & "; " & ¬
"FILE=" & the quoted form of (myTempFile as text) & "; " & ¬
"DESTINATION=" & the quoted form of (myDestinationFile as text) & "; " & ¬
"ftp -n $HOST << END_SCRIPT " & lf & ¬
"quote USER $USER " & lf & ¬
"quote PASS $PASSWD " & lf & ¬
"binary " & lf & ¬
"put $FILE $DESTINATION " & lf & ¬
"quit " & lf & ¬
"END_SCRIPT " & lf & ¬
"echo $?" & lf -- return resultcode
set myRC to do shell script s
if myRC ≠"" then display dialog ("FTP resultcode: " & myRC) buttons {"Cancel"} default button 1 -- show shell script errors if any
--display dialog ("dest: " & myDestinationFile)
end doFTP
--
-- clean up file <theFile>
--
on doRemove(theFile)
tell application "Finder"
if exists theFile then
delete theFile
end if
end tell
end doRemove
--
-- write <theData> into a temporary file in <theFolder>
--
on doSaveAsCSV(theData, theFolder)
local myTempFileTXT
local path2Doc
local myCSV
say "Save file in CSV fileformat"
copy (theFolder & prptyTempFileTXT) as text to myTempFileTXT
set path2Doc to myTempFileTXT as text
open for access file path2Doc with write permission
set myCSV to (path2Doc as Unicode text) as alias -- get alias, now the file has been created (in the previous open statement)
write theData to file path2Doc
close access file path2Doc
tell application "System Events" to set the name of myCSV to (prptyTempFileCSV as text) -- change .txt extension to .csv
end doSaveAsCSV
--
-- get <theFile>'s parent path (source: http://www.nobleswan.com/applescript/AS_snippets.html)
--
on parentPath(theFile)
tell application "Finder" to return container of theFile as text
end parentPath
--
-- Return a list that comprises all text items of <theString>
-- with the text items separator being defined by <theDelimter>
--
on cutDel(theString, theDelimiter)
local myList
local saveDelims
set saveDelims to AppleScript's text item delimiters
set AppleScript's text item delimiters to theDelimiter
set myList to text items of theString
set AppleScript's text item delimiters to saveDelims
return myList
end cutDel
--
-- Return a textstring that comprises all items of <theList>
-- the items separator character is defined by <theDelimter>
--
on joinDel(theList, theDelimiter)
local myText
local saveDelims
set saveDelims to AppleScript's text item delimiters
set AppleScript's text item delimiters to theDelimiter
set myText to theList as text
set AppleScript's text item delimiters to saveDelims
return myText
end joinDel
--
-- Search and replace string
-- returns the content, as string, of <sourceStr> wherein <searchString> has been replaced with <replaceString>
--
on replace(theSourceString, theSearchString, theReplaceString)
local mySourceString
local mySearchString
local myReplaceString
set mySourceString to (theSourceString as text)
set mySearchString to (theSearchString as text)
set myReplaceString to (theReplaceString as text)
return my joinDel(my cutDel(mySourceString, mySearchString), myReplaceString)
end replace
-- END SCRIPT