I collect images and files in sporadic flurries under my Scrapbook folder intending to use them in my RootsMagic 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 Scrapbook folder and flag those that are already used by my RootsMagic database. Then I could work my way through the rest and get them referenced in the database. 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:
|C:MyDocsFamilyTreeHolden-Cudworth_Wilson-WasonScrapbookHolden, JamesHolden, James – OntObs adv selling bldg 1863-05-28.png|
|1||C:MyDocsFamilyTreeHolden-Cudworth_Wilson-WasonScrapbookHolden, JamesHolden, James – Ontario Observer published by, 1858.png|
|1||C:MyDocsFamilyTreeHolden-Cudworth_Wilson-WasonScrapbookHolden, JamesHolden, James – Passenger – SS Austrian 2 Sep 1869.pdf|
|C:MyDocsFamilyTreeHolden-Cudworth_Wilson-WasonScrapbookHolden, JamesHolden, James – bio – Port Perry-Scugog Twp Heritage Gallery.odt|
|C:MyDocsFamilyTreeHolden-Cudworth_Wilson-WasonScrapbookHolden, JamesHolden, 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 are stored in the same folder as my RM database and my Multimedia folder is beneath the database folder and named “Scrapbook”. You will have to adapt for your configuration. The permanent and transient files are:
|sqlite3.exe||Command line shell for SQLite|
|Excel.lnk||Shortcut to Excel|
|ScrapbookFilesStatus.lnk||Shortcut to the batch file with the RootsMagic filename passed as a parameter|
|ScrapbookFilesStatus.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)
|ScrapbookFilesList.txt||The directory listing of files under the Multimedia folder path (transient)|
|ScrapbookFilesStatus.sql||SQL query generated by the batch file (transient)|
|ScrapbookFilesStatus.db3||SQLite database with one table with one field filled with the content from ScrapbookFilesList.txt (transient)|
|ScrapbookFilesStatus.txt||The report in tab-delimited format|
Here’s the batch file, downloadable from the link above:
ECHO OFF REM ScrapbookFilesStatus.bat REM Tom Holden 13 Mar 2011 REM Lists files under the path defined in a RootsMagic 4 database for media files REM and flags if they are used by the database. REM Command line: ScrapbookFilesStatus RootsMagicFileName.rmgc REM REM List files under the media path and store to a file. DIR Scrapbook*.* /B /S /ON > ScrapbookFilesList.txt REM REM Erase old sql file and database file ERASE ScrapbookFilesStatus.sql ERASE ScrapbookFilesStatus.db3 REM Build a SQL query and save to a file ECHO CREATE TABLE FILES(file); > ScrapbookFilesStatus.sql ECHO .import ScrapbookFilesList.txt FILES >> ScrapbookFilesStatus.sql ECHO ATTACH DATABASE '%1' AS RM ; >> ScrapbookFilesStatus.sql ECHO .mode tabs >> ScrapbookFilesStatus.sql ECHO .output ScrapbookFilesStatus.txt >> ScrapbookFilesStatus.sql ECHO SELECT LIKE(FILE,MediaPath^|^|MediaFile) AS USED, FILE FROM FILES LEFT JOIN MultimediaTable ON (FILE LIKE MediaPath^|^|MediaFile) >> ScrapbookFilesStatus.sql ECHO WHERE FILE NOT LIKE '%%.THU' AND FILE LIKE '%%.___%%' ORDER BY FILE; >> ScrapbookFilesStatus.sql REM SQL file building finished REM REM Execute SQL to generate report file sqlite3.exe ScrapbookFilesStatus.db3 < ScrapbookFilesStatus.sql REM REM Open report file with Excel excel.lnk ScrapbookFilesStatus.txt END