This page is in response to a question posed in the RootsMagic Forums by member DeliChef in the topic Bulk File Name Change For Hundreds of Family Letters?. He had named the files using the format MM_DD_YYYYPersonName, i.e., the date of the letter followed by name of the sender. To improve the utility of Windows File Explorer, he wanted to change the format to YYYY_MM_DDPersonName so they would sort by name chronologically. They were already linked to the RootsMagic Media Gallery and renaming the files would break the links. The Fix Broken Media Links tool is of no help in this case and manually changing the media item for hundreds of files was too daunting. So he sought help. This page describes a solution that could be adapted to other systematic name revisions.

This solution uses SQLite to query the MultiMediaTable for file names beginning with the MM-DD-YYYY format and generates a set of Windows command line statements that copy these files to a new location with their rearranged names. It also produces a set of SQLite statements that update the MultiMediaTable with the new path and filenames.

N.B., the script was written with “-” instead of “_” as the separator between the date elements and should be edited accordingly to suit your particular needs. Any systematic name pattern can be changed to another pattern.

Here’s an example of the commands produced to relocate and rename the files, copied from the vCmd View created in the database by the script
:

XCOPY "C:SDGoogle Drivegenealogymedia7-25-1900Shaw(Wright)Letitia-PlayCopy.jpg" "C:SDGoogle DrivegenealogymediaNewFolder1900-07-25Shaw(Wright)Letitia-PlayCopy.jpg"
XCOPY "C:SDGoogle Drivegenealogymedia12-01-1949Secombe, Harry (sure).jpg" "C:SDGoogle DrivegenealogymediaNewFolder1949-12-01Secombe, Harry (sure).jpg"
...

and the corresponding SQLite statements to relink the renamed files are copied from the vSQL View created in the database by the script:

BEGIN;
UPDATE MultiMediaTable SET MediaPath = 'C:SDGoogle DrivegenealogymediaNewFolder', MediaFile = '1900-07-25Shaw(Wright)Letitia-PlayCopy.jpg' WHERE MediaID = 18;
UPDATE MultiMediaTable SET MediaPath = 'C:SDGoogle DrivegenealogymediaNewFolder', MediaFile = '1949-12-01Secombe, Harry (sure).jpg' WHERE MediaID = 19;
...
COMMIT;

The XCOPY commands are copied into a DOS batch (.bat) or Windows command line script (.cmd). It is advisable to create the desired destination folder before running this file from the Command Language Interpreter because it may ask whether the target is a folder or a file if the folder does not exist. The correct answer is “file” for which it will create the missing folder(s) in the path.

The SQLite statements are copied into the SQL editor of a SQLite manager such as SQLiteSpy and executed on the database.

The script creates 4 temporary SQLite Views in the RootsMagic database:

• vOld – holds the MediaID, file path and file name of each file name that matches the MM-DD-YYYY… filename format
• vNew – holds the MediaID, new path and new file name for each file in vOld
• vCmd – holds the XCOPY command line statements built from the fields in vOld and vNew
• vSQL – holds the SQLite UPDATE statements created from vNew

MediaShuffle.sql

Usage

1. Open your database (best on a copy to be safe) with SQLiteSpy or other SQLite manager that supports the REGEXP function.
2. Create the NewFolder using File Explorer to match what the script generates (or vice versa).
3. Load and edit the MediaShuffle.sql script to match your desired new folder name and any differences in the pattern of filenames you wish to match.
4. Execute the script.
5. Copy all the Cmd lines from the vCmd View (Ctrl-A, Ctrl-C) in SQLiteSpy to a text file with the .cmd extension.
6. Open the Windows Command Line Interface (Command prompt) on the folder to which you saved the .cmd file and execute it (type the name of the file and press enter). That should result in the renamed files arriving in your folder.
7. Copy all the SQLite statements from the vSQL View the script generates into the SQLiteSpy SQL editor and execute against the database. When you open your database in RootsMagic, the Media Gallery will now be pointing to the renamed copies of the files.
8. When you close the database from SQLiteSpy, the temporary Views will be deleted.

This takes longer to describe than to execute. The slowest part of the process will be the copying of the files. Alternatively, the script could be revised to RENAME files (faster but a little more daring…)