Forum Discussion
Solved
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 produ...
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
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
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"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
