Topic: Track Year/Genre Lookup for Best of and Compilation albums (Read 65362 times)

justsomeguy · « **on:** January 21, 2011, 04:09:49 am »

URL removed, bad site.

Main purpose of this is to look up the original track release year when that track belongs to part of some compilation album. Like having a best of released in 2011 of songs originally released some years earlier. Normally all tracks would get the best of release date.

The idea is to create a custom field in MC with a type of integer (to be able to apply condition statements like > < =). Create a playlist of songs you want to update. Export that list as either .mpl or delimited txt from within MC. You can then open that list inside this program and have it create track years for each track. It will then export that back into a mpl that can be imported back into MC, updating any fields that were updated during the search.

This uses discogs for the search.
During export from this program it allows you to select which fields you want to include in your new mpl file. At this time it's probably best not to select the Date Created, Date Modified or Date Imported field. MC uses unix style date-times and I currently only export each field as displayed. So if you were to update Date Imported field (not sure why you ever would) with a normal readable MM/DD/YY HH:MM AM/PM then include that field and import back into MC you Date Imported field in MC will be wrong. At some point I may add a conversion to the export for handles this but for now it doesn't... just a warning. Backup you database before making any changes.

This is an excel file using VBA. You must allow macros in excel.
At the moment the only way to stop a search in progress is to use ctrl-break. You may have to hit it several time till excel acknowledges this. Then you'll get a dialog saying "Code execution has been interrupted" with several options, just press "End" and you can continue from there. Excel basically disables interaction with button and most other things during the running of code so this is about the only option. I can however add some error catching and make it seem more graceful with a more appropriate dialog box, at some point I will.

URL removed, bad site.
1) re-worked the whole menu system to use a form instead and is broken up into sections. Hopefully more user friendly.
2) Added the ability to import sheets from an old version of the program.
3) disabled opening mpl lists in excel versions before 2010. Causes earlier versions to crash. Exporting as txt from MC is the better option anyways.
4) few other minor things like status updates.

URL removed, bad site.
1) changed file filter for opening other workbooks to *.xl*
2) Lowered the minimum search delay to 1 second. (discogs has a limit of 5000 searches per 24hrs, don't exceed that or they may ban your IP).
3) | is entered as the default value during the delimiter prompt. This is the delimiter used by MC.

URL removed, bad site.
1) Added a cancel button to interrupt searches. During a search the button won't look like it was pressed but it will be acknowledged after the current search finishes.
2) Fixed the minimum 1 second delay. Minimum was getting set back to 5 sec after opening the workbook if you don't open an external list.
3) Added Search URL and Release URL columns to end of the sheet when a search gets performed. Search URL is the main search that returns all releases related to the track. Release URL is a link to the page of the release determined to be the earliest year.
4) Added ability to retrieve Genre for each track. This uses the Release URL found. Currently if discogs lists multiple genres they are all used. Year and genre require different searches so it's possible to double the number of searches to discogs per track.

URL removed, bad site.
1) Will now remember settings for export fields and all search settings after closing.

URL removed, bad site.
Corrected problem where 9999 was still being returned for the year on some failed searches when it should just shade the cell.
Add some user customizable passive filtering.

rick.ca · « **Reply #1 on:** January 21, 2011, 06:37:06 pm »

Making progress...

In many cases the first item (and often more) in the search results has no year, and the routine returns "0" even if the an entry with the correct year is further down the list. Also, it might help to use the search option to sort the results by year. The "no year" entries will probably still be at the top, but the correct entry will be the first one that does have a year. That would make it easier for the user trying to see what's happened by looking at the QueryPage, if not your code.

My test subject, BTW, was the Rolling Stone 500. Of the 500, 76 had this result (i.e., it's very significant).

justsomeguy · « **Reply #2 on:** January 21, 2011, 08:14:33 pm »

Well that was a stupid mistake on my part. I shouldn't have posted the first release a 4am. I had switched one variable to test something else and forgot to change it back. So each year was being compared against 0 to see which was earlier so obviously 0 will always be the result. Easy fix, 2 or 3 keystrokes. This should bring your errors down to almost nothing.... hopefully.

I had originally set the advanced search to sort by year but I ran into several instances where the entire first page of results had no years, for instance Queen - Bohemian Rhapsody. Without sorting it pulls the correct year from the 8th result. I've found (for my testing) that the advanced search without sorting tends to return the most relevant results near the top and that the earliest year is somewhere within that first page, almost always within the first 10 results. Guess that might not always hold true though in rare cases. As of now I'm only looking at the first page. It would be easy enough to search multiple pages but that would result in several requests to the webserver for each track and I wanted to avoid that.

I did add an option to have the results sorted by year however.

Anyhow fixed version released.
URL removed, bad site.

justsomeguy · « **Reply #3 on:** January 21, 2011, 09:18:33 pm »

If you have other searches that return wrong results could you list at least a few of the Artist-TrackNames so that I can check the results against my parsing logic?

Thanks

rick.ca · « **Reply #4 on:** January 21, 2011, 09:43:46 pm »

It's running now, and I notice many of the "not founds" are artists with names beginning with "The." Have you figured out what's going on there? It seems Discogs puts articles at the end (e.g., Rolling Stones, The). What I'm confused about is many of those work (e.g., The Ramones), while many don't (e.g., The Rolling Stones).

justsomeguy · « **Reply #5 on:** January 21, 2011, 10:31:04 pm »

I think I did notice that as well with names including things like "the". I wonder if just doing a find and replace internally when I build the search string and just remove the "the"s and search for just "Rolling Stones" would work around this. I'll play with it and see what I can come up with.
How are the rest of the results looking?

rick.ca · « **Reply #6 on:** January 21, 2011, 10:40:55 pm »

You post while I was preparing this—it seem we've come to the same conclusion...

The following work...

The Animals
The Band
The Beach Boys
The Beatles
The Byrds
The Carpenters
The Chantels
The Chiffons
The Chords
The Clash
The Crystals
The Cure
The Doors
The Drifters
The Flamingos
The Four Tops
The Impressions
The Jam
The Kingsmen
The Kinks
The Lovin' Spoonful
The Mamas & The Papas
The Pixies
The Platters
The Police
The Ramones
The Ronettes
The Shirelles
The Smiths
The Spencer Davis Group
The Stooges
The Supremes
The Temptations
The Troggs
The Verve
The Who
The Zombies

These don't...

The Allman Brothers Band
The B-52's
The Beach Boys
The Byrds
The Everly Brothers
The Five Satins
The Five Stairsteps
The Isley Brothers
The Lovin' Spoonful
The Righteous Brothers
The Rolling Stones
The Sex Pistols
The Shangri-Las
The Staple Singers
The Sugar Hill Gang
The Young Rascals

I don't see any pattern. So I'm re-running the first group with "The " removed...and they still work.

So it seems "The " should be stripped from Artist for the sake of doing the search.

And with that, after changing a few Artist and Track names for obvious issues, the year is found for all 500!

justsomeguy · « **Reply #7 on:** January 21, 2011, 10:50:48 pm »

Well that's what I was hoping to hear. I'll make the change to strip "the" from any artist names. Glad to hear too that it worked on 500 tracks. I hadn't tried that large of a search group yet. Have you tried exporting the results yet?

justsomeguy · « **Reply #8 on:** January 22, 2011, 12:25:31 am »

Uploaded new version that strips "the" internally for the search only from the artist name.

URL removed, bad site.

vagskal · « **Reply #9 on:** January 22, 2011, 03:26:45 am »

Great effort! I tried the latest version out (advanced search) on a compilation and got these results:

   Chicken Wire   The Pernice Brothers
1941   Gloomy Sunday   Billie Holiday
2007   Eleanor Rigby   The Beatles
2003   Hurt   Johnny Cash
1992   I Know It's Over   The Smiths
2000   He Stopped Loving Her Today   George Jones
2007   Hallelujah   Jeff Buckley
   Dance with My Father   Luther Vandross
2002   Concrete Angel   Martina McBride
2007   In the Real World   Roy Orbison
   Brick   Ben Folds Five; Ben Folds
1986   In the Wee Small Hours of the Morning   Frank Sinatra
   Naked as We Came   Iron & Wine
1964   Anyone Who Had a Heart   Dionne Warwick
1998   My Mom   Chocolate Genius
99999   Shilo   Neil Diamond
2007   Back to Black   Amy Winehouse
1981   I've Gotta Get a Message to You   Bee Gees
2002   Lost Cause   Beck
1971   That's the Way I've Always Heard It Should Be   Carly Simon
1969   Space Oddity   David Bowie
1964   A Change Is Gonna Come   Sam Cooke
1998   No Surprises   Radiohead
1997   Nothing Compares 2 U   SinÃ©ad O'Connor
1980   The River   Bruce Springsteen

I think the correct year info should be:

The Pernice Brothers - Chicken Wire - 1998
Billie Holiday - Gloomy Sunday - 1941
The Beatles - Eleanor Rigby - 1966
Johnny Cash - Hurt - 2002
The Smiths - I Know It's Over - 1986
George Jones - He Stopped Loving Her Today - 1980
Jeff Buckley - Hallelujah - 1994
Luther Vandross - Dance with My Father - 2003
Martina McBride - Concrete Angel - 2002
Roy Orbison - In the Real World - 1989
Ben Folds Five; Ben Folds - Brick - 1997
Frank Sinatra - In the Wee Small Hours of the Morning - 1955
Iron & Wine - Naked as We Came - 2004
Dionne Warwick - Anyone Who Had a Heart - 1963
Chocolate Genius - My Mom - 1998
Neil Diamond - Shilo - 1967
Amy Winehouse - Back to Black - 2006
Bee Gees - I've Gotta Get a Message to You - 1968
Beck - Lost Cause - 2002
Carly Simon - That's the Way I've Always Heard It Should Be - 1971
David Bowie - Space Oddity - 1969
Sam Cooke - A Change Is Gonna Come - 1964
Radiohead - No Surprises - 1998
Sinéad O'Connor - Nothing Compares 2 U - 1990
Bruce Springsteen - The River - 1980

rick.ca · « **Reply #10 on:** January 23, 2011, 01:43:25 am »

Quote from: justsomeguy on January 21, 2011, 10:50:48 pm

I'll make the change to strip "the" from any artist names.

Oops. Only from the beginning of the name!!! All the The ___ Brothers are failing because the search is for The ___ Brors.

Here's something you don't need to do—it's best left up to the user. The search will fail if, for whatever reason, the track title is "too wordy." The easiest solution is to forget about what the "correct" title is, and just shorten it to something that's bound to work. For example, I had trouble with the following:

284. Elvis Costello - (What's so funny about) Peace, Love and Understanding
Solution: Elvis Costello - Understanding

319. Alice Cooper - School's Out (for the Summer)
Solution: Alice Cooper - School

397 Blue Oyster Cult - (Don't fear) the Reaper
Solution: Blue Oyster Cult - Reaper

Such problems can easily be fixed by editing the Name, resetting the year (i.e., remove the "not found" highlight) and re-running the routine. It would be helpful if there were a button to reset selected year cells. Or remove the highlighting automatically if the Artist or Name cells are changed (conditional formatting?).

Tomorrow, I'll check the 55 tracks for which the year differed by more than +/- 2 from what I had. I don't have much confidence in the data I looked up manually, but in some cases it seems more likely to be correct than what the plugin returned.

justsomeguy · « **Reply #11 on:** January 23, 2011, 03:44:44 am »

Quote

Oops. Only from the beginning of the name!!! All the The ___ Brothers are failing because the search is for The ___ Brors.

Ya I just read vagskal's post above yours a few hours ago and caught my mistake in a few minutes. I was replacing all instances of "the" when I meant to replace "the ". That one space was critical.

Quote

It would be helpful if there were a button to reset selected year cells. Or remove the highlighting automatically if the Artist or Name cells are changed (conditional formatting?).

I was actually thinking that exact thing yesterday when I was testing something and was constantly resetting the cell highlight. Easy change, I'll make it.

Will be putting up an new version shortly with that and a few other small fixes.

Alex B · « **Reply #12 on:** January 23, 2011, 03:56:52 am »

Doesn't work for me. Perhaps I have a too old Excel version (Office 2003 Professional).

The xls file opens in Excel but it can't load my mpl files (I tried a few).

See the attached screenshots & example mpl file (zipped).

In the first screenshot I have pressed the "Open external list" button and tried to open "anthems.mpl". The second sreenshot shows what I get when I press the "Debug" button. The images are from v.15, but v.17 does not change the behavior.

Log in to the forum if you can't see the attachments.

justsomeguy · « **Reply #13 on:** January 23, 2011, 04:15:05 am »

vagskal, thanks for testing it out. I went through your results and made some tweaks to the logic. Couple of the problems were caused the issue addressed in my above post about replacing "the" in the artist search string. Below is the results I get with the fixes I've made. The first year is the result of my new search and the second year is what you supplied as what the result should be.

Martina McBride - Concrete Angel, this track was released as a single in 2002 but was a new song originally part of her Greatest Hits album that was released 2001. Same thing for Radiohead - No Suprises, single was released in 1998 but was originally part of the album OK Computer released in 1997.

Now the track Bruce Springsteen - The River is a different issue. The way the searches are done is it returns all releases that contained that track name by that artist. 1975 is being pulled because the album Born To Run has a track called Meeting Across the River. The search is obviously matching the last 2 words in that track and returning as a result. Obviously we don't want that to happen. I'm trying to determine if the search string can be formatted to return exact results only which would fix this but at the moment I'm not seeing that it is possible. The other option would be to do a secondary search for all tracks within the release and then look for the exact match. Hopefully something like this would be few and far between.

1998   1998   0      The Pernice Brothers - Chicken Wire
1941   1941   0      Billie Holiday - Gloomy Sunday
1966   1966   0      The Beatles - Eleanor Rigby
2002   2002   0      Johnny Cash - Hurt
1986   1986   0      The Smiths - I Know It's Over
1980   1980   0      George Jones - He Stopped Loving Her Today
1994   1994   0      Jeff Buckley - Hallelujah
2003   2003   0      Luther Vandross - Dance with My Father
2001   2002   -1      Martina McBride - Concrete Angel
1989   1989   0      Roy Orbison - In the Real World
1997   1997   0      Ben Folds Five; Ben Folds - Brick
1955   1955   0      Frank Sinatra - In the Wee Small Hours of the Morning
2004   2004   0      Iron & Wine - Naked as We Came
1963   1963   0      Dionne Warwick - Anyone Who Had a Heart
1998   1998   0      Chocolate Genius - My Mom
1967   1967   0      Neil Diamond - Shilo
2006   2006   0      Amy Winehouse - Back to Black
1968   1968   0      Bee Gees - I've Gotta Get a Message to You
2002   2002   0      Beck - Lost Cause
1971   1971   0      Carly Simon - That's the Way I've Always Heard It Should Be
1969   1969   0      David Bowie - Space Oddity
1964   1964   0      Sam Cooke - A Change Is Gonna Come
1997   1998   -1      Radiohead - No Surprises
1990   1990   0      Sinéad O'Connor - Nothing Compares 2 U
1975   1980   -5      Bruce Springsteen - The River

justsomeguy · « **Reply #14 on:** January 23, 2011, 04:57:33 am »

New version fixing the mentioned issues above and adds a button the clear shading on selected cells ( can select multiple cells at once).

URL removed, bad site.

justsomeguy · « **Reply #15 on:** January 23, 2011, 05:01:50 am »

Quote from: Alex B on January 23, 2011, 03:56:52 am

Doesn't work for me. Perhaps I have a too old Excel version (Office 2003 Professional)

Alex
I just re-installed my copy of 2003 and you are right it errors on trying to open a list. I was a little afraid of that happening. I started and got a ways into this using 2010 before I thought about earlier versions. I'll go through the code and try to find what it is that 2003 doesn't support. Can you try this version. URL removed, bad site.
Opening mpl lists doesn't work but can you try exporting your list from MC as a delimited txt file and open that list. I just tried it here and seems to open fine in 2003.

rick.ca · « **Reply #16 on:** January 24, 2011, 11:16:46 pm »

I'm unable to open an MPL playlist in Excel 2007. It results in a runtime error '5' (invalid procedure call or argument) at line 119. A text file imports fine.

rick.ca · « **Reply #17 on:** January 25, 2011, 12:38:50 am »

Quote from: rick.ca on January 23, 2011, 01:43:25 am

Tomorrow, I'll check the 55 tracks for which the year differed by more than +/- 2 from what I had.

I finished this exercise, hoping to discover some ways to improve the search routines, but found nothing. I was right about one thing—the Rolling Stone 500 is a great "torture test." There are many different artists, most of the tracks are popular and tend to be on many releases, and they span a long period of time. In most cases, it was impossible to discern which result was correct without some research—and even that was often inconclusive. Sometimes Discogs was wrong, sometimes another good source was wrong, sometimes there's no right answer (e.g., there was a previous release by the same individual when he was in a different band).

Fortunately, the more common and intended use-scenario is much easier to deal with. Even if the user has no idea when the tracks of a compilation album were originally released, the results are not going to be all wrong. In many cases, an incorrect result will stand-out among the other correct results. The following approach seems to be working well so far...

Run with the "Advanced Sort Year" option first.
Delete years that seem incorrect and clear failed searches. Remove parenthetical remarks and fix errors in track names that might be causing problems. Run again with the "Advanced Search" option (the routine skips tracks that already have a year).
Again, delete years that seem incorrect. (And this is the lesson of my RS 500 test:) Give up. There's little point going further.
Export the result to an MPL playlist, and import this into MC.
From within MC, use Search Links to look up tracks with missing years at Discogs (where maybe you can figure out what the problem is with the search results using a browser interface) or alternative sources (AMG, Wikipedia, etc.).

I have only a few suggestions for minor improvements...

Add a button to stop processing (some users won't know about Ctrl-Break).
Add a search link to each track, in case the user wants to see a search result in a browser.
Make it easier to move saved sheets between files and be recognized as "Saved list sheet." (I was able to do this, but it required some fiddling I can't now describe.)

jfmus · « **Reply #18 on:** January 25, 2011, 07:25:53 pm »

This looks great! I am having a problem though. I can get it to find a few years on advanced search, but then I just keep getting a message box popup, "Unable to retrieve webpage. Check if discogs.com site is down". If I set it to Simple Search it will run about thirty queries with no problem. What exactly is the difference with searches? Also, just wanted to throw this out there. Would you be willing to add genre to the search as well as the year? Thanks for the work you have done, this is very cool.

jfmus

justsomeguy · « **Reply #19 on:** January 26, 2011, 12:54:37 am »

Quote from: rick.ca on January 25, 2011, 12:38:50 am

I have only a few suggestions for minor improvements...

Add a button to stop processing (some users won't know about Ctrl-Break).

excel and vba doesn't seem to be too flexible when it comes to that. When code is running most all interface interaction seems to be disabled. Not sure if there is a way around it to be able to click a cancel button. At the moment I'll add a note to the sheet while a search is running to use ctrl-break to interrupt the search. If someone smarter than I knows how do do this let me know, Thanks.

Quote

Add a search link to each track, in case the user wants to see a search result in a browser.

That thought had crossed my mind as well. Hadn't done it because there tends to be a very large number of fields included with a mpl playlist. Kind of gets unwieldy to look at in excel. I'll probably add it anyways along with a link to the actual release that was used.
On that note however, I was planning on filtering out only certain fields from the mpl to bring into the sheet. If anyone has input on fields that should be included beyond, Artist, Name, Album, Date, Genre and FileName let me know.

Quote

Make it easier to move saved sheets between files and be recognized as "Saved list sheet." (I was able to do this, but it required some fiddling I can't now describe.)

I'm a little unclear on exactly what you mean. What if when you saved the list to a new sheet, that sheet was named by default to the name of the external list it came from? Or maybe I could add a field to the saved list that specified the full path to the external list it came from and display that on the working list (as of now it just displays the list name).

Quote

The following approach seems to be working well so far...

* Run with the "Advanced Sort Year" option first.

* Delete years that seem incorrect and clear failed searches. Remove parenthetical remarks and fix errors in track names that might be causing problems. Run again with the "Advanced Search" option (the routine skips tracks that already have a year).

* Again, delete years that seem incorrect. (And this is the lesson of my RS 500 test:) Give up. There's little point going further.

* Export the result to an MPL playlist, and import this into MC.

* From within MC, use Search Links to look up tracks with missing years at Discogs (where maybe you can figure out what the problem is with the search results using a browser interface) or alternative sources (AMG, Wikipedia, etc.).

Ya unfortunately this isn't an exact science. Way too many variables involved. The process you outlined above seems like it works fairly well. From the testing I've done it seems like it will give a good starting point especially for those like me that have absolutely no work done on their libraries as far as dating these "random type tracks". It does seem for me that most years that are wrong tend to be kinda obvious and I'm fine correcting those as I come across them in the library.
I've been looking at different databases out there and seeing if there's anyways of integrating them as part of a search or using them on their own instead of discogs. Was looking at musicbrainz, seems to be fairly popular but I had alot of trouble getting results and ran across alot of what seemed to be duplicate entries to me(or maybe it was just me).

justsomeguy · « **Reply #20 on:** January 26, 2011, 01:09:47 am »

Quote from: jfmus on January 25, 2011, 07:25:53 pm

This looks great! I am having a problem though. I can get it to find a few years on advanced search, but then I just keep getting a message box popup, "Unable to retrieve webpage. Check if discogs.com site is down". If I set it to Simple Search it will run about thirty queries with no problem. What exactly is the difference with searches? Also, just wanted to throw this out there. Would you be willing to add genre to the search as well as the year? Thanks for the work you have done, this is very cool.

jfmus

Thanks

I'm not sure exactly why this might be happening. I just got done running a list of about 200 tracks and it went through without a hitch. I'm assuming you were using the latest version .18. That message you are getting is displayed if the query gets no response from the website and it just times out, same as your browser would. It might be possible the site was a little overloaded and slow at the time. Did you try again at a later time? Most of my searches have been done at night usually after 11PM when I have time to work on this.

Genre is a very subjective thing and not every one agrees on what should be tagged as what. That said it would be possible. It would require 2 queries to discogs per track though. Looking at the info they supply I could also pull info for the style field as well. Might be nice to have. I'll look at possibly adding it. Not gonna have much time the next few days to work on any of this but hopefully by the weekend I'll have more time.

justsomeguy · « **Reply #21 on:** January 26, 2011, 02:01:53 am »

Just a quick update before I goto bed.
Fixed 2 bugs
1) sometimes retrieving a saved list sheet didn't load all tracks.
2) in building the search string I wasn't accounting for "&" within the artist name that MC uses in the mpl when a "&" exists in the name.

URL removed, bad site.

rick.ca · « **Reply #22 on:** January 26, 2011, 02:23:40 am »

Quote

I was planning on filtering out only certain fields from the mpl to bring into the sheet...

I can't import MPL anyway. But if I could, I wouldn't want to include all fields in my normal view anyway. What I've been doing is sending the tracks to a "Track Year" playlist. I've configured that playlist to include only the fields I need (Artist, Name, Year and Filename). In MC, I copy [Year] to [Year.old] for backup. In Excel, I rename [Year] to [Year.old], add new columns for [Year] and [Difference] = Year - Year.old.

I suppose it would be handy to let the routine extract the relevant fields from an MPL, but I think you have to provide the user some control over that. Maybe selection list with the essential fields pre-selected.

Quote

I'm a little unclear on exactly what you mean.

When you released a new version, I tried moving the list I was working on into the new version of the workbook. I used the Excel function for moving/copying a sheet from one workbook to another. When the sheet was in the new workbook, I was unable to load it as the "Working List." I can't remember how I resolved that exactly. Something like re-loading the original list, and then cutting & pasting the current list contents into it.

broncodan · « **Reply #23 on:** January 28, 2011, 12:33:28 pm »

I was taking a look at this. Wasn't exactly sure how to start - but I already had a custom field for Best Of/Greatest Hits albums tied to a playlist - so I exported the playlist as an MPL and went to load it in your spreadsheet - using excel 10. I clicked open external list - selected my playlist (mpl) and got a run time error. see screen shot for error:

rick.ca · « **Reply #24 on:** January 28, 2011, 03:30:57 pm »

Quote

I clicked open external list - selected my playlist (mpl) and got a run time error.

This appears to be the same as I reported above. A text playlist will import fine.

broncodan · « **Reply #25 on:** January 28, 2011, 06:10:30 pm »

Thanks - text file import worked. What is the difference beteween advanced sort year and advanced search? I have about 10000 tracks that are from best of/greatest hits albums - some of them are duplicates becasue I have 2 differnt best of collections for some. At 5 seconds apiece will still take about 14 hours to complete list. I am guessing the delay is to not bog down Discogs? Any way to speed this up?

I am going to run a test on some compliations that I have - rock of the 80's a rolling stone one that spans several decades, etc. - will post comments after test.

I have noticed that I am getting the year 99999 for several Beatles songs - not sure why this happens.

And finally - I how does this update the library after you finish getting the years? You run the program, update the years in the column selected, export to mpl. What happens when you import this list - does it just update the specific tracks in the library with the updated data? Do you need to add the custom column to MC prior to importing?

rick.ca · « **Reply #26 on:** January 28, 2011, 08:01:47 pm »

"Advance Sort Year" is the same as "Advanced" but uses the Discogs option to present results sorted in ascending order by year. This ensures the earliest release will appear first. Unfortunately, it also puts all the releases with no year first. If those fill the first page of 20 items, the result is "99999." Thus my suggestion to run with this option first, then clear the "99999" results and run again with the "Advanced" option.

To get the results back into MC, export to MPL, selecting (at least) [Year] and [Filename] (to identify the record). Importing that into MC will overwrite [Year] in all the same records in MC. Note that an empty [Year] in the list will effectively delete any existing value for that record in MC. I take a cautious approach and first copy [Year] to [Year.old] in MC. After import, I review the expression column Math([Year]-[Year.old]) before deciding which value to keep.

broncodan · « **Reply #27 on:** January 30, 2011, 03:36:08 pm »

Thanks Rick - your instructions on how to process the files works well - pointed out some not so obvious spelling errors in some of my files.

I have to say after messing around a bit with this - it is great and something that I always wanted to add to my library. And the potential it has to allow updates to the library via Excel are very promising (I have a bunch of chart data in excel that I want to add to my library and I am hoping that I can use this program to add that too.

Anyway A few things I wanted to confirm/add -

it appears to me the if I add a new column to the library before I export the list I want to add years to - I used "Original Year - Track", export the list with this field available, have the program add the years to that field in the excel program - save as a playlist including that field - importing the playlist via MC - it updates the Original Year - Track column in MC bbut doesn't appear to up the actual mp3 files. Which is fine with me - as I prefer to keep this data out of the tag anyway. I also have the save in tag button unchecked for this new column. - I did try to save the tag to one of the MP3's - the date modified changed on the file and I could see that it was in there with MP3 tag.

If you import the MPL without adding the column it does appear to add the column in the pain view but if you go to library options and look at the library fields it is not there. But if you add it in the library field and re-import the playlist the data shows up.

Thanks for your work - I envy your excel skills!

rick.ca · « **Reply #28 on:** January 30, 2011, 04:54:12 pm »

The relationship of fields in MC and column headings in Excel is straightforward. Anything column can be added to Excel and imported into MC (via an MPL playlist) as long as the column heading corresponds exactly to a field name in MC. If it doesn't, it won't be added. Whether or not the data is written to file tags depends only on how the applicable fields in MC are configured. If you wanted, you could configure [Original Year - Track] to be saved, and [Year] not saved.

Note that [Year] is not actually a field in MC, although it will behave as if it is. It's actually the expression field [Date (year)] which extracts the year from the date. When [Year] is changed, the result is actually to change [Date] to that value (i.e., if it were a full date before, it will be changed to just a year). And by default, [Date] is saved in the file tag.

justsomeguy · « **Reply #29 on:** January 30, 2011, 06:31:14 pm »

Ok, been a while since I was able to do any work on this but had time yesterday and have a new version available.

1) re-worked the whole menu system to use a form instead and is broken up into sections. Hopefully more user friendly.
2) Added the ability to import sheets from an old version of the program.
3) disabled opening mpl lists in excel versions before 2010. Causes earlier versions to crash. Exporting as txt from MC is the better option anyways.
4) few other minor things like status updates.

URL removed, bad site.

justsomeguy · « **Reply #30 on:** January 30, 2011, 07:41:22 pm »

First, thanks Rick for helping out with answering questions on the program.

Quote from: broncodan on January 28, 2011, 06:10:30 pm

I have about 10000 tracks that are from best of/greatest hits albums - some of them are duplicates becasue I have 2 differnt best of collections for some. At 5 seconds apiece will still take about 14 hours to complete list. I am guessing the delay is to not bog down Discogs? Any way to speed this up?

Yes delay is there to keep the hits to the discogs server reasonable. 5 seconds is just an arbitrary number I picked for the minimum delay. I put it in just in case they may ban your ip for too many requests too quickly.

Quote from: broncodan on January 28, 2011, 06:10:30 pm

I have noticed that I am getting the year 99999 for several Beatles songs - not sure why this happens.

hmm, I thought I changed that. I'll have to look again. It should be shading the cell if a search returns no year.

Quote from: broncodan on January 30, 2011, 03:36:08 pm

Thanks Rick - your instructions on how to process the files works well - pointed out some not so obvious spelling errors in some of my files. I have to say after messing around a bit with this - it is great and something that I always wanted to add to my library. And the potential it has to allow updates to the library via Excel are very promising (I have a bunch of chart data in excel that I want to add to my library and I am hoping that I can use this program to add that too.

Glad it sounds like this should be useful for you. What type of chart data are you referring to?

rick.ca · « **Reply #31 on:** January 30, 2011, 10:52:20 pm »

Quote from: justsomeguy on January 30, 2011, 06:31:14 pm

1) re-worked the whole menu system to use a form instead and is broken up into sections. Hopefully more user friendly.

Nice.

Quote

2) Added the ability to import sheets from an old version of the program.

The file mask for the import should be *.xl*, not *.xls. Those using Excel 2007 and 2010 probably want to save this workbook as an .xlsm—to avoid all of those annoying warnings about compatibility, macros, etc.

And not that it matters, but this is not just so sheets from prior versions can be imported. The data to be imported might come from anywhere—if the user wants to use the export to MPL functionality for other data (e.g., as mentioned, chart data). For the record, I note it will import sheets from other workbooks (I changed the file mask)—as long as the sheet name begins with a period.

Quote

3) Exporting as txt from MC is the better option anyways.

Not a big deal, but maybe the delimiter could default to the "|" used by MC. That would make it easier on the first-time user who might not know that's what's used. Or what a delimiter is.

Quote

Yes delay is there to keep the hits to the discogs server reasonable. 5 seconds is just an arbitrary number I picked for the minimum delay. I put it in just in case they may ban your ip for too many requests too quickly.

I've been wondering about this from the start. In looking for answers, I find Discogs has an API. It's existence might imply we're supposed to use it, although I couldn't find any policy statement to that effect. Maybe it's just there for the convenience of those who want results in XML. In any case, it includes a restriction: "API usage is limited to 5,000 requests per 24-hour period, per IP address." Regardless of our interpretation of whether we should be using the API, it would be wise to assume the restriction applies whether the API is used or not.

This also suggests the time delay is probably making no difference. If Discogs is concerned about this sort of use (e.g., not because of the use, but because non-API users are harder to monitor) then one IP's 4,999 queries are going to be equally apparent to them whether 1 or 5 or 10 seconds apart. Anyway, that's my rationalization. I'm going to stick with it until banned.

justsomeguy · « **Reply #32 on:** January 31, 2011, 12:43:25 am »

Quote from: rick.ca on January 30, 2011, 10:52:20 pm

Nice.

The file mask for the import should be *.xl*, not *.xls. Those using Excel 2007 and 2010 probably want to save this workbook as an .xlsm—to avoid all of those annoying warnings about compatibility, macros, etc.

And not that it matters, but this is not just so sheets from prior versions can be imported. The data to be imported might come from anywhere—if the user wants to use the export to MPL functionality for other data (e.g., as mentioned, chart data). For the record, I note it will import sheets from other workbooks (I changed the file mask)—as long as the sheet name begins with a period.

Not a big deal, but maybe the delimiter could default to the "|" used by MC. That would make it easier on the first-time user who might not know that's what's used. Or what a delimiter is.

All good points. I'll change the file mask.
About the delimiter I'll make sure that it is stated during the import process that MC uses "|" and maybe have that already entered as default.
Also true about the import function. You can bring in sheets from any other workbook and then use the export function to bring that into MC. Like you said above though, as long as the sheet name you are importing starts with a ".". I just use the period at the beginning of each sheet name that is user generated to be able to easily distinguish them in code. I could do it without the periods and just filter the known static sheet names but wanted to leave it so I could add other sheets without having to add them to the filter in code.

Quote from: rick.ca on January 30, 2011, 10:52:20 pm

I've been wondering about this from the start. In looking for answers, I find Discogs has an API. It's existence might imply we're supposed to use it, although I couldn't find any policy statement to that effect. Maybe it's just there for the convenience of those who want results in XML. In any case, it includes a restriction: "API usage is limited to 5,000 requests per 24-hour period, per IP address." Regardless of our interpretation of whether we should be using the API, it would be wise to assume the restriction applies whether the API is used or not.

This also suggests the time delay is probably making no difference. If Discogs is concerned about this sort of use (e.g., not because of the use, but because non-API users are harder to monitor) then one IP's 4,999 queries are going to be equally apparent to them whether 1 or 5 or 10 seconds apart. Anyway, that's my rationalization. I'm going to stick with it until banned.

I also seen the reference to the API. I was thinking of implementing that method at some point instead. It would require each user to register at the website to get their own "API key". It would be an easier and safer way (if for some reason discogs changed the format of their results page) to parse the info from xml. Only thing I'm hesitant about is I'm trying to keep this not dependent on any outside or non default libraries in vba (or outside programs) and it looks like the xml file returned is gzip'd and I don't think vba will handle that on its own.

I have run with no delay myself as well and have been fine so far. I guess I'll open it up to allow any user to set it down to 1 second. With the warning to anyone "IF YOU GET YOUR IP BANNED DON'T BLAME ME". I would think it's safe though as long as you're conscious not to exceed the 5000 searches per 24 hrs (with the same ip).

justsomeguy · « **Reply #33 on:** January 31, 2011, 01:44:00 am »

Quick update:

1) changed file filter for opening other workbooks to *.xl*
2) Lowered the minimum search delay to 1 second.
3) | is entered as the default value during the delimiter prompt. This is the delimiter used by MC.

URL removed, bad site.

rick.ca · « **Reply #34 on:** January 31, 2011, 02:49:04 am »

Quote

Quick update:

Quick indeed! Thanks!

I don't think you need to be concerned about the API. Hopefully, those who need it for large numbers will use some common sense. It takes some time to review the results, so there's no reason to be running batches anywhere near 5,000. And after updating a collection initially, most would only use it occasionally to update no more than a few hundred files.

Quote

I could do it without the periods and just filter the known static sheet names but wanted to leave it so I could add other sheets without having to add them to the filter in code.

That's fine with me. On the rare occasion I need to do that, I'll be happy to rename the sheet in the source.

rick.ca · « **Reply #35 on:** February 02, 2011, 12:57:27 am »

Buglet: The minimum search delay is 1 second, but after the first pass it reverts to 5 seconds.

bytestar · « **Reply #36 on:** February 02, 2011, 05:55:24 am »

I would love to see this Feature as a real Plug-in for MC16

justsomeguy · « **Reply #37 on:** February 03, 2011, 12:03:08 am »

Another update, few fixes and new features.

1) Added a cancel button to interrupt searches. During a search the button won't look like it was pressed but it will be acknowledged after the current search finishes.
2) Fixed the minimum 1 second delay. Minimum was getting set back to 5 sec after opening the workbook if you don't open an external list.
3) Added Search URL and Release URL columns to end of the sheet when a search gets performed. Search URL is the main search that returns all releases related to the track. Release URL is a link to the page of the release determined to be the earliest year.
4) Added ability to retrieve Genre for each track. This uses the Release URL found. Currently if discogs lists multiple genres they are all used. Year and genre require different searches so it's possible to double the number of searches to discogs per track.

URL removed, bad site.

rick.ca · « **Reply #38 on:** February 03, 2011, 04:41:22 pm »

Wow!

Very nice!

Thanks!

bytestar · « **Reply #39 on:** February 03, 2011, 06:36:46 pm »

Please add a Option to save the default Columns for export.
also save for the default Field from Genre and Year.

justsomeguy · « **Reply #40 on:** February 15, 2011, 04:56:56 am »

Minor update:

URL removed, bad site.
1) Will now remember settings for export fields and all search settings after closing.

rick.ca · « **Reply #41 on:** February 15, 2011, 04:43:14 pm »

Thanks for the update.

As you know, I've pounded this thing—using it for big jobs like updating all my "Best of" albums at once—and found it works well. Recently, I've added a few new compilation albums, and used this in my regular routine of tagging a new album. I'm happy to report it now works very well for updating the "one-off" album as well. The controls make it easy to use and possible to efficiently update one album in just a few minutes. Nice job!

justsomeguy · « **Reply #42 on:** February 16, 2011, 01:48:19 am »

Thanks Rick, I appreciate the feedback you and others have provided to help me get the bugs worked out.

broncodan · « **Reply #43 on:** February 24, 2011, 08:27:57 pm »

I also think this is great. It seems really slow now though - I am not sure if that is discogs or the fact that it is bringing back the url information. I don't seem to be able to turn this option off. I am not sure how this information is valuable but I would like to be able to turn it off.

Other than that no real complaints.

Thanks.

justsomeguy · « **Reply #44 on:** February 25, 2011, 07:13:31 pm »

Quote from: broncodan on February 24, 2011, 08:27:57 pm

I also think this is great. It seems really slow now though - I am not sure if that is discogs or the fact that it is bringing back the url information. I don't seem to be able to turn this option off. I am not sure how this information is valuable but I would like to be able to turn it off.

Other than that no real complaints.

Thanks.

I used it a couple days ago and just tried it a few minutes ago and it seems the speed is normal to me. I have noticed at times the discogs site is a little slow to respond though. If you are searching for both year and genre it will take twice as long as this requires two seperate searched.
Right now the options for search and release urls aren't un-checkable. These have no effect on the speed of the searches. They are retrieved during a search no matter what. I'm just basically saving the results url for each search. Allows you to view the exact results page if you paste it into your browser, useful for sometimes working out incorrect results in a search. The "Search URL" is the results page that the year is pulled from and the "Release URL" is the page to the actual release that was used for that year. The release is also where the genre is pulled from.

broncodan · « **Reply #45 on:** February 26, 2011, 06:09:03 pm »

Just tried this on my old xp computer and it is way faster than my new computer with win 7 and office 10. Not sure why that is - maybe the excel differences? Is there a certain way that I should be running this in Excel 2010?

And by way faster I mean old computer checks for about 10 to 15 to one..... something must not match up in excel versions.... Any suggestions?

rick.ca · « **Reply #46 on:** February 26, 2011, 06:30:21 pm »

It works fine in my Excel 2007, and I believe justsomeguy is using Excel 2010. I don't know why it should make a difference, but have you saved it as a native .xlsm macro-enabled worksheet? Could a virus scanner be interfering with it?

broncodan · « **Reply #47 on:** February 26, 2011, 06:53:21 pm »

yes saved as xlsm. At one point is was running in compatiability mode but it has never run as fast as it is on my old computer..... Not sure what the issue is. It has always run slow for me but I thought that was the nature of the beast - even changing between sheets, saving sheets, loading sheets all take an extra long time. Now I see that something is amiss but not sure where to begin to troubleshoot it. Norton is not scanning office files and that didn't make a difference.

rick.ca · « **Reply #48 on:** February 26, 2011, 07:37:16 pm »

Quote

Norton is not scanning office files and that didn't make a difference.

Sorry, it's really a firewall action I was thinking of. Whether or not the file is included in scanning wouldn't make a difference. If it's causing your problem (and I'm only guessing), it would be because it's doing something every time the website is being queried. Does Excel have full permission to access the Internet?

broncodan · « **Reply #49 on:** March 26, 2011, 12:01:44 pm »

anyone know if discogs is down?

INTERACT FORUM

Author Topic: Track Year/Genre Lookup for Best of and Compilation albums (Read 65362 times)