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.

Answer 1

_{(I know you are on a MAC, so my answer might not be that useful for you. But for Windows users maybe. Btw. there is a Powershell open source reimplementation for MAC und Linux out there called PASH)}

How to easely convert multiple Excel files to any desired format

Download this converter Powershell script and execute it. Thats all. :)

It will ask you for a folder and iterates through all XLSX, XLS, XLSB in this folder and its subfolders. Next, Powershell creates a hidden instance of Excel to use Excels internal Open and Save as commands for converting all files to your desired format. Currently to tab-delimited TXT files since OP asks for. File names and folder structures are preserved.

A neat thing is, that even multiple worksheets are saved to a separate file if you choose for example CSV or TXT. Normally, only the first sheet gets saved when using Excel's Save as dialog

If you need another format just change -4158 in the source code to your value. Below are some common formats taken from MSDN.

Open XML Workbook      XLSX            51           xlOpenXMLWorkbook 
Excel 2003             XLS             56           xlExcel8
Excel12                XLSB            50           xlExcel12 
Current Platform Text  CSV             -4158        xlCurrentPlatformText 
HTML format            HTML            44           xlHtml 
Unicode Text           TXT             42           xlUnicodeText 
DBF4                   DBF             11           xlDBF4 

Source code

    $object = New-Object -comObject Shell.Application  
    $folder = $object.BrowseForFolder(0, 'Select the folder', 0)    

    if (!$folder) {exit} 

    $excel = New-Object -comObject Excel.Application
    $excel.Visible = $false
    $excel.DisplayAlerts = $false

    foreach ($file in Get-ChildItem -literalPath $folder.self.Path*.xls? -recurse) {
        $workbook = $excel.Workbooks.Open($file.Fullname)    
        foreach ($worksheet in $workbook.Sheets) {                    
            $worksheet.activate()          
            $newpath = $File.DirectoryName +"\"+ $file.BaseName + " - " + $worksheet.name + ".csv"
            $workbook.SaveAs($newpath,-4158 ,$null,$null)
        }
        $workbook.Close()
    }
    $excel.quit()    

    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbook)
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)
    [System.GC]::Collect() 
    [System.GC]::WaitForPendingFinalizers()

• Dependencies: Excel 2003 or higher and Powershell (preinstalled under Windows 7)

Answer 2

Open one of the workbooks, go to the developer tab, click visual basic and enter this code as a module

Change PATH to the folder where all of the workbooks are. The second PATH is wherever you want to save the text files.

Note that you can only save the first worksheet in each file as tab delimited text doesn't support multiple worksheets.

Sub openandsave()
Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook


Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False

On Error Resume Next
    Set wbCodeBook = ThisWorkbook
        With Application.FileSearch
            .NewSearch
            .LookIn = "PATH"
            .FileType = msoFileTypeExcelWorkbooks
                If .Execute > 0 Then
                    For lCount = 1 To .FoundFiles.Count
                        Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)
                            ActiveWorkbook.SaveAs Filename:="PATH" AND .Foundfiles(lcount) AND ".txt", FileFormat _
                            :=xlText, CreateBackup:=False
                    Next lCount
                End If
        End With
On Error GoTo 0
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub

If it doesn't work, I might have messed up in the SaveAs Filename

Resourced from here

Answer 3

You could use a scripting language to iterate through them all with some Excel library and do some kind of RegEx to convert certain characters to tabs. I may draft it out and post later.

Answer 4

Here is an Applescript intended to be implemented as a droplet (i.e, an app you can drag a bunch of files onto).

There is room for polishing but I hope you find it gets the essential job done.

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 2011"
                --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