Home > database >  Using Powershell to get the sum of values per imported CSV
Using Powershell to get the sum of values per imported CSV

Time:03-04

I'm learning PowerShell at the moment, and I have an assignment for which I have a set of CSV files containing (arbitrary) data about the energy usage per day (as E_TodayKwH) for each day of the month. I need to sum up the values for each day to get a monthly value, and I need to create a CSV file containing all the data about energy usage per month. In the end I need to create a function that allows the user to input where to get the data from and summarize it for them.

I'm trying to learn so I'm tackling the problems one by one, but I'm stuck on this step; I want to get a sum value of the data E_TodayKwH per CSV file. My instinct says I should use a ForEach loop or ForEach-Object on my $Source variable (see below), and then use Measure-Object to get a sum of the values. However I can only seem to get the sum value of all CSV files, not the sum per CSV.

(relevant) Code:

$Directory = gci 'C:\Projects\Jr_2014\*.csv'
$Source = Import-CSV $Directory -Delimiter ";" 
$Source | Measure-Object E_TodayKwh -Sum 

This outputs a value which is the sum of all values imported by $Source (i.e. the total value of E_TodayKwH for the whole year).

So I tried a ForEach loop:

ForEach($File in $Source) { Measure-Object E_TodayKwh -Sum }

However this has no output at all (no error message either).

$Source | ForEach-Object { Measure-Object E_TodayKwh -Sum }

Has no output either (no error message either).

I suspect that because I'm importing everything at once in $Source PowerShell doesn't recognize the ForEach/ForEach-Object. However I'm unsure how to proceed. I'm hoping I can get some advice here!

CodePudding user response:

You are on the right track and got very close!

You were right about using a foreach loop for the files. For simplicity sake, i have modified your code as little as possible. It will loop through the files as you did, but then it will import that iterations csv and measure it.

$Directory = gci 'C:\Projects\Jr_2014\*.csv'

ForEach($File in $Directory) { 
    $Source = Import-CSV $File -Delimiter ";" 
    $Source | Measure-Object E_TodayKwh -Sum 
}
  • Related