Clicky

Ease shell operations using a spreadsheet

This post refers to HoudahSpot 3. The same general ideas apply to HoudahSpot 4. A future post will provide more details.

Diehard Unix gurus are going to be shocked to see Excel and the Unix shell mentioned in the same breath. Yet this can prove a very powerful combination.
Say you want to remove a bunch of files from different locations in your file system. E.g. temporary files with names ending in ~. You could do this with the following command in Terminal.app :

find . -name "*~" -exec rm {} \;

We are doing nothing special here: find by name and then remove. Yet this may already be above the skill or the courage of many a Mac user. In real life, you may want to do more advanced searches (by modification date, in multiple locations, file type, etc.), or do more complex operations (copy, rename, …). You will quickly find yourself in need of a full-blown shell script to loops over the results from find or mdfind.

There is an easier solution involving HoudahSpot and Excel.

First, use HoudahSpot to formulate a query matching your files. You may create arbitrarily complex searches by combining criteria using boolean operators. You may search several locations at once, yet exclude others. Once you are satisfied with the result, configure HoudahSpot to show the file paths in the first column of the result list.

Select the desired results from the list and hit Command-C (copy). In Excel, create a new spreadsheet and paste the file paths into column P.

In the first cell of column B, enter:

=concatenate("""",P1,"""")

This will wrap the path from cell P1 in double quotes. This is needed if file paths contain spaces. Without the double quotes, the shell would treat text left and right of the space as two separate arguments. Watch for paths and file names containing double quotes. These will need escaping.

In the first cell of column A, enter your command. E.g. rm for remove.

Instruct the Excel application to “Fill > Down” column A and B.

Then copy all data rows from columns A and B.

If you were to paste the result into Terminal.app, you would be asking the shell to remove all the files matching your HoudahSpot search. Nothing special: you could have achieved the thing using HoudahSpot alone.

We can also make use of the text editing capabilities of the spreadsheet application. We can, for instance, change all .jpg extensions to .jpeg extensions. Copy the contents of column B to column C and use “Find & Replace” to replace .jpg with .jpeg in column C. In column A, change rm to mv: the move command. Now, copy columns A, B and C to Terminal.app. You have now renamed a batch of files without having to write a loop.

Likewise we could use other columns from HoudahSpot search results – like modification date, image resolution, etc. – in column C to create dynamic file names.  Remember to wrap these values in double quotes if they contain spaces.

Words of caution:

  • Before attempting potentially destructive operations, make sure you have a current backup.
  • Double-check the commands you hand to Terminal.app

 

The same concepts should work with any spreadsheet application. Unfortunately Apple’s Numbers.app tries to embed the actual files rather than just their paths in the spreadsheet. The workaround is to first paste the results into a text editor (e.g. TextMate). Copy the resulting text and paste that into the Numbers spreadsheet.