I mostly use Musicbee as music manager (instead of player) and play my music from my phone (which scrobbles to Last.fm). I couldn't just sync my Last.fm play counts to Musicbee without loosing a bunch play counts from before I started scrobbling and until recently there was no play count sync possible between my phone and Musicbee (Luckily there is now!). The outdated play counts kept bothering me and I finally found a way to fix this.
This specific case won't occur often, but I figured some people might have use for part of the code I wrote, so I wanted to share it here. The idea is simple: download a CSV file with last.fm scrobbles; convert those scrobbles into a list of artist, title and play count since *timestamp*; add the play count in this list to the current play count in the music library.
Workflow:
- Download CSV file from
https://mainstream.ghan.nl/export.html. There are other sites out there, but this one allows you to add a timestamp, which shortens the download time. Find your unix timestamp from a converter site like
https://www.unixtimestamp.com/.
- Convert this file into a CSV file in the correct format by using excel and VBA:
1) Folder setup
2) Excel overview
3) VBA code
Option Explicit
Public Sub CreateCSV()
Dim DataWS As Worksheet
Dim TempWS As Worksheet
Dim CountWS As Worksheet
Dim CSV_WS As Worksheet
Dim OverviewWS As Worksheet
Dim Full_Path As String
Dim LastRow As Long
Dim CountLastRow As Long
Dim Idx As Long
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set OverviewWS = ThisWorkbook.Worksheets("Overview")
Set DataWS = ThisWorkbook.Worksheets("Data")
Set CountWS = ThisWorkbook.Worksheets("Count")
Set CSV_WS = ThisWorkbook.Worksheets("CSV")
DataWS.UsedRange.ClearContents
CountWS.UsedRange.ClearContents
CSV_WS.UsedRange.ClearContents
With ThisWorkbook
.Worksheets.Add After:=.Worksheets(.Worksheets.Count)
End With
Set TempWS = ThisWorkbook.ActiveSheet
Full_Path = Application.ThisWorkbook.Path & "\" & OverviewWS.Range("B2").Value
TempWS.Activate
TempWS.Cells.ClearContents
With TempWS.QueryTables.Add(Connection:="TEXT;" & Full_Path, Destination:=TempWS.Range("$A$1"))
.TextFilePlatform = 1252
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileDecimalSeparator = "."
.TextFileThousandsSeparator = ","
.TextFileColumnDataTypes = Array(xlTextFormat, xlSkipColumn, xlTextFormat, xlSkipColumn, xlSkipColumn, xlSkipColumn, xlTextFormat, xlSkipColumn)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
LastRow = TempWS.Cells(TempWS.Rows.Count, 1).End(xlUp).Row
TempWS.Rows(1).Delete
Idx = LastRow
Do While CLng(TempWS.Cells(Idx, 1).Value) < CLng(OverviewWS.Range("B1").Value)
Idx = Idx - 1
Loop
TempWS.Range("A" & Idx + 1 & ":" & "C" & LastRow).ClearContents
TempWS.UsedRange.Copy 'Range("A1:D" & Idx-1)
DataWS.Cells(1, 1).PasteSpecial xlValue
TempWS.Delete
With DataWS
.Columns("D:D").NumberFormat = "General"
.Range("D1").FormulaR1C1 = "=RC[-2] & ""##"" & RC[-1]"
.Range("D1").AutoFill Destination:=.Range("D1:D" & Idx - 1)
.Columns("D:D").Copy
End With
With CountWS
.Columns("A:A").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
.Columns("A:A").RemoveDuplicates Columns:=1, Header:=xlNo
CountLastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
.Columns("A:A").Copy
.Columns("B:B").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
.Columns("B:B").TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="#", FieldInfo:=Array(Array(1, 2), Array(2, 9), Array(3, 2)), _
TrailingMinusNumbers:=True
.Range("D1").FormulaR1C1 = "=COUNTIF(Data!C,Count!RC[-3])"
.Range("D1").AutoFill Destination:=.Range("D1:D" & CountLastRow)
.Columns("B:D").Copy
End With
With CSV_WS
.Columns("A:C").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
.Sort.SortFields.Clear
.Sort.SortFields.Add Key:=.Columns("A:A"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.Sort.SortFields.Add Key:=.Columns("B:B"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With .Sort
.SetRange CSV_WS.Range("A1:C" & CountLastRow)
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End With
OverviewWS.Range("B1").Value = DataWS.Range("A1").Value + 5 '1540080000
ThisWorkbook.Save
CSV_WS.Copy
ActiveWorkbook.SaveAs FileName:=Application.ThisWorkbook.Path & "\" & OverviewWS.Range("B3").Value, FileFormat:=xlCSV, CreateBackup:=False, Local:=True
ActiveWorkbook.Close False
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
- Run python script (Add MusicBeeIPC plugin to plugin folder first and install the python SDK -
https://getmusicbee.com/forum/index.php?topic=11492)
import csv
import logging
import win32api
from datetime import datetime
from musicbeeipc import *
mbIPC = MusicBeeIPC()
pathCSV = r"C:\Users\Michiel\Documents\MB_Development_Area\Source_file_PlayCountUpdateCSV.csv"
now = datetime.now()
failureFileName = "failureLogFile.log"
logging.basicConfig(filename=failureFileName, level=logging.INFO)
logging.info('start new logging session @ ' + str(now))
result = win32api.MessageBox(None, "You are about to change play counts in your MusicBee library.", "Script Warning", 1)
if result == 1:
with open(pathCSV, encoding="utf8") as csv_file:
csv_reader = csv.reader(csv_file, delimiter=';')
for row in csv_reader:
songCounter = 0
foundURL = ""
artistComparator = str(row[0])
titleToSearch = str(row[1])
playCountToAdd = int(row[2])
results = mbIPC.library_search(titleToSearch)
for res in results:
artist = mbIPC.library_get_file_tag(res, MBMD_Artist)
title = mbIPC.library_get_file_tag(res, MBMD_TrackTitle)
if artist.lower() == artistComparator.lower():
if titleToSearch.lower() == title.lower():
songCounter += 1
foundURL = res
if songCounter == 1:
currentPlayCount = mbIPC.library_get_file_property(foundURL, MBFP_PlayCount)
newPlayCount = int(currentPlayCount) + playCountToAdd
# Update
# noinspection PyUnboundLocalVariable
mbIPC.library_set_file_tag(foundURL, MBFP_PlayCount, str(newPlayCount))
mbIPC.library_commit_tags_to_file(foundURL)
else:
logging.info(artistComparator + ';' + titleToSearch + ';' + str(playCountToAdd))
I did some minor testing for error cases and tried to code it in a cautious way. However I can not guarantee this will work as intended for everyone. Use at your own risk.
Show Posts
