Sort and add

[Juan Pablo Gallardo]

I have a PowerShell script that comes up with the following result as a csv:

Input file:

SITEA 20190405 SITEB ProductX productX Description SITEA EA 20 20190412
SITEA 20190405 SITEB ProductY productY Description SITEA EA 16 20190412
SITEA 20190405 SITEB ProductY productY Description SITEA EA 4 20190412
SITEA 20190405 SITEB productW productW Description SITEA EA 4 20190412
SITEA 20190405 SITEB productA productA Description SITEA EA 2 20190412
SITEA 20190405 SITEB productB productB Description SITEA EA 2 20190412
SITEA 20190405 SITEB productX productX Description SITEA EA 160 20190412
SITEA 20190405 SITEB ProductY productY Description SITEA EA 10 20190412

Now what I need to do is find all the same product lines and add the qties making a summary and remove the lines not needed anymore so in the above case, the result would be:

Output file:

SITEA 20190405 SITEB ProductX productX Description SITEA EA 180 20190412
SITEA 20190405 SITEB ProductY productY Description SITEA EA 30 20190412
SITEA 20190405 SITEB productW productW Description SITEA EA 4 20190412
SITEA 20190405 SITEB productA productA Description SITEA EA 2 20190412
SITEA 20190405 SITEB productB productB Description SITEA EA 2 20190412
SITEA 20190405 SITEB productX productX Description SITEA EA 160 20190412

Does anybody know?

thank you

[#2 · Accepted solution]

@Juan Pablo Gallardo 

Can you try below script ?

$filepath = 'C:\Users\UserName\Documents\input.csv';
$csvInput = Import-CSV $filepath -Header C1,C2,C3,C4,C5,C6,C7,C8,C9
$csvOutput = @();
$csvInput | Foreach-Object{ 
$currentRow = $_;
$existingRow = $csvOutput | Where-Object { $_.C4 -eq $currentRow.C4}
if($existingRow -ne $null) {
$existingRow.C8 = $existingRow.C8 + [int]$currentRow.C8; 
} else { 
$csvOutput += New-Object -TypeName PSCustomObject -Property @{
C1 = $currentRow.C1
C2 = $currentRow.C2
C3 = $currentRow.C3
C4 = $currentRow.C4
C5 = $currentRow.C5
C6 = $currentRow.C6
C7 = $currentRow.C7
C8 = [int]$currentRow.C8
C9 = $currentRow.C9
}
}
}
$csvOutput | Select-Object C1,C2,C3,C4,C5,C6,C7,C8,C9 |
Export-Csv -Path 'C:\Users\UserName\Documents\output.csv' -NoTypeInformation

[#3]

Excellent  thank you so much, and to remove the header on the output? @Kevin_Morgan 

[#4]

@Juan Pablo Gallardo 

Replace the last two lines with below code to remove headers

$csvOutput | Select-Object C1,C2,C3,C4,C5,C6,C7,C8,C9 |
ConvertTo-Csv -NoTypeInformation | # Convert to CSV string data without the type metadata
Select-Object -Skip 1 | # Trim header row, leaving only data columns
Set-Content -Path "C:\Users\UserName\Documents\output.csv"

Complete script :

$filepath = 'C:\Users\UserName\Documents\input.csv';
$csvInput = Import-CSV $filepath -Header C1,C2,C3,C4,C5,C6,C7,C8,C9
$csvOutput = @();
$csvInput | Foreach-Object{ 
$currentRow = $_;
$existingRow = $csvOutput | Where-Object { $_.C4 -eq $currentRow.C4}
if($existingRow -ne $null) {
$existingRow.C8 = $existingRow.C8 + [int]$currentRow.C8; 
} else { 
$csvOutput += New-Object -TypeName PSCustomObject -Property @{
C1 = $currentRow.C1
C2 = $currentRow.C2
C3 = $currentRow.C3
C4 = $currentRow.C4
C5 = $currentRow.C5
C6 = $currentRow.C6
C7 = $currentRow.C7
C8 = [int]$currentRow.C8
C9 = $currentRow.C9
}
}
}

$csvOutput | Select-Object C1,C2,C3,C4,C5,C6,C7,C8,C9 |
ConvertTo-Csv -NoTypeInformation | # Convert to CSV string data without the type metadata
Select-Object -Skip 1 | # Trim header row, leaving only data columns
Set-Content -Path "C:\Users\UserName\Documents\output.csv"

[#5]

Amazing thank you so much @Kevin_Morgan 

[#6]

And if I have a restriction like productX can not sum more than for example 50 and if it does then split the line? @Kevin_Morgan 

[#7]

Just noticed that this script does not work correctly because it does not take into account that the sites might chane and so is summarizing regardless of the site change. @Kevin_Morgan