Mass Convert .xls and .xlsx to .txt (Tab Delimited) on a Mac

Question

I have about 150 .xls and .xlsx files that I need converting into tab-delimited. I tried using automator, but I was only able to do it one-by-one. It's definitely faster than opening up each one individually, though. I have very little scripting knowledge, so I would appreciate a way to do this as painlessly as possible.

Will you make your Automator workflow available so I can see the solution that you do have? — Kaydell, Jul 23 '13 at 03:49
Many times with Automator, it's necessary to use an Automator action called "Run AppleScript". Excel supports AppleEvents so that scripts written in AppleScript (or in Python) can control Excel via AppleEvents. Ideally, AppleEvents give you a second user-interface, in addition to the GUI. — Kaydell, Jul 23 '13 at 03:53
Get Specified Finder Items --> Open Excel Workbooks --> Save Excel Workbook (downloaded http://automatorworld.com/archives/microsoft-office-automator-actions/, NOT built-in) --> Close Excel Workbooks ------------------------------------- The custom "Save Excel Workbook" has a dropdown that says "save as" with the options "New", "Other", and "New Variable". Below it, it has a dropdown menu for "Format", containing a ton of options that the default Excel conversion workflow does not have. — Jarrett G., Jul 23 '13 at 16:36

ChrisProsser · Accepted Answer · 2013-07-23T17:40:52.233

0

If you would be prepared to use Python for this I have written a script that converts Excel spreadsheets to csv files. The code is available in Pastebin.

You would just need to change the following line:

writer = csv.writer(fileout)

to:

writer = csv.writer(fileout, delimiter="\t")

to make the output file tab delimited rather than the standard comma delimited.

As it stands this script prompts you for files one at a time (allows you to select from a dialogue), but it could easily be adapted to pick up all of the Excel files in a given directory tree or where the names match a given pattern.

If you give this a try with an individual file first and let me know how you get on, I can help with the changes to automate the rest if you like.

UPDATE

Here is a wrapper script you could use:

#!/usr/bin/python

import os, sys, traceback
sys.path.insert(0,os.getenv('py'))
import excel_to_csv

def main():
    # drop out if no arg for excel dir
    if len(sys.argv) < 2:
        print 'Usage: Python xl_csv_wrapper <path_to_excel_files>'
        sys.exit(1)
    else:
        xl_path = sys.argv[1]

    xl_files = os.listdir(xl_path)
    valid_ext = ['.xls', '.xlsx', '.xlsm']

    # loop through files in path
    for f in xl_files:
        f_name, ext = os.path.splitext(f)
        if ext.lower() in valid_ext:
            try:
                print 'arg1:', os.path.join(xl_path,f)
                print 'arg2:', os.path.join(xl_path,f_name+'.csv')
                excel_to_csv.xl_to_csv(os.path.join(xl_path,f),
                                       os.path.join(xl_path,f_name+'.csv'))
            except:
                print '** Failed to convert file:', f, '**'
                exc_type, exc_value, exc_traceback = sys.exc_info()
                lines = traceback.format_exception(exc_type, exc_value, exc_traceback)
                for line in lines:
                    print '!!', line
            else:
                print 'Sucessfully conveted', f, 'to .csv'


if __name__ == '__main__':
    main()

You will need to replace the :

sys.path.insert(0,os.getenv('py'))

At the top with an absolute path to the excel_to_csv script or an environment variable on your system.

edited Jul 23 '13 at 17:40

answered Jul 22 '13 at 23:33

ChrisProsser

12,598
6
35
44

1

The funny thing is that I actually know python somewhat. Sorry about this, but how do you run it? the output says "~/Desktop/done/excel_to_csv.py:9: ImportError: No module named xlrd " – Jarrett G. Jul 22 '13 at 23:49
Sorry forgot to mention that you will need to install the xlrd library. You can pick up the source code from here: https://pypi.python.org/pypi/xlrd/0.9.2. I have only used python on Windows / Linux but I assume that it is the same on osx - you should just need to unzip the package then run python setup.py install once you have downloaded – ChrisProsser Jul 22 '13 at 23:53
Thinking about it you may have to add Sudo to the front of the command above to install as superuser. – ChrisProsser Jul 22 '13 at 23:57
I got an error: File "/Users/[me]/Desktop/excel_to_csv.py", line 130, in main() File "/Users/[me]/Desktop/excel_to_csv.py", line 92, in main os.chdir(os.path.join(os.getenv('userprofile'),'documents')) File "/System/Library/Frameworks/Python.framework/Versions/2.6/lib/python2.6/posixpath.py", line 67, in join AttributeError: 'NoneType' object has no attribute 'endswith' – Jarrett G. Jul 23 '13 at 16:22
sorry, I forgot about this. The 'userprofile' environment variable will be Windows specific. The os library should still work, but you can replace the path inside os.chdir() to a path that you enter into the script or use an environment variable that will be valid on your system. – ChrisProsser Jul 23 '13 at 16:51
You can also call this script with cmd line args for the source and destination file paths. This may be handy if we come to write a small script to automate the process for all of your files. – ChrisProsser Jul 23 '13 at 16:52
Works for the individual files! Can you modify it to convert all the files in a directory? – Jarrett G. Jul 23 '13 at 17:02
Yes, I can write a quick wrapper script for this. Are the files all in one directory or will some be in sub directories? Also are all of the files in this directory / tree excel files or do I need to check for this? – ChrisProsser Jul 23 '13 at 17:13
Never mind. A coworker and I figured it out. Thanks for all your help! – Jarrett G. Jul 23 '13 at 17:37
I have written it now, so posted above incase it is useful for anyone else seeing the answer. Please could you accept this as the answer to your question if it has helped. Regards, Chris – ChrisProsser Jul 23 '13 at 17:42

score 0 · Answer 2 · answered Jul 23 '13 at 01:00

0

Use VBA in a control workbook to loop through the source workbooks in a specified directory or a list of workbooks, opening each, saving out the converted data, then closing each in turn.

answered Jul 23 '13 at 01:00

Monty Wild

3,981
1
21
36

Mass Convert .xls and .xlsx to .txt (Tab Delimited) on a Mac

2 Answers2

Linked