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”:

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:

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:

 ECHO OFF
 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
 REM List files under the media path and store to a file.
 DIR %2 /B /S /ON > MediaFilesList.txt
 REM
 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
 REM Execute SQL to generate report file
 sqlite3.exe MediaFilesStatus.db3 &lt; MediaFilesStatus.sql
 REM
 REM Open report file with Excel
 excel.lnk MediaFilesStatus.txt
 REM END