Mass Convert .xls and .xlsx to .txt (Tab Delimited

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.

Answer 1

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.

Answer 2

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.