Automate conversion of .log files to .txt

[MrMcTabernaffy]

Hello, I was wondering if it was at all possible to automate the conversion of the .log files to .txt files as shown in this HammerDB blog post.

I.e. instead of manually typing out the input and output files in command line, have the script simply convert all .log files in a certain directory when run.

For reference I am running HammerDB on Windows.

[sm-shaw]

OK this is only adding a couple of lines to loop through the files. As an windows example I have done a few runs and used "Use unique Log Name" to get the unique log extensions. You don't want the data for different runs in the same file as this will get mixed up.

C:\temp>dir *.log
27/03/2020  10:49             5,111 hammerdb_5E7DD9F8D73003E233530393.log
27/03/2020  10:53             5,110 hammerdb_5E7DDACEE3F203E243131373.log
27/03/2020  10:58             5,089 hammerdb_5E7DDC35F95A03E203432303.log
27/03/2020  11:01             5,092 hammerdb_5E7DDCC11B4C03E263633363.log

Then copy the script below, name it something like "convert.tcl" - note my example logs are generated in C:\temp yours may go elsewhere and you will need to change the directory name - make sure you keep the Linux type naming ie C:/temp (TCL will convert it for you). We've also changed the output to go to a named file so don't need to redirect it.

#!/usr/bin/tclsh
set tmpdir C:/temp
set filenames [glob -nocomplain -tails -directory $tmpdir hammer*.log]
foreach filename [ split $filenames ] {
set excelfile [ join [ file rootname $filename ].txt ]
puts "Converting $filename to $excelfile"
set fp [ open [ join $tmpdir/$filename ] r ] 
set op [ open [ join $tmpdir/$excelfile ] w ] 
set file_data [ read $fp ]
 set data [split $file_data "\n"]
 foreach line $data {
 if {[ string match *PERCENTILES* $line ]} {
 set timeval "[ lindex [ split $line ] 3 ]"
 append xaxis "$timeval\t"
         }
     }
 puts $op "TIME INTERVALS"
 puts $op "\t$xaxis"
 foreach storedproc {neword payment delivery slev ostat} {
 puts $op [ string toupper $storedproc ]
 foreach line $data {
 if {[ string match *PROCNAME* $line ]} { break }
 if {[ string match *$storedproc* $line ]} {
 regexp {MIN-[0-9.]+} $line min
 regsub {MIN-} $min "" min
 append minlist "$min\t"
 regexp {P50%-[0-9.]+} $line p50
 regsub {P50%-} $p50 "" p50
 append p50list "$p50\t"
 regexp {P95%-[0-9.]+} $line p95
 regsub {P95%-} $p95 "" p95
 append p95list "$p95\t"
 regexp {P99%-[0-9.]+} $line p99
 regsub {P99%-} $p99 "" p99
 append p99list "$p99\t"
 regexp {MAX-[0-9.]+} $line max
 regsub {MAX-} $max "" max
 append maxlist "$max\t"
     }
       }
 puts -nonewline $op "MIN\t"
 puts $op $minlist
 unset -nocomplain minlist
 puts -nonewline $op "P50\t"
 puts $op $p50list 
 unset -nocomplain p50list
 puts -nonewline $op "P95\t"
 puts $op $p95list 
 unset -nocomplain p95list
 puts -nonewline $op "P99\t"
 puts $op $p99list
 unset -nocomplain p99list
 puts -nonewline $op "MAX\t"
 puts $op $maxlist
 unset -nocomplain maxlist
     }
 close $fp
 close $op
}

double click on hammerdbcli and type "source convert.tcl"

HammerDB CLI v3.3
Copyright (C) 2003-2019 Steve Shaw
Type "help" for a list of commands
The xml is well-formed, applying configuration
hammerdb>source convert.tcl
Converting hammerdb_5E7DD9F8D73003E233530393.log to hammerdb_5E7DD9F8D73003E233530393.txt
Converting hammerdb_5E7DDACEE3F203E243131373.log to hammerdb_5E7DDACEE3F203E243131373.txt
Converting hammerdb_5E7DDC35F95A03E203432303.log to hammerdb_5E7DDC35F95A03E203432303.txt
Converting hammerdb_5E7DDCC11B4C03E263633363.log to hammerdb_5E7DDCC11B4C03E263633363.txt

You now have text files with the excel formatted data with the same file name before the extension

C:\temp>dir *.txt
27/03/2020  11:34             1,014 hammerdb_5E7DD9F8D73003E233530393.txt
27/03/2020  11:34             1,068 hammerdb_5E7DDACEE3F203E243131373.txt
27/03/2020  11:34             1,101 hammerdb_5E7DDC35F95A03E203432303.txt
27/03/2020  11:34             1,159 hammerdb_5E7DDCC11B4C03E263633363.txt

Follow the example in the blog for opening these in excel and generating the graphs.

[MrMcTabernaffy]

Fantastic, this works perfectly.

Thank you again for the help.