When I export a playlist and then open in excel, do whatever sorting I want, I then save the file and reimport into itunes but not all the songs are there. It appears to be from "comma's" in the song name. Any solutions?

Question

When I export a playlist and then open in excel, do whatever sorting I want, I then save the file and reimport into itunes but not all the songs are there. It appears to be from "comma's" in the song name. Any solutions?

I see songs such as "Monday, Monday" or "Paint It, Black" or even the artist name such as "Fireballs, with Jimmy Gilmer" which have comma's in them, being a problem when I try to do the following:

1. Export a playlist which contains songs which are all there and play fine, but some have comma's in the title such as listed above.

2. I then open that file in Excel and bring it in as a Tab-delimited file. Everything lays out fine, and all the songs are thing (including those with comma's).

3. I then sort the varoius fields I want (which I can't do in i-tunes because i-tunes won't sort it properly), and resave the file as a newly named file, which is also a .txt file.

4. I then import that playlist into my itunes. It is exactly the same songs that I just had exported but i-tunes says some of the songs can't be found.

I have traced this problem to comma's in the file name of the song such as listed above. There must be a solution to this either as the file is opened in excel or in how it's saved or some other solution short of having to rename song/artists without using comma's.

Thanks.

Posted on Aug 31, 2011 8:41 AM

Reply

Answer 1

turingtest2

Level 10

278,179 points

Aug 31, 2011 10:31 AM in response to William Richards3

It may help to surround the path in quotes or replace the commas with some valid alternative such as an escape sequence or code like ,

Could you give a two line sample of the export format you end up with? One line that works and one that doesn't Something might become obvious.

What are you trying to achieve in terms of playlist order? It might be possible to find an alternative method which makes the import problem go away.

tt2

Reply

Answer 2

Aug 31, 2011 10:45 AM in response to turingtest2

The additional problem is the file name of the song itself. Even if I were able to delete the commas or replace with a space for example, changing the file name would result in itunes not being able to find the file. I would have to go through each file (out of about 15,000+ songs) to find any that have titles such as described in this thread and change not only the song and artist in the fields, but changing the mp3 file itself.

I fear that I am really pretty much going to have to start with a list of songs that have no comma and then do a file compare to find those songs with commas and then just physically moving them into a new playlist I'm trying to create, but this is a royal pain as I want to be able to create a variety of playlists based on these chart dates/peak positions.

I'm trying to sort the songs in this manner: I use different fields in itunes so that I can put the "peak position" of each song based on Billboard charts, as well as the date the peak position was acheived. I use the "comment box" for that. I use the "grouping" field for my peak position.

So I end up with something like "10/14/1980" in the comment box as the peak position for a song which is in the grouping field, and so forth. If I try to sort the comments column in itunes for example, it won't sort properly. If I sort the Grouping field, it will bring up all the "1's", but that's followed by "10", 11, ...19, then "2", etc. This is why I want to export the playlist and sort it as I want, and then reimport the playlist into itunes.

What I'm ultimately trying to achieve is a year-by-year peak position playlist so that I end up with every #1 song (in this case) from, say, January 1st 1980 and then every #1 song in sequential order for the rest of the year falls in line. I have chart position dates from 1955 through 1999. I have entered in all the data from Billboard books I own so I can really create some amazing playlists, but these commas are problematic. Had I known this ahead of time I would have made "Monday, Monday" as "Monday Monday" and not included commas, but it's too late for that.

Any suggestions?

Reply

Answer 3

turingtest2

Level 10

278,179 points

Aug 31, 2011 12:18 PM in response to William Richards3

The first thought is that text sorting of dates works best when the date is in yyyy-mm-dd format. If your comment field was formatted in this fashion then a smart playlist with the rules "Grouping is 1" and "Comment contains 1980" would, when sorted on the Comments field, list the number one tracks for 1980 in the order that they were at the top of the charts.

I've written a number of Windows scripts for iTunes one of which could be easily modified to rearrange the date string you've been using up until now.

An alternative approach might be to create a script to automate the creation of non-smart playlists populated and ordered with tracks that meet the criteria as an alternative to the manual manipulation I assume you are currently doing in Excel, or we could try to find a workaround for your current playlist import problem, but frankly I'd recommend reordering the date field. If you don't fancy hacking around in VBScript I don't mind reworking one of my scripts for you.

tt2

Reply

Answer 4

Aug 31, 2011 1:33 PM in response to turingtest2

Interesting. That's a great idea. I take backups of my itunes regularly (xml, etc) from the mymusic>itunes folder so it would be interesting to see what script you have in mind would look like/how it would work. I could manually go in to each comment field and reoder the dates as you suggest, but that's a lot of work, though not impossible. What would you do? Is this something you charge to do? Please let me know, and thanks for the yyyy-mm-dd formatting suggestion. It's interesting that excel can sort by the mm-dd-yyyy format. Wish itunes could.

Your workaround looks like it would eliminate the need to export the playlist and manipulate in excel and then back in again. Plus it still would leave me with the commas issue.

Let me know. Thanks!

Reply

Answer 5

turingtest2

Level 10

278,179 points

Aug 31, 2011 1:54 PM in response to William Richards3

Excel, given a chance, will see the pattern mm/dd/yyyy and interpret it as a date, convert it into numeric value which it can sort on and then format the field to convert the numeric value back to a date for display. iTunes works with numbers or text. Switching the order of the parts of the date and padding (as with track numbers) allows an alphabetic sort to give the correct results.

The offer to write a custom script for you is purely for the fun of it. I like solving problems. 🙂

Do the existing dates include zeros for padding? Do they always occur at the start or end of the comments field? Have you used the format mm/dd/yyyy consistently or might you have some in say mm/dd/yy or mm-dd-yyyy? The script I propose would want you to select some tracks for processing, then work through each one, checking the comments field for a date string in mm/dd/yyyy format (as you gave it earlier) and rewrite it as yyyy/mm/dd leaving the rest of the comments field unchanged. In theory this should be an ideal task to tackle with something called a regular expression. I keep meaning to teach myself more about them so it's all good. 😉

It would still be interesting to see an example line or two of the text file that won't import the way you want it to. I've never been big on manual playlists and I've not explored the import options but if I can nail down what is going on it might be worth filing a bug report, particularly if a playlist file exported from iTunes turns out not to be valid for importing the same information.

tt2

Reply

Answer 6

Aug 31, 2011 2:07 PM in response to turingtest2

Here are some file examples of songs in itunes, then exported as a playlist, then imported into excel using tab-delimmited. Then I sort as I want, resave as .txt (same format that itunes exports playlist with) and then import to itunes but songs with commas are not brought in. Here are some examples;

Aquarius, Let The Sun Shine In - 5th Dimension / song file is: 5th Dimension - Aquarius, Let The Sun Shine In.mp3

Uncle Albert, Admiral Halsey - Paul McCartney / song file is: Paul McCartney - Uncle Albert, Admiral Halsey.mp3

Yes, all the dates are entered as mm/dd/yyyy and are consistent. None are entered in any different way unless for a rare mistake I may have made and not noticed. And I did notice that I occasionally put 07/14/1980 for example, whereas on some I may have put 7/14/1980, but I'd have to check. NOTE: Not all songs have dates entered because they were never chart hits in Billboard such as album tracks that were never released as singles. So there are many songs with empty comment fields. There are some that have comments in them such as; "dd/mm/yyyy Released as the B-side to Hey Jude" for example.

Does this help? And yeah, this would be cool to get fixed with a script if that works. In fact, I would probably just do it on another computer just in case so that the library won't get screwed up.

As for manual playlists, that's what I use mainly though I'll start with a smart playlist created and then copy its contents into a new folder/playlist. The reason for this is that unlike smart playlists, you can move songs using dragging and dropping, plus you can delete songs you don't want that the search brought up. This happens a lot with things that are similarly rated (***** stars for example) but are comedy cuts (Jerry Seinfeld, Cheech & Chong, Monty Python, etc).

Reply

Answer 7

turingtest2

Level 10

278,179 points

Aug 31, 2011 2:24 PM in response to William Richards3

OK, give me a day or so and I should have something for you... Might be quicker if I strike lucky with a suitable RegEx example otherwise I'll need a little time to wrap my head around how it works.

tt2

Reply

Answer 8

Aug 31, 2011 3:02 PM in response to turingtest2

Terrific. I'll look forward to that and thank you for your investigating. 🙂

Reply

Answer 9

turingtest2

Level 10

278,179 points

Sep 1, 2011 8:26 AM in response to William Richards3

<ding> Your custom script called DateArranger.vbs is ready. 🙂

Download it to somewhere like your desktop. Select a few tracks in iTunes, double-click the script to run it, acknowledge any warning that comes up and allow the script to run. It starts with a prompt that allows for track by track confirmation of each change if you click no, or automatic processing of each item if you click yes. Click no the first time around so you are happy it works as you need it to.

The bigger your iTunes library the slower the task will run as iTunes rewrites the entire database after every change. The script does an in-place rearrangement of the date from mm/dd/yyyy form to yyyy/mm/dd. Leading zeros will be added to the output where appropriate in the day or the month but are optional for the input. The rest of the comment is preserved as is.

tt2

Reply

Answer 10

turingtest2

Level 10

278,179 points

Sep 1, 2011 8:26 AM in response to William Richards3

William Richards3 wrote:

As for manual playlists, that's what I use mainly though I'll start with a smart playlist created and then copy its contents into a new folder/playlist. The reason for this is that unlike smart playlists, you can move songs using dragging and dropping, plus you can delete songs you don't want that the search brought up. This happens a lot with things that are similarly rated (***** stars for example) but are comedy cuts (Jerry Seinfeld, Cheech & Chong, Monty Python, etc).

You can rearrange smart playlists too! Simply select the left-hand column that is full of numbers and you'll find you can drag songs up and down the list. Right-click on the playlist title and click Copy to Play Order to set the order that will be used if the playlist is synced to a device. iTunes will always play the order displayed on screen.

As for excluding certain things from the lists, instead of copying the smart playlist to a regular one, then filleting it, another approach is to make a playlist for things to exclude (call it Exclude perhaps) and use the rules of the smart playlists to exclude that playlist. Then you can quickly exclude any item from multiple lists simply by throwing it into the exclude list.

tt2

Reply

Answer 11

Sep 1, 2011 9:23 AM in response to turingtest2

I'm replying here as I got the email from Apple related to the most recent post above. If I try to reply to your script note, it keeps opening up this window. So I guess I'll reply to both in this one window;

On the Script you wrote, I just downloaded it. I'll give it a shot ASAP. I'm working so I'll try to slip that in ASAP. Thanks so much.

The smart playlist does not work in dragging titles whether I use the number in the left column or some other field, it still doens't work for me. 10.04.80 is the itunes version I'm on. I've never been able to do that (drag & drop within a smart playlist). I just tried again but no luck.

On the exclude suggestion, I'll try that.

Reply

Answer 12

turingtest2

Level 10

278,179 points

Sep 1, 2011 10:31 AM in response to William Richards3

You can't drag stuff into or out of a smart playlist but you should be able to reorder it...

Having selected the left hand column of numbers first I've then clicked on successive odd-numbered tracks in turn and pushed them up the list for this alternative ordering of The White Album. The image shows that I'm just about to drop track 29 in after track 27.

tt2

Reply

Answer 13

Sep 1, 2011 10:34 AM in response to turingtest2

You are right. What I wasn't doing was clicking the column at the top. Never thought to do that as I didn't follow instruction well I guess. And the fact that you don't have to do that in playlists you create (non-smart) so not sure why the behavior would be different. Now if I could only "delete" things out of a smart playlist, that would be great.

Nice on the Beatles White album. Like many, I'm a big fan.

Reply

Answer 14

turingtest2

Level 10

278,179 points

Sep 1, 2011 10:41 AM in response to William Richards3

Regular playlists are initially sorted on the play order column. If you click the heading of another column to make it sort on that then you will find that you can't reorder tracks any more...

As for "deleting" tracks from smart playlists, the Exclude playlist is the way to go. You could also use check boxes for that function (and set the smart playlists to match only checked tracks) though check boxes also have other side effects that you may not want, e.g. any unchecked tracks are skipped in normal playback.

tt2

Reply

Answer 15

Sep 1, 2011 11:08 AM in response to turingtest2

If you click the "number" column (as your suggestion in smart playlists) you can resort the order in a playlist you create even after clicking a different column.

I'm working but will check your script ASAP. Can't wait!

Thanks. Cheers!

Reply