question

johnch99-7360 avatar image
0 Votes"
johnch99-7360 asked RichMatheisen-8856 edited

Convert test results to comma delimited csv for Excel

I have a test program that outputs the results below:

Total Time (s): 44.5401
59974 23.0763 170.325 uploads|seconds|bandwidth


m_startStreamTime: 1531825300, m_endStreamTime: 1947303390
testTime[us]: 41547809, idleTime[us]: 18175234, procesing time[us]: 23372575, data_len[bytes]: 7659847680, submitted data: 7659847680
Rate[MiB/s]: 312.546
Rate[MB/s]: 327.728

I want to convert test results to comma delimited csv for Excel, like below.
Total Time,Uploads,Seconds,BW Upload,BW Storage,TestTime,Idle Time,Process Time,Data_Len
44.5401,59974,23.0763,170.325,327.728,41547809,18175234,23372575,659847680

Can or should I use Powershell's Import-Csv cmdlet? Or is there another way to do this?
Can you show me how this can me coded?

Thanks

windows-server-powershell
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

RichMatheisen-8856 avatar image
0 Votes"
RichMatheisen-8856 answered

Something like this?

 $h = [ordered]@{
     'Total Time' = ""
     Uploads = ""
     Seconds = ""
     'BW Upload' = ""
     'BW Storage' = ""
     TestTime = ""
     'Idle Time' = ""
     'Process Time' = ""
     Data_Len = ""
 }
 $testresults = get-content C:\Junk\TestResults.txt
 ForEach ($line in $testresults){
     if ($line -match "Total Time \(s\): (.+)$") {
         $h.'Total Time' = $matches[1].trim()
         Continue
     }
     if ($line -match "(.+)\suploads\|seconds\|bandwidth"){
         $triplet = ($matches[1] -split "\s").Trim()
         $h.Uploads = $triplet[0]
         $h.Seconds = $triplet[1]
         $h.'BW Upload' = $triplet[2]
         Continue
     }
     if ($line -match "Rate\[MB/s\]:\s(.+)$"){
         $h.'BW Storage' = $matches[1].Trim()
         Continue
     }
     if ($line -match "testTime\[us\]:\s"){
         $quint = ($line -split ",").Trim()
         if ($quint[0] -match "testTime\[us\]:\s(.+)$"){
             $h.TestTime = $matches[1].Trim()
         }
         if ($quint[1] -match "idleTime\[us\]:\s(.+)$"){
             $h.'Idle Time' = $matches[1].Trim()
         }
         if ($quint[2] -match "procesing time\[us\]:\s(.+)$"){
             $h.'Process Time' = $matches[1].Trim()
         }
         if ($quint[3] -match "data_len\[bytes\]:\s(.+)$"){
             $h.Data_Len = $matches[1].Trim()
         }
         Continue
     }
 }
 [PSCustomObject]$h
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

johnch99-7360 avatar image
0 Votes"
johnch99-7360 answered

I tested the code, it works very good, where should I insert code so all the numbers are on 1 line in a CSV file, and separated by comma, so Excel can read in the file.

Where should I go to learn more about Powershell? A book or online videos?

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

RichMatheisen-8856 avatar image
0 Votes"
RichMatheisen-8856 answered RichMatheisen-8856 edited

Change line 46 in code:

 [PSCustomObject]$h | Export-Csv x:\dir\file.csv -NoTypeInfo

This is a very good learning experience (AND it's free!): Windows-PowerShell-4

The book covers PS version 4, but for your purposes the differences between 4.0 and 5.1 are trivial.

Work through the 1st half of the book and do the exercises. Then, once you feel more confident with PowerShell, go through the 2nd half of the book which covers using PowerShell to do common administrative tasks.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.