Extraction of an Artist and Album database from iTunes

[DavidL]

Like many others I guess, I have kept a simple database of my CD "wants" on my PDA (just upgraded to using Bento on an iPod touch) so that I can browse with more success at retailers. This database has been very useful but it is not much help if I find a CD that looks interesting but is not on this list and I cannot remember whether I have it already. As the collection grows (now ~42,000 tracks) this is becoming a frequent occurrence! So what I needed was a simple database of Artist & Album covering my current collection.

Now of course I have got this already as I am ripping all my music to iTunes. Unfortunately the iTunes database "iTunes Music Library" file is in xml. OK I can output this as text but it is littered with separators etc. So how do I get a 'plain' text version?

 

A neat way of doing this was covered by darrenwm in a thread in the Computer Audiophile general forum last year:

http://www.computeraudiophile.com/content/How-should-I-create-database-stored-CDs

All that is necessary is to get iTunes to export the text data using the 'Export song list" (PC) or "File>library>export playlist (Mac) commands. This text data can then be imported to your favourite database program.

Now this is fine if you want a database of all tracks for all albums for all artists. I only wanted all albums for all artists. I achieved this by:

(1) reading the text data into Excel (or a database program)

(2) removing all fields except album, artist and track number

(3) sorting the spreadsheet in ascending order versus track number

(4) removing all records after those with track 1

(5) removing the track number field

 

This process is sufficiently quick and simple to make regular updating only a minor chore.

Hope others find this information useful,

 

David

 

 

 

[DavidL]

In common with others, I edit the "album" field in the iTunes library for classical albums containing multiple works to group the album tracks for each work into sub-albums each starting with the composer and work e.g. "Mozart - Piano Concert No.20".

 

The process I described above will not work for this case as only 1 sub-album has a track 1. Instead I do the following (which actually works for all cases and is quicker):

(1) read the text data into Excel (or a database program)

(2) remove all fields except those for album and artist

(3) use the Advanced filter command from the Excel drop-down menu: Data>Filter>Advanced filter

(4) click "Unique records only"

(5) copy and save the resulting spreadsheet, which has hidden all repeated records.

 

David

 

 

[DavidL]

I've resurrected a very old thread because I found that the above procedure did not allow me to extract Album Artist from an iTunes library playlist. Searching Apple support fora I found a simpler and quicker method of extracting a database. Here are the detailed instructions:

 

Extraction of an Artist / Album Artist and Album database from iTunes

 

(1) Drag all the music library or an individual genre to create a playlist for the desired database.

 

(2) Set up the view options for this playlist using the Edit>Show View Options menu item. Un-tick all the viewing options apart from the columns you wish to export to make a database; typically this will be Artist or Album Artist and Album.

 

(3) Click on a row in this view and then select all using the Edit> Select All menu item. Copy all this text data using the Edit>Copy menu item.

 

(4) Open a blank worksheet in Excel and highlight the first cell in the first row i.e. A1. Paste the data copied in the iTunes playlist here. Add a header row with the iTunes headings (Album, Artist etc).

 

(5) Select all the records in the spreadsheet (scroll down while holding ‘shift’). Use the Advanced filter command from the Excel drop-down menu: Data>Filter>Advanced filter and click the "Unique records only" box.

 

(6) Copy the resulting columns to another worksheet because the original spreadsheet still contains all the records: the repeated rows are merely hidden.

 

(7) Sort this spreadsheet as required (e.g. versus ‘Album’), edit where necessary (e.g. to remove any album repeats having augmented / reduced artist lists, keeping only those entries having entirely different artists), and save the spreadsheet.

 

(8) Import the saved worksheet into the Bento (or other) database program, set the appropriate display fields and sort as required.