header image

Summing and counting

Posted by: | April 23, 2010 | No Comment |

One problem that seems to keep arising is counting the number of records in a file and summing the contents of one or more files.  If we start with a couple of files:

test.txt

Green,123.45
Brown,456.77
Black,890.12
White,679.43

and test.csv

Name,Amount
Green,123.45
Brown,456.77
Black,890.12
White,679.43

The only difference is that the csv file has a header row.

If we start with the csv file we can do this

PS> Import-Csv c:\test\test.csv | Measure-Object -Property Amount -Sum

Count    : 4
Average  :
Sum      : 2149.77
Maximum  :
Minimum  :
Property : Amount

Nice and easy – just the way we like.  Working with the text file may be a bit more difficult as we can’t use Measure-Object directly. The traditional approach would be

001
002
003
004
005
006
007
008
009
010
$count = 0
$amount = 0.00

Get-Content c:\test\test.txt |
foreach {
    $data = $_ -split ","
    $count++   
    $amount += $data[1]
}
Write-Host "$count records and $amount in total"

 

Set a couple of variables – one as a counter and one to hold the sum.  Use Get-Content to read the file on to the pipeline. We then use foreach to sum the amounts. Our final action is to write out the answers.

There is a better way. In PowerShell v2 we get some extra parameters that enable us to add a header to the file before we add it to the pipeline.

PS> Import-Csv c:\test\test.txt -Header "Name","Amount" | Measure-Object -Property Amount -Sum

Count    : 4
Average  :
Sum      : 2149.77
Maximum  :
Minimum  :
Property : Amount

If we change the delimiter to something other than a comma e.g. a semi-colon

Green;123.45
Brown;456.77
Black;890.12
White;679.43

We can still add the header.  We just need to define the delimiter. Check the import-csv help file for full details.

PS> Import-Csv c:\test\test.txt -Header "Name","Amount" -Delimiter ";" | Measure-Object -Property Amount -Sum

Count    : 4
Average  :
Sum      : 2149.77
Maximum  :
Minimum  :
Property : Amount

Just because a file doesn’t have a header doesn’t mean we can’t work with it as a csv!

Technorati Tags: ,
under: PowerShellV2