使用Powershell进行CSV统计

debugcn 发表于 Dev

vxs8122

假设我创建了一个脚本，该脚本创建了一个长的csv表，如下所示：

Tag                 Value       Date        Time        Mode  Alarm
PPP_VE0160A.F_CV    5.04E-03    5/1/2014    12:00:00 AM 11    Alm Disabled
PPP_VE0160A.F_CV    4.95E-03    5/1/2014    12:01:00 AM 11    Alm Disabled
PPP_VE0160A.F_CV    5.16E-03    5/1/2014    12:02:00 AM 11    Alm Disabled
.
.
.
PPP_VE0160B.F_CV    1.42E-02    5/1/2014    12:00:00 AM 11    Alm Disabled
PPP_VE0160B.F_CV    1.46E-02    5/1/2014    12:01:00 AM 11    Alm Disabled
PPP_VE0160B.F_CV    1.26E-02    5/1/2014    12:02:00 AM 11    Alm Disabled
.
.
.
PPP_VE0161A.F_CV    4.43E-03    5/1/2014    12:00:00 AM 11    Alm Disabled
PPP_VE0161A.F_CV    3.16E-03    5/1/2014    12:01:00 AM 11    Alm Disabled
PPP_VE0161A.F_CV    4.13E-03    5/1/2014    12:02:00 AM 11    Alm Disabled
.
.
.

我想用统计信息制作一个csv文件，如下所示：

Tag               Minimum   Average   Maximum
PPP_VE0160A.F_CV  4.95E-03  5.12E-03  5.25E-03
PPP_VE0160B.F_CV  1.23E-02  1.35E-02  1.51E-02
PPP_VE0161A.F_CV  4.07E-03  4.32E-03  4.51E-03
.
.
.

我看过使用Measure-Object cmdlet，但是我不知道如何将其分隔为单个标签。到目前为止，我的代码见下文：

Param ( [Datetime]$date = ( Get-Date ).AddDays( -1 ) )

$currentPath = [System.IO.Path]::GetDirectoryName($myInvocation.MyCommand.Definition)
$sourcePath = ""
$destinationPath = "$currentPath\Output_Files"

# Only using a few from long list of tagnames, for testing
$tagnames = @(
    "PPP_VE0160A", "PPP_VE0160B", "PPP_VE0161A"
)

$dateInput = $date.ToString( "MM/dd/yy" )
$dateOutput = $date.ToString( "yyMMdd" )

ForEach ( $tagname in $tagnames ) {
    .\HTD2CSV\HTD2CSV.exe `
        PPP:$tagname.F_CV `
        /dur:00:23:59:00 `
        /int:00:01:00 `
        /sd:$dateInput `
        /st:00:00:00 `
        /sp:$sourcePath `
        /dp:$destinationPath\$tagname.csv `
        /dtf:1 `
        /dbg:0
}

Wait-Process htd2csv

$data = Get-ChildItem $destinationPath\PPP_*.csv | 
    ForEach-Object { $_.FullName } | 
    Import-Csv -Header Tag, Value, Date, Time, Mode, Alarm

Remove-Item $destinationPath\PPP_*.csv

$data | Export-Csv -Path "$destinationPath\$dateOutput summary.csv"

我尝试使用类似的东西

$data | Group Tag | ForEach-Object { 
    Measure-Object $_.Group.Value -Property Value -Minimum -Average -Maximum 
} | Export-Csv -Path "$destinationPath\$dateOutput statistics.csv"

并且这仅产生空白的csv文件。我不确定在导出之前如何对所有统计信息进行分组。

vxs8122

经过长时间的思考和玩耍，我自己解决了这个问题。Measure-Object需要管道中的对象。所以，我写了这样的方式来获得想要的结果：

$collection = @()

$data | Group-Object Tag | ForEach-Object { 

    $datarow = New-Object PSObject -Property @{ Tag = $_.Name }

    $stat = $_.Group | Measure-Object -Property Value -Minimum -Average -Maximum

    $datarow | Add-Member NoteProperty -Name "Minimum" -Value $stat.Minimum
    $datarow | Add-Member NoteProperty -Name "Average" -Value $stat.Average
    $datarow | Add-Member NoteProperty -Name "Maximum" -Value $stat.Maximum

    $collection += $datarow
}

$collection | Export-Csv -Path "$destinationPath\$dateOutput statistics.csv" -NoTypeInformation

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。