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

Question

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.

Nasir · Accepted Answer · 2020-10-19 16:26:09Z

1

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

answered Oct 19, 2020 at 16:26

Nasir

11.5k9 gold badges34 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

RaRdEvA Over a year ago

Thenk you. What if the user whats to have multiple calculations? to know rows And to know sum.

Nasir Over a year ago

Measure-Object will return both the Sum as well as Count that will have the information you need.

Doug Maurer · Accepted Answer · 2020-10-19 16:25:04Z

1

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

answered Oct 19, 2020 at 16:25

Doug Maurer

8,9773 gold badges16 silver badges18 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related