INTERACT FORUM

Please login or register.

Login with username, password and session length
Advanced search  
Pages: 1 [2]   Go Down

Author Topic: Guide: Data Fiddler (MC Library <-> Spreadsheet Import/Export/Update)  (Read 54104 times)

jmone

  • Administrator
  • Citizen of the Universe
  • *****
  • Posts: 14401
  • I won! I won!
Re: Guide: Data Fiddler (MC Library <-> Spreadsheet Import/Export/Update)
« Reply #50 on: February 01, 2016, 02:44:40 am »

Example:  Updating a System Field (Date Imported).  It's hard to update a System Field in MC such as "Date Imported".  Here is an example of how to do it.  In Excel, create a spreadsheet with just three columns as follows and enter the following data and formulae.

Use Format Cells--> Date on Col C so you can enter a Human Readable date format.  Col B should remain as a Format Cells--> General but just past in the formulae "=(C2-25569)*86400" which will the convert the Excel Date Format to Epoc Date and it will be "1454284800" in this case.

 A                     B                            C
Key          Date Imported             New Date
12345      =(C2-25569)*86400    1/02/2016
etc
etc

Save as a Unicode/Tab Delimited TXT File and use Data Fiddler to "Update MC Library" and the new date(s) will be set for those (file)Key.  There is no need to delete Col C as it will be ignored given it does not match any existing MC Library Field
Logged
JRiver CEO Elect

jmone

  • Administrator
  • Citizen of the Universe
  • *****
  • Posts: 14401
  • I won! I won!
Re: Guide: Data Fiddler (MC Library <-> Spreadsheet Import/Export/Update)
« Reply #51 on: February 01, 2016, 02:51:26 am »


If editing the TXT file, you can added/remove {CR}{LF}{TAB} as needed

Let me know of any other "weird" characters that cause issues.

Ugg the joy of different progs and formats... FYI - Unlike a plain text editor, it seems Excel likes to add extra " marks around a value if you try to use " in the value (so you end up with multiple " in the TXT file that then updates MC.).    It also can drop a ' at the beginning of a value
Logged
JRiver CEO Elect

Arindelle

  • Citizen of the Universe
  • *****
  • Posts: 2772
Re: Guide: Data Fiddler (MC Library <-> Spreadsheet Import/Export/Update)
« Reply #52 on: February 01, 2016, 03:51:55 am »

@8139david

careful with messing with system fields Date Imported is one that can get tricky.  There is a new expression tha will aloow you to port over the coded system field to a more useable date string

You seem to be just getting started with JRiver, so not meaning to sound condescending, but I have had some problems messing with system fields in the past .. best to leave them alone and use custom fields IMHO. This is getting into some pretty advanced stuff here

here's an example of what I'm referring to for date imported http://yabb.jriver.com/interact/index.php?topic=86834.msg713807#msg713807

PS why not rip at least the audio cds of yours?? seems to defeat the purpose a bit, no?
Logged

8139david

  • Galactic Citizen
  • ****
  • Posts: 345
Re: Guide: Data Fiddler (MC Library <-> Spreadsheet Import/Export/Update)
« Reply #53 on: February 01, 2016, 03:59:39 am »

@Arindelle

The system fields is entirely jmone's idea: so he should be warned, not me :)

Some don't have the time to rip all their cds, but they might want to catalog them.
Logged

Arindelle

  • Citizen of the Universe
  • *****
  • Posts: 2772
Re: Guide: Data Fiddler (MC Library <-> Spreadsheet Import/Export/Update)
« Reply #54 on: February 01, 2016, 04:12:33 am »

@Arindelle

The system fields is entirely jmone's idea: so he should be warned, not me :)


oh I'm sure Jmone knows all about it - he knows more about it than I do ...  he's one of JRiver's Yodas

Quote from: Jmone
- *BEWARE* it WILL change or add stuff to your Library and there is no "Undo".... so backup your Library before you modify it.

just  trying to help. Its a third party plugin .. sort of like jail breaking an iphone  :) Powerful stuff, so a heads up to you is all.

Have fun with it.

@Nathan, I've got a cool 15 field template set up ... seems to work well. Thanks for the effort
Logged

jmone

  • Administrator
  • Citizen of the Universe
  • *****
  • Posts: 14401
  • I won! I won!
Re: Guide: Data Fiddler (MC Library <-> Spreadsheet Import/Export/Update)
« Reply #55 on: February 01, 2016, 04:54:05 am »

Yup - you could mass update "unintended" data easily (esp with any Non Editable Field in MC's GUI) so always backup your library first.  Keep in mind that as it is a published I/F, MC "knows" what you are doing and hence your risk is low on actually corrupting the MC DB itself.  The worst is you roll back to your last DB backup to reverse any unintended changes (FYI - I've been in SI for a long time, when modifying data using the API calls is pretty OK... directly modifying the underling DB, now that can be risky!).

On updating the Date Imported there are valid reasons (eg if you imported with a bad system clock).  My example was more to show how to covert from a Human Readable date format to that used by MC.  The same Method could be used to change any of the Date fields.

Arindelle, I'd be interested to hear what your template does (as others would I'm sure) and if there are any tweaks I need to make.
Logged
JRiver CEO Elect

8139david

  • Galactic Citizen
  • ****
  • Posts: 345
Re: Guide: Data Fiddler (MC Library <-> Spreadsheet Import/Export/Update)
« Reply #56 on: February 01, 2016, 08:59:57 am »

By the way, JRiver assigns a duration of 0 seconds to the "dummy" movies (without dummy files) I import using Data Fiddler.
And Get Movie Information (with overwrite information) doesn't change that.
Indeed, as might have been expected, all the fields which would be read from a real video file are gray and <empty> on the Tag pane.
Logged

8139david

  • Galactic Citizen
  • ****
  • Posts: 345
Re: Guide: Data Fiddler (MC Library <-> Spreadsheet Import/Export/Update)
« Reply #57 on: February 19, 2016, 07:55:55 am »

- New: Added the ability to Update MC from a Tab Delimited TXT File
Updating Items in MC from a Spreadsheet:  You can now use any editor that can create a "Tab Delimited Text File" (just about anything but in particular Excel for most) and use it to update a MC Library directly. 

1. Create Your Spreadsheet (see pic) - Make sure in each cell the First Row (the Header Row) contains valid "MC Library Field" name that you want to CHANGE (non-MC Library Field names will be ignored by MC).  One of the Fields MUST be labeled "Key" and have the "FileKey" reference shown in MC.  This is an oddity, MC shows it in it's GUI as "FileKey", but Media Center Web Services (MCWS) use "Key" and hence so does the MPL and TXT files.
Add you data for each item you want to modify in the rows below.
2. Save Your Spreadsheet as a Tab Delimited Txt File
3. Run MC Data Fiddler and select "WriteInfo : Update MC Library from Tab Delimited TXT File" and when prompted select the file you created in Point 2.  Your Data should then be updated in the MC Library (see pic)

*** WARNING *** - Be careful, backup your library first as there is no Undo if something goes wrong.  Try a small update first.
I'm trying to use this function.
I've created a txt file (attached). But when I run DataFiddler, I get the error message on the attached image.
What should I do/try?
Logged

Arindelle

  • Citizen of the Universe
  • *****
  • Posts: 2772
Re: Guide: Data Fiddler (MC Library <-> Spreadsheet Import/Export/Update)
« Reply #58 on: February 19, 2016, 08:21:54 am »

should look something like this


in a spreadsheet program try saving as a *.CSV text file. Use the semi-colon as the field delimiter. JRiver uses the ";" as its default separator

you just have a text file in columns here. It can't parse anything
Logged

jmone

  • Administrator
  • Citizen of the Universe
  • *****
  • Posts: 14401
  • I won! I won!
Re: Guide: Data Fiddler (MC Library <-> Spreadsheet Import/Export/Update)
« Reply #59 on: February 19, 2016, 01:51:09 pm »

Hi David, thanks for the data file.  I'll have a look and get back to you, it could be the treatment of the "-", "é", "è", "ç" characters but I'll need to test.  The rest of it looks OK to my eyes.  Try breaking the file into a couple of batches and see how that goes.  I should be able to test properly this weekend however.
Thanks
Nathan
Logged
JRiver CEO Elect

jmone

  • Administrator
  • Citizen of the Universe
  • *****
  • Posts: 14401
  • I won! I won!
Re: Guide: Data Fiddler (MC Library <-> Spreadsheet Import/Export/Update)
« Reply #60 on: February 19, 2016, 01:54:31 pm »


in a spreadsheet program try saving as a *.CSV text file. Use the semi-colon as the field delimiter. JRiver uses the ";" as its default separator

you just have a text file in columns here. It can't parse anything

Thanks but I think David's format is correct.  DataFiddler is using Tab Separated for input files to then make a MCWS call using CSV from that.
Logged
JRiver CEO Elect

8139david

  • Galactic Citizen
  • ****
  • Posts: 345
Re: Guide: Data Fiddler (MC Library <-> Spreadsheet Import/Export/Update)
« Reply #61 on: February 19, 2016, 02:20:14 pm »

Thanks to both for your help!
Arindelle's tip made the command apparently go through.
BUT it didn't update any field at all (including the field that was supposed to change, namely, 'First').
Logged

jmone

  • Administrator
  • Citizen of the Universe
  • *****
  • Posts: 14401
  • I won! I won!
Re: Guide: Data Fiddler (MC Library <-> Spreadsheet Import/Export/Update)
« Reply #62 on: February 19, 2016, 02:45:59 pm »

The text file has to be Tab Delimited for DataFiddler.  Changing the Delimiter to another (like ";") will not work.  Your best bet is to cut it into some some batches till we find the particular data that is not working (I'm guessing it is one of the extended char sets)
Logged
JRiver CEO Elect

8139david

  • Galactic Citizen
  • ****
  • Posts: 345
Re: Guide: Data Fiddler (MC Library <-> Spreadsheet Import/Export/Update)
« Reply #63 on: February 19, 2016, 02:56:26 pm »

I tried all possible variations, on a very small file, nothing worked.

When the fields are separated by tabs, I get the error message above.
When they are separated by ; or , the scripts seems to run through but nothing is updated.
Some examples (I tried many variations):

Key,First
170510,PierreLaurent
170511,PierreLaurent
170512,PierreLaurent

"Key";"First"
"170510";"PierreLaurent"
"170511";"PierreLaurent"
"170512";"PierreLaurent"
Logged

8139david

  • Galactic Citizen
  • ****
  • Posts: 345
Re: Guide: Data Fiddler (MC Library <-> Spreadsheet Import/Export/Update)
« Reply #64 on: February 19, 2016, 03:26:08 pm »

Thinking the keys I indicated where perhaps not good, I tried similar things with my first 3 keys (as recovered by exporting again my library and reading the keyfile in the txt file generated on demande), same problem: no change is made.

To be exact, I just used this text file (and variations, including tabs, with no success):
Key,First
1,tata
2,toto
3,tutu

PS: I also tried using a normal field, eg:
Key,Comment
1,tata
2,toto
Logged

jmone

  • Administrator
  • Citizen of the Universe
  • *****
  • Posts: 14401
  • I won! I won!
Re: Guide: Data Fiddler (MC Library <-> Spreadsheet Import/Export/Update)
« Reply #65 on: February 19, 2016, 04:46:10 pm »

I'm having a look now and I'm seeing the same failure msg from MC.  (note: it's not the delimiter - it HAS to be a TAB)
Logged
JRiver CEO Elect

jmone

  • Administrator
  • Citizen of the Universe
  • *****
  • Posts: 14401
  • I won! I won!
Re: Guide: Data Fiddler (MC Library <-> Spreadsheet Import/Export/Update)
« Reply #66 on: February 19, 2016, 05:11:17 pm »

OK - Found the issue.  It is because MC is expecting to update more than one Field when I use the call I'm using and fails as this data set only has one field to update.

This works for example:

Key     First    NotAnything
1         tata    a
2         toto    a
3         tutu    a

This will then update the values in "First" and ignore the "NotAnthing" field as it does not exist.

I've made a new version that now allows single fields in the TXT file when updating MC
Logged
JRiver CEO Elect

jmone

  • Administrator
  • Citizen of the Universe
  • *****
  • Posts: 14401
  • I won! I won!
Re: Guide: Data Fiddler (MC Library <-> Spreadsheet Import/Export/Update)
« Reply #67 on: February 19, 2016, 05:38:50 pm »

Update: 20FEB16
- Fixed: Added Workaround where MC Would reject Updates to the Library where the TXT file only had one field
Logged
JRiver CEO Elect

8139david

  • Galactic Citizen
  • ****
  • Posts: 345
Re: Guide: Data Fiddler (MC Library <-> Spreadsheet Import/Export/Update)
« Reply #68 on: February 20, 2016, 01:23:52 am »

Thanks! Now it works!
Logged

Arindelle

  • Citizen of the Universe
  • *****
  • Posts: 2772
Re: Guide: Data Fiddler (MC Library <-> Spreadsheet Import/Export/Update)
« Reply #69 on: February 20, 2016, 03:22:42 am »

Thanks but I think David's format is correct.  DataFiddler is using Tab Separated for input files to then make a MCWS call using CSV from that.
hmm okay sorry for steering you wrong.

@Jmone why then did this work for me ... I'm going to dig up my "template" and see what I did
Logged

jmone

  • Administrator
  • Citizen of the Universe
  • *****
  • Posts: 14401
  • I won! I won!
Re: Guide: Data Fiddler (MC Library <-> Spreadsheet Import/Export/Update)
« Reply #70 on: February 20, 2016, 05:22:27 am »

David - glad it works!  had me stumped for a bit as well :) I had no idea the call needed more than one field.

Arindelle - no probs.  My script breaks the text file into fields by TAB delimited so I'd be surprised if anything else works (as it would be just part of the the data to be imported into MC).
Logged
JRiver CEO Elect

Ferdi

  • World Citizen
  • ***
  • Posts: 195
Re: Guide: Data Fiddler (MC Library <-> Spreadsheet Import/Export/Update)
« Reply #71 on: July 30, 2016, 09:27:50 pm »

@Jmone - thanks, this utility is great fun to work with and helped me to finally enhance my collection of classical music!
Combining the downloaded library file with a data base of composers and the era they were active, I can now browse much my collection by era like Baroque, Renaissance etc. Love it!
I found two quirks with the program:
  • Update of fields in MC: In my local CSV, I maintain the composers data as, for example, "Georges Bizet (1838–1875)". When uploaded to MC, this is changed to "Georges Bizet (1838 311875)". This is easily fixed in MC itself (find/replace on the 'composer' fiel), so no big concern. Curious though why that would happen.
  • Special characters: the downloaded library text file can not handle special characters, in particular letters that are often used in other languages. In the world of classical music, names that contain such characters are quite commen (e.g., Frédéric Chopin, Antonín Dvořák). I haven't figured out how to deal with these entries during download and upload. Probably by living by the 80/20 rule and be happy with what I have :)

Again - great tool, and I already have many other ideas how I can now enhance my library!
Logged

jmone

  • Administrator
  • Citizen of the Universe
  • *****
  • Posts: 14401
  • I won! I won!
Re: Guide: Data Fiddler (MC Library <-> Spreadsheet Import/Export/Update)
« Reply #72 on: July 31, 2016, 12:54:51 am »

Thanks for the feedback.  I know...

Quote
- It does not handle some extended characters sets well, so keep an eye on your files

...but I've not had the time to go through to find a better way of handling extended character (for example your "-" is probably a long dash or "Unit Separator" symbol not a minus sign).  One day... maybe :)

Nathan
Logged
JRiver CEO Elect

jmone

  • Administrator
  • Citizen of the Universe
  • *****
  • Posts: 14401
  • I won! I won!
Re: Guide: Data Fiddler (MC Library <-> Spreadsheet Import/Export/Update)
« Reply #73 on: August 13, 2016, 11:33:26 pm »

Update: 14AUG16

Updated the OP with an new version that should handle Unicode chars better.  Please let me know of any issues esp if the Unicode chars are now working correctly.

Notes on Changes:
- Will now work with just one Field (needed at least two previously)
- Better support for Unicode Characters
- Better support for handling commas in a field
- If you make a field blank in an Update it will now make that field blank in MC (instead of ignoring it)
- Added a CheckBox in the Menu to only Export items that are Selected in the MC Menu (instead of the Whole Library)


"Export the Library From MC (Creates TXT File)" : This will download the MC Library (in UTF-8) format then ask if you want to create a TXT Files (with a UTF-16 Header so it will open in Excel Correctly).  You should be able to just drag this TXT file into Excel for editing and it will be recognised as a "Unicode" File.  When you save in Excel you should see a MSG saying "Some features in your workbook might be lost if you save it as Unicode Text, Do you want to keep using that format".  The answer is Yes.

Creating a Blank Spreadsheet : If you are creating a Spreadsheet in Excel manually (not from a Library Export) and have Unicode chars then in the Save As box select "Save as Type: Unicode Text (*.txt)"
Logged
JRiver CEO Elect

jmone

  • Administrator
  • Citizen of the Universe
  • *****
  • Posts: 14401
  • I won! I won!
Re: Guide: Data Fiddler (MC Library <-> Spreadsheet Import/Export/Update)
« Reply #74 on: August 19, 2016, 06:02:25 pm »

Update: 20AUG16

Notes on Changes:
- Increased Max Memory Limit to handle larger library (can now use up to the Max Available memory for a 32-Bit App if needed, eg 4GB)
- Fixed typo that had broken the creation of the TXT file correctly
Logged
JRiver CEO Elect

jmone

  • Administrator
  • Citizen of the Universe
  • *****
  • Posts: 14401
  • I won! I won!
Re: Guide: Data Fiddler (MC Library <-> Spreadsheet Import/Export/Update)
« Reply #75 on: August 20, 2016, 12:16:03 am »

Update: 20AUG16b

Notes on Changes:
- Another Change to try to avoid Out of Mem error with large librarys being read.  It is slower but it prepares one item at a time rather than the whole Library
- Fixed some parsing logic on the creation of MPL files
Logged
JRiver CEO Elect

jmone

  • Administrator
  • Citizen of the Universe
  • *****
  • Posts: 14401
  • I won! I won!
Re: Guide: Data Fiddler (MC Library <-> Spreadsheet Import/Export/Update)
« Reply #76 on: August 20, 2016, 05:36:02 pm »

Still issues with Memory usage on large libraries.  Here is a little MemTest prog that will
- keep increasing the RAM used till the script stops
- Just move the Error Window out of the way to see the final amount of Mem User (or look in Task Manager)
- Press ESC to close the app and release the memory

FYI
- The most I get on the 32Bit Version is about 1,800 MB
- The 64Bit Version just keeps going and Windows seems to manage it just fine

Looking at the result, I'm thinking for the DataFiddler Script, I'll just post both a 32 and 64-Bit compiled version of the script.
Logged
JRiver CEO Elect

jmone

  • Administrator
  • Citizen of the Universe
  • *****
  • Posts: 14401
  • I won! I won!
Re: Guide: Data Fiddler (MC Library <-> Spreadsheet Import/Export/Update)
« Reply #77 on: August 20, 2016, 07:07:03 pm »

Update: 21AUG16b

Notes on Changes:
- Gave up on the Out of Mem issue with Large Libraries with my 32-Bit version of the script and instead now also include a 64-Bit version as well.  The 64-Bit Version should have no issues with running out of Memory but will only run on 64Bit versions of Windows
- Reverted to the previous was of reading the MPL into Memory as it is quicker (and the alt method I tried did not help with Out of Memory errors anyway).
- Included a "fix" for "&" becoming "&amp;" in MPL Exports
Logged
JRiver CEO Elect

Ferdi

  • World Citizen
  • ***
  • Posts: 195
Re: Guide: Data Fiddler (MC Library <-> Spreadsheet Import/Export/Update)
« Reply #78 on: August 20, 2016, 09:45:36 pm »

Thanks @Jmone! This works perfect now: special characters are downloaded properly and my large library is exported completely.
The addition to download only highlighted files from MC is most useful: saves time (quicker download, no deleting of unwanted files in excel required) and reduces risk to screw up files you did not even want to touch in the first place.
Off to do some cleansing in Excel now :)

cheers
Logged

jmone

  • Administrator
  • Citizen of the Universe
  • *****
  • Posts: 14401
  • I won! I won!
Re: Guide: Data Fiddler (MC Library <-> Spreadsheet Import/Export/Update)
« Reply #79 on: August 20, 2016, 10:01:34 pm »

Thanks for all your testing and patience!  I'm glad it is working... well until the next bug is found that is :)
Logged
JRiver CEO Elect

jmone

  • Administrator
  • Citizen of the Universe
  • *****
  • Posts: 14401
  • I won! I won!
Re: Guide: Data Fiddler (MC Library <-> Spreadsheet Import/Export/Update)
« Reply #80 on: August 29, 2016, 03:15:43 am »

I'm going to close this thread, as future updates will be part of MC Tool Box
Logged
JRiver CEO Elect
Pages: 1 [2]   Go Up