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

automatorcsvmacmacosmicrosoft excel

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.

Best Answer

  • (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

    enter image description here enter image description here

    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)
  • Related Question