Quote from nconway567 on 2021-07-02, 4:50 pm

Hello - I sort of got this working - I ran this at a command prompt:

sqlite> .read '|ScrapbookFilesStatus.bat'
Could Not Find C:\Users\username\Documents\RootsMagic\Data\ScrapbookFilesStatus.sql
Could Not Find C:\Users\username\Documents\RootsMagic\Data\ScrapbookFilesStatus.db3
Error: near line 6: no such table: MultimediaTable
'excel.lnk' is not recognized as an internal or external command,
operable program or batch file.
Error: near line 2: near "C": syntax error

I end up with ScrapbookfilesStatus.sql, ScrapbookFilesList.txt, ScrapbookFilesStatus.txt and ScrapbookFilesStatus.db3 in my \RM\Data folder.

ScrapbookFilesStatus.txt is empty and ScrapbookFilesList.txt looks like it's a list of every media file in my db based on the size (3.3MB).

I opened up the db3 file and there's 32050 rows, same as my live RM7 MultimediaTable.

 

Not sure what's going on here or what I'm supposed to do next.

thanks for the help!

Nicole

Quote from Tom Holden on 2021-07-02, 4:56 pm

I have my media files stored in a virtual path - I use OneDrive, but my database is stored locally on my hard drive. Do I have to modify the ScrapbookFileStatus.bat file?

For your media files to be accessible to RM, they must be available to it through the Operating System on a lettered drive. With OneDrive, you should have a local OneDrive folder on one of those drives under which your media files will then be accessible. I know OneDrive has options to store in the Cloud and list in the local drive as though they are stored there and a file is only downloaded when an app attempts to access it. AFAIK, that works fine for RM but adds delay awaiting the download to complete.

While ScrapbookFilesStatus.bat was written 10 years ago, I think it should still work fine from the Windows CMD prompt. I just now found an error in the page listing from WordPress treating the backslash "\" as an escape character. And my folder name for media was "Scrapbook" and it was under the folder in which the database and the BAT file were located. So you do have to make adjustments accordingly to the line:

DIR Scrapbook\*.* /B /S /ON > ScrapbookFilesList.txt

Say your media folder for this database is at C:\SD\OneDrive\ConwayTree\Media, then the line should be:

DIR C:\SD\OneDrive\ConwayTree\Media\*.* /B /S /ON > ScrapbookFilesList.txt

 

Quote from Tom Holden on 2021-07-02, 5:04 pm

How do I tell it what my RM7 database file's name is and what directory it's in?

Per the comments in the BAT file, the command line that calls the BAT file should be:

ScrapbookFilesStatus RootsMagicFileName.rmgc

Replace RootsMagicFileName with the name of your RM file if you have the BAT file in the same folder. That's how the BAT file gets to know what database file to process.

If the BAT file is not in the same folder as the database, then you would have to provide either the relative or full path to the .rmgc file on the command line.

Quote from Tom Holden on 2021-07-02, 5:17 pm

Quote from nconway567 on 2021-07-02, 4:50 pm

Hello - I sort of got this working - I ran this at a command prompt:

sqlite> .read '|ScrapbookFilesStatus.bat'
Could Not Find C:\Users\username\Documents\RootsMagic\Data\ScrapbookFilesStatus.sql
Could Not Find C:\Users\username\Documents\RootsMagic\Data\ScrapbookFilesStatus.db3

Looks like you tried starting from the sqlite command prompt, not the Windows CMD prompt. So exit sqlite and use the syntax I explained. The BAT file will call sqlite when needed (although it does appear that some of the CMD commands were executed so maybe there is a valid way of launching from the sqlite prompt).

The two COULD NOT FIND errors are to be expected the first time you run it because CMD is told to ERASE these files that do not exist. I didn't attempt to trap out that condition.

I have not run this procedure in quite a while so there may be more surprises for me.

 

Tom

 

Quote from nconway567 on 2021-07-02, 5:34 pm

Sorry to be confusing - I'm old school - I think of one drive like a Netbios network mapped drive. I did edit the bat file to point to my \user\conway\Pictures\GedcomMedia. I also changed the database name to my filename.rmgc.

I'm running the .bat file out of my Rootsmagic\data folder so I don't have to worry about a path for that.

I'll run it straight from CMD and see what happens.

Quote from nconway567 on 2021-07-02, 5:43 pm

Ok, so here's what I get:

C:\Users\nconw\Documents\RootsMagic\Data>ECHO OFF
Could Not Find C:\Users\nconw\Documents\RootsMagic\Data\ScrapbookFilesStatus.sql
Could Not Find C:\Users\nconw\Documents\RootsMagic\Data\ScrapbookFilesStatus.db3
Error: near line 6: no such table: MultimediaTable
'excel.lnk' is not recognized as an internal or external command,
operable program or batch file.

Obviously, that Error on line 6 is weird - it's finding my database because the ScrapbookFilesList.txt is the same size - 3.3MB and the ScrapbookFilesStatus.db3 is about the same size and filled with valid file names that match my GedcomMedia folder

Quote from Tom Holden on 2021-07-02, 11:45 pm

The "no such table: MultimediaTable" error indicates that sqlite failed to ATTACH the RM database file and so it would fail to output the ScrapbookFilesStatus.txt file or perhaps output an empty one. That's the final report which is supposed to open in Excel. I can only speculate that perhaps the reason that sqlite could not ATTACH the RM database was because it is not in the same folder as the .rmgc file, or there is an error in the file name.

'excel.lnk' is not recognized... suggests that you have not created a shortcut to Excel in the same folder as the database, BAT and sqlite3.exe files. Just copy the Excel shortcut from your Windows Start menu and paste it into the database folder.

I succeeded in getting the whole procedure to work on a RM7 database and have updated the page to flag where edits need to be made to suit each database. My excel.lnk was for an older computer so that needed changing. Also found that a RM file name in quotes cannot be passed via the shortcut through the bat file to the SQL script; I had to change a file name to have no space characters so it could be unquoted in the shortcut's Target.

The procedure, as is, does not work with the #RM8 database because it now substitutes "*" for that part of the path set for Media folder in Settings.

I've updated the page to emphasize and clarify some of these points.

Quote from nconway567 on 2021-07-03, 1:35 pm

That's what I would've thought - but no. Everything is in the same folder - the only exception being the Media which is in OneDrive.

Quote from Tom Holden on 2021-07-03, 3:23 pm

Sorry you are having so many problems getting the results you want, Nicole. The batch procedure was cobbled together to make it an easily repeated task from a shortcut.

So it looks like the procedure has an issue with the ATTACH of your database which I see is a large one among typical RM databases. While unlikely that the ATTACH statement is incomplete when the SELECT statement is issued, maybe we need a pause in between. Try going through the SQLite steps one at a time using SQLiteSpy or some other GUI SQLite manager, starting with the DB3 database that appears to have been successfully created:

1. Open ScrapbookFilesStatus.db3 in the SQLite manager
2. In the SQL editor, type and execute the statement:

3. ATTACH DATABASE 'C:\Users\nconw\Documents\RootsMagic\Data\Nauheimer2020.rmgc' AS RM;

4. Inspect the RM database structure and confirm that you see the table MultimediaTable
5. Copy/paste to the SQL editor this statement and execute it:

6. SELECT LIKE(FILE,MediaPath||MediaFile) AS USED, FILE FROM FILES LEFT JOIN MultimediaTable ON (FILE LIKE MediaPath||MediaFile) WHERE FILE NOT LIKE '%.THU' AND FILE LIKE '%.___%' ORDER BY FILE;

7. The results of that SELECT are what we are after. The batch file outputs it to a tab-delimited TXT file for use in Excel but your SQLite manager may have a copy/paste or export to CSV or XLS function or you may be happiest working within SQLite to modify output to what you want. For example, the results could be stored in another table in the DB3 or the SELECT stored as a VIEW.

Hope this helps...

Tom