Submit Hint Search The Forums LinksStatsPollsHeadlinesRSS
14,000 hints and counting!

Ease shell operations using a spreadsheet UNIX
Diehard Unix gurus are going to be shocked to see Excel and the Unix shell mentioned in the same hint. Yet this can prove a very powerful combination for the average user. Unix gurus should read on, too. Say you want to remove a bunch of files from different locations in your file system, for instance, temporary files. The Unix way of doing things would be:
find -name "*~" -exec rm {} ;
We are doing nothing special here: find by name and then remove. Yet this bit of shell may be above the skill or the courage of many Mac users. In real life, you may want to do more complex searches (by modification date, in multiple locations, file type, ...), or do more complex operations (copy, rename, ...). You will quickly find yourself in need of a full-blown shell script which loops over the results from mdfind. Well, there is an easier solution involving HoudahSpot and your favorite spreadsheet (Numbers, 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 path column for the result list. (Control-click on the column headers in the results area, and select File Path from the pop-up menu.)

Next, select the desired results from the list and hit Command-C (copy). In your spreadsheet application, paste the file paths into column B (or any column but the first). You may have to drop excess columns (file name, modification date, ...). You may also find a use for such a column in some special situations.

In the first cell of column A, enter your command. E.g. rm for remove. Instruct the spreadsheet application to "fill down" column A.

Now copy all data rows from columns A and B. If you were to paste the result into, you would be asking the shell to remove all the files matching your HoudahSpot search. Nothing special: you could have gotten the same result using HoudahSpot alone.

So now we factor in the text editing capabilities of the spreadsheet app. For instance, change all .jpg extensions to .jpeg extensions. For this we copy column B to column C and use Find & Replace to replace .jpg with .jpeg in column C. In column A we fill down with the mv string: the move command. Now, if we copy columns A, B and C to, we would rename a batch of files without having to write a loop.

Likewise we could use other columns from HoudahSpot -- like modification date, image resolution, etc. -- in column C to create dynamic file names.

[robg adds: If you're going to try this, I would suggest using echo in front of your command in Excel, then seeing what comes up when you paste in Terminal -- just to be sure you're getting what you expect to be getting. Also, any time you're doing rm on large numbers of files, making sure you've got a current backup is a good idea.]
  • Currently 2.33 / 5
  • 1
  • 2
  • 3
  • 4
  • 5
  (3 votes cast)

Ease shell operations using a spreadsheet | 5 comments | Create New Account
Click here to return to the 'Ease shell operations using a spreadsheet' hint
The following comments are owned by whoever posted them. This site is not responsible for what they say.
The Unix Way
Authored by: lullabud on Feb 01, '08 08:14:11AM
The OP mentions doing things the xargs shell way but doesn't give an example. For the curious, here is a basic example. In this example we merely issue `ls -la` to see the attributes of the file.
mdfind -0 asdf | xargs -0 ls -la

[ Reply to This | # ]
Small correction to UNIX command line
Authored by: ButchAnton on Feb 01, '08 09:20:09AM
Sorry for being pedantic, but

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

is not correct. It should be

find <dirName> -name "*~" -exec rm {} \;

where <dirName> is the root of the directory structure from which you want to find all the temporary files ending in '~'.

Of course, probably the OP got all this right and the HTML formatting screwed up his post . . .

[ Reply to This | # ]

Ease shell operations using a spreadsheet
Authored by: juanfal on Feb 02, '08 11:26:47AM
you all should be more careful. The command is

find . -name "*~" -delete
if you forget the first parameter '.' you can delete all of your files in your disk without any warning. I hate this command!!

[ Reply to This | # ]
Ease shell operations using a spreadsheet
Authored by: alys on Feb 04, '08 02:19:15PM
find . -name "*~" -delete
if you forget the first parameter '.' you can delete all of your files in your disk
That's not true. If you forget the first parameter, the command simply does not work - it does no damage at all.

If you want to be sure that you are deleting the correct files using find, don't use the delete option, use rm as in the original example, but add the -i flag:
find . -name *~ -exec rm -i {} \;
Or use the excellent spreadsheet idea, but still with rm -i

[ Reply to This | # ]
Ease shell operations using a spreadsheet
Authored by: gloubibou on Oct 30, '08 09:18:41AM
I see the shell command got a bit mangled. The correct command is:

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


find WHERE -name WHAT -exec COMMAND {} \;


[ Reply to This | # ]