Scrapbook Files Status #filenames #media #links #broken

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

Leave a Reply