Bulk Move, Copy and Rename Files or Directories

BULK MOVE
  Move files
    Move files to 'DestinationDirectory', keeping original names
    Move files to 'DestinationDirectory', giving new names
      E.g. Move every Excel file into a flat destination directory. Version One: Don't move files that would overwrite
      E.g. Move every Excel file into a flat destination directory. Version Two: Give each file a unique name
      E.g. Recursively move all files in a directory except 'DoNotMoveMe.txt', to a flat destination directory
      E.g. Recursively move all files in 'SourceDirectory' except those in subdirectory 'DoNotMoveMyContents', to one flat directory
      E.g. Organise Photos into directories by date
  Move directories
    Move directories into 'DestinationDirectory', keeping original names
    Move directories into 'DestinationDirectory', giving new names
      E.g. Move all files and subdirectories in a directory, except 'DoNotMove', to a destination directory
      E.g. Move all subdirectories of 'SourceDirectory' except 'DoNotMove', to a destination directory
      E.g. Move all subdirectories of 'SourceDirectory' to a destination directory, appending date of move to their names
BULK COPY
  Copy files to flat directory
    Copy files to 'DestinationDirectory', keeping original names
    Copy files to 'DestinationDirectory', giving new names
      E.g. Back up all .pdf files to a directory on Dropbox
      E.g. Copy every file from directory 'SourceDir' to 'TargetDir' without overwriting
      E.g. Recursively copy every file from 'MyProject' to 'Backup' directory, appending backup date to each filename
      E.g. Recursively copy all files in 'SourceDirectory' except 'DoNotMoveMe.txt', to a flat destination directory
      E.g. Recursively copy all files in 'SourceDirectory' except those in subdirectory 'DoNotMoveMyContents', to one flat directory
  Copy directory tree without contents
      E.g. Copy a directory tree from 'MyProject' to 'MyNewProject' without the files
  Copy files from one directory tree to another
      E.g. Copy files from directory tree in 'MyProject' to copy of directory tree in 'MyNewProject'
BULK RENAME
  Rename files or directories
  Rename files
    E.g. Recursively rename all files in 'MyProject' to lower case
    E.g. Recursively append 'V1' to the end of every filename
    E.g. Replace 'V1' in every filename by 'V2' for the files in 'MyProject'. Not recursive.
    E.g. Recursively convert '.html' extensions to '.txt' extensions for all files in directory
  Rename directories
    E.g. Recursively remove spaces from the names of any files or directories inside the 'MyProject' directory
  Regex transformation of file names
    E.g. Fix Dropbox Conflicted Copies.
TIPS

Bulk Move

Use Crab's exec() function to invoke the Windows 'move' 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.

Use 'move' with its /y option, meaning yes, go ahead and overwrite, so as to suppress run-time prompts: exec() doesn't display them.

If you don't want to overwrite objects use Crab's pathexists() function in your query's WHERE clause, to check the destination directory doesn't already have a file or directory with the same name. This interrogates the filesystem at query runtime to return current information.

Move files

Move files to 'DestinationDirectory', keeping original names


SELECT exec('move', '/y', fullpath, 'C:\somepath\DestinationDirectory\')
FROM files
WHERE ...


  • Use the 'move' command's /y option so as to suppress run-time prompts: exec() doesn't display them.

  • Specify which files to move using the WHERE clause.
    No WHERE clause means move every object scanned

  • Objects with the same name will be overwritten. To avoid this add

    WHERE not pathexists('C:\somepath\DestinationDirectory\' || name)

  • To move only files (If you want to move directories, see "Move Directories" later this page)

    WHERE type = 'f'

  • To move direct children of 'SourceDirectory', i.e. not recursive

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

    To move all children of 'SourceDirectory,' i.e. recursive

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

  • The destination directory must already exist.

Move files to 'DestinationDirectory', giving new names


SELECT exec('move', '/y', fullpath, 'C:\somepath\DestinationDirectory\' || NewName)
FROM files
WHERE ...


  • Use the 'move' command's /y option so as to suppress run-time prompts: exec() doesn't display them.

  • Concatenate the new name after the DestinationDirectory path. This is usually a transformation of the original name, see files table fields: name, basename and extension

  • Specify which files to move using the WHERE clause.
    No WHERE clause means move every object scanned.

  • Objects with the same name will be overwritten. To avoid this add

    WHERE not pathexists('C:\somepath\DestinationDirectory\' || NewName)

  • To move only files (If you want to move directories, see "Move Directories" later this page)

    WHERE type = 'f'

  • To move direct children of 'SourceDirectory', i.e. not recursive

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

    To move all children of 'SourceDirectory,' i.e. recursive

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

  • The destination directory must already exist.

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


SELECT exec('move', '/y', fullpath, 'C:\somepath\AllMySpreadsheets\')
FROM files
WHERE extension like '.xls%' and not pathexists('C:\somepath\AllMySpreadsheets\' || name);

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 call to pathexists() 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


SELECT exec('move', '/y', fullpath, 'C:\somepath\AllMySpreadsheets\' ||
basename || '-' || hex(randomblob(16)) || extension)
FROM files
WHERE extension like '.xls%';

There is no restriction on fullpath or parentpath in the WHERE clause so every Excel file scanned will be moved.

This query renames each file as it is copied by appending a random 32 character hex string to each filename to ensure every name is unique.

E.g. Recursively move all files in a directory except 'DoNotMoveMe.txt', to a flat destination directory


SELECT exec('move', '/y', fullpath, 'C:\somepath2\destinationDirectory\')
FROM files
WHERE parentpath like 'C:\somepath\sourceDirectory\%'
and type = 'f'
and name != 'DoNotMoveMe.txt'
and not pathexists('C:\somepath2\destinationDirectory\' || name);

E.g. Recursively move all files in 'SourceDirectory' except those in subdirectory 'DoNotMoveMyContents', to one flat directory


SELECT exec('move', '/y', fullpath, 'C:\somepath2\DestinationDirectory\')
FROM files
WHERE parentpath like 'C:\somepath\SourceDirectory\%'
and type = 'f'
and parentpath not like '%\DoNotMoveMyContents\%'
and not pathexists('C:\somepath2\DestinationDirectory\' || name);

E.g. Organise Photos into directories by date


Step One Make a directory for each year and month from the photo modification dates


SELECT exec('mkdir', 'C:\somepath2\Albums\' || strftime('Photos %Y-%m', modified))
FROM files
WHERE parentpath like 'C:\somepath\Camera Uploads\%'
and type = 'f'
GROUP BY strftime('Photos %Y-%m', modified);

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

and not pathexists('C:\somepath2\Albums\' || strftime('Photos %Y-%m', modified))


Step Two Move each photo to the appropriate directory


SELECT exec('move', '/y', fullpath, 'C:\somepath2\Albums\' || strftime('Photos %Y-%m', modified))
FROM files
WHERE parentpath like 'C:\somepath\Camera Uploads\%'
and type = 'f'
and not pathexists('C:\somepath2\Albums\' || strftime('Photos %Y-%m', modified) || '\' || name);

If multiple photos taken in the same month have the same filename, only the first one will be moved. Later files will fail the pathexists() check.

Move directories

Avoid recursive queries, moving a directory will move its contents - your query should not also search inside it for things to move.

When moving directories use Crab's notbs() function to remove trailing backslashes from the fullpath of the directory you want to move e.g. notbs(fullpath).
(Windows 'move' gives an error if the source path ends with a backslash.)

If you're renaming a directory while moving it, don't add a trailing backslash after the new name.
(Windows 'move' gives an error here too)

    ALLOWED:      C:\somepath\DestinationDirectory\NewName

    NOT ALLOWED:      C:\somepath\DestinationDirectory\NewName\

Move directories into 'DestinationDirectory', keeping original names


SELECT exec('move', '/y',  notbs(fullpath), 'C:\somepath\DestinationDirectory\')
FROM files
WHERE ...


  • Use the 'move' command's /y option so as to suppress run-time prompts: exec() doesn't display them.

  • Use Crab's notbs() function on the fullpath of the source directory to remove its trailing backslash

    Windows 'move' gives an error if the source path ends with a backslash. notbs() will remove it if there is one, and will return the string unchanged if not. If you're moving a mix of files and directories, its ok to use notbs() on both kinds of fullpath.

  • Specify which directories to move using the WHERE clause.
    No WHERE clause means move every object scanned.

  • Objects with the same name will be overwritten. To avoid this add

    WHERE not pathexists('C:\somepath\DestinationDirectory\' || name)

  • To move only directories

    WHERE type = 'd'

  • To move direct children of 'SourceDirectory', i.e. not recursive

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

  • Recursive queries are not recommended when moving directories.

  • The destination directory must already exist.

Move directories into 'DestinationDirectory', giving new names


SELECT exec('move', '/y', notbs(fullpath), 'C:\somepath\DestinationDirectory\' || NewName)
FROM files
WHERE ...


  • Use the 'move' command's /y option so as to suppress run-time prompts: exec() doesn't display them.

  • Use Crab's notbs() function on the fullpath of the source directory to remove its trailing backslash

    Windows 'move' gives an error if the source path ends with a backslash. notbs() will remove it if there is one, and will return the string unchanged if not.

  • Concatenate the new directory name to the DestinationDirectory path. Do not add a backslash after the name

    Windows 'move' gives an error if the full path for a new directory ends with a backslash. If moving multiple directories the new name is usually a transformation of the original name. (See files table fields name, basename and extension)

  • Specify which directories to move using the WHERE clause.
    No WHERE clause means move every object scanned.

  • Objects with the same name will be overwritten. To avoid this add

    WHERE not pathexists('C:\somepath\DestinationDirectory\' || name)

  • To move only directories

    WHERE type = 'd'

  • To move direct children of 'SourceDirectory', i.e. not recursive

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

  • Recursive queries are not recommended when moving directories.

  • The destination directory must already exist.

E.g. Move all files and subdirectories in a directory, except 'DoNotMove', into a destination directory


SELECT exec('move', '/y', notbs(fullpath), 'C:\somepath2\DestinationDirectory\')
FROM files
WHERE name != 'DoNotMove'
and parentpath = 'C:\somepath\SourceDirectory\';

parentpath is used to identify direct children of the source directory, ie this query isn't recursive.

Files or directories with matching names will be overwritten.

E.g. Move all subdirectories of 'SourceDirectory' except 'DoNotMove', into a destination directory


SELECT exec('move', '/y', notbs(fullpath), 'C:\somepath2\DestinationDirectory\')
FROM files
WHERE name != 'DoNotMove'
and parentpath = 'C:\somepath\SourceDirectory\'
and type = 'd'
and not pathexists('C:\somepath2\DestinationDirectory\' || name);

parentpath is used to identify direct children of the source directory, ie this query isn't recursive.

Only type = 'd', ie directories, are moved.

Overwriting objects in destination directory is prevented by pathexists() check.

E.g. Move all subdirectories of 'SourceDirectory' into a destination directory, appending date of move to their names


SELECT exec('move', '/y', notbs(fullpath), 'C:\somepath2\DestinationDirectory\' || name || '-' || date('now'))
FROM files
WHERE name != 'DoNotMove'
and parentpath = 'C:\somepath\SourceDirectory\'
and type = 'd'
and not pathexists('C:\somepath2\DestinationDirectory\' || name || '-' || date('now'));

parentpath is used to identify direct children of the source directory, ie this query isn't recursive.

Only type = 'd', ie directories, are moved.

Overwriting objects in destination directory is prevented by pathexists() check.

Bulk Copy

Use Crab's exec() function to invoke the Windows 'copy' command when you want 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'.

Use the 'copy' command's /y option, meaning yes, go ahead and overwrite, so as to suppress run-time prompts: exec() doesn't display them.

If you don't want to overwrite objects use Crab's pathexists() function in your query's WHERE clause, to check the destination directory doesn't already have a file or directory with the same name. This interrogates the filesystem at query runtime to return current information

Copy files to flat directory

Copy files to 'DestinationDirectory', keeping original names


SELECT exec('copy', '/y', fullpath, 'C:\somepath\DestinationDirectory\')
FROM files
WHERE ...


  • Use the 'copy' command's /y option to suppress run-time prompts, because exec() won't display them.

  • Specify which files to copy using the WHERE clause.
    No WHERE clause means copy every object scanned.

  • Objects with the same name will be overwritten. To avoid this add

    WHERE not pathexists('C:\somepath\DestinationDirectory\' || name)

  • To copy only files (If you want to copy directories, see "Copy directory tree" later this page)

    WHERE type = 'f'

  • To copy direct children of 'SourceDirectory', i.e. not recursive

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

    To copy all children of 'SourceDirectory,' i.e. recursive

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

  • The destination directory must already exist.

Copy files to 'DestinationDirectory', giving them new names


SELECT exec('copy', '/y', fullpath, 'C:\somepath\DestinationDirectory\' || NewName)
FROM files
WHERE ...


  • Use the 'copy' command's /y option to suppress run-time prompts, because exec() won't display them.

  • Concatenate the new name after the DestinationDirectory path. This is usually a transformation of the original name, see files table fields: name, basename and extension

  • Specify which files to copy using the WHERE clause.
    No WHERE clause means copy every object scanned.

  • Objects with the same name will be overwritten. To avoid this add

    WHERE not pathexists('C:\somepath\DestinationDirectory\' || NewName)

  • To copy only files (If you want to copy directories, see "Copy directory tree" later this page)

    WHERE type = 'f'

  • To copy direct children of 'SourceDirectory', i.e. not recursive

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

    To copy all children of 'SourceDirectory,' i.e. recursive

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

  • The destination directory must already exist.

E.g. Back up all .pdf files to a directory on Dropbox


SELECT exec('copy', '/y', fullpath, 'C:\Users\johnsmith\Dropbox\PdfLibrary\')
FROM files
WHERE extension = '.pdf'
and not pathexists('C:\Users\johnsmith\Dropbox\PdfLibrary\'||name);

This query uses pathexists() to avoid overwriting.

There is no path filter in the WHERE clause so every .pdf file scanned will be copied, unless it will overwrite.

The query doesn't specify type = 'f', because it's checking for a .pdf extension, which is not likely for a directory.

E.g. Copy every file from directory 'SourceDir' to 'TargetDir' without overwriting


SELECT exec('copy', '/y', fullpath, 'C:\somepath2\TargetDir\')
FROM files
WHERE type = 'f'
and parentpath = 'C:\somepath\SourceDir\'
and not pathexists('C:\somepath2\TargetDir\'||name);

This isn't recursive, only direct children are copied because the WHERE clause specifies files with a parentpath equal to 'SourceDir'.

The call to pathexists() means files will not be copied if they're already in 'TargetDir'

E.g. Recursively copy every file from 'MyProject' to 'Backup' directory, appending backup date to each filename


SELECT exec('copy', '/y', fullpath, 'C:\somepath2\Backup\' || basename || '-' || date('now') || extension)
FROM files
WHERE type = 'f'
and parentpath like 'C:\somepath\MyProject\%';

Files with the same name will be overwritten. Add a pathexists() check for the new name to prevent this.

E.g. Recursively copy all files in 'SourceDirectory' except 'DoNotMoveMe.txt', to a flat destination directory


SELECT exec('copy', '/y', fullpath, 'C:\somepath2\DestinationDirectory\')
FROM files
WHERE parentpath like 'C:\somepath\SourceDirectory\%'
and type = 'f'
and name != 'DoNotMoveMe.txt'
and not pathexists('C:\somepath2\DestinationDirectory\' || name);

The pathexists() check means that files with the same name won't be overwritten.

E.g. Recursively copy all files in 'SourceDirectory' except those in subdirectory 'DoNotMoveMyContents', to one flat directory


SELECT exec('copy', '/y', fullpath, 'C:\somepath2\DestinationDirectory\')
FROM files
WHERE parentpath like 'C:\somepath\SourceDirectory\%'
and type = 'f'
and parentpath not like '%\DoNotMoveMyContents\%'
and not pathexists('C:\somepath2\DestinationDirectory\' || name);

The pathexists() check means that files with the same name won't be overwritten.

Copy directory tree without contents

To copy a set of directories without their contents, just recreate each of them using exec() with 'mkdir' and the appropriate path. Create parent directories before their subdirectories using ORDER BY depth. If you need to set permissions, use the Windows utility icacls

E.g. Copy a directory tree from 'MyProject' to 'MyNewProject' without the files


This query creates directory 'MyNewProject' and a bunch of directories below it, to match the directory tree below 'MyProject'.

SELECT length('C:\Users\johnsmith\MyProject\');
{length('C:\Users\johnsmith\MyProject\'): 29}


SELECT exec('mkdir', 'C:\Users\johnsmith\MyNewProject\' || substr(fullpath,30))
FROM files
WHERE fullpath like 'C:\Users\johnsmith\MyProject\%'
and type = 'd'
and not pathexists('C:\Users\johnsmith\MyNewProject\' || substr(fullpath,30))
ORDER BY depth;


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 'C:\Users\johnsmith\MyProject\' and substitute 'C:\Users\johnsmith\MyNewProject\'. In this example the part of each fullpath below the original root is substr(fullpath,30).

N.B We're filtering by

fullpath like 'C:\Users\johnsmith\MyProject\%'

instead of

parentpath like 'C:\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'


SELECT length('C:\Users\johnsmith\MyProject\');
{length('C:\Users\johnsmith\MyProject\'): 29}


SELECT exec('copy', '/y', fullpath, 'C:\Users\johnsmith\MyNewProject\' || substr(fullpath,30))
FROM files
WHERE parentpath like 'C:\Users\johnsmith\MyProject\%'
and type = 'f';


For each file in the original directory tree we use 'copy' with the /y 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 'C:\Users\johnsmith\myNewProject\' , appended to the part of the path below the original root substr(fullpath,30). The magic number 30 is the start position for the part of the path below the original root (29+1).

N.B. This will overwrite files of the same name that are already in the target directory. Check pathexists() if you prefer to keep existing files.

Bulk Rename

Use Crab's exec() function to invoke the Windows 'rename' 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.

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().

If an object already exists with the new name, the rename command will return an error, and the query will halt. To avoid this use Crab's pathexists() function in your query's WHERE clause. pathexists() interrogates the filesystem at query runtime to return current information.

Rename files or directories


SELECT exec('rename', fullpath, NewName)
FROM files
WHERE ...


  • In place of NewName put your expression for the new name of the file or directory, without a path prefix or any backslashes, just a name. This is usually a transformation of the original name, see files table fields: name, basename and extension

  • Specify which objects to rename using the WHERE clause.
    No WHERE clause means rename every object scanned.

  • If an object already exists with the new name, an error will be raised. To avoid the error add

    WHERE not pathexists(parentpath || NewName)

  • To rename direct children of 'MyProject', i.e. not recursive

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

    To rename all children of 'MyProject,' i.e. recursive

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

  • To rename only files

    WHERE type = 'f'

    To rename only directories

    WHERE type = 'd'

  • If renaming directories, you must rename contents before their parent directories using

    ORDER BY depth DESC

Rename files

E.g. Recursively rename all files in 'MyProject' to lower case

SELECT exec('rename', fullpath, lower(name))
FROM files
WHERE parentpath like 'C:\somepath\MyProject\%'
and type = 'f';

E.g. 'SomeFile.txt' becomes 'somefile.txt'

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

E.g. Recursively append 'V1' to the end of every filename

SELECT exec('rename', fullpath, basename || 'V1' || extension)
FROM files
WHERE parentpath like 'C:\somepath\MyProject\%'
and type = 'f';

E.g. 'SomeFile.txt' becomes 'SomeFileV1.txt'

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

E.g. Replace 'V1' in every filename by 'V2' for the files in 'MyProject'. Not recursive.

SELECT exec('rename', fullpath, replace(name,'V1','V2'))
FROM files
WHERE parentpath = 'C:\somepath\MyProject\'
and name like '%V1%' and type = 'f';

E.g. 'SomeFileV1.txt' becomes 'SomeFileV2.txt'

This query is not recursive because the WHERE clause restricts matches to files with a parentpath equal to the 'MyProject' directory..

E.g. Recursively convert '.html' extensions to '.txt' extensions for all files in directory

SELECT exec('rename', fullpath, basename || '.txt')
FROM files
WHERE parentpath like 'C:\somepath\MyProject\%'
and type = 'f'
and extension = '.html';

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

It's possible, although unlikely, for a directory to have an .html extension, so we've included a check for type = 'f' to restrict the matches to files.

Rename directories

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

SELECT exec('rename', fullpath, replace(name,' ','')) FROM files 
WHERE parentpath like 'C:\somepath\MyProject\%'
ORDER BY depth DESC;

The ORDER BY clause is necessary because we must rename objects before renaming their parent directories. Otherwise 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 renamed as a conflicted copy, e.g. 'somefile (Peter's conflicted copy 2016-09-22).txt'

This query identifies files and their corresponding conflicted copies:


SELECT f1.parentpath, f1.name, f2.name
FROM files f1 JOIN files f2
ON f1.parentpath = f2.parentpath
and f1.name = groupn(f2.name,'(.*) \(.*conflicted copy.*',1)||f2.extension
WHERE f2.name like '%conflicted copy%';


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.


Option 1: Move/delete the files labelled 'conflicted copy'

Here we'll move the conflicted copies to a 'ConflictedCopies' folder.

First create the target directory

CRAB> !mkdir 'C:\somepath\ConflictedCopies'

Then move the conflicted copies there

SELECT exec('move','/y', fullpath,'C:\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 originals

The other option is to move the 'live copy', files to a 'ConflictedCopyOriginals' folder and remove the 'conflicted copy' text from the conflicted copies

First create the target directory

CRAB> !mkdir 'C:\somepath\ConflictedCopyOriginals'

Move any original files which have matching conflicted copies

SELECT exec('move','/y', f1.fullpath, 'C:\somepath\ConflictedCopyOriginals\'||basename||'-'||fileid||extension)
FROM files f1 JOIN files f2
ON f1.parentpath = f2.parentpath
and f1.name = groupn(f2.name,'(.*) \(.*conflicted copy.*',1)||f2.extension
WHERE f2.name like '%conflicted copy%';

Finally rename the conflicted copies to remove the conflict text

SELECT exec('rename', fullpath, groupn(name,'(.*) \(.*conflicted copy.*',1) || extension )
FROM files
WHERE name like '%conflicted copy%';

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


Crab doesn't track files that are moved or deleted after a scan. If you try to copy 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 copy are still present at query run time using pathexists()

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


SELECT exec('copy', '/y', fullpath, 'C:\somepath2\TempFileSnapshot\')
FROM files
WHERE parentpath like 'C:\somepath\MyProject\%' and extension = '.tmp' and pathexists(fullpath);

Tip: Test exec() queries by running them with the echo command


Prior to running a query that will modify the filesystem you should prefix your exec() function call with an echo command to check exactly which commands will be run on which files. E.g.


SELECT exec('echo', 'rename', fullpath, replace(name,'V1','V2'))
FROM files
WHERE fullpath like 'C:\somepath\MyProject\%'
and name like '%V1%' and type = 'f';


Once you're happy with the result, remove the 'echo':

SELECT exec('rename', fullpath, replace(name,'V1','V2'))
FROM files
WHERE fullpath like 'C:\somepath\MyProject\%'
and name like '%V1%' and type = 'f';




Tip: Suppress exec() screen output


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

You can speed things up by throwing away this 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 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 NUL, e.g.


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


© 2017 Etia UK