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:
PowerShell,
csv file