ListOnSongData relates to OnSong, an excellent iOS app for managing your music. As they say:
OnSong replaces the mess of paper and binders with interactive chord charts and sheet music on your iPad, iPhone or iPod touch.
My motivation in creating this project was to enable me to easily see what songs I had played at a venue, when was the last time I had played each song and how many times had I played each song there in the past.
From that initial goal the project expanded somewhat into providing a whole bunch of information about songs, books and sets.
This package comprises two public domain utilities (7-zip and SQLite3), a set of SQL scripts and a batch file that makes the magic happen. Relax, everything you need is in the zipfile.
The result of running the batch file is a set of files in comma separated values (CSV) format. These CSV files may then be opened by a program such as MS Excel or OpenOffice Calc and the data filtered, formatted and printed as required.
The CSV files produced are listed immediately below. The description of each data field is afterwards.
This is a list of every song that appears in the AllSongs collection. The data included are shown below. The data are sorted first by SongTitle and then by SongArtist.
| Bin | SongTitle | SongArtist | Key | Capo | Flow | Topic | Lent | Rest | Del |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
This is a list of every song that appears in the UnboundSongs collection. The data included are shown below. The data are sorted first by SongTitle and then by SongArtist.
| Bin | SongTitle | SongArtist | Key | Capo | Flow |
| ... | ... | ... | ... | ... | ... |
This is a list of every song that appears in every book. The data included are shown below. The data are sorted first by Book then by SongTitle and then by SongArtist.
| Bin | Book | SongTitle | SongArtist | Key | Capo | Flow |
| ... | ... | ... | ... | ... | ... | ... |
This is a list of every song in every set in every folder. The data included are shown below. The data are sorted first by FolderName then by SetOrderInFolder and then by SongOrderInSet.
| Bin | FolderName | SetName | SetTime | SongTitle | SongArtist | Key | Capo | Flow |
| ... | ... | ... | ... | ... | ... | ... | ... | ... |
This is a list of songs that are in any set in a folder. The songs are ordered by their most recent set and the number of sets in which they are present is also given.
The data included are shown below. The data are sorted first by FolderName then by OrderInFolder descending then by SetName then by SetTimestamp descending and then by OccurenceInFolder descending.
This list may seem complex, however, this is the one that is useful for creating new sets that don't repeat songs at a venue too often.
| Bin | FolderName | SetName | SetTime | SongTitle | SongArtist | Key | Inst |
| ... | ... | ... | ... | ... | ... | ... | ... |
| Data Field | Description |
| Bin | Set to 'Yes' if the song's source is in a binary format (a DOCX or PDF file) otherwise it is blank. |
| Book | The name of the book in which a song is present. |
| FolderName | The name of the folder in which a set is present. If the set is not in a folder then ROOT FOLDER is displayed. |
| SetName | The name of the set if one has been given. |
| SetTime | The date and time of the set in YYYY-MM-DD HH:MM format. N.B. Your spreadsheet may convert this to your local date and time format. |
| SongTitle | The title of the song. |
| SongArtist | The song's artist/composer or whatever you use this field for. |
| Key | The key of the song ignoring any capo setting. |
| Capo | The fret on which the capo should be placed. Zero means no capo. |
| Flow | The Flow string for the song. |
| Topic | The Topic string for the song. |
| Inst | The number of instances of this song in sets in this folder. |
| Lent | Set to 'Yes' if the song has been lent. |
| Rest | A list of the restrictions that have been applied to the song. |
| Del | Set to 'Yes' if the song has been deleted. |
This has been developed to run on Windows 10. It will probably work on Windows 7 and 8 too.
No attempt has been made to make it run on any other operating system although this could be done. There are command line versions of SQLite for Linux and Mac OS X (x86) available on the SQLite website.
Create a folder on your PC, we will assume that you create C:\ListOnSongData but it could be anywhere else.
In future when I refer to C:\ListOnSongData, please substitute the folder that you created.
Download ListOnSongData.zip and unzip it into that folder.
Installation is now complete.
OnSong's backup only backs up a single library therefore if you have more than one library you will have to back them up separately.
Make sure that you have selected the library for which you want to create the listings.
Create a backup of the chosen library using Onsong's internal Backup facility. N.B. It is advisable not to backup media files for this particular backup in order to save time and file size. You can switch this on and off in Utilities>Settings>Advanced>Backup Media Files.
Make a note of where the backup is being written (the Export Location), which should be a cloud-based location that you can access from your PC, e.g. Dropbox, Google Drive, etc.
Once the backup has completed, download the backup file from the export location to C:\ListOnSongData.
If more than one backup file exists in the folder then the one with the newest timestamp will be used. It is better to move/delete any backup files from the folder before downloading the desired backup file.
In Windows Explorer navigate to C:\ListOnSongData and then double click the Windows Batch File named ListOnSongData.
If an error is encountered you will be informed otherwise the CSV files, named above, will be created overwriting any previous ones.
Open the CSV files in your favourite spreadsheet program and filter, format and print as required.
Using this listing package as described above cannot change anything in Onsong on your device. It takes a copy of the backup file that you have created, reads data from it and creates some files on your PC. At no point is anything written back to your device or even the cloud-based storage where you created your backup. If you are at all concerned then perform steps 4 to 6 with your device powered down and your PC disconnected from the internet.
Making a change to Onsong on your device will not change the data in the CSV files. You will have to perform the Instructions For Use again in order to produce new CSV files; and yes, you will have to format them all over again, which is a very good reason to learn to use macros in order to automate that.
7za.exe, the 7-Zip standalone console version is covered by the GNU LGPL, see License 7-Zip.txt
sqlite3.exe, the SQLite command line shell program, is public domain, see the SQLite copyright page.