Bulk Move

Use Crab's exec() function to invoke the 'mv' command to move multiple files or directories based on text in the path, the name, the file extension, or other metadata such as file size or modification date.

E.g. Move every Excel file into a flat destination directory. Version One: Don't move files that would overwrite

There is no restriction on fullpath or parentpath in the WHERE clause so every Excel file scanned will be moved unless there is a name collision. The -n option prevents files being moved if they would overwrite another object.

E.g. Move every Excel file into a flat destination directory. Version Two: Give each file a unique name

The GROUP BY clause runs the exec function once for each year and month combination string, so the 'mkdir' command doesn't try to create duplicate directories.

N.B If there is a chance that any of these directories might already exist, add the following line to the WHERE clause, to prevent query execution halting due to an 'already exists' error from the 'mkdir' command

Bulk Copy

Use Crab's exec() function to invoke the 'cp' command to copy multiple files based on text in the path, the name, the file extension, or other metadata such as file size or modification date. To copy an empty directory tree, use exec() with 'mkdir'.

For each directory in the original tree we use 'mkdir' to create a new directory with a full path that we specify.

These new paths are made from the fullpath of each directory in the original tree, by chopping off that part of the path that is the root of the original tree, and substituting the root of the new tree. e.g. chop off '/Users/johnsmith/MyProject/' and substitute '/Users/johnsmith/MyNewProject/'. In this example the part of each fullpath below the original root is substr(fullpath,28). N.B We're filtering by fullpath like '/Users/johnsmith/MyProject/%'instead ofparentpath like '/Users/johnsmith/MyProject/%'because the fullpath filter includes the 'MyProject' directory itself, so the code will also create the root of the new tree, 'MyNewProject'

Copy files from one directory tree to another

To populate the directory tree we copied in the query above:

E.g. Copy files from directory tree in 'MyProject' to copy of directory tree in 'MyNewProject'

For each file in the original directory tree we use 'cp' with the -f option from the file's existing fullpath to the target path. The target path is a string made from the root of the new directory tree '/Users/johnsmith/MyProject/' , appended to the part of the path below the original root substr(fullpath,28). The magic number 28 is the start position for the part of the path below the original root (27+1).

N.B. This will overwrite files of the same name that are already in the target directory, because of the -f option. Use the -n option instead if you prefer to keep existing files.

Bulk Rename

Use Crab's exec() function to invoke the 'mv' command when you want to rename multiple files or directories based on text in the path, the name, the file extension, or other metadata such as file size or modification date.

Use 'mv' withits -f option (which means always overwrite) or -n option (which meansnever overwrite), so as to suppress run-time prompts: exec() doesn't display them.Often you'll want to transform the original object name to construct the new name. The files table fields name, basename, and extension are useful for this, as are the SQLite string functions replace() and substr() and the concatenation operator || (two pipe symbols). For regex transformations look at the Crab functions groupn() and matchn().

Remember to scan again after making changes to the filesystem to update Crab's indexes.

E.g. Recursively remove spaces from the names of any files or directories inside the 'MyProject' directory

The ORDER BYclauseis necessary because we must rename objects before renaming their parent directories. If we don't do this, renaming some objects would fail, because their fullpath recorded in the scan data would be out of date.

There is no filter by type in the where clause, and no filter by extension, so this applies to both files and directories.

The search is recursive because the wildcard pattern for parentpath will match objects below 'MyProject' at any depth.

Regex transformation of file names

More complicated transformations are possible using Crab's regex functions matchn(), which returns matches for a regex pattern and groupn(), which returns matches for a specified group in a regex pattern. See Option 2 in the example below.

E.g. Fix Dropbox Conflicted Copies.

When Dropbox detects that a file, say somefile.txt, has been changed in two places at once, the last copy to sync gets a conflicted copy name like 'somefile (Peter's conflicted copy 2016-09-22).txt'

The query looks for files whose names include the text 'conflicted copy', and uses Crab's regex function groupn() to extract the original filename. Then it identifies files with the same parentpath which match that original filename.

To process the conflicted copies there are two options: 1) move/delete the files labelled 'conflicted copy', or 2) move/delete the 'live' copies and rename the 'conflicted copy' files to make them live.

N.B. Review the files before processing conflicted copies. It may be that for some files you want Option 1, and for others you want Option 2. Be careful of applications such as Scrivener that index the contents of files, recovering conflicted copies may make program data inconsistent.

SELECT exec('mv','-n', fullpath, '/somepath/ConflictedCopies/'||basename||'-'||fileid||extension)FROM files WHERE name like '%conflicted copy%';

In this query the fileid is appended to each filename to prevent name collisions. The fileid is a unique number for each file scanned, actually the files table row number.

Option 2: Make live the files labelled 'conflicted copy' and move/delete the originalsThe other option is to we'll move the 'live copy', files to a 'ConflictedCopyOriginals' folder and remove the 'conflicted copy' text from the conflicted copies.First create the target directory

Tip: Suppress exec() echo to screenBy default exec() writes every command that is run to your Terminal window, together with its output. If you are running hundreds of thousands or millions of commands this will be slow.

You can speed things up by throwing away the output. Use the following command before running the query:

%output /dev/null

Any error messages will still go to the Terminal window, as will subsequent CRAB>promptsTo restore output do this:

%output stdout

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

%output '/somepath/MyOutputLog.txt'

Tip: Check if files or directories are still present with pathexists()Crab doesn't track files that are moved or deleted after a scan. If you try to move a file that isn't there any more, the query will stop with an error. To prevent this, check that the files or directories you want to move are still present at query run time using pathexists()E.g. to move a bunch of files when some have been deleted since the last scan: