Creating an iTunes Song Inventory in Excel

I've talked in the past about automating iTunes, and about automating Excel. Let's now look at how to use Ruby to produce an iTunes report in Excel. Our finished product will be a sorted worksheet containing Artist, Year, Album, and Song Name.

As usual, we'll be working with the win32ole library, so include the following at the top of your code:

require 'win32ole'

Next, we want to launch the iTunes application using the WIN32OLE.new() method:

itunes = WIN32OLE.new('iTunes.Application')

We'll create an array to hold the iTunes Library data:

data = []

The following code iterates over the iTunes LibraryPlaylist.Tracks collection. For each track in the library that is not a podcast, our code adds a row to the data array containing Artist, Year, Album, and Year, all of which are properties of the Track object.

itunes.LibraryPlaylist.Tracks.each do |track|
  if not track.Podcast
    data << [track.Artist, track.Year, track.Album, track.Name]
  end
end

To include podcasts in your report, simply remove the Podcast conditional:

itunes.LibraryPlaylist.Tracks.each do |track|
  data << [track.Artist, track.Year, track.Album, track.Name]
end

Now that we have our data array, let's sort it...

data.sort!

...and then insert a row of field names as the first row:

data.insert(0, ['ARTIST', 'YEAR', 'ALBUM', 'NAME'])

Next, we'll launch a new instance of Excel, assigning it to the xl variable...

xl = WIN32OLE.new('Excel.Application')

...and make the application window visible:

xl.Visible = true

We create a new workbook by calling the Application object's Workbooks.Add() method...

wb = xl.Workbooks.Add()

...and we get a reference to the first worksheet in the Workbook object's Worksheets collection:

ws = wb.Worksheets(1)

Now we're ready to insert our data into a range of cells. We'll define a Range of cells that begins at cell A1. We'll call the Range.Resize() method to resize the Range to fit the number of rows (data.size) and the number of columns (data.first.size) in our data array.

rng = ws.Range('A1').Resize(data.size, data.first.size)

Then we insert our data:

rng.Value = data

Finally, we'll do a wee bit of formatting, making the first row bold...

ws.Rows(1).Font.Bold = true

...and adding AutoFilter drop-down lists to the column headers...

ws.Rows(1).AutoFilter()

There's a variety of other formatting you could apply, as discussed here.

That's all for now, but feel free to post a comment here or send me email with questions, comments, or suggested topics.

Thanks for stopping by!

Automating and Managing iTunes with Ruby

We've spent a lot of time on this blog looking at how to get work done with Ruby and Microsoft Office (Excel, Word, Access, Outlook). Let's take a break and go play... with iTunes.

Apple's popular iTunes application for Windows includes a COM interface. This allows you to automate and manage iTunes with Ruby. You can launch the application, search for and play songs, set user interface properties, and manage your iTunes library and PlayLists using Ruby code. Let's take a look...

First, of course, we start by connecting to the iTunes application object, launching iTunes if it isn't already running:

require 'win32ole'
itunes = WIN32OLE.new('iTunes.Application')

Controlling the iTunes User Interface

To place iTunes into MiniPlayer mode (or return it to Full Mode), set the BrowserWindow.MiniPlayer property:

itunes.BrowserWindow.MiniPlayer = true

To toggle the play/pause button, simply call the PlayPause method:

itunes.PlayPause

To increase or decrease sound volume, adjust the SoundVolume property:

itunes.SoundVolume = itunes.SoundVolume + 50
itunes.SoundVolume = itunes.SoundVolume - 25

To go to the previous or next track, call those methods:

itunes.PreviousTrack
itunes.NextTrack

Managing Your iTunes Library

OK, that covers several standard user interface features. Now, let's look at how you can work with your content.

Let's create an instance of the Library, which is a child object of the Application object:

library = itunes.LibraryPlaylist

We'll be working with this LibraryPlaylist object a lot going forward. It provides several methods that will return collections of Track and PlayList objects. For example, calling the Tracks method returns a collection of all tracks in the library:

tracks = library.Tracks

You can then select a song by name from this collection of Track objects:

song = tracks.ItemByName('At Long Last Love')

...and then play the track by calling its Play method:

song.Play

To search the LibraryPlaylist, call its Search method, passing it a string to search for, and an integer that determines what field to search, such as Artist, Album Title, or Track Title:

artist_tracks = library.Search('Sinatra', 2)
album_tracks = library.Search('Come Fly With Me', 3)
title_tracks = library.Search('Fly Me To The Moon', 5)

The Search and Tracks methods return a collection of Track objects, which you could iterate over. We might want to add all track objects in this collection to a ruby array called songs:

songs = []
for track in tracks
    songs << track
end

This would then allow us to use Ruby's excellent sort_by method to sort the songs array:

songs = songs.sort_by{|song| [song.Artist, song.Year, song.Album, song.TrackNumber]}

The Track object includes dozens of attributes pertaining to this track. As indicated above, there's Artist, Album, Year, and TrackNumber. But there's also Time (in minutes and seconds), Duration (total seconds), BPM (beats-per-minutes), Composer, Genre, and many more.

You could iterate over the songs collection and write data for each track to a file:

songs.each do |song|
    my_file.puts [song.Artist, song.Album, song.Name, song.Time].join("\t")
end

...or add the data to an Excel worksheet or SQLite database.

Working with iTunes PlayLists

To create a new PlayList, call the Application object's CreatePlaylist method, passing it the name of the new PlayList:

playlist = itunes.CreatePlaylist('My Playlist')

To add a song to a PlayList object, first get a Track object by calling the ItemByName method on a Tracks collection:

song = library.Tracks.ItemByName('At Long Last Love')

...then call the PlayList's AddTrack method, passing it the Track object:

playlist.AddTrack(song)

In the iTunes object model, PlayLists are child objects of Source objects. So, to create an array of all PlayList objects, we iterate over each Source object and get each PlayList object:

playlists = []
itunes.Sources.each do |source|
    source.PlayLists.each do |playlist|
        playlists << playlist
    end
end

This gives us a collection of PlayList objects that we can now work with:

for playlist in playlists do
    puts playlist.Name
end

To select a single PlayList by name:

playlist = itunes.Sources.ItemByName('Library').Playlists.ItemByName('All Sinatra')

To play the first track of a PlayList:

playlist.PlayFirstTrack

To exit the iTunes application, call its Quit method:

itunes.Quit

Further Reading

Further details can be found in this Microsoft TechNet article.

For further examples in Ruby, you might want to check out the itunes-control (itch) library source code.

And for those running iTunes on Mac OS, you may find this blog post by Zack Hobson to be of interest.

I hope you have found this information helpful. As always, post a comment here or send me an email if you have questions, comments, or suggestions.

Thanks for stopping by!