Print

Please login or register.
1 Hour 1 Day 1 Week 1 Month Forever
Login with username, password and session length

[FenceFurniture]

...also I assume you have 64-Bit windows? (from the "Type here to search" in the windows taskbar type "About" and hit enter.  It should say something like "System type: 64-bit operating systems, x64-based processor)

Yes, 64 bit Win 10 (I may be "somewhat" tech challenged, but......slightly above knuckle dragging 

[jmone]

Well it is now working!  You got an OK for the creation of the Fields.

I'll edit you log down now (as it has some authentication stuff in it).

[jmone]

...and turn off Verbose if you are using SOT to do stuff, (it only good for problem solving and is.... verbose!).

[FenceFurniture]

Ok, thanks for your help Nathan. Does that mean the version I downloaded has a bug in it? Or....

What I want to do is import from Excel data for 2 fields that I have created:
<Field Name="Muso Name" DataType="List" EditType="Standard"/>
<Field Name="Instrum used" DataType="List" EditType="Standard"/>

Is there some sort of instruction list somewhere?

EDIT: They are both Relational fields: one per Album. I'll post a screenie of the excel sheet.

[jmone]

Ok, thanks for your help Nathan. Does that mean the version I downloaded has a bug in it? Or....

Maybe, but that part of the code has not changed for a long time and was no different to the Test Version (which had expanded logging capabilities).

What I want to do is import from Excel data for 2 fields that I have created:
<Field Name="Muso Name" DataType="List" EditType="Standard"/>
<Field Name="Instrum used" DataType="List" EditType="Standard"/>

Is there some sort of instruction list somewhere?

There is some instructions in the Data Fiddler post on the first page, but I'll do up a screen shot for you in a sec.

[FenceFurniture]

Maybe, but that part of the code has not changed for a long time and was no different to the Test Version (which had expanded logging capabilities).

Well who knows what was going on, but the version you linked to in your post went straight through.

There is some instructions in the Data Fiddler post on the first page, but I'll do up a screen shot for you in a sec.

Great thanks.

Screenie of the Excel sheet attached. Column F is the semi-colon delimited data to go into "Muso name" field, and Col H is for "Instrum used" field.

I can make it whatever it needs to be, but IIRC I need to export the data as a .TXT or .csv file?
Am I correct in thinking that Album Artist and Album are sufficient to identify the correct album within MC?
Will I need one line per album or one line for each file of the Album?


As a point of interest I have created these so that with a view I can now cross-reference what artists and albums each musician is with/on. The Instrum used is so that I can Xref the lesser known instruments in the same way - find out where they appear in my collection. It's fascinating seeing a list of all the people/albums that musicians have played with. I started massaging the data with Jazz & Fusion (being the Elephant in the collection) because they play with so very many different people. Had to write considerable VB code to do the data massaging (data sourced from the web with different spellings for the same name, instrument etc - been QUITE the project!).

[jmone]

Make a backup of your library and try this with 1 Item first in case you get unexpected results

In MC
- Highlight the one file you want to change "Muso Name" and "Instrum used" and make sure that these columns are displayed
- In SOT --> Data Fiddler --> press the button "Export Selected Items Only for the MC Library (Creates a TXT File).  This will create a file like "MC_Output_20210120124916.txt" in the same folder as SOT
- Open this file in Excel (needs to be a Tab Delimited TXT file not converted to XLS etc)
- Modify the contents of the "Muso Name" and "Instrum used" separating list values with a ";" without the quotes
- When finished, you should delete all columns except the one called "Key" and "Muso Name" and "Instrum used" as SOT will update MC with all the data in the spreadsheet even if it does not change
- Save the file in Excel (making sure it is still a Tab Delimited TXT file)
- in Sot --> Data Fiddler --> press the button "Update MC Library (From Txt File)"

Check the results.

Attached is an example of what it looks like in Excel for updating a List Field (Language in this case).

[jmone]

Yes, that did work! Thanks!

 But.., I believe that SWOT created the fileKey field and labeled it filekey and not Key as it should be - is that correct?

Thanks again, now time for a lot of updating.....

@mvandyke, I should mention that if you use SOT to do the Export (instead of MC), you will see the Col is labelled as "Key" not "FileKey" so there is nothing to change.  It is MC's export that does not push out the "Key" col.

[FenceFurniture]

Make a backup of your library and try this with 1 Item first in case you get unexpected results

In MC
- Highlight the one file you want to change "Muso Name" and "Instrum used" and make sure that these columns are displayed

Yes, have already backed up the library earlier, and also did a complete backup of the Folders/Files.

Both fields a re relational- one per album. Should I be displaying (and therefore exporting) one line per album or one line per file in the album? (suspect the latter, given that as I understand it, the FileKey is....probably for each file.... )

[jmone]

Screenie of the Excel sheet attached. Column F is the semi-colon delimited data to go into "Muso name" field, and Col H is for "Instrum used" field.

I can make it whatever it needs to be, but IIRC I need to export the data as a .TXT or .csv file?
Am I correct in thinking that Album Artist and Album are sufficient to identify the correct album within MC?
Will I need one line per album or one line for each file of the Album?

Yes - TXT File (Tab Delimited)
Yes - semi-colon delimited for data that goes to a list field in MC
Matching - It is done on the (primary) KEY value.  SOT will search for the Key then update the field values in MC, so it is one line per entry in MC

As a point of interest I have created these so that with a view I can now cross-reference what artists and albums each musician is with/on. The Instrum used is so that I can Xref the lesser known instruments in the same way - find out where they appear in my collection. It's fascinating seeing a list of all the people/albums that musicians have played with. I started massaging the data with Jazz & Fusion (being the Elephant in the collection) because they play with so very many different people. Had to write considerable VB code to do the data massaging (data sourced from the web with different spellings for the same name, instrument etc - been QUITE the project!).

Sounds good, one think I'm not sure of is how well I handle the conversion of UTF (and special) characters back and forth, so keep an eye on what shows up in MC.

[jmone]

Both fields a re relational- one per album. Should I be displaying (and therefore exporting) one line per album or one line per file in the album? (suspect the latter, given that as I understand it, the FileKey is....probably for each file.... )

I can only update stuff with a Key, so I guess you are the first to test this out !

[FenceFurniture]

I can only update stuff with a Key, so I guess you are the first to test this out !

Well if it's by key then I will have to do one per file.

How do I open a .txt file in Excel? If I try to open from within Excel...it only wants to look at Excel files. If I right click on the MC_Output file > Choose another app then Excel doesn't show on the list.

[FenceFurniture]

Belay that - I found a way to open it.

[jmone]

FYI The Best way to import the txt file into excel is covered in the Instructions - https://yabb.jriver.com/interact/index.php/topic,106802.msg742297.html#msg742297

Importing the TXT file into Excel:  If you just drag and drop your TXT file into Excel, it will import the file using a "General" data format.  This General data format will have Excel process every cell and it may change the text as a result.
 As we want nothing to be changed by Excel we want to use "Text" as the data format.  To do this, open the file using the Wizard, select all the Col (Select Col 1, scroll to the end and Shift Click the last Col) then change the format to "Text" (see pic).

[FenceFurniture]

See atatched screenie:
The two fields I want to update have not been exported (MUSO NAME and Instrum used)
BUT
other fields I have created are there such as Musicians (which is where I have extracted the data from for th above two), Wiki Page, and Studio Album #. They are all relational fields (one per album) just like the two I want to update.

Now I did delete that data from the two fields before I exported (I have been pasting some data in along the way in doing my massive edit job for the last 6 weeks). However, I do know that the Instrum field is not up to date. I deleted the data so that I could readily the changes....that I am trying to make.

Would I be right in thinking that even though the fields are not exported (presumably because they are empty of data) that I can just enter those filed names in the headers of the txt file in Excel?

[jmone]

Correct, it only pulls out the fields with Data but you can just add in the header any of the Field Names (as long as the match or it will just be skipped during the update).

[FenceFurniture]

And....VOILA! We have a result (attached). Just the first album so far (the one I deleted the data from for this test).

Thank you very much for
A) guiding me through the process and
B) creating the software.

I do think that this would be a very useful part if it was built into MC.

[jmone]

No Probs - glad it works. 

I guess the reason it is not part of MC is that not many people actually want such a feature.  What is great about MC is that with their Web Services interface, you can build specialty tools to make it extensible. 

[jmone]

Update: 20 January 2021 - Git Hub - https://github.com/jmone1/Swag-of-Tools/releases
- Media Info (Bug Fix): Now supports UTF Names
- Media Info (New Option): Don't overwrite existing MediaInfo "Extra" file
- General: ESC will no longer exit SOT
- Data Fiddler: Log will update with the # of entries updated in MC
- Data Fiddler: Log will warn if there were 0 entries updated in MC and ask you to check there is a "Key" column
- General: Log now has a "Turn on Verbose Logging of MCWS Calls" - Careful using this one as it may be very long if you are doing a lot of MCWS calls (eg a big batch)

Thanks everyone for the testing over the last week.

[jmone]

Test Version:  Update to Chapterfy regarding ChapterDB's decommissioning

ChapterDB has been down for well over a year and is not coming back.  The Folks over at Plex however did take an "Archive" of it where you can still browse, see, and download Chapter.txt files for titles that were updated prior to ChapterDB's demise. 

As a result, in this Test Version, I've:
- Removed the Auto Lookup based on the MD5 Hash (there is no Chapter DB API anymore)
- Changed the link to point at the Archive for browsing
- Allow you to Chapterfy an item in MC based on the reference # from the Archive

[FenceFurniture]

Nathan do you have any thoughts on a practical limit to the number of files to be updated in one pass, or will it cope with say 5000 or 10000 ok?

Also, is it better to empty the fields in MC first or just overwrite them with the incoming data. Not all of them have data currently.

[jmone]

It should be fine on any size (will just take a bit longer).  I've tested up to 30K items.  Again, it is best to only include the data you want to make a change to so remove any Rows for items that don't need to update and any unnecessary Columns without changes.  You should see a counter showing the % processed on large jobs in the Log screen (it is there for small jobs but it is so quick you don't see it)

As SOT will overwrite the data in every cell in MC with the cell from excel (including if something in blank in your spreadsheet it will become blank in MC) there is no need to "clear" anything out in MC before hand. 

[jmone]

...so backup you Library and let it rip!  Then check to see if it worked as expected! 

[jmone]

Just did a single field update on 33K Items in MC and it took me about 6 minutes.  I did find a bug in my % to go calculation in the log that was showing I'd only done about 5% at that point so I need to look at that.... so that is waaaaay off.

[FenceFurniture]

It appears that SOT has to be shut down and reopened with each use? I exported one lot, and then decided to export that whole section (given your response of no real file number limit). What I got is attached. Lots of album called Data Fiddler.

So I shut it down and restarted it. All ok except the last line which is by an Album Artist called "About", but the other tracks for this album are correctly attributed to Tinariwen as the Album Artist. It is the lowest Key number for this album, so I assume it's the first track.

EDITED to attach screenie of "About".

[jmone]

Yup - looks like you have found a bug.  SOT Data Fiddler gets MC to download a MPL file that SOT then process to a TXT file..... but if you do 2 in a row then it is not re-reading the new MPL file.  I'll need to track that down.  For now you will need to restart SOT between operations. 

[FenceFurniture]

Yeah, rightio. That doesn't explain the "About" though (was the first run after re-opening).

[FenceFurniture]

I'm prone to putting in a column of spaces between the two columns of long data (because it enables seeing which cells have values more easily). If the column has no header then can I assume that it will not upset anything when it goes back into MC?

[jmone]

Ahhh - the About thing is weird - I'll need to have a look after dinner.  SOT seems to be putting About into all empty data fields for me during the conversion from MPL to Txt.  I must have moved something around.

I've not tested what happens with a empty header but with data below (you would have to test).... you could put a header in for something that is not a field in MC and it will be ignored.

[FenceFurniture]

I can now confirm that Data Fiddler is not handling special characters at all. I use En-dash (Alt 0150) extensively, plus all the accents, graves and so on for European and African names have all turned into a black diamond with a ? in it. Even the most common one which is é (Alt 130).

BTW, that was the first time ever I have used the restore Library function.

[FenceFurniture]

I'm not sure if there will be a solution to special characters - there are quite a few of the little blighters.

Out of my first section of music (essentially "World" music with lots of Europeans and Africans), I have stripped out A-Z, a-z, 0-9 and that reveals what is left.
Apart from ; , . ( ) ' [ ] -
there is

– á â å Å ả æ ç é É ë ğ ı í ï ł ñ Ó ó ò ö Ö ø Ø ş ú ü

but of course, that is just so far. There will be some more coming out of the Jazz section (lots of Scandinavians).

[zybex]

Nathan, not sure if you're already doing this: if you're including values in the URL of the MCWS calls you'll need to URL-Encode them in UTF8.

For instance, "Träume?" needs to go over as "Tr%C3%A4ume%3F".

If the values are in the body of POST requests then the encoding is not needed, but the request needs to have an header saying "Content-Type: text/html; charset=utf-8".

Not sure how AHK handles these things as I don't use it much.

[jmone]

Here is an update that (I hope) fixes the reuse of variables on the same run and issues with blank fields.

Mmmm - the UTF Encoding seems to be working for me (see a PIC), but @FenceFurniture if it is still not working correctly can you please post the MPL & TXT file that SOT makes when you press "Export Selected Items ...." from Data Fiddler that shows the issue?

[FenceFurniture]

It's not so much the export Nathan, it's the import to MC. (for the export I can just paste over the top - in fact I don't have to because the import is done by Key, so it's only data I'm importing that gets  a "?")

[jmone]

I entered the "á â å Å ả æ ç é É ë ğ ı í ï ł ñ Ó ó ò ö Ö ø Ø ş ú ü" data into Excel then used SOT to update MC and got the correct results (as per the pic above). 
- Try the new build?
- Maybe if you save out a bit of the XL spreadsheet and post it here so I can have a look at it?

[FenceFurniture]

Ok, but mañana - early night tonight to watch the inauguration.

[jmone]

Yeah - I'm off as well   - FYI, this is what I tested..

Have a good night

[FenceFurniture]

Have just downloaded that latest version from a few posts back, and am yet to run it.

I thought I should probably let you know (if you don't already) that Windows gets a bit narky when it is opened:
"Windows Protected your PC"
have to click More Info and only then does the option come up to "Run Anyway".
This happens every time the pgm is opened, not just the first time of each new build. I suspect (but am not sure) that this has something to do with
Publisher:  Unknown publisher

It also defaults to MC version 26.

Not sure if this one is working yet:

Error - MediaCenter not Found at 127.0.0.1:52199
- Check MC Server is running and the Configuration Options are Correct
Adding "FileKey" Field to MC Library
Check if "FileKey" Field already exists in MC Library
Error - MediaCenter not Found at 127.0.0.1:52199
- Check MC Server is running and the Configuration Options are Correct
Adding "ChapterDB #" Field to MC Library
Check if "ChapterDB #" Field already exists in MC Library
Error - MediaCenter not Found at 127.0.0.1:52199
- Check MC Server is running and the Configuration Options are Correct
Writing Registry Keys to HKEY_CURRENT_USER, SOFTWARE\JRiver\Media Center 27
Writing to Swag of Tools.ini
Error - MediaCenter not Found at 127.0.0.1:52199
- Check MC Server is running and the Configuration Options are Correct
Checking for Media Center at 127.0.0.1:52199


I'll try to access MCWS.

(a few minutes later) I had to enter the Authentication again - don't know if that's usual with a new version of SWOT.

When I saved Config:

Error 401 Access Denied
- Check Username / Password is correct in the Configuration Options
Adding "FileKey" Field to MC Library
Check if "FileKey" Field already exists in MC Library
Error 401 Access Denied
- Check Username / Password is correct in the Configuration Options
Adding "ChapterDB #" Field to MC Library
Check if "ChapterDB #" Field already exists in MC Library
Writing Registry Keys to HKEY_CURRENT_USER, SOFTWARE\JRiver\Media Center 27
Writing to Swag of Tools.ini
Success, Media Center Found at 127.0.0.1:52199!
Checking for Media Center at 127.0.0.1:52199


However, I am able to access the web page of MCWS (without having to enter user/psw)

[FenceFurniture]

Ah, I've just realised why I had to re-authenticate - I restored the Library earlier, as previously posted.

[FenceFurniture]

No success with getting the special characters into MC. Have attached the small txt file I used to test with the new version, plus a screenie of the result in MC.

[jmone]

Thanks for the sample TXT file!!!! - Found it (and why it was working for me and not you!)..... well I think?!?!!  I'm so glad you were taking backups

The TXT file for "Update MC Library" has to be UTF Encoded for the extended chars to work:
- For Me:  My Text file was being created from SOT (UTF Encoded) using the "Export Selected Items .... " and I'd then make changes (like add Muso) in Excel, save, then "Update MC Library (from TXT File)"
- For You: I think you created the TXT file in Excel and the sample you sent me is ANSI (not UTF) encoded, and you were making the changes in that spreadsheet (adding the Key for example) then trying "Update MC Library (from TXT File)" ?

If so, there are two ways to fix it:
1) Use "Exported Selected Items....." from SOT and edit that one, or
2) Use a File in Excel but convert it UTF (see pic).  Keep in mind that Excel also tries to be "helpful" in converting dates and numbers, so you have to be carful on that as well (see the instructions on the first page for how to make sure TXT files are imported where all the cell formats are "TXT" instead of some of them being "GENERAL").

I "may" have made some changes in SOT trying to work this out so here is the latest test version.

Thanks
Nathan

[FenceFurniture]

Wonderful - that did it (saving as UFT). Screenshot attached.

I was sure I'd read that it had to be a Tab delimited file? I'll try to find where I read that.

[jmone]

Yes, it has to be Tab Delimited & UTF.  I'll update the instructions on that in the first page.  It seems that to get both in Excel, you need to save as Unicode Text (*.TXT).

[FenceFurniture]

In MC
- Highlight the one file you want to change "Muso Name" and "Instrum used" and make sure that these columns are displayed
- In SOT --> Data Fiddler --> press the button "Export Selected Items Only for the MC Library (Creates a TXT File).  This will create a file like "MC_Output_20210120124916.txt" in the same folder as SOT
- Open this file in Excel (needs to be a Tab Delimited TXT file not converted to XLS etc)
- Modify the contents of the "Muso Name" and "Instrum used" separating list values with a ";" without the quotes
- When finished, you should delete all columns except the one called "Key" and "Muso Name" and "Instrum used" as SOT will update MC with all the data in the spreadsheet even if it does not change
- Save the file in Excel (making sure it is still a Tab Delimited TXT file)
- in Sot --> Data Fiddler --> press the button "Update MC Library (From Txt File)"

Yes, here it is here in reply #256. I knew I had to have read it because on the very first save Excel defaulted to UTF so I had to deliberately select Tab delimited.

[jmone]

My apologies. 

I did not even think about checking that your TXT file was not UTF encoded till you sent though the sample, as I'm guessing most of us normally uses the file created from SOT which is UTF & Tab Delimited by default.  This is the first time I've seen a TXT file initially created in Excel being used (or at least anyone reporting the issue with Unicode).

I'm going to add a new para and pic on that first page about saving from Excel in particular.

Thanks for testing so far, and please still be carful to check the results, as I made a few changes trying to hunt this down.

[FenceFurniture]

My apologies. 

Thanks for testing so far, and please still be careful to check the results, as I made a few changes trying to hunt this down.

Not at all Nathan. Thank YOU for your ongoing work!

Should you need anything else destruction tested....you know where to call. 

[jmone]

No probs - what Time Zone are you in?  You don't seem to sleep

[FenceFurniture]

BTW, the reason I'm creating the txt file, rather than exporting one from MC, is because I have to run a macro on the data first.
1. From MC (in which I have created a special view for this) I copy & paste the fields FileKey, Album Artist, Album for the bunch of files I want to update. Paste into an Excel sheet that contains the macro code.

2. Next, I run my macro which collects the data I have created for the two delimited fields - but only one per album - so I have to run a couple of Do loops to repeat it into the MC data sheet for each file on the Album.

3. Next, make sure there are no "Data Fiddler" or "About" artists, then delete the Album Artist & Album columns (because they are only there for my recognition in that checking after the macro has run). I probably don't really have to check for DF or About, because I delete the columns anyway, but you'd probably like to know if it's still happening.

4. Save the stripped-down file as UTF and run SWOT to update MC.

I didn't fancy running a VB macro on a txt file - it may work - but it's really 6 of one and a ½doz of the other. In fact, it's slightly better this way because I don't get ALL the field's data columns which then have to be deleted. if I export via SWOT.

[FenceFurniture]

No probs - what Time Zone are you in?  You don't seem to sleep

Sydney (Katoomba - just west). I was planning on getting up at 3.30am to watch the inauguration, but I couldn't sleep past 2 anyway. Then, after that effort, he got sworn in 10 minutes early and I missed it while I was fooling around with other stuff! 

[jmone]

Nice, - I'm near Chatswood.  Good to see another Aussie.