Convert test results to comma delimited csv for Excel

johnch99 1 Reputation point
2022-05-20T17:12:17.9+00:00

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
Windows Server PowerShell
Windows Server: A family of Microsoft server operating systems that support enterprise-level management, data storage, applications, and communications.PowerShell: A family of Microsoft task automation and configuration management frameworks consisting of a command-line shell and associated scripting language.
5,345 questions
0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Rich Matheisen 44,541 Reputation points
    2022-05-20T19:57:11.98+00:00

    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
    0 comments
  2. johnch99 1 Reputation point
    2022-05-20T20:38:24.23+00:00

    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?

    0 comments
  3. Rich Matheisen 44,541 Reputation points
    2022-05-20T21:16:53.673+00:00

    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.

    0 comments