Forum Discussion
Sort and add
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
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
6 Replies
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' -NoTypeInformationExcellent thank you so much, and to remove the header on the output? Kevin_Morgan
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"
