Topic: Help! Fill date from filenames... (Read 9010 times)

darichman · « **on:** June 07, 2007, 07:26:57 am »

Hi guys!

After backing up all my home videos to an external, reformatting my hard drive and restoring my library.... I have lost all metadata for my video files. MC is importing them as new files... and because fields for video files can be saved in the database only, I've essentially lost all my tags for these files. I have a feeling the problem was related to the fact my external had a FAT system and the PC HD is NTFS - so different file sizes and MC got tricked. Anyway, that's not the problem ~ I thought it might happen anyway....

But at least all my video files are saved in the format [Date (filename friendly)] - [Album] - [Name]
So I figured a fill properties from filename should do the trick! Well the problem is that fill [Date (filename friendly)] from filename doesn't work. Ok.
Then I thought fill [Year] fill [Month] fill [Day] etc might work... but no luck. And it seems that Time data (ie hour, minute) is stored in [Date] only, and there appears to be no way to fill this from filename.

So my question is, how can I restore the proper dates AND times for my 450 video files? All of the information, from Year down to Minute is in the filename. How can I get MC to recognise this? If MC could recognise [Date (filename friendly)] my job would be a lot easier

I even managed to get [Date (filename friendly)] into the [Name] field, did some nifty find and replace formatting to get a value like 2004/06/11 09:34:57 (which is the format you can type in for [Date] ). Then did a move/copy fields from [Name] to [Date]. But no luck. Date then gets populated with "Unknown Date"

Please please please if anyone knows a workaround for this I'd be in your debt... All of my photos and videos are organised and sorted by [Date] (which includes Time) and most of my renaming expressions also use this data

Alex B · « **Reply #1 on:** June 07, 2007, 07:52:09 am »

Quote

I even managed to get [Date (filename friendly)] into the [Name] field, did some nifty find and replace formatting to get a value like 2004/06/11 09:34:57 (which is the format you can type in for [Date] ). Then did a move/copy fields from [Name] to [Date]. But no luck. Date then gets populated with "Unknown Date"

That's a tricky one. MC uses internally a date format like this:

1105999200

which is "1105999200 seconds since January 1st, 1970"

I have explained that in these posts:
http://yabb.jriver.com/interact/index.php?topic=37245.msg254398#msg254398
http://yabb.jriver.com/interact/index.php?topic=36906.msg251847#msg251847

Probably MC tries to copy the field value directly without doing the conversion to the internally used format, which normally happens when you type or paste the information.

Mr ChriZ · « **Reply #2 on:** June 07, 2007, 07:54:34 am »

If you can't work out a way to do it, message me,
and I could probably sort out a .NET script which will do it for you.

Alex B · « **Reply #3 on:** June 07, 2007, 08:06:04 am »

A "human readable date <-> Unix date" converter would be handy.

I wonder if an Excel sheet could be used. Then it would be possible to paste a list of values and Excel would automatically calculate the new values. Unfortunately, reading Excel documentation and experimenting with its formulas is not one of my favorite tasks.

glynor · « **Reply #4 on:** June 07, 2007, 09:04:39 am »

Web Conversion: http://www.onlineconversion.com/unix_time.htm

Instructions for Excel: http://exceltips.vitalnews.com/Pages/T0096_Converting_UNIX_DateTime_Stamps.html

darichman · « **Reply #5 on:** June 07, 2007, 05:11:33 pm »

Thanks everyone for your help.

I inverted the equation in glynor's post to convert the excel date format to unix, which I understand is how MC stores date & time internally. I have three columns in excel: Human Readable, Excel and Unix... so I now have all the dates in a column in unix format within an excel spreadsheet.

How can I get these into MC? I tried copying a current view into excel, which gives me the dates, but once the dates are transformed, how can I get them back into MC? Side note, if I paste a single unix date into the date field for a file in MC, it can't read it. Is this normal behaviour?

I also tried exporting an MC playlist to xml, but Excel reports there is no schema.

Mr ChriZ · « **Reply #6 on:** June 07, 2007, 06:27:33 pm »

If I do a
track.Set("Date", "1105999200");

Using the SDK, then the date remains empty.

If i do "1105990" then MC gives the date 04/02/4928
$:-\$

Mr ChriZ · « **Reply #7 on:** June 07, 2007, 06:38:59 pm »

Hmm actually it's stored as a double presision number
so the time now here in the uk is
39241.025694444441
Though I've no idea how you get that from a standard date time.

Looks far to complicated to be looking at at 12:30am anyhow.

Alex B · « **Reply #8 on:** June 07, 2007, 07:03:56 pm »

Quote from: Mr ChriZ on June 07, 2007, 06:38:59 pm

Hmm actually it's stored as a double presision number
so the time now here in the uk is
39241.025694444441
Though I've no idea how you get that from a standard date time.

Looks far to complicated to be looking at at 12:30am anyhow.

The actual Date field is different from the other time related fields. Somehow I had forgotten that, even though I posted this answer a few months ago:

Quote from: Alex B on February 11, 2007, 01:43:14 pm

The Date field seems to be a day counter.

- the number 1 represents December 31st, 1899
- the current date is 39124 (February 11th, 2007)
- December 30th, 1899 would be zero but that is not possible with the used logic and MC doesn't accept this value.
- December 29th, 1899 and older dates use negative numbers starting from -1
- if only the year number is in the Date field MC writes the date as January 1st of that year.

I tried to find a reason for the odd looking December 31th, 1899 date. Why isn't it January 1st, 1900? Looks like this is the explanation:

Quote from: http://blogs.msdn.com/ericlippert/archive/2003/09/16/53013.aspx
And finally, why 30 December 1899? Why not, say, 31 December 1899, or 1 January 1900 as the zero day? Actually, it turns out that this is to work around a bug in Lotus 1-2-3! The details are lost in the mists of time, but apparently Lotus 1-2-3 used this date format but their devs forgot that 1900 was not a leap year. Microsoft fixed this bug by moving day one back one day.

That blog explains some other date related oddities too.

The numbers after the dot are just digits after the decimal point. For example,
37808.5 results 7/6/2003 12:00 PM and 39232.618055555555 results 5/30/2007 2:50 PM

I think the used format is the same that Excel uses internally. Possibly there is a way to display that format.

Also, a way to convert the excel info to MC's mpl format is needed for updating the library data.

The new import playlist menu item can be used for updating file data from an mpl playlist file. (Top menu > File > Import Playlist...)

If the video files are already imported then only two fields are needed, Filename and Date.
Here's an example:

Code: [Select]

<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>
<MPL Version="2.0" Title="a test mpl playlist">
<Item>
<Field Name="Filename">D:\Test\Video 1.avi</Field>
<Field Name="Date">37778.354166666664</Field>
</Item>
<Item>
<Field Name="Filename">D:\Test\Video 2.avi</Field>
<Field Name="Date">37808.5</Field>
</Item>
<Item>
<Field Name="Filename">D:\Test\Video 3.avi</Field>
<Field Name="Date">37839.618055555555</Field>
</Item>
</MPL>

Alex B · « **Reply #9 on:** June 08, 2007, 04:42:11 am »

I found this from glynor's link:

http://exceltips.vitalnews.com/Pages/T0096_Converting_UNIX_DateTime_Stamps.html

Quote

Excel, on the other hand, stores time stamps as a real number representing the number of days since 1 January 1900 (the default setting). The integer portion of the time stamp represents the number of full days, while the portion of the time stamp to the right of the decimal point represents the fractional portion of a day, which can be converted to hours, minutes, and seconds.

To do a straight conversion of a UNIX time stamp to the Excel system, all you need to do is use this formula:
=UnixTime / 86400 + 25569

In this example, UnixTime can be either a named cell containing the integer UNIX time stamp value, or it can be replaced with the actual integer value. Since the UNIX time stamp is stored as seconds, the division by 86400 is necessary to convert to days, which is used by Excel. (86400 is the number of seconds in a day.) You then add 25569, which is the number of days between 1 January 1900 and 1 January 1970. (It is the value returned if you use the =DATE(1970,1,1) function.)

However, possibly the day #1 should be 31 December 1899, not 1 January 1900.

darichman · « **Reply #10 on:** June 08, 2007, 05:45:26 am »

Eck! I'm totally lost. As I said, I used some algebra to go back the other way and turn the Excel date format into Unix... but as far as where to go from there?

I'm not at all familiar with xml... Can anyone direct me to a good source where I can learn how to export an xml of a playlist from MC, open it so that excel can read it and then save it with excel so that MC can reimport the files?

When I try to open an xml or mpl file in excel, I get a "Open XML dialog" and I have the options: as an xml table, as a read only workbook, or use the xml source task pane.

If I select the first one, I get a message saying the source file has not specified a schema and that excel will automatically create one. The resultant table doesn't make much sense to me... all the data is there, similar to what was in Alex B's code box, but it's more like a table...

How do I get my dates in there in the correct format?

I am precariously close to one by one copy pasting dates... it might only take me a day or so, unless someone can rescue me!

Thanks again for everyone's help!

Alex B · « **Reply #11 on:** June 08, 2007, 06:42:41 am »

After getting the excel data (filenames & dates) correct the rest is not very difficult. You can organize the excel sheet like this:

After that copy-paste the sheet contents to MS Word (Paste Special > Unformatted Text) and do a couple of Find & Replace operations before saving the file as "text only" and changing the filename extension to .mpl

The first replace:

The second replace:

The result:

EDIT

This is easy enough for one time use. In Excel you can easily dublicate single cell values in columns and copy-paste all data cells from a column to another place.

However, some forum users may have more automated ways for organizing table data for mpl creation.

I think Jaguu has developed a system for this. (I sent a PM to him.)

Alex B · « **Reply #12 on:** June 08, 2007, 07:36:09 am »

If you want to go the other way around, i.e. transfer MC data to Excel, you don't need to export an mpl file and convert it to a different format.

You can organize a details view scheme so that it contains only the needed files and columns, select all files and copy-paste to an Excel sheet.

darichman · « **Reply #13 on:** June 08, 2007, 10:35:43 pm »

Thank you so much Alex. I'm working on it

Will let you know how it turns out!

sufokeet · « **Reply #14 on:** June 09, 2007, 06:17:32 am »

Hello All,

I exported a playlist in XML and then modified data.
Then I drag and drop the modified playlist in MC12.
When I open it to play, it still shows all the old field data. None of the changes are shown.

Do I something wrong?

In advance, thanks for the help.

Alex B · « **Reply #15 on:** June 09, 2007, 06:53:34 am »

Quote from: sufokeet on June 09, 2007, 06:17:32 am

Hello All,

I exported a playlist in XML and then modified data.
Then I drag and drop the modified playlist in MC12.
When I open it to play, it still shows all the old field data. None of the changes are shown.

Do I something wrong?

In advance, thanks for the help.

The answer is already in this thread:

Quote from: Alex B on June 07, 2007, 07:03:56 pm

The new import playlist menu item can be used for updating file data from an mpl playlist file. (Top menu > File > Import Playlist...)

If the video files are already imported then only two fields are needed, Filename and Date.

This Import Playlist menu function is different from the other import methods. It can update library data. Be aware that it doesn't check anything before blindly and quickly updating the library fields. Make sure that your mpl file has correct data before proceeding. The media files must be already imported to the library to make this work.

The other import methods (Auto-Import, drag & drop, right-click import) can import field data from an mpl file only if the media files are not previously imported. Otherwise an mpl file is handled as a new simple playlist that appears in the "Imported Playlists" playlist group.

sufokeet · « **Reply #16 on:** June 09, 2007, 06:59:10 am »

Thanks! I overlooked it!

darichman · « **Reply #17 on:** June 10, 2007, 06:13:24 am »

Still no luck for me. I'm not sure what I'm doing wrong, but MC doesn't update any data when I use the import playlist function (either from MPL or XML).

Here is the code I used... abridged

Am I on the right track? Sorry for being such a pain!

Code: [Select]

<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>
<MPL Version="2.0" Title="schmooga">
<Item>
<Field Name="Filename">C:\Documents and Settings\Chris\Desktop\Videos\Around Medicine\Chris\2005-06-03 10,43,56 - 100_1969.mov</Field>
<Field Name="Date">38506.44718</Field>
</Item>
<Item>
<Field Name="Filename">C:\Documents and Settings\Chris\Desktop\Videos\Around Medicine\Chris\2005-06-03 13,12,00 - 100_1980.mov</Field>
<Field Name="Date">38506.55000</Field>
</Item>
<Item>
<Field Name="Filename">C:\Documents and Settings\Chris\Desktop\Videos\Around Medicine\Unknown Photo Source\2006-08-01 10,34,22 - 100_4180.mov</Field>
<Field Name="Date">38930.44053</Field>
</Item>

etc etc
</MPL>

Mr ChriZ · « **Reply #18 on:** June 10, 2007, 06:32:11 pm »

OK you can try this at your own risk.

I made a new script which takes the date from the filename,
and inserts it into the Date field.
It is run against all files in the Playing Now Playlist.
The script is available from here, with a stand alone tool to run the script (so no plugin is required for MC)
http://uppit.com/d/9FXTI

Extract the script files into an empty directory.

As a precaution, start small.
From within Media Center add one video file to Playing now then build up.

Open a command prompt change to the directory you extracted the script files too.

Type:
scriptrunner datefromfilename.cs css_config.dat

If successful it will output the datetime, followed by the unix style datetime
for each file
1999/12/30 22:30:00:36524.9375

The files that I tried it on had names like the following
19991230-223000 - 03 - Double Cross.ape

If yours differ from this alot then the tool will probably cause an exception and die.
(It's highly unlikely any damage will be done however so testing one or two files should be fine).

Unfortunatley I'm out for 2 days so may not be around to respond.

If you want to see how the script works open the dateFromFileName.cs file in any
text editor. It's written in C#.

darichman · « **Reply #19 on:** June 10, 2007, 07:59:03 pm »

Mr ChriZ

Thanks for putting this together, it did the trick splendidly and saved me a lot of time

I'll keep it for future use as well!

Still not sure what was happening with the whole import playlist thing... but this seems to have sorted out my issue.

Thanks guys for all your help.

Alex B · « **Reply #20 on:** June 11, 2007, 05:02:08 am »

I replicated your setup and it worked for me.

I created a quicktime video file using your filename and path, imported it and exported this mpl file:

Quote

<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>
<MPL Version="2.0" Title="Playing Now">
<Item>
<Field Name="Filename">C:\Documents and Settings\Chris\Desktop\Videos\Around Medicine\Chris\2005-06-03 10,43,56 - 100_1969.mov</Field>
<Field Name="Album">5/27/2004</Field>
<Field Name="Name">2005-06-03 10,43,56 - 100_1969</Field>
<Field Name="File Type">mov</Field>
<Field Name="Bitrate">137</Field>
<Field Name="Media Type">Video</Field>
<Field Name="File Size">2128819</Field>
<Field Name="Duration">120</Field>
<Field Name="Date Created">1181520009</Field>
<Field Name="Date Modified">1085656584</Field>
<Field Name="Date Imported">1181554360</Field>
<Field Name="Date">38134.594722222224</Field>
</Item>
</MPL>

Then I updated the Date value with this playlist using the File > Import Playlist feature:

Quote

<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>
<MPL Version="2.0" Title="schmooga">
<Item>
<Field Name="Filename">C:\Documents and Settings\Chris\Desktop\Videos\Around Medicine\Chris\2005-06-03 10,43,56 - 100_1969.mov</Field>
<Field Name="Date">38506.44718</Field>
</Item>
</MPL>

MC changed the Date value correctly from 5/27/2004 2:16 PM to 6/3/2005 10:43 AM.

MC12.0.251

darichman · « **Reply #21 on:** June 12, 2007, 04:33:47 am »

Alex! You're right, it worked with that setup, but I had to copy paste parts of your code to get it right.

It might be the way I'm generating xml's perhaps? I am using Excel 2007. When I open an xml file exported from MC, it comes up with all these options which, I must confess, are a bit beyond me

In excel, I don't see the
<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>
<MPL Version="2.0" Title="Playing Now">

parts. What i get is columns corresponding to "Items" "Field Names" "Title" etc. These are sortable. It might take some fiddling around with for me to figure it out I think.

Thanks for your help with everything!

In other news, I have started converting all my home videos to wmv for use with Media Center, as it's a taggable format and this got me a bit worried! Don't worry, I've kept a backup copy of all the original files, I know it's a lossy conversion

Alex B · « **Reply #22 on:** June 12, 2007, 04:42:58 am »

I marked the needed additional lines with blue color in this screenshot:

Each mpl file must start with
<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>
<MPL Version="2.0" Title="the name of the playlist">

and end with
</MPL>

You can add these three lines in the excel sheet (as I did) or later with a text editor.

darichman · « **Reply #23 on:** June 12, 2007, 04:49:57 am »

Question: If I make an mpl file for media files in a particular folder, and I run an import from that folder at a later date or on a different machine, will MC populate fields based on the mpl or the files themselves?

And also, can the mpl be for files in multiple directories and still be used to import properly? Could I create a single mpl for an entire video directory and use this to restore info on a different machine?

Alex B · « **Reply #24 on:** June 12, 2007, 04:55:20 am »

Quote from: darichman on June 12, 2007, 04:33:47 am

In other news, I have started converting all my home videos to wmv for use with Media Center, as it's a taggable format and this got me a bit worried! Don't worry, I've kept a backup copy of all the original files, I know it's a lossy conversion

You could just create a playlist of the current video files and export it in mpl format. An exported mpl playlist contains all library fields and it is automatically in the correct format for MC.

EDIT

You posted while I was writing this reply, but I think I answered your new questions too.

EDIT 2

Quote

Could I create a single mpl for an entire video directory and use this to restore info on a different machine?

Yes. An MPL file can contain any file types in any location. All needed info is included in the mpl file if it is exported from MC.

The files are identified by the complete filename path so naturally the Filename field must be correct when importing files or updating file data with an mpl file. If the file locations are different on another PC you can fix the Filename fields outside MC before importing the mpl file.

darichman · « **Reply #25 on:** June 12, 2007, 06:41:52 am »

Thanks Alex

hit_ny · « **Reply #26 on:** June 17, 2007, 10:29:07 am »

Quote from: glynor on June 07, 2007, 09:04:39 am

Web Conversion: http://www.onlineconversion.com/unix_time.htm

Only good if you live in the GMT time zone

http://www.4webhelp.net/us/timestamp.php

if you don't

INTERACT FORUM

Author Topic: Help! Fill date from filenames... (Read 9010 times)