Use exec() to run OS commands or other programs on files returned in query results, using their fullpath. On Windows the basic file management commands such as move, copy, del and rename are always available because exec() runs in a shell.

Run 'command' with option '/opt1' on multiple files

The first argument to exec() should be a string specifying the command/program to run. Each argument or option for the command should be a separate argument to exec(), generally one of these will be the fullpath of the file you want to operate on.

Progams you invoke should be on the PATH, or in the directory where you launched Crab, or you must provide the full path for the program. Use the windows 'start' command to run gui applications.

Use the WHERE clause to specify which objects to run 'command' on. No WHERE clause means run it on every object scanned

To run only on direct children of 'SourceDirectory', i.e. not recursive

WHERE parentpath = 'C:\somepath\SourceDirectory\'

To run on all children of 'SourceDirectory,' i.e. recursive

WHERE parentpath like 'C:\somepath\SourceDirectory\%'

You should test the logic of queries that change the filesystem with an 'echo' command before use

exec() runs sychronously, meaning exec() waits for each command to complete before starting the next.

What it means for a command to complete varies from one program to another, e.g. if you open an .mp3 file in windows media player, the .mp3 file will start playing immediately and the command will complete immediately. So if you play 100 mp3's in quick succession you'll only hear the last one. Similarly, if you open files in explorer, the command is complete when the explorer window has opened, not when you have finished interacting with the window. If you use exec() to open 100 files in explorer, you'll get 100 open windows.

This example is recursive because we're using a wildcard pattern to filter parentpath, which is satisfied by objects in the 'MyProject' directory tree at any depth.

E.g. Open in File Explorer the directory that has the most Excel spreadsheets

SELECT exec('explorer', parentpath), count(*) FROM files WHERE extension like '.xls%' GROUP BY parentpath ORDER BY count(*) DESC LIMIT 1;

This query counts files which have an extension starting '.xls' (so it matches '.xls' and '.xlsx' files), and groups the count by parent directory. Then it sorts the parent directories by this count and returns just one row - the parent directory with the most Excel files. Finally the query runs 'explorer' to open that directory in File Explorer.

E.g. Play a random Jack Johnson song

SELECT exec('start', 'wmplayer', fullpath) FROM files WHERE fullpath like '%jack%' and fullpath like '%jo%son%' and extension = '.mp3'ORDER BY random() LIMIT 1;

This query returns .mp3 files which have 'jack' and 'johnson' or 'jonson' somewhere in the filename or the directory name. The results are sorted randomly and the first row is returned. The exec() call uses the Windows start command to open windows media player on the file at fullpath. If you haven't set up media player, substitute 'itunes' or similar.

Run 'command' 1000 times

SELECT exec('command')FROM iterator WHERE i <= 1000;

This is the generic template for running a command multiple times.

iterator is a virtual table with one field i which goes from 1 to 1000000000

Unzip files

The open source utility 7za.exe supports unpacking .zip files at the Windows command line. It is distributed with Crab, you can find it at CrabHome\CrabData\7za.exe.

By default exec() writes every command that is run to your Command Line window, together with its output. If you are running hundreds of thousands or millions of commands this will be slow, and the screen will be a mess.

You can resolve this by throwing away the output, use the following command before running the query:

%output NUL

Any error messages will still go to the Command Line window, as will subsequent CRAB> prompts

To get output back do this:

%output stdout

If you want to improve query speed and keep a log of the output, use a filename in place of NUL, e.g.

%output 'C:\somepath\MyLogFile.txt'

Tip: Check if files are still present with pathexists()

Crab doesn't track files that are moved or deleted after a scan. To check that a file or directory is still present at query run time use the pathexists() function.

E.g. to unzip a bunch of files when some have been moved or deleted since the last scan:

Because exec() always runs in a shell on Windows, you can use pipes and I/O redirection. If you build strings to pass to the shell you must concatenate double quotes around names and paths to avoid problems with any that contain spaces. E.g.