Trying to Use AppleScript to Convert .xls and .xlsx to .txt (Tab Delimited), Needs Fixing

Question

I copied this from an answer I got from another thread. I am trying to convert ~300 .xls and .xlsx files to tab delimited. They are all in the same folder. If anyone knows a better way, please let me know.

property type_list : {"XLS6", "XLS7", "XLS8", "XLSX"}
property extension_list : {"xls", "xlsx"}


on open these_workbooks
repeat with k from 1 to the count of these_workbooks
    set this_item to item k of these_workbooks
    set the item_info to info for this_item

    --this if statement tests to make sure the items you're converting are Excel spreadsheets and not folders or aliases
    if (folder of the item_info is false) and (alias of the item_info is false) and ((the file type of the item_info is in the type_list) or the name extension of the item_info is in the extension_list) then

        tell application "Finder" to open this_item

        tell application "Microsoft Excel"
            --this just tacks on ".txt" to your file name
            set workbookName to (name of active workbook & ".txt")
            --save the current open workbook as a tab-delimited text file
            tell active workbook to save workbook as filename workbookName file format text Mac file format
            close active workbook saving no
        end tell
    end if
end repeat
end open

on run
    display dialog "Drop Excel files onto this icon."
end run

All this does is open a dialog box and does nothing. Even though it's meant to be a droplet, nothing happens when I drag a file to it.

This script is using `Droplets` (http://macscripter.net/viewtopic.php?id=24772). Save it as an executable application and then drop the files onto it (That's what that display dialog is trying to tell you) — scohe001, Aug 15 '13 at 18:54

scohe001 · Accepted Answer · 2013-08-16T01:15:58.363

4

In Applescript the run handler gets run when the script or application is run normally. Meanwhile, the open VarName handler gets run when some file or files are dropped onto the icon of the application and the files get set to the variable VarName. The script you posted cleverly places that display dialog in the on run handler to try to help you understand this usage. Instead, save the script as an application and then drop your files onto it.

EDIT:

After a quick test on a Mountain Lion machine that finally has Excel 2011 (I didn't realize how much I'd been overcomplicating):

property type_list : {"XLS6", "XLS7", "XLS8", "XLSX"}
property extension_list : {"xls", "xlsx"}


on open these_workbooks
    repeat with k from 1 to the count of these_workbooks
        set this_item to item k of these_workbooks
        set the item_info to info for this_item

        --this if statement tests to make sure the items you're converting are Excel spreadsheets and not folders or aliases
        if (folder of the item_info is false) and (alias of the item_info is false) and ((the file type of the item_info is in the type_list) or the name extension of the item_info is in the extension_list) then

            tell application "Finder" to open this_item

            tell application "Microsoft Excel"
                --this just tacks on ".txt" to your file name
                set workbookName to (path to desktop as string) & "After:" & (name of active workbook & ".txt")
                display dialog workbookName
                --save the current open workbook as a tab-delimited text file
                tell active workbook to save workbook as filename workbookName file format text Mac file format
                close active workbook saving no
            end tell
        end if
    end repeat
end open

on run
    display dialog "Drop Excel files onto this icon."
end run

edited Aug 16 '13 at 01:15

answered Aug 15 '13 at 19:00

scohe001

15,110
2
31
51

Awesome! Thanks! Is there anything I can add to the script to export to a different folder? Also, how do I change the filename to get rid of the .xls.txt extension and just change it to .txt? – Jarrett G. Aug 15 '13 at 19:08
So this basically spewed everything all over my desktop with the filenames "Document 1", "Document 2", etc. Is there a way to specify the output destination for a whole bunch of files while still keeping their original names? – Jarrett G. Aug 15 '13 at 19:50
@Jart Sorry, I was trying to demonstrate that whatever path you set `workbookName` to will be where it will save it. If you want a different destination, just change that variable (You'll notice right now it just goes to the desktop and then names it "Doc k.txt" where k is the current iteration) – scohe001 Aug 15 '13 at 20:10
Err... can you clarify? I'm not good with VBA. Let's say I have a file called spreadsheet.xlsx in a folder on my desktop called "Before". I want it called spreadsheet.txt (as opposed to spreadsheet.xlsx.txt) in tab delimited form in a folder on my desktop called "After". – Jarrett G. Aug 15 '13 at 20:13
@Jart After a bit of finagling, I just updated my answer. This will work for `xls` only, not `xlsx`. For `xlsx` change `text 1 thru -4` to `text 1 thru -5`. I could add an if statement to check for that, but I figured all of your files would probably be the same format so there's no need to add the extra code – scohe001 Aug 15 '13 at 20:32
They aren't unfortunately, but that's okay. Now I'm getting a "File 1 Not Found" Error. – Jarrett G. Aug 15 '13 at 20:39
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/35545/discussion-between-josh-and-jart) – scohe001 Aug 15 '13 at 20:40

Trying to Use AppleScript to Convert .xls and .xlsx to .txt (Tab Delimited), Needs Fixing

1 Answers1