Hey everyone…first time dealing with Automator so bare with me and thanks ahead of time for the ability to post…
Here is my current workflow…
I have an excel spreadsheet that contains 2 columns that I need to extract data from. Column A has the text that I need converted to speech files. Column B contains the naming conventions that I need the file saved as when it converts it to the AIFF format. I am looking to read the text in column A, convert it to a Aiff file (text to speech) and then save it off as a file with the name associated on column B.
Is it possible to do this with automator or would a script need to be written? I have over 2000 lines that need converting and it would be huge if I could reduce my workflow with automator.
Here is your script…if anyone else out there knows a more efficient way, please post. I would like to know.
Copy this droplet script into your applescript editor and save as an application to your desktop for easy finding. Then all you have to do is drop your excel file onto the scrip icon and it will do the rest. Please read through the comments I wrote as you may need to modify slightly to your file orientation, but I followed your requirements as far as Column A and B go.
If you have any questions feel free to ask.
Cheers,
Mark
on open (excelFile)
tell application "Microsoft Excel"
open alias excelFile
set targetRange to column 1 of active sheet
set afterCell to cell "A1" in the active sheet
--the next command looks for the first blank cell in column A, assuming that is the end of your data
set findRange to find targetRange what "" after afterCell look in values look at whole search direction search next
--This sets the rowCount variable to one less than where it found the blank cell, which is presumably your last cell with data in it
set rowCount to (first row index of findRange) - 1
repeat with i from 2 to rowCount --2 is the starting row, assumes header, replace with 1 if no header row
--grabs, from column A the text to be spoken/recorded here
set the clipboard to (value of cell 1 of row i) as text
--grabs the filename from column B to save as
set outputFile to ((value of cell 2 of row i) as text)
--this places all the aiff files in a folder called text2speech on your desktop
set outputFilePath to (((path to desktop folder) as text) & "text2speech:" & outputFile)
tell application "Finder"
--this do shell script... creates the .aiff file(s)
do shell script "say -o " & quoted form of POSIX path of outputFilePath & " " & quoted form of (the clipboard as text)
end tell
end repeat
end tell
end open
Model: iMac (late 2006)
AppleScript: 2.1.1
Browser: Safari 531.21.10
Operating System: Mac OS X (10.6)
The prm? error is due to the fact that the text2speech folder doesn’t exist on your desktop. I assume you are running it unchanged. I just tested it myself and received the same error. Once I put the folder on the desktop is worked again. Not sure where the “?” in the say command line came from, but it is not necessary. the “-o” is, so as long as that is there its all good. If you changed any of the locations let me know where and I can try and work through that with you.
Good Luck,
Mark
P.S. Here is the script with a check for the folder…if it doesn’t exist it creates it. This keeps the evil “prm?” error away.
on open (excelFile)
tell application "Microsoft Excel"
open alias excelFile
set targetRange to column 1 of active sheet
set afterCell to cell "A1" in the active sheet
--the next command looks for the first blank cell in column A, assuming that is the end of your data
set findRange to find targetRange what "" after afterCell look in values look at whole search direction search next
--This sets the rowCount variable to one less than where it found the blank cell, which is presumably your last cell with data in it
set rowCount to (first row index of findRange) - 1
repeat with i from 2 to rowCount --2 is the starting row, assumes header, replace with 1 if no header row
--grabs, from column A the text to be spoken/recorded here
set the clipboard to (value of cell 1 of row i) as text
--grabs the filename from column B to save as
set outputFile to ((value of cell 2 of row i) as text)
set outputFilePath to (((path to desktop folder) as text) & "text2speech:" & outputFile)
tell application "Finder"
if not (exists folder "text2speech" of (path to desktop folder)) then
make new folder at (path to desktop folder) with properties {name:"text2speech"}
end if
--this places all the aiff files in a folder called text2speech on your desktop
set outputFilePath to (((path to desktop folder) as text) & "text2speech:" & outputFile)
end tell
tell application "Finder"
--this do shell script... creates the .aiff file(s)
do shell script "say -o " & quoted form of POSIX path of outputFilePath & " " & quoted form of (the clipboard as text)
end tell
end repeat
quit --quits Excel when done
end tell
end open
Just ran through it again and it works flawlessly. I didn’t realize the folder was needed prior to you adding the check. This is a really nice and easy way of getting the work done. I appreciate the help and time you put into this…it’s technically my first stab at learning applescript but with the success not the last. Thanks again.
Glad to hear it. I find AS a pretty easy language to work with. Just as an aside, how long did it take for the script to handle your file? I am just curious as to the speed of the process.
It took about 5 minutes for 200 lines from the excel doc. I broke it out just as a test to make sure it didn’t run though all 20K+ lines. I will setup the full process and run it today.
this might be faster. All Microsoft applications are very expensive while sending Apple Events.
The script retrieves the values once, so only one Apple Event is sent. To check for the folder on desktop
in every iteration isn’t necessary, too.
on open (excelFile)
tell application "Microsoft Excel"
open alias excelFile
set targetRange to column 1 of active sheet
set afterCell to cell "A1" in the active sheet
--the next command looks for the first blank cell in column A, assuming that is the end of your data
set findRange to find targetRange what "" after afterCell look in values look at whole search direction search next
--This sets the rowCount variable to one less than where it found the blank cell, which is presumably your last cell with data in it
set rowCount to (first row index of findRange) - 1
set excelValue to value of range ("A1:B" & rowCount as text)
end tell
do shell script "/bin/mkdir -p ~/Desktop/text2speech"
set outputFolder to (POSIX path of (path to desktop) & "text2speech/")
repeat with i from 1 to count excelValue
set {textToSpeech, fileName} to item i of excelValue
set outputFilePath to quoted form of (outputFolder & fileName)
do shell script "say -o " & outputFilePath & " " & quoted form of textToSpeech
end repeat
end open
I knew there was a smoother way. I was contemplating loading an array/list with the data, but went for the quick and dirty method. Had I taken the time I would have gotten there. I am still learning all the “do shell script” stuff the mkdir definitely is cleaner than the way I did it. I will be modifying the script currently in my reference library to the updated version you provided. I appreciate the review and code adjustments. It’s always nice to learn from a more advanced scripter on better ways to do stuff.