Hello,
I am new to Applescript and have run into a problem. I currently have an add in to Microsoft PowerPoint that records the time of each slide change and the title of the slide, then exports this to a .csv fil. My goal is to have this data be inserted into an FCPXXML file to display each slide change as a marker. Currently I have a script that will create a Numbers spreadsheet, convert the data to the proper format for FCPX and adds the XML. These sit in a spreadsheet with one row for each slide change and columns for Time, Title, Time Conversion (rippling the markers and converting to the format for FCPX) and, finally the XML. I want to now select the FCPXXML file and have it replace one marker (each xml file will have one marker inserted before export from FCPX) with the correct number of PowerPoint slides. I can have it remove the the marker reference, but I can’t get it to insert each line of the column as text. I have tried a number of options, but none have given me the correct output.
The workflow would be:
- Select XML File
- XML file is searched for phrase <marker start=
- Phrase is replaced by the contents of every row in column E with a return between row
I am wracking my brain, if anyone could offer assistance I would appreciate it.
Thanks,
Hi. Welcome to MacScripter.
I don’t see any mention of TextEdit in your question. Perhaps you were thinking of editing the XML text in it. But in fact that’s not necessary ” unless you particularly want to, of course. The script below formats the contents of column E of a Numbers spreadsheet into a return-delimited text and substitutes this for your marker phrase in the XML file. As written, it uses a ‘choose file’ dialog to identify the file, but presumably you’ll already have this from earlier in the process.
set markerPhrase to "<marker start="
tell application "Numbers"
-- The AppleScript value of an empty cell in Numbers 2.3 is 0.0. In Numbers 3.5.2, it's missing value.
if (version < 3.0) then
set empty to 0.0
else
set empty to missing value
end if
-- Get the values of all the cells in column E which actually contain values.
tell table 1 of sheet 1 of document 1
set columnEValues to value of cells of column "E" whose value is not empty
-- If the first value's a column header you don't want to include, change the above to:
-- set columnEValues to value of cells 2 thru -1 of column "E" whose value is not empty
end tell
end tell
-- Coerce the list of values to a return-delimited text.
set astid to AppleScript's text item delimiters
set AppleScript's text item delimiters to return
set columnEValues to columnEValues as text
-- Choose the XML file and open it for writable access.
set fRef to (open for access (choose file of type "xml") with write permission)
try
-- Read the text (assumed to be UTF-8) from the XML file.
set xmlText to (read fRef as «class utf8»)
-- Replace the marker phrase with the column text.
set AppleScript's text item delimiters to markerPhrase
set xmlText to xmlText's text items
set AppleScript's text item delimiters to columnEValues
set xmlText to xmlText as text
-- Write the result back to the file, replacing the original text.
set eof fRef to 0
write xmlText to fRef as «class utf8»
-- Close the file.
close access fRef
set AppleScript's text item delimiters to astid
on error errMsg
-- If something falls over, close the file, report the problem, and stop the script.
close access fRef
set AppleScript's text item delimiters to astid
display dialog errMsg buttons {"Stop"} default button 1 cancel button 1 with icon stop
end try
Hi Nigel,
Thanks for your help. I have it almost working now. The markers all start with the text <marker start= but there is more that follows and it varies depending on where the marker was placed. A full marker looks like: . In my previous version, I could set the delimiter to paragraph and delete the entire line. How can I do that with our code?
Once again, thanks!
I suspected from the phrase itself that the task had been wrongly described. 
Fortunately, only a slight tweak’s required. The version below replaces the entire line containing the marker phrase. It assumes that the phrase does occur in the text, that it only occurs once, and that it’s not in the first or last lines.
set markerPhrase to "<marker start="
tell application "Numbers"
-- The AppleScript value of an empty cell in Numbers 2.3 is 0.0. In Numbers 3.5.2, it's missing value.
if (version < 3.0) then
set empty to 0.0
else
set empty to missing value
end if
-- Get the values of all the cells in column E which actually contain values.
tell table 1 of sheet 1 of document 1
set columnEValues to value of cells of column "E" whose value is not empty
-- If the first value's a column header you don't want to include, change the above to:
-- set columnEValues to value of cells 2 thru -1 of column "E" whose value is not empty
end tell
end tell
-- Coerce the list of values to a return-delimited text.
set astid to AppleScript's text item delimiters
set AppleScript's text item delimiters to return
set columnEValues to columnEValues
-- Choose the XML file and open it for writable access.
set fRef to (open for access (choose file of type "xml") with write permission)
try
-- Read the text (assumed to be UTF-8) from the XML file.
set xmlText to (read fRef as «class utf8»)
-- Replace the marker phrase with the column text. -- In fact, replace the entire line containing the phrase.
set AppleScript's text item delimiters to markerPhrase
set xmlText to (text 1 thru paragraph -2 of text item 1 of xmlText & return) & columnEValues & (return & text from paragraph 2 to -1 of text item 2 of xmlText)
-- Write the result back to the file, replacing the original text.
set eof fRef to 0
write xmlText to fRef as «class utf8»
-- Close the file.
close access fRef
set AppleScript's text item delimiters to astid
on error errMsg
-- If something falls over, close the file, report the problem, and stop the script.
close access fRef
set AppleScript's text item delimiters to astid
display dialog errMsg buttons {"Stop"} default button 1 cancel button 1 with icon stop
end try