PDA

View Full Version : Ratings to files



Robbyell
2018-04-01, 13:49
Hi all - unsure whether this is the right subforum, so let me know if not...

Has anyone found a way of exporting track ratings (1-5) from LMS / Trackstat into the files/tags themselves so that these can be read by other music players? The idea would be to be able to do a quick search for fave songs (eg rated 4 and above) to play in the car.

I've got most of the ratings stored by file in an Excel database as well, if it's easier to do it that way: upload of ratings from Excel to the file tags via csv or other file.

Hope someone can help!


Rob

markm9999
2018-04-02, 00:23
Definitely interested in the answer to this!

Bronx
2018-04-02, 00:46
Definitely interested in the answer to this!

So do I - I fear the loss of my ratings due to the fact that trackstat is not maintained anymore, at least it seems so!

Robbyell
2018-04-02, 08:35
Hmmm - no I'm not as IT literate as that! My OS is Windows, and I just about follow your suggestion, but not enough to risk trying it;)

Thanks anyway - were it simple enough, someone could make a few quid writing a bit of software to pull ratings from one player to another, ideally via the file (I understand there's s POPM field/tag within ID3v2 which a few players understand). I created the Excel version so that if LMS broke I still had my ratings, even if I can't get them into a music player.

Welcome other suggestions!

Roland0
2018-04-02, 12:22
ideally via the file (I understand there's s POPM field/tag within ID3v2 which a few players understand).

Unfortunately, rating tags are a mess. Support of them is spotty and inconsistent across players (which tag to use (e.g. not all use POPM (which is for MP3 only, FLAC uses a different one)), and even if they agree on the tag, the semantics of the tags are not standardized (e.g. POPM has a range of 1-255, so it is up to the player to map ranges to "stars" etc. LMS uses 0-100, so 0-20=* etc.))
The first thing to do would be therefore to check how the target player handles this (if it supports it at all - most don't)



I created the Excel version so that if LMS broke I still had my ratings, even if I can't get them into a music player.




So do I - I fear the loss of my ratings due to the fact that trackstat is not maintained anymore, at least it seems so!
Ratings are built into LMS, so it's rather simple to export them directly from the LMS database using any SQLite tool.



Welcome other suggestions!
A simple solution is to create a playlist in LMS containing all >= **** tracks (e.g. using the SQL playlist plugin (which supports this out of the box), or extgui4lms / LMS Playlist Editor (using a custom search)), and copy the file to the target device (that's what I do for both my phone and car)

Robbyell
2018-04-05, 13:00
Thanks mate - looks like a good workround.

It's one of those things you'd think is basic, migrating your ratings to whatever player you want to use, but I guess that's in the interest of the user, not the manufacturer, who want you to stay with them, so if you want your ratings YOU CAN NEVER LEAVE!

As you say, it's relatively easy to get the ratings out of LMS - I've got (written?) a database query which pulls them out into a csv which I can copy into Excel. There are some inconsistencies - for new files I need to swap all the %20s / %27s for spaces etc - but I only need to do it once for each bunch of files.

Anyone got any thoughts then as to how I'd get the Excel info into a new player?! By the sounds, if the player accepts POPM as an example, I'd need to:
a) take the filename and the ratings (currently expressed as 1-5 in Excel) - 2 columns
b) find how the desired player "reads" ratings (eg, of 255, 0-20 = 1, 20-60 = 2 etc)
c) convert my second XL column so that 1 = 10, 2 = 40 etc
d) find what input format the desired player can take (csv, XL html(?) etc)
e) convert my 2 columns to that format?

Am I on the right track?!

Roland0
2018-04-06, 12:51
Anyone got any thoughts then as to how I'd get the Excel info into a new player?! By the sounds, if the player accepts POPM as an example, I'd need to:
a) take the filename and the ratings (currently expressed as 1-5 in Excel) - 2 columns
b) find how the desired player "reads" ratings (eg, of 255, 0-20 = 1, 20-60 = 2 etc)
c) convert my second XL column so that 1 = 10, 2 = 40 etc
d) find what input format the desired player can take (csv, XL html(?) etc)
e) convert my 2 columns to that format?

imo, the main challenge still will be how to import the ratings (d) - good luck finding a player capable of that (LMS+TrackStat can (using XML), but I've yet to see anything similar anywhere else)
For file tags, a python script probably would be the simplest solution.
If you want to try this, start by installing python and understanding how to install python modules (needed for the tagging bits later on)
Here's a test script which exports ratings for mp3s directly from the LMS database as a csv file (adjust the paths in lines 4 and 5)
Note is also checks if the files can be accessed, which isn't required now, but will be as soon as tags are written.


import sqlite3, os, sys
from urllib.parse import urlparse,urlunparse,unquote

outfile="/tmp/lms-ratings.csv"
sconn = sqlite3.connect( "persist.db")
scursor = sconn.cursor()

with open( outfile, 'w', encoding='utf-8') as fp:
scursor.execute("SELECT url, rating FROM track_statistics WHERE rating NOT NULL AND UPPER(url) LIKE '%.MP3' ORDER BY url")
for row in scursor:
fpath = unquote( urlparse( row[0]).path)
if not os.path.exists( fpath):
print("ERROR: file [{}] not found".format( fpath))
fp.write('"{}",{}\n'.format( fpath, row[1]))

Roland0
2018-04-07, 03:30
I would prefer a simple cmd for that - the python demo from Roland (IMHO) only make sense if the Filenames/paths are UTF-8 like

Paths are stored urlencoded in whatever encoding LMS is using



If "thats" not needed - python is too complex for a "excel is a database" user.
Changing the script to write tags is trivial, however, it's pointless for me to post this version until OP has confirmed he can actually run it (for which he will have to install python and one python module).

Robbyell
2018-04-11, 11:45
Thanks chaps - much food for thought. I'll carve some time out and have a play.

DJanGo - I'm sure you're trying to be helpful, but you're coming across as a bit of a bellend. What's the problem with Excel? Don't you know how to work it?