For the benefit of other councils, here is the process I use:
- Download / export the data
Download the list of MBCs from Scoutbook:
Scoutbook: https://scoutbook.scouting.org | my dashboard > Administration | manage approved merit badge counselor list > download the current mbcdetails.csv file
This downloads a file with a name of the form [COUNCIL#]_Detailmbc_download_[DATE].csv
Download the list of MBCs from my.scouting:
Scoutnet: https://my.scouting.org | menu | (council) | roster > reports | council merit badge counselor listing | run > export to CSV
This produces a file with the name CouncilMeritBadgeCounselorListing.csv. I always stick an underscore and the date on the end of the filename, just like the SB file above. So my file is like CouncilMeritBadgeCounselorListing_[DATE].csv
Note: if you don’t have rights to these reports, your council registrar can run them for you and send you the data.
- Generate TSV from CSV
sed 1,10d CouncilMeritBadgeCounselorListing_[DATE].csv | CSVFileView /load stdin: /stab scoutnet.tsv
CSVFileView /load [COUNCIL#]Detailmbc_download[DATE].csv /stab scoutbook.tsv
The ‘sed’ command just deletes the useless first 10 lines of the council MB listing file that actually shouldn’t be there. You can do this manually if you prefer not to use the ‘sed’ utility.
If you don’t want to use the CSVFileView utility, or you are on a platform other than Windows, you can do the same thing by simply loading the CSV file into Excel and then save it back out as a ‘Tab Separated Value’ file. That’s all CSVFileView does is convert the commas to tabs.
- Normalize the data
perl sbnormalize.pl scoutbook.tsv > sb.tsv
perl snnormalize.pl scoutnet.tsv > sn.tsv
This fixes the numerous errors and anomalies in the data from national. This includes converting email addresses to lower case, fixing the horrible choice of separators in the merit badge list, fixing up field names to not have spaces, etc. It also fixes the incorrect merit badge names found in the my.scouting data & confirms that only valid MB names are present.
- Load the data into SQLite & generate reports
sqlite3 (then paste the SQL script from below)
Alternatively, save the script as queries.sql and do sqlite3 <queries.sql
You can, of course, combine most of this into a single batch file, bash script, or scripting language of your choice to execute the steps in order for you. And, potentially, write the resulting output to a file.
All the tools I use are open source and freely available on the internet. They are available on all platforms excpet CSVFileView which is windows only. However, the same thing can be accomplished by loading the content into Excel and saving as tab separated values as mentioned above.
Since the forum here won’t allow me to upload the scripts with their proper names, I’ve uploaded a single text file with these instructions and the text of all the scripts I use including the SQL queries and views.
scripts.txt (11.7 KB)
Here are the counts from my council’s data. Obviously I can’t post the actual records because of privacy issues. Keep in mind that I’ve been hounding both national and my council to correct all of these things literally for YEARS and they are still messed up. All I get is finger pointing and “that’s someone else’s issue” rather than fixes for these obvious problems.
sqlite> – merit badge mismatch between SB & MS
sqlite> select count(*) as count from mb_mismatch;
count
1
sqlite> – users in MS but not SB
sqlite> select count(*) as count from missing_from_sb;
count
8
sqlite> – users in SB but not MS
sqlite> select count(*) as count from missing_from_sn;
count
2
sqlite> – users who appear to be the same but have different IDs in SB vs MS
sqlite> select count(*) as count from missing_correlation;
count
2
sqlite> – duplicate SB entries
sqlite> select count(*) as count from duplicate_sb_entry;
count
7
sqlite> – duplicate MS entries
sqlite> select count(*) as count from duplicate_sn_entry;
count
9
sqlite> – email mismatch between SB & MS entries
sqlite> select count(*) as count from email_mismatch;
count
21
