Duplicate file scan pulls up files with only one copy

Bex · Post by **Bex** » Mon Jan 16, 2006 6:18 pm

OK, try this

1) Open up Excel, goto Data->Import External Data->New Database Query
2) Choose Mediamonkey and a Query wizard starts
3) We dont want the wizard so click Cancel
4) A dialog window comes up "Do you want to continue blah blah" Click YES!
5) A Add Table windows appears, close it and press the SQL button.
6) A SQL Window appears. Copy and paste this code into the window:

Code: Select all

SELECT Songs.ID, Songs.SongTitle, Songs.SongPath
FROM 
(SELECT Songs.SignPart1, Songs.SignPart2, Songs.SignPart3, Songs.SignPart4, Count(Songs.ID) 
FROM Songs
GROUP BY Songs.SignPart1, Songs.SignPart2, Songs.SignPart3, Songs.SignPart4
HAVING (((Songs.SignPart1)<>0) AND ((Songs.SignPart2)<>0) AND ((Songs.SignPart3)<>0) AND ((Songs.SignPart4)<>0) AND ((Count(Songs.ID))>1))) as Query1,
Songs
Where Songs.SignPart4 = Query1.SignPart4 AND Songs.SignPart3 = Query1.SignPart3 AND Songs.SignPart2 = Query1.SignPart2 AND Songs.SignPart1 = Query1.SignPart1
ORDER BY Query1.SignPart1, Query1.SignPart2, Query1.SignPart3, Query1.SignPart4;

7) Click OK and a new dialog box appears "SQL Query can't be bla bla" Click OK!
8 ) Now you should see a result, if so what do you get?

/Bex

Exaxxion · Post by **Exaxxion** » Mon Jan 16, 2006 6:29 pm

I got a huge list of songs...

It appears that for every instance that includes two of the same file path, there are different ID numbers for each entry. It would appear that one entry is in the 3000 range and one is in the 9000 range for each of the "false duplicate" pairs.

Bex · Post by **Bex** » Mon Jan 16, 2006 6:36 pm

And if you open up a folder of one of these files do you see the song twice?
If you play the file do both files get bolded?

Exaxxion · Post by **Exaxxion** » Mon Jan 16, 2006 6:40 pm

When I open the folder containing these files, they are only listed once. This means that only that one copy is bolded when I play it, as another copy of the file is neither listed nor actually exists.

Bex · Post by **Bex** » Mon Jan 16, 2006 6:47 pm

Strange! But in someway your db seems to be corrupt.
Try to compact your db. In MM goto File->Maintain Library check everything BUT the first one and click ok.

Did it help?

Exaxxion · Post by **Exaxxion** » Mon Jan 16, 2006 6:51 pm

I compacted the database as you said, then restarted MM, but the list of (1x) tracks is still there.

Bex · Post by **Bex** » Mon Jan 16, 2006 7:00 pm

Sorry, but I dont know whats going on. I cant help you anymore. If you had MS Access I could have shown you a way to delete those annoying duplicates. But since you dont, sorry.

Wait! Is a final check, copy and paste this code into MS Query:

Code: Select all

SELECT Songs.ID, Songs.Idmedia, Songs.SongTitle, Songs.SongPath
FROM 
(SELECT Songs.SignPart1, Songs.SignPart2, Songs.SignPart3, Songs.SignPart4, Count(Songs.ID) 
FROM Songs
GROUP BY Songs.SignPart1, Songs.SignPart2, Songs.SignPart3, Songs.SignPart4
HAVING (((Songs.SignPart1)<>0) AND ((Songs.SignPart2)<>0) AND ((Songs.SignPart3)<>0) AND ((Songs.SignPart4)<>0) AND ((Count(Songs.ID))>1))) as Query1,
Songs
Where Songs.SignPart4 = Query1.SignPart4 AND Songs.SignPart3 = Query1.SignPart3 AND Songs.SignPart2 = Query1.SignPart2 AND Songs.SignPart1 = Query1.SignPart1
ORDER BY Query1.SignPart1, Query1.SignPart2, Query1.SignPart3, Query1.SignPart4;

And tell me if the IDMedia is the same for two identical tracks?

Exaxxion · Post by **Exaxxion** » Mon Jan 16, 2006 7:04 pm

Actually, the IDMedia column is identical for all of the listed tracks. All of them are in mp3 format, which is what I assume the "46" means.

EDIT: Come to think of it, would the trial version of Access 2003 be able to perform the duplicate removal?

Bex · Post by **Bex** » Mon Jan 16, 2006 7:14 pm

IdMedia is the drive (Eg C, D etc..).

This is bugging me. Try this code and tell me what the differens is between two tracks.

Code: Select all

SELECT Songs.ID, Songs.IDArtist, Songs.IDAlbum, Songs.SongOrder, Songs.SongTitle, Songs.SongPath, Songs.Year, Songs.Genre, Songs.FileLength, Songs.SongLength, Songs.IDMedia, Songs.CacheStatus, Songs.CacheName, Songs.Rating, Songs.Bitrate, Songs.Seekable, Songs.Broadcast, Songs.PreviewState, Songs.PreviewName, Songs.PreviewStartTime, Songs.PreviewLength, Songs.Author, Songs.SamplingFrequency, Songs.Stereo, Songs.Copyrighted, Songs.Original, Songs.VBR, Songs.BPM, Songs.SignType, Songs.SignPart1, Songs.SignPart2, Songs.SignPart3, Songs.SignPart4, Songs.PlayCounter, Songs.LastTimePlayed, Songs.AudioCDTrack, Songs.FileModified, Songs.MaxSample, Songs.NormalizeTrack, Songs.Custom1, Songs.Custom2, Songs.Custom3, Songs.DateAdded, Songs.OrigFileLength
FROM 
(SELECT Songs.SignPart1, Songs.SignPart2, Songs.SignPart3, Songs.SignPart4, Count(Songs.ID) 
FROM Songs
GROUP BY Songs.SignPart1, Songs.SignPart2, Songs.SignPart3, Songs.SignPart4
HAVING (((Songs.SignPart1)<>0) AND ((Songs.SignPart2)<>0) AND ((Songs.SignPart3)<>0) AND ((Songs.SignPart4)<>0) AND ((Count(Songs.ID))>1))) as Query1,
Songs
Where Songs.SignPart4 = Query1.SignPart4 AND Songs.SignPart3 = Query1.SignPart3 AND Songs.SignPart2 = Query1.SignPart2 AND Songs.SignPart1 = Query1.SignPart1
ORDER BY Query1.SignPart1, Query1.SignPart2, Query1.SignPart3, Query1.SignPart4;

To make it more visible goto File->"Return Data to Microsoft Excel"

/Bex

Bex · Post by **Bex** » Mon Jan 16, 2006 7:17 pm

Exaxxion wrote: EDIT: Come to think of it, would the trial version of Access 2003 be able to perform the duplicate removal?

Yes, I think so! Do you have it?

Exaxxion · Post by **Exaxxion** » Mon Jan 16, 2006 7:24 pm

I tried the last code you posted, and it would seem that the difference between the false duplicates is the IDAlbum. All of them are between 770 and 810, and the other in the set is in the 3000s. Everything else appears to be normal.

I'm getting the trial for Access 2003 now.

EDIT: Crap... the trial is an "Order a CD" option only. I don't think I want to do that ^_^;

Bex · Post by **Bex** » Mon Jan 16, 2006 7:26 pm

I see, how many tracks are we talking about?

Exaxxion · Post by **Exaxxion** » Mon Jan 16, 2006 7:30 pm

There are 36 (1x) groups listed in mediamonkey, totalling 72 false-duplicate files in the SQL list. There are a few groups of valid duplicates listed though.

EDIT: Upon further inspection, there is an online-only "Test Drive" for Access that may be able to do the job. It says that only saving and printing documents is disabled.

Bex · Post by **Bex** » Mon Jan 16, 2006 7:46 pm

Good news here, it seems that the SQL window in MS Query accepts delete commands. So do this:
1) Make a backup (Copy) of your mediamonkey.mdb located in \My Doucuments\My Music\Mediamonkey folder.
2) Study the result from the last query carefully and decide what tracks should be deleted.
3) Give me the ID of those track you want to delete

/Bex

Exaxxion · Post by **Exaxxion** » Mon Jan 16, 2006 7:56 pm

The list is as follows:

9479
9492
9462
9476
9480
9495
9496
9497
9471
9484
9477
9469
9473
9463
9488
9485
9472
9483
9465
9481
9486
9494
9467
9470
9475
9468
9466
9491
9482
9478
9489
9487
9474
9464
9490
9493

Sorry, I know it's quite long ^_^;