CODEWITHSUNDEEP
csvpowershelltext-parsing
BrianA
BrianA

Reputation: 3

Powershell - Parsing a result text file to a .CSV file

I'm very new to powershell and I need some guidance, please. Trying to read the Text File below and create a custom .csv file that will allow for easy database managing.

Currently have tried using:

Attempt #1

Get-Content D:\ParsingProject\CDMv3\CDMv3.txt
$SequentialRead  = $Array | Select-String -Pattern "Sequential Read :"
$SequentialReadResults = $SequentialRead -Split "Sequential Read :"

$csvContents = @() # Create the empty array that will eventually be the CSV file
$row = New-Object System.Object # Create an object to append to the array
$row | Add-Member -MemberType NoteProperty -Name "Seuqential Reads" -Value $SequentialReadResults 
$csvContents += $row # append the new data to the array
$csvContents | Export-CSV -Path D:\ParsingProject\CDMv3\mycsv.csv

Attempt # 2 (this one is incomplete)

$input_path = 'D:\ParsingProject\CDMv3\CDMv3.txt'
$output_file = 'D:\ParsingProject\CDMv3\CDMv3_scores.txt'
$regex = ‘[A-Z\s]+[A-Z]+[:\s}+[\d]+.[0-9]+\s[A-Za\/]{1,4}’
Select-string -path $input_path -pattern $regex -AllMatches | %{$_.Matches} | %{$_.Value} > $Output_file

Are either option 1 or 2 going in the right direction to what I'm trying to do?

Thanks for all help in advanced, much appreciated. =)

Text File:

-----------------------------------------------------------------------
CrystalDiskMark 3.0.3 x64 (C) 2007-2013 hiyohiyo
-----------------------------------------------------------------------
* MB/s = 1,000,000 byte/s [SATA/300 = 300,000,000 byte/s]

Sequential Read :   135.091 MB/s

Sequential Write :   126.046 MB/s

Random Read 512KB :    44.569 MB/s

Random Write 512KB :   117.965 MB/s

Random Read 4KB (QD=1) :     0.468 MB/s [   114.4 IOPS]

Random Write 4KB (QD=1) :     8.412 MB/s [  2053.6 IOPS]

Random Read 4KB (QD=32) :     0.654 MB/s [   159.6 IOPS]

Random Write 4KB (QD=32) :    10.751 MB/s [  2624.7 IOPS]

Test : 1000 MB [C: 7.3% (64.9/889.4 GB)] (x5)

Date : 2015/12/09 22:06:02

OS : Windows 8.1  [6.3 Build 9600] (x64)

Expected Output in CSV (without the all the ------)

------Column1-----------------------------Column2----------Column3 

Row1-SubTest----------------------------MB/s-------------IOPS

Row2-Sequential Read :----------------135.091 MB/s

Row3 Random Read 4KB (QD=1) :--0.468 MB/s---114.4 IOPS

Upvotes: 0

Views: 2492

Answers (3)

Romain Ferraton
Romain Ferraton

Reputation: 71

Another solution base on the @themadTechnician to compile results of several files

$finalresult = @()
Get-ChildItem "D:\Data\CrystalDiskMarkResults" | 
Foreach-Object {
    $FullFileName = $_.FullName
    $Filename = $_.Name
    $content = Get-Content $FullFileName
$counter = 1
$Results = $content | Where{$_ -match "(SEQ|RND)(.*): ((.+?) MB\/s) (\[(.*) IOPS\]) < (.*) us"}|ForEach{
    [pscustomobject]@{
        'Filename'=$Filename
        'Index'=$counter
        'TestType'=$Matches[1].Trim(' ')
        'SubTest'=$Matches[2].Trim(' ')
        'bandwidth'=$Matches[4].Trim(' ')
        'IOPS'=$Matches[6].Trim(' []')
        'latency'=$Matches[7].Trim(' ')
        }
        $counter++
    }
#$Results | ft
$finalresult+=$Results
}

$finalresult | Export-CSV D:\Data\CrystalDiskMarkResults.csv -NoType

Csv Result file can be easily exploited using a viz tool. exemple with Tableau : CrystalDiskMark Dashboard

Upvotes: 0

TheMadTechnician
TheMadTechnician

Reputation: 36332

Ok, RegEx works fine here I think. Let's try this now...

Get-Content D:\ParsingProject\CDMv3\CDMv3.txt |Where{$_ -match "^(.*?) : (.+? MB\/s)( \[.*)?$"}|ForEach{
    [pscustomobject]@{
        'SubTest'=$Matches[1]
        'MB/s'=$Matches[2]
        'IOPS'=If($($Matches[3])){$Matches[3].Trim(' []')
        }
    }
} | Export-CSV D:\ParsingProject\CDMv3\mycsv.csv -NoType

That will Match for the RegEx defined here, createa custom object based on the matches found, and convert the array of objects into a CSV file like this:

SubTest                  MB/s         IOPS       
Sequential Read          135.091 MB/s            
Sequential Write         126.046 MB/s            
Random Read 512KB        44.569 MB/s             
Random Write 512KB       117.965 MB/s            
Random Read 4KB (QD=1)   0.468 MB/s   114.4 IOPS 
Random Write 4KB (QD=1)  8.412 MB/s   2053.6 IOPS
Random Read 4KB (QD=32)  0.654 MB/s   159.6 IOPS 
Random Write 4KB (QD=32) 10.751 MB/s  2624.7 IOPS

Upvotes: 2

Χpẘ
Χpẘ

Reputation: 3451

If "Sequential Read" is always the first result you want to capture then you could do this:

# Assume $inText is an array of lines, one per line of a particular file

for ($lineNum = 0; $lineNum -lt $inText.count; $lineNum++) {
  if ($inText[$lineNum] -like "Sequential Read*") {break}
}
if ($lineNum -eq $inText.count) {
  write-error '"Sequential Read" not found in file`n'
  return # or "exit" if this code is not in a function
}

# Output any constant content to file
@"
------Column1-----------------------------Column2----------Column3     
Row1-SubTest----------------------------MB/s-------------IOPS
"@ | add-content myfile.csv

for (; $lineNum -lt $inText.count; $lineNum++) {
  $testName, $testResult = $inText[$lineNum] -split ':'
  $mbps, $iops = $testResult -split '['
  if ($iops -eq $null) {$iops = "IOPs not reported"}
  if ($testName -like "Test*") {break}
} | 
export-csv myfile.csv # add other arguments as needed

As written the script does not output the lines beginning with "Test" to the CSV. If you want that or subsequent lines to be output, some tweaking will be necessary.

Also the output file won't look exactly exactly like the OP. In particular "RowX-" won't be at the beginning of each line. Also there will be a comma delimiting fields, not a series of dashes. The script can be tweaked if necessary to make the output exactly like what's shown in the OP.

Upvotes: 0

Related Questions