Mass Convert .xls and .xlsx to .txt (Tab Delimited) on a Mac
Solution 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
$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)
Solution 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
Related videos on Youtube
Jarrett G.
There are no bad ideas. Only great ideas that go terribly wrong. -Jack Donaghy
Updated on September 18, 2022Comments
-
Jarrett G. over 1 year
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.
-
Raystafarian almost 11 yearsare they all in the same place?
-
Jarrett G. almost 11 yearsyeah, they are all in the same folder
-
Gabriel Ryan Nahmias almost 11 yearsWhat characters need to be converted to tabs?
-
Jarrett G. almost 11 yearsThe whole file needs to be converted. It's in .xls or .xlsx right now, but I need it to be in a tab delimited format, meaning that cells are separated by tabs
-
-
Jarrett G. almost 11 yearsI'm pressing the "play" button, but nothing is happening
-
Jarrett G. over 10 yearsI can't drop files onto the icon. It's just a dialog box. Is there a way to do it using folder path as a parameter for both opening and exporting?
-
Bob Caceres over 10 yearsCut and paste the script into a new window in AppleScript Editor. When you save it, at the bottom of the window where it says File Format, choose "application." This will enable the drag-and-drop feature.