Media Files Status #filenames #media #links #broken

I collect images and files in sporadic flurries under each RootsMagic database’s Media folder intending to use them in the database later on, when I have time. Naturally, I get sidetracked and forget about some of them. So I thought it would be desirable to have a report that listed all the files under the Media folder and flag those that are already used by the relevant RootsMagic database. Then I could work my way through the rest and get them referenced in the database or moved or deleted. There are many ways this might be done, even without SQLite, by starting with RM’s built-in Multimedia List report. That would be outside the intent of this Wiki. Besides, this SQLite query produces the desired report in the blink of an eye!

Here’s a sample for which the Media folder for this database was named “Scrapbook”:

 C:\MyDocs\FamilyTree\Holden-Cudworth_Wilson-Wason\Scrapbook\Holden, James\Holden, James – OntObs adv selling bldg 1863-05-28.png
1C:\MyDocs\FamilyTree\Holden-Cudworth_Wilson-Wason\Scrapbook\Holden, James\Holden, James – Ontario Observer published by, 1858.png
1C:\MyDocs\FamilyTree\Holden-Cudworth_Wilson-Wason\Scrapbook\Holden, James\Holden, James – Passenger – SS Austrian 2 Sep 1869.pdf
 C:\MyDocs\FamilyTree\Holden-Cudworth_Wilson-Wason\Scrapbook\Holden, James\Holden, James – bio – Port Perry-Scugog Twp Heritage Gallery.odt
 C:\MyDocs\FamilyTree\Holden-Cudworth_Wilson-Wason\Scrapbook\Holden, James\Holden, James – poet.txt

The ‘1’ flags that the file is referenced by the subject database; its absence indicates the opposite.

The procedure uses Windows shortcuts, the Windows CMD shell to automate some things, the command line utility sqlite3.exe, and launches Excel with the resulting report. In my case, all the files that support this procedure are stored in one folder. The shortcut that calls the batch file may be located elsewhere and multiple shortcuts can be created for each database and associated media folder.

The permanent and transient files are:


Command line shell for SQLite (download file sqlite-tools-… under “Precompiled Binaries for yourOS” at; extract sqlite3.exe which does not have to be in the procedure folder if there is a Windows PATH setting for it or the batch file is revised to have the full path to it)
Shortcut to Excel (copy from the Start menu to the procedure folder or revise the batch file with the full path to the Excel shortcut)

Shortcut to the batch file with the RootsMagic filename and media folder passed as parameters. You create the shortcut; for example:
Target: C:\SD\OneDrive\RootsMagic\MediaFilesStatus\MediaFilesStatus.bat “..\Data\Bradshaw-OneDrive.rmgc” “..\Data\Bradshaw-OneDrive_media”
Start In: C:\SD\OneDrive\RootsMagic\MediaFilesStatus

Windows command shell batch that gets the directory listing, writes and executes a SQL file to generate a report file which it opens with Excel.
(Remove .bak extension after download )
MediaFilesList.txtThe directory listing of files under the Multimedia folder path (transient)
MediaFilesStatus.sqlSQL query generated by the batch file (transient)
SQLite database with one table with one field filled with the content from ScrapbookFilesList.txt (transient)
The report in tab-delimited format (batch file opens it with Excel; you can use Excel to generate CMD shell commands to delete or move unused files)

N.B. I advise using the RM tool “Fix broken media links” before running this procedure because a file that is in a different location than recorded in the database will be reported as unused. You might not want to delete it!

Here’s the batch file, downloadable from the link above:

 REM MediaFilesStatus.bat
 REM Tom Holden 13 Mar 2011
 REM rev 2021-07-04 Media folder parameterized as %2; support double-quotes around calling parameters
 REM Lists files under a folder and sub-folders and checks 
 REM and flags if they are used by a RM4-7 database; does not support RM8.
 REM Command line: MediaFilesStatus RootsMagicFile.rmgc MediaFolder
 REM - full paths or relative to the 'Start in' location, double quoted if space character is in path
 REM List files under the media path and store to a file.
 DIR %2 /B /S /ON > MediaFilesList.txt
 REM Erase old sql file and database file
 ERASE MediaFilesStatus.sql
 ERASE MediaFilesStatus.db3
 REM Build a SQL query and save to a file
 ECHO CREATE TABLE FILES(file); > MediaFilesStatus.sql
 ECHO .import MediaFilesList.txt FILES >> MediaFilesStatus.sql
 ECHO ATTACH DATABASE REPLACE('%1', '^"', '') AS RM ; >> MediaFilesStatus.sql 
 ECHO .mode tabs >> MediaFilesStatus.sql
 ECHO .output MediaFilesStatus.txt >> MediaFilesStatus.sql
 ECHO SELECT LIKE(FILE,MediaPath^|^|MediaFile) AS USED, FILE FROM FILES LEFT JOIN MultimediaTable ON (FILE LIKE MediaPath^|^|MediaFile) >> MediaFilesStatus.sql
 ECHO  WHERE FILE NOT LIKE '%%.THU' AND FILE LIKE '%%.<em>_</em>%%' ORDER BY FILE; >> MediaFilesStatus.sql
 REM SQL file building finished
 REM Execute SQL to generate report file
 sqlite3.exe MediaFilesStatus.db3 &lt; MediaFilesStatus.sql
 REM Open report file with Excel
 excel.lnk MediaFilesStatus.txt

One Reply to “Media Files Status #filenames #media #links #broken

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.