So - this weekend's obsessive-compulsive activity.... warning, this post contains windows batch file coding.

As I posted some time earlier, CCLI in the UK have started asking us to report not only which songs we use, but how many times per year we use them.  They suggest visiting their website every week, which I was NEVER going to do, so over the course of the year I saved every powerpoint file we used, with the intention that I'd find some way of counting the song use later.

This weekend was counting time, so I thought... can I get my computer to count for me?  I could use the windows search facility, but then it would take a long time every year, and I figured it might be worth spending a bit more time this year if I can get a solution that will run quickly next year.  I almost found a good way of doing this.  I'm sure there are good solutions on unix and mac, but for us windows folk we need to use the dos command prompt.  So, I created a text file "songfind.bat" and (this is what took a long time) filled it with loads of lines like:

find  /i /c "To redeem for Your praise" ./*.ppt > youremightyandstrong.txt
find /i /c ": 1" ./youremightyandstrong.txt
del youremightyandstrong.txt
find  /i /c "Yet You left the gaze of angels," ./*.ppt > yourethewordofgod.txt
find /i /c ": 1" ./yourethewordofgod.txt
del yourethewordofgod.txt
find  /i /c "Steady and unchanging;" ./*.ppt > yourloveisamazing.txt
find /i /c ": 1" ./yourloveisamazing.txt
del yourloveisamazing.txt
find  /i /c "Raging like the storm," ./*.ppt > yourloveshinglikethesun.txt
find /i /c ": 1" ./yourloveshinglikethesun.txt
del yourloveshinglikethesun.txt

Essentially this is a group of three lines for each song.  The first line searches through all the powerpoint files in the current directory, looking for the search phrase, and puts the result in a file "youremightyandstrong.txt".  The second line looks through the file you just created, and counts how many times the phrase ": 1: appears.  This has the effect of counting the number of files with the song in... and the third line deletes the file "youremightyandstrong.txt".

Finally.. I put the file songfind.bat into the directory with all my year's powerpoint files, open a command prompt, go to the directory, and then type:

songfind.bat > CCLIstats.txt

In principle this should give me a file containing a load of text, but crucially, some numbers counting the songs.

PROBLEM: It turns out not all of my powerpoint files are in the same format. The dos command "find" searches for plain text, and powerpoint files are saved in a funny format. Most of my files contain the song lyrics, or some other bit of text, saved in plain text... but some have the text separated out with funny spaces which the dos "find" command can't read....  so I had to open each of my song files in Notepad to look for suitable text to search for!  All in all, there are 16 songs in my database for which the above doesn't work.  But the good news is that I got it working for ALL MY OTHER SONGS....

It did take a long time to make my batch file but, now I have it, it should save a lot of work next year.

So, while my computer counts, I can go and have a coffee.

Anyone else tried this sort of thing?

Views: 126

Reply to This

Replies to This Discussion

Two words: Planning Center.

Nate

Looks like Media Shout stores its data as XML (which is all text), although it does include the song lyrics in addition to the titles, so one would have to be sure they were extracting only one record per "song done" (and allowing for the fact that one MIGHT do a song twice in the same service).  I'd probably do it in perl, but that's just because I know perl... also, we're still on MS 3.5, so maybe they're doing it differently nowadays...

If I was running church as a business, maybe....  but it looks like planning center has way too much stuff for my needs!

Nathan Lawrenson said:

Two words: Planning Center.

Nate

Yes, I found myself wishing my files were all in text..... then it would be easy!  You need to find a unique lyric for each song, not necessarily the title, and you need to know how many times that lyric occurs per song.  It is actually very easy to find unique lyrics, usually just a few words from the song will do....

... then if you are concerned about using a song multiple times in a service, you can add lines such as:

find /i /c ": 2" ./youremightyandstrong.txt

(this will count the number of files for which the line you picked occurs twice in a file).

And, yes, perl would work too... I sometimes wish I knew perl....

Charles Wolff said:

Looks like Media Shout stores its data as XML (which is all text), although it does include the song lyrics in addition to the titles, so one would have to be sure they were extracting only one record per "song done" (and allowing for the fact that one MIGHT do a song twice in the same service).  I'd probably do it in perl, but that's just because I know perl... also, we're still on MS 3.5, so maybe they're doing it differently nowadays...

My frustration with the CCLI reporting website is that it neither records enough data to make itself useful for anything more than their payment calculations nor does it allow importing from an external tool.

Ideally I'd like to record the date and time of the service, the key and the source used. That way I could extract useful information such as the top songs from the morning service over the past year or what range of keys we have used for a particular song. The CCLI just records the song, how it was reproduced and when it was entered on the site (not when it was actually performed).

The obvious solution (to my programmer orientated brain) is to create a local database - I did run one for a number of years at my previous church including a web interface, which was a useful tool for the worship leaders. Unfortunately, while it would be trivial to also include the official identifier for each song, there is no way that I've spotted to be able to upload a batch of data to the reporting website.

I think extracting the data from Powerpoint files sounds like a clever solution (although you're still going to have to retype it to report it). The one tweak I'd suggest would be to run the extract process more often to feed an intermediary data store - it shouldn't take long to run every week or month and, because you are closer to the events, it will be easier to spot anomalies.

The other thing I'm wondering is how you figure out what lines to search for? If you forget to look for an identifying piece of text, does that mean it gets missed entirely. If you had a system like the Media Shout one Charles describes, storing its data as XML, then it should be easy to look for all the <songtitle> elements... assuming they are properly recorded and identified in a way that you can pick them out programmatically.

Wulf

I'm not saying you should use Planning Center.  My point was, Planning Center reports to CCLI for me.

Also, is "running church as a business" always a bad thing?  There are areas of church/ministry that need to be run like a business.  If that's not your thing, no worries, but it's a great tool for those who need it.

Nate

I've not got anything strongly against running church "as a business" ... it's just that particular model isn't really appropriate in my local church setting (small group in a small UK church).

Nathan Lawrenson said:

I'm not saying you should use Planning Center.  My point was, Planning Center reports to CCLI for me.

Also, is "running church as a business" always a bad thing?  There are areas of church/ministry that need to be run like a business.  If that's not your thing, no worries, but it's a great tool for those who need it.

Nate

Hi Wulf

Oh, I've got multiple frustrations with CCLI...  it seems to me they replaced one arbitrary system of distributing money with a different arbitrary system but which requires an awful lot more effort on our part.  And I think all this effort is wholly out of proportion to the money they are distributing on our behalf (I mean... our licence for projecting words costs about £130). The latter was the main driver for me deciding to do this... it took a bit more time this year, but hopefully less time next year.

I actually have every song that we use saved as a separate powerpoint file... I use these to create the powerpoint show for each Sunday service.  So, this allowed me to test the code... if I run the code in the folder containing all my song powerpoints, I get a single "hit" for each song.

Figuring out what lines to search for wasn't hard... the English language is sufficiently flexible that almost every sentence we speak or write is unique in the history of the universe....  but I did have to work my way through on a song-by-song basis, working through my song powerpoint files, which is what took the time.  It was a bit more complicated because of the problems I alluded to about how text was stored in the powerpoint file.  For some songs I had to resort to using something unique from the copyright text, because all the song lyrics were "unreadable" to the "find" command!  If I could figure out how to automate the process of deciding on unique text then it would have been even better....

Most likely there will be the odd anomaly still.  But I am not too worried about that... if CCLI think they can get perfectly accurate data through this exercise (however churches decide to do the counting) then they are kidding themselves! And a bit of common sense (and statistical reasoning) indicates they don't actually need perfectly accurate data.

Any, yes, you are right, there is no way to get the resuts onto their site except to type them in.  So I still have work to do....

Wulf Forrester-Barker said:

My frustration with the CCLI reporting website is that it neither records enough data to make itself useful for anything more than their payment calculations nor does it allow importing from an external tool.

Ideally I'd like to record the date and time of the service, the key and the source used. That way I could extract useful information such as the top songs from the morning service over the past year or what range of keys we have used for a particular song. The CCLI just records the song, how it was reproduced and when it was entered on the site (not when it was actually performed).

The obvious solution (to my programmer orientated brain) is to create a local database - I did run one for a number of years at my previous church including a web interface, which was a useful tool for the worship leaders. Unfortunately, while it would be trivial to also include the official identifier for each song, there is no way that I've spotted to be able to upload a batch of data to the reporting website.

I think extracting the data from Powerpoint files sounds like a clever solution (although you're still going to have to retype it to report it). The one tweak I'd suggest would be to run the extract process more often to feed an intermediary data store - it shouldn't take long to run every week or month and, because you are closer to the events, it will be easier to spot anomalies.

The other thing I'm wondering is how you figure out what lines to search for? If you forget to look for an identifying piece of text, does that mean it gets missed entirely. If you had a system like the Media Shout one Charles describes, storing its data as XML, then it should be easy to look for all the <songtitle> elements... assuming they are properly recorded and identified in a way that you can pick them out programmatically.

Wulf

I've found the download thing that CCLI seems to have instituted on our MediaShout program (or the other way around) can be convenient for getting the words to a song on there in a hurry - but it leads to all sorts of problems (two songs with the same title, one keeps getting thrown out of the list; choruses labelled as verses or bridges; spending a half-hour trying to add or subtract a word from an ending or a silly "yeah!" from the text).  Maybe it's all right - I'm not in charge of the visuals presently, but I find myself waiting around a lot at rehearsals when the words don't mach up.

I do see how having CCLI and the Media outlets link up can be an efficient way for getting the songwriters properly and equitably rewarded - so perhaps it is worth the confusion.

RSS

© 2017       Powered by

Badges  |  Report an Issue  |  Terms of Service