That is astounding.
Could you show us the script snippet where you use it? Perhaps as a screendump?
Is the %_length% value missing for ALL of your files? Have you tried to validate the files in question to make sure there isn’t a header issue? It seems very strange that this value is missing but %_length_seconds% is populated. You mentioned that you need to divide this by 60, but that is correct for this field. Does this return the expected minutes/seconds?
$filename(csv,utf-16)Title;Year;Artist;Album;Track;Length
$loop(%_filename_ext%)%title%;%year%;%artist%;%album%;%track%;%_length%
$loopend()build on %_date% with %_app% - the universal Tag editor - Mp3tag - the universal Tag Editor (ID3v2, MP4, OGG, FLAC, ...)
This is what I’m using to export using CSV. The format for according to “Placeholders for export configurations” is supposed to be
%_length% = Length (formatted)
Which you noticed I put at the end of the string. All you are doing is rearranging items to fit your needs and adding time. I've tried the%_length, as you can see, I've tried using just %lenght%, I've tried using %_length_seconds% but all that does is give you the length of time in seconds. I'm doing something wrong in the string, I'm not a programmer so to me everything seems right according to what I see. So what is it Im deoing wrong? I have no idea.
I would like to correct myself in regards to the string for time. The format %_length% does give you length, however, IT'S IN HOURS, not minutes and seconds. I did an AutoSum of 1137 songs just to see what I would get. The last cell was formatted in dd:hh:mm:ss the total was 14:19:14:00, that's 14 days 19 hours, and 14 minutes. Really? I added a column and did a formula converting the hours to minutes and seconds for the same amount of songs. The outcome was 02:18:43:14 and again the final cell was formatted as dd:hh:mm:ss. Quite a difference. This comes in handy if you want to calculate how long it would take to play all songs in your collection or you just might want to do just some of them. If there is a string somewhere that can convert the hours into minutes and seconds rather than hours and minutes please let me know what it is because %_length" sure doesn't.
I exported a csv file with a few tracks, adding %_length% as the last field. All came out in the correct format. I had one track longer than 60 minutes, it came out as expected in the format 01:07:04 (1 hour, 7 minutes, 4 seconds) as expected.
The rest were regular songs and came out in mm:ss only.
I’m not sure if you can run a simple summing formula for this field. You may have to use the %_length_seconds% field, then calculate days:hours:minutes:seconds from there.
The export in MP3Tag knows global variables for this purpose which work for each loop.
Have a look at the help in section export for the variables.
%_total_time% Total playing time (formatted output)
%_total_time2% Total playing time (formatted output without days)
%_total_time_raw% Total playing time in seconds
I cannot reproduce this.I pasted your script to an export script and I got:
Sky never die;1997;Fresh Moods;Ambient Diary.One;A02;07:45
Scuba Tongues;1997;Dr. Scissorss;Ambient Diary.One;A02;10:06
Breezer in (the) Green;1997;Insect Jazz;Ambient Diary.One;A03;07:19
Alone? Lonely.;1997;Taklamakan 18';Ambient Diary.One;A04;09:40
The Witness;1997;Saafi Brothers / Sisters;Ambient Diary.One;A05;07:37
Strobe "Light" Network [Blue Glass Mix];1997;Cold;Ambient Diary.One;7;09:52
`
exactly the minutes that each track has in length.ohrenkino: Even though you can open the CSV file once produced I never open the CSV file, I open it directly into excel. The reason, the csv file produces one line of data per row and is deposited into column A. I did however find a way to convert this data into columns and rows directly in excel but, unfortunately, it was just by chance that I found this function and I can't remember how I did it. You can use the "From Text/CSV' function found under the "Data" tab to import the CSV file but the result as far as time is concerned is formated as "AM". I must admit that looking at the raw data it does seem as though there are minutes and seconds. I just wish I could find that function to convert those single rows into columns in Excel. My only question is how.
You have to define a field delimiter, a character like ;
In Excel, you can set this character during the import. If Excel can find this character in your raw CSV data, it will split the data in separates columns for you.
I would advise to use the tab character.
So
would become
$loop(%_filename_ext%)%title%$char(9)%year%$char(9)%artist%$char(9)%album%$char(9)%track%$char(9)%_length%
You would then have to convert the column with the length to "Time"
And if that does not work, try
$loop(%_filename_ext%)%title%$char(9)%year%$char(9)%artist%$char(9)%album%$char(9)%track%$char(9)"%_length%"
To sum it up: MP3tag works (again) without flaw, it is the target application that causes the problems.
1 Like
If you allow Excel to import a csv file without changing the separator, it will include the colon “:” as one of those. And since this field was the last one and you don’t have another header, the minutes are being dropped during the import.
Use the tab character, or even some other keyboard character that you wouldn’t otherwise use. I personally use the § character since I have no use in any of my tags.
Additonal note for the import of a CSV into MS Excel:
Don't just double click on your CSV file to open it in Excel.
Open Excel first and then inside Excel click on
Open -> Search
then change the extension from the default *.xls to
All files *.* or
Text files *.csv to find your *.csv file.
Then click on the Open-Button.
This way, you get a 3 step wizard to import your data into Excel.
In Step #1 of #3 you should choose Delimited (even if Excel tells you it has detected "Fixed width")
In Step #2 of #3 you can define your delimiter (a TAB or ; or § or whatever you use)
2 Likes
LyricsLover: The steps you provided does work but with only one drawback, the time factor. Bringing in the CSV file that way formats the time from minutes and seconds to hours and minutes. PROOF. Take and bring a number of songs you have into Excel in this manner you described, I've actually used it before. Highlight your time (length) column and do an AutoSum. You will find that your minutes have now turned to hours because importing time is a bear. Just for numbers, you may have a song that is 03:45 minutes long. Excel doesn't know this and will bring in the song at 3:45 which actually is hours and minutes. This may be OK for some but if you try to total the length column, just to see how long an album is you will end up with the wrong time. That's where a Formula comes in. You must convert the hours and minutes into minutes and seconds. If you bring in the raw data from the CSV file it will give you the minutes and seconds. I'm using Office 365 and in Excel, I accidentally found how to do this, putting the data into columns and rows, but for the life of me, I can't remember how I did it. Perhaps I should go over to the Office Forum and maybe somebody over there can point me in the right direction.
I appreciate all of the responses I've gotten. I know TIME (length) is a bear to convert to the correct format but unless you know how to use the Formulas in Excel, which I don't, it's very frustrating.
Is this necessary if you export %_total_time% also?
But the alleged lack of functions in Excel or the like is hardly the business of MP3tag - an excursion to an Excel forum would be appropriate, I would think.
Just an idea: if you know that there will never be a track that is longer than 59 minutes, 59 seconds, then export
00:%_length% instead of the simple
%_length%
Yep, because your questions are no more Mp3tag related.
Maybe this one can help:
And the information that in the Excel Import Wizard Step #3 of #3 you can define your column LENGTH as "Text" (not "Standard". Then it will appear as 03:45, not 03:45:00 anymore.)
ohrenkino: I tried the 00:%_length% and it does give you minutes and seconds BUT you cannot SUM the column. Excel is so darn picky. If you try to sum the column Excel will go to the first entry and request a formula. When I first started with Mp3Tag I had problems with time and that was years ago. I went to the Excel Forums and a guy gave me the formula to convert the time into minutes and seconds, again I am not a programmer so I had to rely on someone to help. Once the data is in Excel you have to insert another column in front of the time (length) column then format that column using format>custom>mm:ss. In the first cell you have a formula:=TIME(0,INT(J124),MOD(J124,1)*60) this will convert the hours and minutes into minutes and seconds. Once the formula is done just copy (FILL) down and everything is converted. The last cell can be formatted using format>custom>dd:hh:mm:ss and it will give you the total of how long it will take you to play every song you have. Why this? Well, you could say it's interesting to see how long it takes to play all your songs. Your albums, which I have over 118 and counting, even though you know how long it takes to play them, converted to mp3 and placed into your song collection, I have mine in separate folders and have them in Excel as well, total time still must be converted to hours, minutes and seconds. The Bottomline was to see if there was an easier way for Mp3Tag to convert length into a format that Excel could understand and calculate unfortunately it cant Thought I have the formula to change the time I was hoping that Mp3Tag could do it
I am not sure what you do ...
The column with the time gets imported as "user-defined" with hh:mm:ss as pattern.
The function to auto-sum that column sums up all seconds, minutes and hours without the need to add other fomulars.
The shown amount is, BTW, exactly that what MP3tag has calculated:
The biggest window part shows the excel sheet, the cut-off part further down is the MP3tag window status bar of the same files.
So I would again advise you to use the MP3tag built-in variable %_total_time% and add that as the last output in your export script.
Excel needs a custom time format for the column in this case. You can format it as time, use mm:SS and everything should work for you. As long as you don’t have any with more than 60 minutes.
EDIT:
Looks like Excel will always force these values to start with ours first. So this is a limitation there.
I did find this potential solution to use when exporting from mp3tag, using the %_length_seconds% seconds-only value, then applying a new column in Excel with a formula. It’s the closest I can find to get that which you seek.
The tip you link to, which uses TIME() is slightly cumbersome. Excel stores times as parts of a day (and dates as a number of days). Therefore it suffices to divide the number of seconds by 86400 s/d = 24 h/d × 60 min/h × 60 s/min. Then format it as a duration. One could enforce that with =TEXT(seconds/86400, "[h]:mm:ss").
I know this is a late response, and I am assuming that you are trying to achieve the time format in "HH:MM:SS", even if its less than 60 seconds/1 minute or less than an hour (aka MM:SS)? If you want to, for example, convert an "MM:SS", 4 minutes and 32 seconds (i.e. "04:32") or just 9 seconds (i.e. "00:09") into HH:MM:SS (i.e. "00:04:32" and "00:00:09"), then use this single-line replace function below that I wrote:
$replace(%_length%,$regexp(%_length%,'^(\d{2}:\d{2})$',$1),$regexp(%_length%,'^(\d{2}:\d{2})$',00:$1))
Likewise, it will NOT alter the length format of files greater than an hour. Basically, it will keep, for example, 1 hour 39 mins 47 seconds ("01:39:47") as "01:39:47". Moreover, under COLUMNS, you can replace "%_length%" with my string above to represent all durations (aka "%_length%") as "HH:MM:SS".