iTunes 12.x; Print a song-list insert for a CD jewel case (with an excursion into Excel formulas)

I wanted to burn a mix CD from an iTunes playlist, and make a nice cover for it with a list of artists, songs, etc..  I can see this information in iTunes when I look at my playlist.  But can I print it in a way that’s suitable to insert into the front of a CD jewel case?

Yes!  I’ve found two ways to print a jewel-case insert; easy and hard.

The easy way

First let’s look at the easy way.  If you’re satisfied with the result then I guess we’re done here.  In iTunes, display your playlist, pull down the File menu and click Print

Screen Shot 2014-12-02 at 5.21.52 PM

You get a choice of themes, some of which include artwork.  What you don’t get to choose:

  • What columns in the iTunes database are listed.  (The columns you set to display in the iTunes playlist have no effect here.)
  • Whether to truncate long fields, such as song titles
  • What artwork to use.  You can let iTunes use album artwork, but you can’t use your own artwork.

The hard way

So, you didn’t like the results of the Easy Way either!  And you want to know, how hard is “Hard”?  There may be other ways to approach this problem.  I know a little about Excel, so I used what I know.

I extracted the metadata from iTunes and massaged it with Excel.  Then I pasted it into a Word document to format it as a jewel case insert.  (Prerequisites; Excel, Word.)

Extract the data

In iTunes, display your playlist.  Pull down the File menu and mouse over Library

A submenu appears.  On it, click Export playlist … .

Screen Shot 2014-11-30 at 9.56.32 PM

Next you get a choice of file formats.  The only one that seems to be of any use is the default format, a plain text file.  Click Save.

Screen Shot 2014-11-30 at 9.57.37 PM

Now we’re done with iTunes and it’s time for the big guns.

Find the .txt file you created in Finder.  (Pre-Windows 8 people; use Windows Explorer.  Windows 8 people; I salute you.)  Alt-click it; in the floating menu that appears, mouse over Open with.  A submenu appears, listing the programs you have installed that could open this file.  Pick Excel.

Screen Shot 2014-11-30 at 9.59.11 PM

Excel magically divides each record into fields and aligns them in columns (using invisible tab characters as delimiters, I’d guess).

Screen Shot 2014-12-02 at 8.04.33 PM

You probably know how to reorder and hide columns to make this list the way you want it to look, so perhaps we’ll part ways here.

An option that’s even harder (but stick with me, you can do it)

But if you want to include the song lengths, take a look at column H, “Time;” they’re stored in seconds.  I wanted minutes:seconds.  I’ve found some forum posts about how to convert seconds to minutes:seconds, but I couldn’t get any of them to work the way I wanted.  So I invented my own way; it takes several steps, but they’re each pretty simple.

If you’re comfy with Excel you can skip down to “Convert The Time.”  For those who aren’t,

A brief orientation:

  • What you see in an Excel spreadsheet cell may not be what’s really stored there.  It may be a little different, such as a currency amount that really includes cents but that you’ve formatted to round up to dollars.  In this case, the cents are still invisibly stored.  Or, it may be a lot different!
  • A cell can hold either of two kinds of information (that I know of); a value or a formula.  If a cell contains a formula, Excel displays a value that is the result of the formula.  But that value isn’t stored; the formula that Excel used to create the value is what’s stored.  So, every cell displays a value; but some of them come from formulas.
  • When you type into a whole cell, your typing replaces what it held before. If you type into a cell that contains a formula, you replace the invisible formula with whatever value you type.  So I color cells that hold formulas green to remind myself not to type in them.

Insert working columns

To get some working space, let’s insert 7 columns to the right of column H, Time.  Click on the “I” cell at the top of column Disk Number, and don’t release it.  You’ll see that the whole column is highlighted.  Drag it to the right to highlight seven columns.  While these columns are highlighted, right-click; a floating menu appears.  Select Insert.

Screen Shot 2015-06-30 at 3.15.35 PMNow you have 7 new columns I, J, K, L, M, N, O.  No harm to the columns further to the right; they get moved out of the way.

Screen Shot 2015-06-30 at 3.31.32 PM

As we progress, type a column heading in row 1 of each column.  Color the rest of the column green as a reminder not to type over the formulas that we’re going to put there.

Convert the time

For example, let’s do my first row song, “Lazy” by X-Press 2.  Its play time (cell H2) is 417 seconds. 

Column I: Decimal minutes

Convert time to minutes expressed as a decimal fraction by dividing it by 60 (because there are 60 seconds in a minute): In cell I2, type  =H2/60  (a formula always starts with an equal sign).  You see the result, 6.95 minutes.  Keep in mind that the 95 means 95/100ths of a minute, not 95 seconds.

Mouse over the bottom right corner of this cell; the cursor turns into a solid black cross.  Click and drag it down to the bottom of the column to populate the whole column with the formula.  When you release the mouse button, all the fields in the column will show calculated decimal minutes.  While the column’s displayed values and not its heading are highlighted, color them green as a reminder that these are really formulas.

J: Minutes

Get just the whole minutes.  In cell J2, type  =FLOOR(I2,1)  You see  6  minutes.

Formulas can include functions.  FLOOR() is a function that discards precision in a value, working leftward.

Every function expects a list of parameters; it follows the name of the function and is enclosed in parentheses.  FLOOR() wants two parameters; the value on which to operate, and the significance (I’m not sure quite what “significance” is, but “1” is the value that works here).

Populate the rest of the column with the formula, like we did for column I.

K: Fractional seconds

Get just the fractional part of the minutes, by subtracting the whole minutes (J2) from the decimal-fraction minutes (I2).  In cell K2, type  =I2-J2   You see   0.95   of a minute.

L: Seconds

Convert the fractional part of the minutes to seconds.  You’d think you could just multiply the fractional minutes by 60, but noooooo.  Because this operation may yield another decimal fraction instead of the whole number of seconds that we need.  So, in cell L2, type  =ROUND(60*K2,0)  You see  57  seconds.

ROUND is a function for rounding numbers; it takes two parameters.  The first parameter is the value on which it operates; in this case, 0.95 .  The second is the number of fractional digits to preserve in the rounded value.  In this case, 0   Not any!

M: Tens digit of seconds

Things have been going along smoothly, so far.  But I want the final result of all this footwork to show seconds as two digits, using leading zeros if needed.  For example, “Political Science” should show a time of 2:09 not 2:9

Get the tens digit of seconds, or “0” if it’s zero.  In cell M2, type  =IF(L2<10,”0″,LEFT(L2,1))  You see  5  .

This formula involves two functions.  The stuff that belongs to the IF function is:  =IF(L2<10,”0″,LEFT(L2,1))

IF wants three parameters; an operation that could be true or false, what to do if it’s true, and what to do if it’s false.  The operation tests whether seconds (L2) is less than 10.  If it is, the function produces a display value of “0” which will be our leading zero.  If it isn’t, the function uses another function to produce a display value.  And that’s the branch we take for this row.

The stuff that belongs to the LEFT function is:  =IF(L2<10,”0″,LEFT(L2,1))

I give the IF function the LEFT function as a parameter.  LEFT wants two parameters; a value on which to operate, and the number of characters of that value to produce, starting at the value’s left end.  I gave it seconds (L2) and told it to grab one character, that is the tens digit.

Trying this formula on “Political Science,” which has 3 seconds (L13) you see  0

N: Ones digit of seconds

Get the ones digit of seconds.  No need for fancy footwork here; even if it’s a zero it’s still good.  In cell N2 type  =RIGHT(L2,1)  You see  7

RIGHT is the fraternal twin of LEFT; it grabs characters from a value starting at its right end.  It wants two parameters; the value to operate on and the number of characters to get.

O: Min:Sec

Assemble the minutes and seconds we figured out.  In cell O2 type  =J2&”:”&M2&N2  You see 6:57

Now, instead of grabbing characters out of text strings, we’re sticking (concatenating) characters together to make a text string.  They can be contents of cells, or actual characters (that is, literals, which are book-ended with quote marks).  The & (ampersand) operator is the glue.  Here we stick minutes (J2) to a colon symbol and stick that to the tens and ones digits of seconds (M2 and N2).

Now we’ve got something resembling this:

Screen Shot 2014-12-02 at 8.01.49 PMAt this point you may be wishing that Apple had just given us each song’s play time in minutes and seconds.  Or that they had provided a way to print jewel case inserts that lets us pick what information to include and doesn’t truncate any of it.  Me too!

Concatenate song information

Now that we’re done messing around with time, let’s get back to space — and do another concatenated string.  This one is going to hold all of the information I want about a song; who’s playing, the title, how long it is, and the name of the album.  Somewhere out of the way (cells referred to by formulas don’t have to be in the same row), type  =B2&” / “&A2&” “&O2&” / “&D2   Notice that each / (slash) has a space on each side of it, like this:

<space>/<space>

You see:

X-Press 2 / Lazy 6:57 / Muzikizum

I copy the song information from Excel, and paste it into MS Word.  Here I format it as a jewel case insert:

Screen Shot 2014-12-02 at 7.59.18 PM

I measure the insert the manufacturer put in the jewel case.  Then I use the same measurements to create the Word document.  It is 4.78″ wide, 4.68″ high.  If the song list is longer than that, I just fold it to fit inside the jewel case.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s