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|
|1||C:\MyDocs\FamilyTree\Holden-Cudworth_Wilson-Wason\Scrapbook\Holden, James\Holden, James – Ontario Observer published by, 1858.png|
|1||C:\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:
|sqlite3.exe||Command line shell for SQLite (download file sqlite-tools-… under “Precompiled Binaries for yourOS” at https://www.sqlite.org/download.html; 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)|
|Excel.lnk||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)|
|MediaFilesStatus.lnk||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
|MediaFilesStatus.bat.bak||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.txt||The directory listing of files under the Multimedia folder path (transient)|
|MediaFilesStatus.sql||SQL query generated by the batch file (transient)|
|MediaFilesStatus.db3||SQLite database with one table with one field filled with the content from ScrapbookFilesList.txt (transient)|
|MediaFilesStatus.txt||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:
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 < MediaFilesStatus.sql REM REM Open report file with Excel excel.lnk MediaFilesStatus.txt REM END
One Reply to “Media Files Status #filenames #media #links #broken”
There’s a discussion about this procedure in the Forum at https://sqlitetoolsforrootsmagic.com/forum/topic/managing-surplus-media-media/#postid-388 that touches on pitfalls, very large database, OneDrive and it has prompted some revisions in the description on this page to make it clearer.