Guide: Data Fiddler (MC Library <-> Spreadsheet Import/Export/Update) Old Data Fiddler ThreadBackground: MC has all the power you need to maintain and modify data within it's GUI. A few members however want to be able to import/export their library to tools like excel, some want to the modify and update this information, and others would like to be able to use a spreadsheet to add new items (for existing meta data holdings, dummy files etc)
This utility has 4 Options - Export the Entire MC Library: Exports your entire Library to a MPL and converts to a Unicode/Tab Delimited TXT file which you edit in Excel etc
- Export Selected Items from the MC Library: Exports only the Items Selected in MC to a MPL and converts to a Unicode/Tab Delimited TXT file which you edit in Excel etc
- Convert a MC MPL Playlist File: Converts an existing MC MPL (playlist) File to a Unicode/Tab Delimited TXT file which you can edit in Excel etc
- Update MC Library: Updates
existing and adds
new Items in your MC Library from a Unicode/Tab Delimited TXT file. The ability to directly add new items is only supported with MC Version 22.0.21 or later
- Create/Import a MC Playlist File: Creates a MC MPL (playlist) File from a Unicode/Tab Delimited TXT file (optionally asks it you want import) that lets you
add new items to the MC Library - This is kept for those with older versions of MC.
Examples: Read the posts below for examples of what it can do.
-
Adding Items to MC from a Spreadsheet - useful if you want to add items with no associated file such as managed a physical disk collection
-
Updating Items in MC from a Spreadsheet - useful if you want bulk update vales for existing items.
-
add your "off line" media library to MC by 8139david
-
How to update a non editable System Field like Date Imported- others???
Notes, Limitations, Future Stuff: - It takes me about 2mins to export my 30K library to a Unicode/Tab Delimited Text file and will use a bunch of Memory in the process. You may need to use the 64-Bit Version of MC Tool Box for large libraires if you run out of Memory
- It will create a spreadsheet of all used Library Fields (eg if you have not populated a field it will not appear), many of these fields are system fields
- *BEWARE* it WILL change or add stuff to your Library and there is no "Undo".... so backup your Library before you modify it.
- To increase the compatibility with various TXT editors, Excel etc, any field in MC that has Tabs and Carriage Returns Line Feeds
embedded in the data will be shown as {TAB}, {CR}{LF} in the exported Text File. So in an Excel you may have a Description that looks like "This Movie version is:{CR}{LF}{TAB}- The Original Theatrical Release{CR}{LF}{TAB}- Filmed in Sydney Australia" all in one cell, and in MC it will appear as:
This Movie version is:
- The Original Theatrical Release
- Filmed in Sydney Australia
"Key" Vs "FileKey": In the MC GUI, the primary "Key" for all entries is a hidden field so SOT will add a "Calculated Field" called "FileKey" that will show this information (see attached pic). During the import and export this field is just called "Key". So if you are using the "Update MC Library" the Column Header in your TXT file should be called "Key" (not "FileKey") or nothing will match and be updated. If you want to be able to view the FileKey from within MC's views then you need add "FileKey to your view.
MC Version 22.0.21 or later: Lets DataFiddler add new items from a TXT file using the "Update MC Library" option where the "Key" field is populated with "new".
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). Keep in mind that even with the above technique, some longer integer #'s get truncated by Excel. It will probably not be an issue but don't update Columns you don't need to (say Frame per Sec for those NTSC frame rates).
Saving the TXT file from Excel: The text file to be used to update MC must be a Unicode/Tab Delimited TXT file. In Excel, make sure it is saved as "Unicode Text (*.txt)". If you used DataFiddler to create the TXT file, it will already be in this format.