open text files & save as worksheets in excel with Automator?

jill,

Here is a solution.

Create an Automator workflow with the following actions:
1) Get Specified Finder Items (specify the folder containing your space delimited text files)
2) Get Folder Contents
3) Run AppleScript (delete all of the text in the action, click on the link below which will put the AppleScript into Script Editor, select the whole thing, copy it to the Clipboard, paste it into the action)

click here to open this script in your editor <pre style="font-family: 'Monaco', 'Courier New', Courier, monospace; overflow:auto; color: #222; background: #DDD; padding: 0.2em; font-size: 10px; width:400px">on run {input, parameters}

repeat with aTextFile in input
set theTextFilePath to aTextFile as string
tell application "Microsoft Excel"
open text file filename theTextFilePath data type delimited with space
set theWorkbook to last workbook
set thePath to path of last workbook
set theOriginalWorkbookName to name of last workbook
set theNewWorkbookName to ((characters 1 through -5 of theOriginalWorkbookName) as string) & ".xls"
set theNewFilePath to thePath & ":" & theNewWorkbookName
save workbook as theWorkbook filename theNewFilePath file format workbook normal file format
close theWorkbook saving no
end tell

end repeat

return input
end run</pre>

I am assuming that the text files are the only files in the folder. If they are not then some further filtering will be required likely through inserting a Filter Finder Items action after step 2).



PowerBook 12" Mac OS X (10.4.7)

jill,

Yes the Excel files should be in with the original text fiels.

See what the Run AppleScript action is actually receiving by placing a "View Results" action just before it and also befor the Filter Finder Items action. The Filter Finder Items might be too efficient and prevent any files from reaching the Run AppleScript action.

I'm confused as I was under the impression that your text files were already space-delimited. That is why I used the AppleScript statement:

open text file filename theTextFilePath data type delimited with space

Are they not?

If they are then the text that is separated by spaces should be placed in separate columns in Excel. Is this not happening?

That's great!

Also congratulations on being able to figure out how to change things to suit your needs. Learning through doing, in most cases, is the best way to learn and put these technologies to use for yours and others needs.

Hope you have fun with all that extra time on your hands.

jill,

Yes you can reduce the size of the column containing the actions and this will expand the right column. Once you do that, if you still need room, then you will see a faint line representing a column handle in the titles row of the action. Just like in Excel you can grab the handle and expand the column.

If all else fails then hopefully you have a widescreen and then just increase the size of the window.