CODEWITHSUNDEEP
powershellsum
Mr Printer
Mr Printer

Reputation: 29

Sum to columns in csv and give the result in a 3rd column with Powershell

I´m in need to sum to columns by identifier and show the result in a 3rd column.

Something like this.

The data in the csv look like this(real data):

"PagesBW";"PagesColor";"Filename"
"238119";"86099";"A01 25-08-2020"
"78270";"32262";"A02 25-08-2020"
"158";"933";"A04 25-08-2020"
"510";"82";"A05 25-08-2020"

The result i want is this

"Filename";"PagesBW";"PagesColor";"Total"
"A01 25-08-2020";"238119";"86099";"324218"
"A02 25-08-2020";"78270";"32262";"110527"
"A04 25-08-2020";"158";"933";"1091"
"A05 25-08-2020";"510";"82";"592"

I manage to write this :

$COUNT = import-csv "C:\temp\test\testcount\finalfile3.csv" -Delimiter ";" -Encoding UTF8
$COUNT | Select-Object  Filename, PagesBW, PagesColor, @{Name="Total";e={ $_.PagesBW + $_.PagesColor}}| Export-Csv -Delimiter ";" -Path "C:\temp\test\testcount\new.csv" -NoTypeInformation

But with this i got , this:

"PagesBW";"PagesColor";"TOTAL";"Filename"
"2";"1";"21";"A"
"2";"5";"25";"B"

Instead of sum, what does , is putting together the numbers.

Can i get some advice or help. Please.

Thanks in advance.

Upvotes: 0

Views: 162

Answers (1)

Olaf
Olaf

Reputation: 5232

Import-Csv outputs strings. If you want to calculate with the values you will have to cast them to a number type like [INT] for example. Like this:

$Count = @'
"PagesBW";"PagesColor";"Filename"
"2";"1";"A"
"2";"5";"B"
'@ | ConvertFrom-Csv -Delimiter ';'

$COUNT | 
Select-Object  Filename, PagesBW, PagesColor, @{Name='Total';Expression={ [INT]$_.PagesBW + [INT]$_.PagesColor}}| 
Export-Csv -Delimiter ';' -Path 'C:\temp\test\testcount\new.csv' -NoTypeInformation

Of course you can keep your code to import your csv file like this:

$COUNT = import-csv 'C:\temp\test\testcount\finalfile3.csv' -Delimiter ';' -Encoding UTF8
$COUNT | 
    Select-Object  Filename, PagesBW, PagesColor, @{Name='Total';Expression={ [INT]$_.PagesBW + [INT]$_.PagesColor}}| 
    Export-Csv -Delimiter ';' -Path 'C:\temp\test\testcount\new.csv' -NoTypeInformation

Checked with the newly posted sample data and it still works:

$Count = @'
"PagesBW";"PagesColor";"Filename"
"238119";"86099";"A01 25-08-2020"
"78270";"32262";"A02 25-08-2020"
"158";"933";"A04 25-08-2020"
"510";"82";"A05 25-08-2020"
'@ | ConvertFrom-Csv -Delimiter ';'

$COUNT | 
Select-Object  Filename, PagesBW, PagesColor, @{Name='Total';Expression={ [INT]$_.PagesBW + [INT]$_.PagesColor}}

The output I get is this:

Filename       PagesBW PagesColor  Total
--------       ------- ----------  -----
A01 25-08-2020 238119  86099      324218
A02 25-08-2020 78270   32262      110532
A04 25-08-2020 158     933          1091
A05 25-08-2020 510     82            592

Upvotes: 3

Related Questions