CODEWITHSUNDEEP
powershellcsv
rasm
rasm

Reputation: 13

Use Powershell to sum a column in a directory of CSVs with headers

I have a batch of .csv files in a directory with a column in each featuring values I need to sum. The .csv files all have identical headers such as ID, SIZE, FILE_PATH, etc., and "SIZE" is the column I need to sum. I found a handy one-liner to do it one by one (below), but as I try to have it cycle through each of the CSVs with a wildcard operator, I keep getting this error message: "Measure-Object: Input object 'SIZE' is not numeric." The command executes but only gives a total count of all of the rows, not of the summed size.

The one-by-one version that works is:
Get-Content "file.csv" | ConvertFrom-Csv | Measure-Object "SIZE" -Sum

The version I have tried to make work is:
Get-Content ".\*.csv" | ConvertFrom-Csv | Measure-Object "SIZE" -Sum

Everything in the column is numeric except the header, but how do I both specify the column by its name "SIZE" and ignore the header?

Do you have any suggestions? I have to run this monthly over hundreds of .csv files, so it would be ideal to be able to get it to work in one go.

Upvotes: 1

Views: 1247

Answers (2)

Nasir
Nasir

Reputation: 11401

You're getting headers in the middle because Get-Content against multiple files will combine text from all files before processing it. You can use Get-ChildItem and Import-Csv for a better experience.

for example:

Get-ChildItem "*.csv" | ForEach-Object { Import-Csv -Path $_.FullName } | Measure-Object -Property Size -Sum

Upvotes: 1

Doug Maurer
Doug Maurer

Reputation: 8868

Try this instead

Get-ChildItem .\*.csv |
    foreach {Import-Csv $_} |
        Measure-Object size -sum

And if you want just the sum

(Get-ChildItem .\*.csv |
    foreach {Import-Csv $_} |
        Measure-Object size -sum).sum

or

Get-ChildItem .\*.csv |
    foreach {Import-Csv $_} |
        Measure-Object size -sum |
            Select-Object -Expand Sum

or

$sum = Get-ChildItem .\*.csv |
    ForEach-Object {Import-Csv $_} |
        Measure-Object -Property size -sum

$sum.sum

Upvotes: 1

Related Questions