Script to edit playcounts based on CSV file

[NighTeagle]

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

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)

Code

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))

**Disclaimer**
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.

[phred]

I moved this from the Developers Area to here.

[Kasionz]

This is interesting! I might try doing this with a spotify output to sync playcount from there to my updated library. I imagine there's an issue if the song doesn't exist in my library or the artist details are formatted incorrectly, so i'll have to check how the vba updates that.