List OnSong Data

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.

List OnSong Data v1.0


Click here to download


List OnSong Data is available at no charge

Description

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.

AllSongs.csv

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
... ... ... ... ... ... ... ... ... ...

UnboundSongs.csv

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
... ... ... ... ... ...

BookSongs.csv

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
... ... ... ... ... ... ...

AllSetsAndSongs.csv

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
... ... ... ... ... ... ... ... ...

FolderSongs.csv

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 Descriptions

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.

Preconditions

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.

Installation

  1. 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.

  2. Download ListOnSongData.zip and unzip it into that folder.

  3. Installation is now complete.

Instructions For Use

  1. 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.

  2. 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.

  3. 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.

  4. In Windows Explorer navigate to C:\ListOnSongData and then double click the Windows Batch File named ListOnSongData.

  5. If an error is encountered you will be informed otherwise the CSV files, named above, will be created overwriting any previous ones.

  6. Open the CSV files in your favourite spreadsheet program and filter, format and print as required.

And Finally

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.