1

I am writing a PowerShell script to collect the F5 LTM device statistics and open it via Excel. But there I am getting timestamp column which I want to change to human readable datetime. I was able to change it manually via Excel formula =(A2/86400)+25569+(-5/24) and then Format cell to date. After that I was able to change it to human readable datetime.

My script is as follows:

 # Allocate a new Query Object
    $Query = New-Object -TypeName iControl.SystemStatisticsPerformanceStatisticQuery
    $Query.object_name = "throughput"
    $Query.start_time = 1479686400
    $Query.end_time = 1480327200
    $Query.interval = 0
    $Query.maximum_rows = 0
JasonMArcher
  • 14,195
  • 22
  • 56
  • 52
user2769144
  • 15
  • 7
  • Insert a new column after the date column, enter your formula in all cells of the new column that have a value to their left, copy the column, paste values, format, then delete the first column. Record these steps as a macro and translate that to PowerShell (see [here](http://sdb.planetcobalt.net/vba2psh.shtml) for translation guidelines). – Ansgar Wiechers Nov 21 '16 at 21:16
  • Thanks. I tried this but when I add the formula it pastes the same value in all cells of column b. =(A2/86400)+25569+(-5/24) For Example it uses A2 value in all the cells down the column B. Is there a way to use a drag function of excel via shell script. ? – user2769144 Nov 23 '16 at 19:17
  • Shell scripts are something else entirely. You should be able to record auto-filling the cells. Otherwise to a loop to put the formula with incremented index in each cell. – Ansgar Wiechers Nov 23 '16 at 21:25
  • Yes, I tried using a loop, but it was too slow. It was taking almost 1 sec to fill one cell and there are almost 400 cells. So I guess there is no straight forward way of changing timestamps to date time – user2769144 Nov 28 '16 at 15:10
  • Filling a cell should take significantly less than a second. For further analysis please update your question with a more complete code sample. – Ansgar Wiechers Nov 29 '16 at 23:54
  • updated with the full code ... – user2769144 Dec 02 '16 at 19:36

1 Answers1

1

Entering a formula into 400 cells is going to take quite some time if you're going to iterate over all cells and enter the formula into each of them. Using the Excel autofill mechanisms is much faster.

$ws.Range("B2").Formula = "=(A2/86400)+25569+(-5/24)"
$ws.Range("B2").AutoFill($ws.Range("B2:B400"), 0)
Ansgar Wiechers
  • 193,178
  • 25
  • 254
  • 328
  • Thanks Ansgar, It worked. Also if you see me script I am passing start time and end time as "Timestamps" and for that, I have to manually convert Date time through an online calculator to timestamps then i have to paste it before the script execution. Is it possible, if I run this script it will fetch past 7 days of data ? For example, I have to run this script Every Monday morning to get data from Past week Monday up till Sunday night 12 AM or just say up till the time I run the script – user2769144 Dec 05 '16 at 17:29
  • For converting regular timestamps to epoch see [this question](http://stackoverflow.com/q/4192971/1630171). – Ansgar Wiechers Dec 05 '16 at 23:07