Auto playlist generator - update

[Jasper Warwick]

I have done an update to my auto playlist generator. It now stores the year the album was released and this can be used to create playlists based on the date the album was produced (e,g, "70's albums",  "2019 albums"). 

The spreadsheet scans the B2 and builds tables of artist, album, and track, it then reads the ID3 tag of each track and stores it in the spreadsheet along with the date the album was loaded onto the B2 and the release date of the album. Pressing a button gets the spreadsheet to auto create playlists based on genre, newness and release date of the album. By default the B2 only stores basic information (Artist and Album) but it is possible using software such as ID3Tag to automatically add additional information (Genre and Date).

What is means is that I can now segment my music by a number of different criteria which makes accessing my library much easier.

If I want reminding of what I have bought recently I use the "album-new" playlist which lists the most recent 200 albums I loaded onto the system in date order, newest on top.

If I am in a 70's mood then using "Album - 70's" will give me a list of all albums released in the seventies

If I am in the mood for opera then the "Opera" playlist will give me list of artists that have operatic works in their folder. etc. etc.

I can also create my own custom playlists at artist, album, and track level on the spreadsheet which are then uploaded to the B2. The spreadsheet preserved any customer playlists so they don't get deleted if you re-scan 

If like me you have created an MP3 version of the library for use on a phone or in the car it will also generate M3U playlists.

If a playlist accidentally gets deleted or corrupted on the B2 then it takes a couple of minutes to upload a replacement set from a pen drive.

It requires Excel 2010 or later to work, the copy I have uploaded has my library on it so you can see how it works but it only takes a couple of button presses to clear the spreadsheet and load a new library into it.

There is a help section on the spreadsheet to assist with getting it working

Feel free to give it a go.

[PMB]

Hi Jasper,

Thanks for the update.

BTW if you post your screenshots as attachments, rather than pasted into the text, we can view them at a larger scale.

Paul

Brennan Support.

[Jasper Warwick]

good point Paul, attached.

[Dick Cooper]

Hi

I used an ealier version to create playlists but somehow my playlists got corrupted and I need to rebuild. Do you have a version that will work with Office 2000 or do you know if the .xlsm version will work with OpenOfffice Calc please?

Thanks

[Dick Cooper]

... or I could try one of the online converters and see if that will produce something workable if you don't already have a .xls version yourself...

[Jasper Warwick]

Hi,

I don't think the xlsm version will work with open office. The later versions use data tables and those only came in with (I think 2007). There are a couple of options

If you want to post the the version you have been using I should be able to save it as an xls file that should work with earlier versions of office. 

I could also run the speadsheet for you and output the playlists which you could then import.

I also have some old copies of Office 2003 which might work better than office 2000 which I could let you have. PM me if interested.

Jasper

[Jasper Warwick]

As an aside I have just done a minor upgrade; the system now works out the total play duration in days of your library. Mine is 49.01 days!

I'll just check that there are no bugs in the playlist generation then I will post it.

Jasper.

[Jasper Warwick]

Here is the latest version 

[Dick Cooper]

Hi Jasper

After more research in the Forums to work out what I did last time, I see I was using the Microsoft Open XML converter Moc.exe to open Brennan18.xlsm on a Windows XP computer and running the sheets using Excel 2003 ... https://groups.google.com/d/msg/brennanb2/p15eQH349Lo/bBj4rBsyBQAJ

Now I'm using Excel 2000 (I couldn't port Excel 2003 across) on a Windows 10 computer, no xlsm file will open because the converter won't run. As you predicted, OpenOffice doesn't work either.

HOWEVER... I uploaded Brennan18.xlsm to cloudconvert.com and when Brennan18.xls came back, it seems to work OK (brief test, don't have a Brennan backup to run it on as the B2 is still bumbling along making a USB backup)

BUT... converting Brennan30.xlsm gives me a file I can open and run but I get a runtime error 438 in Playlists code at the line

With Worksheets ("Tracks").ListObjects("Tracks")

SO... When the B2 has finished its backup sometime maybe I'll try to run the whole thing on Brennan18.xls. If not I still have the XP computer as a last resort.

Thanks again, and I'll post again when I've re-tried running the whole thing.

p.s.Oddly, the Find Genre worksheet doesn't run on either the converted 18 or 30.xls files, although that's not a problem since I know its offset already.

[Dick Cooper]

Hi again

My mistake - Find Genre does work, forgot the trailing backslash.

The spreadsheet doesn't seem to work on a B2 backup. Maybe because the folder arrangement is X:\hardfi\music\artistname  albumname\tracks - not - X:\...\artist\album\tracks as on the B2 itself. My backup created using Backup to C on Bennan - is this the wrong kind of backup?

Instead I'm now running the spreadsheet on the \\BRENNANB2\music\ folder and it will report on progress. Processing artist 11 or 406 after 10 minutes running Build Genre List - will leave overnight as network quite slow.

Regards

[Daniel Taylor]

On the B2, the Backup and Restore functions are there only to maintain backward compatibility with the old JB7 units.  If the JB7 is not a concern for you, you should be using the Export and Import commands.  I think the spreadsheet playlist functions will work on a B2 backup that was done with the Export command.

[Jasper Warwick]

Yes best use the export function, it will definitely not work on a B2 backup.

That is quite slow, on my PC ir takes about 90 minutes to do all the steps to completely process 15,000 tracks, I am afraid I built the system for ease of programming not speed of operation!

[Dick Cooper]

OK, thanks - I was looking at the Brennan18 help which referred to backup rather than export. Confusing, eh?

Using the Brennan18.xlsm which I converted online, running Excel 2000 on my Windows 10 machine, I got runtime error 91 'Object variable or With block variable not set' processing album 633 or 861 running the Build Album Playlist function. Debug show the line of code in Albums is 'For Each Ofile in .Items'. So I guess that doesn't work after all, although all the tables up to that point looked to be correctly populated.

So I am now making an export database instead of a backup database on the B2 and I'll try running that on my old XP machine with Excel 2003 which I know worked last time. I'll see if I get the same problem. If I don't post again it's because I've died of old age waiting for the B2 export (now on track 900 of 12,000 after 30 minutes), not because I've given up.

Thanks again for your help.

[Dick Cooper]

sorry for not being clear - I got that runtime error 91 running the Brennan18 spreadsheet directly on the B2 over the network, not on the 'backup'... now on track 1500 of 12,000 creating an export database on USB... (yawn)

[Jasper Warwick]

Thanks for point that out I will change the wording.

If it fell over after processing 633 albums it is a problem with Excel not handling a funny character in the file name or album name.

If you look at where it crashed, scroll down the list of albums until you find the one where the processing stopped. then look at the files contained in it. you might find that one of them has some strange characters or punctuation. What has happened is that Excel has (not) helpfully translated those which means that it has problems locating one of the files. Change the file name to something simpler and it should work. You don't need to re run everything just from the point it fell over.

Hope this helps, email me the file if you like and I can probably tell you where to look.

Jasper

[Jasper Warwick]

Dick, it looks like you were able to progress past the error which is great, sorry it is so slow for you!

[Dick Cooper]

No, I haven't progressed past the error, I'm just off on another tack and trying to create an exported database to use with my XP computer.

Here are screenshots of where the spreadsheet got to on the Windows 10 computer, and what Explorer says is in the folder on the B2. Looks OK to me. I saved the spreadsheet when it failed, and I'm running Build Album Playlist again (while I wait for the export) in case it's a glitch...

[Jasper Warwick]

I think it may have crash on track 5 of Rock of Ages

[Dick Cooper]

Well, when I ran it again, it ran all the way through... so it was a glitch - maybe a hang on the network. I'll try again tomorrow after the export database finishes. I've made some changes to the B2 in the eatime to tidy up some errors the first run revealed (before it glitched).

Thanks again,

[Dick Cooper]

I ran Brennan18.xlsm on my Windows XP machine using the Microsoft Open XML converter to open Office 2003. Took about 20 minutes in total (12000 tracks, 860 albums, 400 artists) using a 128Gb SanDisk thumb drive export database instead of the network. Seem to have worked fine, just need to check the details. Many thanks indeed.

Meanwhile, I note in your first post you say "If a playlist accidentally gets deleted or corrupted on the B2 then it takes a couple of minutes to upload a replacement set from a pen drive" - do you know whether if I use the Load Playlists function it will REPLACE rather than ADD? And I don't have to delete all the broken playlists? If you don't know I'll delete them all first, although deleting playlists is what got me this problem in the first place...

[Dick Cooper]

Bad news/good news. I also (earlier today) tried to use Brennan30.xlsm using the same XML converter. All worked fine right up to the Create Playlist function which got a runtime 1004 error. Looking at the sheet, I see the error is in the last album and cell BD876 contains =(#REF!+#REF!++#REF!+SUM(#REF!)+SUM(#REF!))>0. All the Genre playlists and Album- playlists created to that point look correct, even the one for the last album.

This is not a big problem for me, since Brennan18 created the Genre playlists which is what I wanted, but you might like to look at the Brennan30 code because I wonder what will happen to albums released in 2020 (not that I have any yet). Snapshots below (sorry about quality, let me know if you need better).

Anyway, sorry to burden you with what might be a problem. At least I'm having a great day now I've created some playlists!

Thanks again

[Jasper Warwick]

I don't think V30 will work for you as it uses tables to manage tracks which did not come in to Excel until 2007. Any version of Excel will crash when it hits the tracks section of the workbook

[Jasper Warwick]

Use the "Load Playlist" function. it will replace all the playlists on your B2 with the copies that are on your pen drive. If you have created any playlists using the B2 features then these should be left intact on the B2. basically the Load Playlist function just copies the files on the pen drive to the B2, overwriting any files of the same name.

[Dick Cooper]

OK, that explains why Brennan30 doesn't work. No problem.

I still wonder about the hardcoded date of 2020 in cell BA876

=IF(AND($F876>=2010,$F876<2020,$BD876),1,)

what happens to albums released in 2020? Should there be a formula in BB876 for albums >=2020?

And there's hardcoded 2019 and 2018 in cells AT876 and AT877. For futureproofing would it be better to select today's year and put it in a hidden cell somewhere?

Cheers

[Dick Cooper]

Ok, thanks for update about Load Playlist. I DO need to remove any bejangled playlists that I'm not replacing. Now why can't the documentation say that (no, don't answer that). Thanks

[Jasper Warwick]

The formula errors in V30 are linked to my code that creates playlists based on the release year of the album. The error is possibly because a non numeric code was read in.

[Jasper Warwick]

Attached is a version of the latest software that has the tracks functionality removed. This should work on an older version of Excel if you want to try it.

[Dick Cooper]

OK, thanks, I will try that - sometime soon next time I power up the XP machine. Meanwhile I am celebrating that I have my playlists back (playlists for each Genre - just for each artist, not by albums - too many different albums).

Having a B2 is like keeping an elderly pet - it's quiet for ages and then it all suddenly kicks off.

Thanks once more.

[Jasper Warwick]

Re the 2020 year, yes there should be, it is just that I don't have any 2020 published albums so it is a bit academic :-)

If you are up for writing simple excel formula it can be a good way of assembling non genre based playlists. or combining genres together.

[Dick Cooper]

Hi

I converted the Brennan33 No tracks.xlsm through cloudconvert and ran with Excel 2000, worked fine up to Export M3U playlists which fell over with runtime 1004 (see attachments). Same error same place with Excel 2003 and XML converter on XP machine. No problem, since I don't need M3U playlists, and now I know not to do that. Just a thought, could you make the No tracks function a button or is the function embedded too deep? By the way, I was wrong, I do have an album released in 2020, not that it matters as I only wanted the Genre/Artists playlists. Which I've got now, thanks. So it's all good.

Thanks

[RichM]

Jasper,

Further to a separate recent thread about Why Tag? I have had another run through of your spreadsheet to see how my partially complete tagging exercise pans out. As is mentioned above, Excel has problems with some characters, in particular for my collection it is Czech names - Dvorak and Janacek which I have as Dvořák and Janáček. It is a shame that the B2 can handle them but Excel, a de facto world standard, can not!

I can rename my albums - I assume that it doesn't have to be done at track level - but I don't suppose anybody out there has any other workrounds?

Thanks for the spreadsheet, BTW, I'm still getting into it.

Rich

[Jasper Warwick]

If you can send me any names that Excel has a problem with I can look at some fixes. Because it has not been a problem for me I have not really investigated the issue. I might try creating a test artist, album, and tracks with the problem characters and see if I can get Excel to work with it.

[Jasper Warwick]

Just looking at my collection I have quite a few tracks with non English accented characters;

117 Bach - Ach grosser König, gross zu allen Zeiten.flac 106 Selig,  Franz-Joseph - 6. Aria (Alt) 'Buß und Reu'.flac

Which the system seems to handle, I can't find any special characters in the folder names. I'll try with  Dvořák and Janáček as artist and album and let you know how I get on

[Jasper Warwick]

I have just build a test folder and files with Dvořák and Janáček and it is definitely not happy!

I have had a quite look at the problem on line and there is a alternative way to read file structures, I will have a look at converting my code and upload a version that will work with special characters.

Japser.

[RichM]

Jasper,

You are a star - I guess you like a challenge. I too have some umlauts and other symbols but seems to be the Czech ones it doesn't like. I'm pleased to say I don't have any Polish music.  Thanks for looking at this - in the meantime I am plugging away with MP3tag so I can take full advantage of your revised code.

Cheers,

Rich

[Dick Cooper]

Well, just when I thought I had my playlists cracked someone else finds a problem.

Me too, I had Danú, Déanta and Lúnasa all missing from my Celtic playlist, although they were on the B2 with the right names (although the B2 web interface sorts Déanta to the end of the Ds, after Dwight Yoakam). I've just added them manually to the playlist. No problem with Meridianu's album Senza Cchiù Terra in my European playlist though, because I only have the Genre/Artists playlists loaded not Genre/Albums.

Since reading this thread, I wonder if using accents on the B2 makes sense - it's pretty difficult to do a search for Déanta when you can't remember the ascii code for é. I remember that the JB7 used to completely mangle special characters, so I edited them out of the track names in most of my albums, long before I got the B2, which only retains accents in more recent acquisitions such as Déanta. Now I'm thinking I'm going back to edit out the accents... at least in artist names.

Good luck with the changes, your spreadsheet's been a game-changer.

[Jasper Warwick]

Hi,

I have almost fixed it!

The sheet now reads and processes filenames and folders with extended character set correctly, I just need to workout how to create the playlists in a form that the B2 will read. I think the B2 needs a utf-8 character set which is a variable width character set but so far my attempts to write a utf-8 file has resulted in a Unicode character set which B2 cannot handle.

Should have it fixed today though (in between weekend chores)

Jasper.

[Jasper Warwick]

New version which show now work with special character embedded in file names.

[RichM]

Brilliant. Thanks Jasper. As I mainly use my Android tablet to control the B2, it is no problem inputting the accented characters, although I do have to remember which way round they go. I actually had a few French tracks which had loaded with the accents being replaced by a string including ¿, but they were few enough to edit out manually.

I'll load the new version today then hopefully it is just a case of plodding through the tagging exercise.

Cheers,

Rich

[Jasper Warwick]

If you are upgrading to V34 from an old version. Carry out steps 1 to 4 in the V34 instructions. For step 5 (grouping genres into playlists) just copy and paste the genre list from your old file, save the work in progress V34 workbook then click on the update all and leave the system to get on with it. This will give you a more or less complete update. You will just then need to copy and paste any custom playlists you have.

Thinking about it I should provide an "Update" button to do this automatically. I will add this if I have an hour or so free today.

Jasper.

[RichM]

Jasper,

Unfortunately that doesn't seem to have fixed the problem. I downloaded the new file and started again from scratch with Clear Data. When I try to Build Genre List, I stll get the same errors. I did try to set up a test version but my IT skills obviously aren't as good as hoped. The error message shows the offending name with no accent on the r in Dvorak, but it does have the accent on the a. This is an example - "Dvořák Legends", "Janáček Glagolitic Mass" is similarly rejected.

Sorry about that,

Rich

[Jasper Warwick]

Sorry, I missed a bit of code in the genre section. I am not sure why it got through my test run but anyway it is easy to fix and I will upload a new version shortly

[Jasper Warwick]

Attached is a new version which should now work. Unfortunately I cant fully test it as I have now deleted the test album with special characters in but I am reasonably confident it should work!

[john Henrick]

Just tried this on my one B2 what a great piece of excel based software

Artists built ok

Tracks doesn't generate anything I have cleared filter

Genres fails at artist 105 subscript out of range

What am I doing wrong ?

[Jasper Warwick]

Hi John

I don't think you are doing anything wrong. I have just rewritten the internals to cope with files that have extended characters built into the filenames (such as Dvořák and Janáček) it is possible that I have not knocked all the bugs out of the new system.

If you could save the file where it crashed and email it to me at: jw "at" jasperwarwick "dot" co "dot" uk  (I would rather not plain text my email address as I will get bombarded with spam if I do!) I can see if I can work out what caused the crash and fix it. 

alternatively try V30 at the top of this topic which will work with most people's libraries (providing they don't have special characters built into the filenames)

Jasper.

[RichM]

Jasper,

Again, thanks. I've not had time to do anything other than the first two steps but they have gone fine.

Cheers,

Rich

[Jamie]

I have an equivalent spreadsheet program to Jasper’s program except that my program auto-generates playlists from my B2 library for use with Sonos rather than with the B2. When I saw this thread I wondered whether there were any character-based scenarios that would cause my program to fail, so I asked my program to generate a playlist that would include all 33,000 tracks in my B2 library. I added an error handler to my program so that it would tell me the details of any track that it could not handle.

I thought it would be worth documenting my findings here as they highlight some other potential data issues that might cause Jasper’s program to fail. All my failures happened when Excel tried to write the offending record to the playlist file.

The first failure relates to a 1992 album by Prince that has a non-alphanumeric symbol as its title. I solved this failure by changing the B2 album title to “Symbol”. I left the non-alphanumeric symbol in the album tag. My program was then able to handle the album. Sonos happily plays the album when a track from it is included in an auto-generated playlist, and it displays the non-alphanumeric symbol from the Album tag.

The second failure relates to an alt-j album called An Awesome Wave. That album has three tracks whose titles include a non-alphanumeric symbol that I believe is called a fleuron. I removed the offending character from the three B2 names. Again, my program was then able to handle the tracks and Sonos plays the tracks successfully while displaying the original titles from the track Title tags.

The third failure was due to a non-displaying character at the end of a B2 track name. The character did not display on the B2 either. I edited the track name on the B2 to remove the offending character. Again, that solved the problem.

The fourth failure was due to accented artist names from two Slovenian Eurovision entries. The B2 did not display the accented characters correctly, so I edited the B2 names to replace these characters with the “correct” versions of the accented characters. Again, that solved the problem.

The last failure was due to a Romanian accented character in the artist name from a Moldovan Eurovision entry. The character displays correctly on the B2. I tried the same solution as for the Slovenian tracks, but it did not work. I might just remove the accented character from the B2 name in this case.

Eurovision tracks are a great test for accented characters as there are many artist and track names that include accented characters of various types. The two Slovenian tracks and the one Moldovan track were the only ones that caused the program to fail, and the Moldovan track is the only one that I cannot yet explain. My library also includes a few accented classical names such as Antonín Dvořák. The program handled them ok.

My suggestion to Jasper would be to include an error-handling routine that displays the artist, album and track details of any track that causes his program to fail. That way people can report back offending track details quickly to help him figure out whether he can change his program to handle an issue or whether the issue can only be handled by changing a name on the B2.

Unfortunately, I suspect that these types of issue can only be uncovered and resolved one at a time, and there is no way of knowing how many other issues remain to be uncovered until you get all the way through your library. Note that I resolved all the issues in my library by changing the B2 names rather than by coding changes. I am not sure what coding changes could have resolved these issues.

Good luck all!

[Mark Fishman]

I will repeat the conclusions that I came to a few years back, when dealing with Microsoft, Apple, Linux, and Sun systems, plus a variety of hardware: the only "portable" character set is 7-bit ASCII. I realize that it is unfair to all those languages that aren't American English, but every other character set I tried (including UTF-8 and UTF-16, and all the ISO variations) produced filenames or displays that differed on different software or hardware.

Bye, bye, accented characters. If pronunciation is important to you, use diphthongs.

Yes it shouldn't be that way. Yes, it is.

[Jasper Warwick]

I have to say that when I first coded this I took Mark's view and just replaced the few tracks I had that had special characters with the ASCII equivalent.

But if more people want to use it then I feel a little bad about leaving such limitations in.This is also quite an interesting exercise for me, I have just written some excel based apps the run in Chinese and Korean and so being able to handle programatically file names that are not purely ASCII will be useful to me for my work.

I will put in some more error trapping for sure, I think Jamie is right to say that it will take some time to knock out all the issues caused by odd characters in file names.

[Jasper Warwick]

I have just had it pointed out that the last update had a bug that caused it to loose file location data when clearing down the database.

This version should fix it.