IPB

Welcome Guest ( Log In | Register )

 
Reply to this topicStart new topic
> Exporting to excel
graqeme
post Jun 16 2012, 03:10
Post #1


Member


Group: Members
Posts: 3
Joined: 16-June 12
From: Lambton Australia
Member No.: 16579
Mp3tag Version: 2.51



I want to know how to add some blank lines to a cvs file (for excel) before doing totals. So that when imported into excel these totals will always be in the same cells not matter how manf music files Im get


--------------------
G Pearce
Go to the top of the page
 
+Quote Post
DetlevD
post Jun 16 2012, 07:31
Post #2


Member


Group: Full Members
Posts: 5009
Joined: 26-May 06
From: Wuppertal, Germany, Planet Earth
Member No.: 3194
Mp3tag Version: 2.63



QUOTE (graqeme @ Jun 16 2012, 04:10) *
I want to know how to add some blank lines to a cvs file (for excel) before doing totals. So that when imported into excel these totals will always be in the same cells not matter how manf music files Im get

;-) A simple solution would be to always use the last possible row of an excel file, but that depends on the version of Excel.

Another thinkable solution would be ...
... detect the row of the last data line resp. count the rows of the data lines while reporting out,
... behind the loop add some blank lines up to the last fixed totals line.

For counting and retrieving you can use the functions $put, $puts, $get, $add.
... for example: $put(MyCounter,$add(MyCounter,1))

For creating forced linefeeds you can use the functions $char, $repeat
... for example: $repeat($char(13)$char(10),100)

To place the last line at the right position you have to do some calculation ...
... for example: $repeat($char(13)$char(10),$sub(100,$get(MyCounter)))

I've never tested this in reality, that is now your part.
Please show us your sample Mp3tag Export Language script.

DD.20120616.0830.CEST


--------------------
* Beyond that, don't ask, when you don't know what to do with the answer. *
♥ home is where the heart is ♥
Go to the top of the page
 
+Quote Post
graqeme
post Jun 16 2012, 08:22
Post #3


Member


Group: Members
Posts: 3
Joined: 16-June 12
From: Lambton Australia
Member No.: 16579
Mp3tag Version: 2.51



QUOTE (DetlevD @ Jun 16 2012, 07:31) *
;-) A simple solution would be to always use the last possible row of an excel file, but that depends on the version of Excel.

Another thinkable solution would be ...
... detect the row of the last data line resp. count the rows of the data lines while reporting out,
... behind the loop add some blank lines up to the last fixed totals line.

For counting and retrieving you can use the functions $put, $puts, $get, $add.
... for example: $put(MyCounter,$add(MyCounter,1))

For creating forced linefeeds you can use the functions $char, $repeat
... for example: $repeat($char(13)$char(10),100)

To place the last line at the right position you have to do some calculation ...
... for example: $repeat($char(13)$char(10),$sub(100,$get(MyCounter)))

I've never tested this in reality, that is now your part.
Please show us your sample Mp3tag Export Language script.

DD.20120616.0830.CEST



Have attached script

What I was attempting to do was transfer the value of %counter% over to mycounter and always have the totals at line 12000 in my excel fil so that no matter how many I export as long as it is beloww 12000 the totals will always be at line 12000

When i run this script it puts the totals at line 1140Attached File  Graeme.mte ( 569bytes ) Number of downloads: 267


--------------------
G Pearce
Go to the top of the page
 
+Quote Post
DetlevD
post Jun 16 2012, 09:20
Post #4


Member


Group: Full Members
Posts: 5009
Joined: 26-May 06
From: Wuppertal, Germany, Planet Earth
Member No.: 3194
Mp3tag Version: 2.63



QUOTE (graqeme @ Jun 16 2012, 09:22) *
Have attached script ...

Yes, using the system variable %_counter% there is no need to set up a user defined counter variable.
Right after a closed loop the system variable %_max_counter% provides the maximum count of the last loop.

I would code the report like this ...
(maybe it would be more safe to replace the semicolon delimiters by the tab character $char(9))

1: $filename($getEnv('USERPROFILE')'\Desktop\20120616.Test.Graeme.csv',UTF-16)'"ID";"Title";"Artist";"Album Artist";"Com";"Album";"Track";"Year";"Length";"Size";"Composer";"Path";"Filename"'
2: $loop(%_filename_ext%)'"'%_counter%'";"'%TITLE%'";"'%ARTIST%'";"'%ALBUMARTIST%'";"'%COMMENT%'";"'%ALBUM%'";"'%TRACK%'";"'%YEAR%'";"'%_length_seconds%'";"'%_file_size_bytes%'";"'%COMPOSER%'";"'%_folderpath%'";"'%_filename_ext%'"'
3: $loopend()$repeat($char(13)$char(10),$sub(17,%_max_counter%))
4: '"Check";"Total Files";"Size in Bytes";"Total Size";"Total Time";"Total Time in Seconds";"Date"'
5: '"'%_max_counter%'";"'%_total_files%'";"'%_total_size_raw%'";"'%_total_size%'";"'%_total_time%'";"'%_total_time_raw%'";"'%_datetime%'"'


You have to take into account the known quirk of the Mp3tag Export Scripting Language.
Each line of code in the report script itself, creates a linefeed in the report output file.
There is no way known today, how to suppress these unwanted linefeed output.

Regarding the example script from above, there are 3 lines to be subtracted implicitely (20-3=17), to get the totals section exactly into line 20 (valid for %_max_counter% <= 17).

DD.20120616.1020.CEST

This post has been edited by DetlevD: Jun 16 2012, 10:38


--------------------
* Beyond that, don't ask, when you don't know what to do with the answer. *
♥ home is where the heart is ♥
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 



RSS Lo-Fi Version Time is now: 1st October 2014 - 13:30