SOLVED

Sort and add

Copper Contributor

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

 

 

 

 

6 Replies
best response confirmed by Juan Pablo Gallardo (Copper Contributor)
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

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

@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"

 

Amazing thank you so much @Kevin Morgan 

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 

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 

1 best response

Accepted Solutions
best response confirmed by Juan Pablo Gallardo (Copper Contributor)
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

View solution in original post