Archive for September, 2011

UltraStar: Creating a Song List

Posted in UltraStar on September 6, 2011 by kokairu

Please note: my UltraStar guides now have their own dedicated website on UltraGuide.net. An updated version of this article can be found here

Wow, it’s been a while since I got around to doing more UltraStar guides. There are only a few things left to cover: acquiring cover art for songs quickly and easily, song creation, and creating a song list. As you can see from the title, I am going with the latter today; in my experience, it can be an incredibly useful to have a printed list of songs when playing UltraStar with your friends. What’s more, there is one invaluable tool that makes it very easy for you: UltraStar Manager, which I mentioned briefly in an earlier post, is good for many things, such as Playlist creation and .txt file organisation. It is available to download on the link below:

http://sourceforge.net/projects/uman/files/uman/1.8.x/uman-1.8.0-win32-setup.exe/download

To get started, download and install this tool if you haven’t already. You should then specify the various directories that it requests on its first start up: the main UltraStar folder (usually C:\Program Files\UltraStar Deluxe by default), the playlist directory (usually C:\Program Files\UltraStar Deluxe\Playlists by default), and the song directory (usually C:\Program Files\UltraStar Deluxe\Songs by default). Depending on how many songs you have, it may then take a while to load up.

Once this is done, you will see a list of your songs arranged by folder. The best thing to do with this program is to play around with it, though be warned: there are a few options that will edit all of your songs automatically, such as correcting the capitalisation of the song titles.

To get started on the Song List, click on the ‘Extras’ tab and then the far left option, ‘Create Report.’

You should then select all of the fields that you want included in your Song List: ‘Artist’ and ‘Title’ are a given and pretty much the only ones you’ll need, but it’s up to you if you want to include something extra, such as ‘Edition’ (to help identify songs that have been taken from SingStar) or ‘Video file exists?’ (if having a background video might be important to you or other players). Do remember, though, that this list will most likely be printed off on a portrait orientation, and it therefore might not be viable to have too many fields.

Once you’re happy with the selection of fields, there are two options to click on. For those that want to get the job done quickly, click on ‘HTML Report.’ For those that are more perfectionist, and have Microsoft Access installed on their PC, go with ‘Text Report.’

For those that went with HTML Report, choose a place you want to save it, and the file will open, listing all of your songs. There you go. Just need to print that off now. The main disadvantage here, though, is that there’s no ‘fine tuning’ what’s been produced – you can’t add or remove songs from that list manually, nor can you apply the tricks that you will see with the other option below. But, as I say, it’s cheap and cheerful, and it serves its purpose.

The other option, then, is to click on ‘Text Report’ instead. Choose a place to save it, then click ‘save.’ In its current form, it may seem pretty useless, but rest assured that file contains everything it needs for the likes of Excel and Access to understand it. Some of you may be familiar with fixed width .txt files; in which case, you can skip some of the next few steps.

First of all, close UltraStar Manager and open Microsoft Access. My screen shots are from Office 2010, though hopefully they will help with an older version even if the two methods vary slightly.

Once you’ve opened Microsoft Access, create a new database and then click on the tab ‘External Data.’ After this, click on the ‘Text file’ option as demonstrated below:

In the pop up box, specify the text file that you want to import (the one that you saved using UltraStar Manager), leave the first option selected, and then click ‘next.’

On the next screen, leave ‘fixed width’ selected, and then click ‘next’ again.

The next part is a little bit fiddly, but it’s not too hard to get your head around. Access is now giving you the option of selecting where each column stops and starts (you can simply click where you want the columns to separate). There’s a lot of stuff there that you don’t need, though – the best thing is to isolate those bits into separate columns and make sure that ‘Artist’ and ‘Title’ only have the relevant text in them. This can be achieved by clicking just before the text for ‘Artist’ starts, and right before the ‘|’ part starts, as demonstrated below. Do the same for ‘title’, and remember to scroll right the way along to the side to cut off the ‘|’ part on the far right, as demonstrated in the second image.

You also have some dud rows here, but that can be sorted out at a later point. When you’re happy that you’ve successfully separated the columns (though if you do it wrong, you can go back and try again), click ‘next.’

The next screen gives you the option of naming the columns. As only two of these interest us, make sure that Field2 is named ‘Artist’ and Field4 is named ‘Title.’ This can be achieved by physically clicking on the respective columns and labelling them in the ‘Field Name’ box.

On the next screen, select ‘No primary key’ and then click ‘next.’

Change the name of your report if you wish, and then click ‘Finish’ followed by ‘Close.’

As you will see when you go to open the newly created table (found on the left hand side of the screen), the format is a little bit of a mess, but it’s starting to look more like a proper list:

The next step is to open the table’s design view, by right clicking on the table as below:

You then just need to click on the irrelevant fields (i.e. those not titled) by right clicking on the appropriate rows and selecting ‘delete.’ You should just be left with the following:

Double click on the table on the left hand side to view the table normally (save changes when prompted), and it should look something like this:

Much better now! Just need to sort out those bothersome rows at the top. Easily done – simply select them both, right click, and select ‘Delete Record.’

We’re done with Microsoft Access now. You just need to select all the records in the table by clicking on the square in the top left hand side (to the left of the ‘Artist’ tile), and then right click on any record and select ‘copy.’ You should then open Microsoft Excel and paste the records into the new spreadsheet. Rearrange the columns in a manner that fits across a sheet of A4 paper with a portrait orientation.

As it’s your songbook, there are several different ways in which you might want to order your song list. I’ve noticed when I’ve been to karaoke nights that song books are traditionally printed arranged by the title of the song, rather than the artist. To arrange this, select column B, go to the ‘data’ tab, and click on the A-Z icon. Make sure you select ‘expand the selection’ on the next screen.

For the rest of this example, however, I will be showing screenshots sorted ascending by artist (the default setting), as that’s my preferred way of doing it. When sorting it this way, however, there is a little trick for counteracting the “The” problem, i.e. band names that start with the word “The,” such as The Beatles or The Cardigans. I for one am used to my iPod automatically disregarding this word, making finding the artist I want much simpler. We can have it this way, too – it requires sticking a formula in column C. Select cell C2 and paste the following mumbo-jumbo into it:

=IF(LEFT(A2,3)=”The”,MID(A2,5,20),A2)

Then copy this formula down to the bottom (hovering over the bottom right hand side of the icon until a little ‘+’ appears, and then double clicking, is the fastest way to do this).

This will return the same value as that in column A, except if the first word is “The,” in which case, it will simply return the remainder of the Artist name, e.g. “Beatles” or “Cardigans.” We can then order the list by column C in a similar way as described above. Once you’ve done this, you can delete the details in this column as they’re no longer required.

The next step is to make sure that the column headings are reprinted on each page for easy viewing. To do this, click on the page layout tab, and then click ‘Print Titles.’

In the ‘rows to repeat at top’ box, enter $1:$1, or select row 1 manually.

The next step is optional, depending on whether you would like to have file dividers in your song book or not, as in the picture below:

You will need to set realistic page breaks wherever you want the text to separate, e.g. between A and B, C and D, S and T-Z, etc. This can be achieved by clicking on the ‘View’ tab, and then selecting ‘Page break preview.’ You then just need to drag the markers until it separates the rows appropriately.

You may now want to double check through the print preview that everything fits nicely across a portrait page of A4.

I think that’s just about everything – make sure you get permission from whoever pays the printer ink bills before printing this beast off!

Here’s one I made earlier: