Hello,
I’ve been searching for a place to get started on this particular problem all day and am pulling my hair out. I can’t even come up with a rough outline for a script very far beyond “repeat for all track in ‘playlist’”.
My intent is to take an excel database containing Title, Artist, Date, Description and compare it against an iTunes playlist full of tracks identified only by Title. The script will compare track Title to database Title and populate the remaining bits of information for each track, avoiding those where there is no match.
…any thoughts?
dennis
Thoughts:
Import info from iTunes to Excel (remembering “where” in iTunes each row of data came from
Use Excel (probably VLOOKUP formulas) to put the data base values next to the import from iTunes. (While preserving the order of titles)
Export from Excel to iTunes. Since the new info is in the proper sequence, no searching is needed to put the right Artist (for example) on the right Title.
Steps 1&3 would be AppleScript
Step 2 would be either formulas that already exist on the ImportFrom sheet or AppleScript could populate the cells with formulas.
The thing that I would avoid (as a time eater) would be using AppleScript to search the Excel database to find the matches. Excel is designed for that function, AppleScript is not.
Mike,
VLOOKUP was exactly what I needed! Granted, it requires some extra construction to the excel document, but it got the job done (or, it will as my tests have been positive so far).
To recreate this, make a second worksheet in your excel sheet named “lookup” for ease and put this in cells A4 through A10 (depending on your number of data containing columns on your “Library Text” worksheet) of your lookup sheet. Make sure to alter the “2” before “FALSE” as that’s telling Excel what column from the left to look for the appropriate data.
=VLOOKUP(A1, ‘Library Text’!$A$2:$H$176, 2, FALSE)
Below is the script I used. PLEASE make a back-up of your iTunes Library anyone who wishes to try it! Also, please note it’s still hard coded in some parts, but if there’s a more enterprising person out there who wants to make it more variable, go to it.
global track_name -- Title
global track_artist -- Director
global track_year -- Theatrical Date
global track_description -- Description
global track_grouping -- Rating
global track_album -- Publisher
global track_genre -- Genre
global track_comment -- Stars
on run
my track_collector()
end run
on track_collector()
tell application "iTunes"
set this_playlist to (get view of front window)
set playlist_name to name of this_playlist
set playlist_tracks to every file track in this_playlist
set track_count to (count playlist_tracks)
repeat with track_number from 1 to track_count
set this_track to (item track_number of playlist_tracks)
set track_name to name of this_track
my data_fetch()
try
set genre of this_track to track_genre
set long description of this_track to track_description
set description of this_track to track_description
set artist of this_track to track_artist
set comment of this_track to track_comment
set grouping of this_track to track_grouping
set year of this_track to track_year
set album of this_track to track_album
end try
end repeat
end tell
end track_collector
on data_fetch()
my excel_initialize()
tell application "Microsoft Excel"
set value of cell "A1" to track_name
set track_genre to value of cell "A4"
set track_description to value of cell "A5"
set track_artist to value of cell "A6"
set track_comment to value of cell "A7"
set track_grouping to value of cell "A8"
set track_year to value of cell "A9"
set track_album to value of cell "A10"
end tell
end data_fetch
on excel_initialize()
tell application "Microsoft Excel"
activate
activate workbook "DVD Library Details.xls"
activate worksheet "Lookup"
end tell
tell application "System Events"
set visible of process "Microsoft Excel" to false
end tell
end excel_initialize
If any of this is still too confusing or someone is interested, I can post a sample of my excel sheet …somehow.
Thanks again, Mike!
dennis