- Powershell
- Windows Batch
- Scripting Languages
Modify Powershell script to report separate lines for a specific condition
I would like to change the attached script to match the Desired Output.
When an "Item" which reports a "Part P on G",
and the same "Item" is found which shares the same "Code", then the "Item" and "Qty" which is reporting the "Part P on G"
must be reported on it's own line, as shown in the "DesiredOutput" file.
For example in the Current Output file you will see:
3 744 12-12121 Desc2 8 0 1 2020/OC/03=744
But the Desired Output must show it separately like this.
3 672 12-12121 Desc2 8 0 0 2020/OC/03=672
4 72 12-12121 Desc2 0 0 1 2020/OC/03=72
What change needs to be made to the script tp report it this way?
OldScript.ps1
Source.in
CurrentOutput.out
DesiredOutput.out
When an "Item" which reports a "Part P on G",
and the same "Item" is found which shares the same "Code", then the "Item" and "Qty" which is reporting the "Part P on G"
must be reported on it's own line, as shown in the "DesiredOutput" file.
For example in the Current Output file you will see:
3 744 12-12121 Desc2 8 0 1 2020/OC/03=744
But the Desired Output must show it separately like this.
3 672 12-12121 Desc2 8 0 0 2020/OC/03=672
4 72 12-12121 Desc2 0 0 1 2020/OC/03=72
What change needs to be made to the script tp report it this way?
OldScript.ps1
Source.in
CurrentOutput.out
DesiredOutput.out
Here you go:
$inFile = 'C:\Source.in'
$outFile = 'C:\Output.out'
$mailArgs = @{
To = 'someone@domain.com'
From = 'someone.else@domain.com'
Subject = 'Output report'
SmtpServer = 'smtp.domain.com'
BodyAsHtml = $true
}
$htmlHead = @'
<style>
table {border-width:1px; border-style:solid; border-color:black;}
th {border-width:1px; border-style:solid; border-color:black; padding:1px;}
td {border-width:1px; border-style:solid; border-color:black; padding:1px;}
</style>
'@
$totalProperties = @(
'Tot PC Qty'
'Code 1 Qty'
'Code 2 Qty'
'Code 3 Qty'
'Full P'
'Part P'
'Part P on G'
'Stnd PU'
'C PU'
'Extra C PU'
'Extra Stnd PU'
)
$reportTotalProperties = @(
'Stnd PU'
'C PU'
'Extra C PU'
'Extra Stnd PU'
)
$removeProperties = @(
'Ref# / Imp #'
'Enter Date'
'Tantmt #'
'Out From'
'Out To'
'L By'
'Going'
'Sight # /Control'
'Code 1 ED'
'Code 2 ED'
'Code 3 EDate'
# 'Tot PC Qty'
'Code 1 Qty'
'Code 2'
'Code 2 Qty'
'Code 3'
'Code 3 Qty'
'Stnd PU'
'C PU'
'Extra C PU'
'Extra Stnd PU'
)
$groupProperties = @(
'Item'
'Code 1'
'Code 2'
'Code 3'
'Part P on G'
)
$orderProperties = @(
'Tot PC Qty'
# 'Sight # /Control'
'Item'
'Docu'
'Full P'
'Part P'
'Part P on G'
# 'Stnd PU'
# 'C PU'
# 'Extra C PU'
# 'Extra Stnd PU'
'Code 1' ## Will contain the merged Code properties
# 'Notes'
)
$renameProperties = @{
# 'Sight # /Control' = 'Cnt#'
'Tot PC Qty' = 'Qty'
'Code 1' = 'Code'
}
$header = Get-Content -Path $inFile -TotalCount 2 | ConvertFrom-Csv
$ref = $header[0].'Ref# / Imp #'
$date = $header[0].'Enter Date'.Split(' ')[0]
$tantmt = $header[0].'Tantmt #'
$originalProperties = $header[0].psobject.Properties | Select-Object -ExpandProperty Name
$data = Import-Csv -Path $inFile
$data = $data | Group-Object -Property $groupProperties | ForEach-Object {
If ($_.Count -eq 1) {
$_.Group
} Else {
$subtotal = $_.Group[0]
ForEach ($prop in ($originalProperties | Where-Object {$totalProperties -contains $_})) {
$subtotal.$prop = ($_.Group | Measure-Object -Property $prop -Sum).Sum
}
If ($originalProperties -contains 'Notes') {
$subtotal.Notes = ($_.Group | Select-Object -ExpandProperty Notes) -join ', '
}
$subtotal
}
}
$totalAll = @{}
ForEach ($prop in ($originalProperties | Where-Object {$totalProperties -contains $_})) {
$totalAll[$prop] = ($data | Measure-Object -Property $prop -Sum).Sum
}
ForEach ($row in $data) {
$codes = @()
If ($row.'Code 1') {$codes += "$($row.'Code 1')=$($row.'Code 1 Qty')"}
If ($row.'Code 2') {$codes += "$($row.'Code 2')=$($row.'Code 2 Qty')"}
If ($row.'Code 3') {$codes += "$($row.'Code 3')=$($row.'Code 3 Qty')"}
If ($codes) {$row.'Code 1' = $codes -join ', '}
}
$properties = @('Line#') + $orderProperties + ($originalProperties | Where-Object {($removeProperties -notcontains $_) -and ($orderProperties -notcontains $_)})
$separator = '' | Select-Object -Property $properties
$total = '' | Select-Object -Property $properties
$total.($properties[0]) = 'Totals:'
ForEach ($prop in ($properties | Where-Object {$totalProperties -contains $_})) {
$total.$prop = $totalAll[$prop]
}
$totalP = 0
$reportTotalProperties | ForEach-Object {$totalP += $totalAll[$_]}
$i = 1
$summary = $data | Select-Object -Property $properties | ForEach-Object {$_.'Line#' = $i++; $_}
$summary = $summary + $separator + $total
$newProperties = $properties
ForEach ($key in $renameProperties.Keys) {
$newProperties = $newProperties -replace "^$($key)$", $renameProperties[$key]
}
$summary = $summary | ConvertTo-Csv -NoTypeInformation | Select-Object -Skip 1 | ConvertFrom-Csv -Header $newProperties
$newProperties | ForEach-Object {
$summary[-2].$_ = '=' * $_.Length
}
@"
General Report
text here..
Ref#: $($ref)
Date: $($date)
Tantmt#: $($tantmt)
text here..
$($summary | Format-Table -Property * -AutoSize | Out-String -Width 1024)
$(($reportTotalProperties | ForEach-Object {"Total $($_): $($totalAll[$_])"}) -join "`r`n")
Total Pallets: $($totalP)
text here..
Signature: ________________________
Date: ________________________
"@ | Set-Content -Path $outFile
$htmlArgs = @{
PreContent = "<H3>Ref#: $($ref)</H3><H3>Date: $($date)</H3><H3>Tantmt#: $($tantmt)</H3>"
PostContent = "<H3>$(($reportTotalProperties | ForEach-Object {"Total $($_): $($totalAll[$_])"}) -join '<br />')<br />Total Pallets: $($totalP)</H3>"
}
$body = ($summary | ConvertTo-Html -Head $htmlHead @htmlArgs) -join "`r`n"
# Send-MailMessage @mailArgs -Body $body -Attachments $outFile
Slight enhancement: the integer columns are now right-aligned.
$inFile = 'C:\Source.in'
$outFile = 'C:\Output.out'
$mailArgs = @{
To = 'someone@domain.com'
From = 'someone.else@domain.com'
Subject = 'Output report'
SmtpServer = 'smtp.domain.com'
BodyAsHtml = $true
}
$htmlHead = @'
<style>
table {border-width:1px; border-style:solid; border-color:black;}
th {border-width:1px; border-style:solid; border-color:black; padding:1px;}
td {border-width:1px; border-style:solid; border-color:black; padding:1px;}
td:nth-child(1) {text-align: right;} /* Line # */
td:nth-child(2) {text-align: right;} /* Qty */
td:nth-child(5) {text-align: right;} /* Full P */
td:nth-child(6) {text-align: right;} /* Part P */
td:nth-child(7) {text-align: right;} /* Part P on G */
</style>
'@
$totalProperties = @(
'Tot PC Qty'
'Code 1 Qty'
'Code 2 Qty'
'Code 3 Qty'
'Full P'
'Part P'
'Part P on G'
'Stnd PU'
'C PU'
'Extra C PU'
'Extra Stnd PU'
)
$reportTotalProperties = @(
'Stnd PU'
'C PU'
'Extra C PU'
'Extra Stnd PU'
)
$removeProperties = @(
'Ref# / Imp #'
'Enter Date'
'Tantmt #'
'Out From'
'Out To'
'L By'
'Going'
'Sight # /Control'
'Code 1 ED'
'Code 2 ED'
'Code 3 EDate'
# 'Tot PC Qty'
'Code 1 Qty'
'Code 2'
'Code 2 Qty'
'Code 3'
'Code 3 Qty'
'Stnd PU'
'C PU'
'Extra C PU'
'Extra Stnd PU'
)
$groupProperties = @(
'Item'
'Code 1'
'Code 2'
'Code 3'
'Part P on G'
)
$orderProperties = @(
'Tot PC Qty'
# 'Sight # /Control'
'Item'
'Docu'
'Full P'
'Part P'
'Part P on G'
# 'Stnd PU'
# 'C PU'
# 'Extra C PU'
# 'Extra Stnd PU'
'Code 1' # Will contain the merged Code properties
# 'Notes'
)
$renameProperties = @{
# 'Sight # /Control' = 'Cnt#'
'Tot PC Qty' = 'Qty'
'Code 1' = 'Code'
}
$integerProperties = @( # So that numbers will be right-aligned; must be the renamed proeprties!
'Line#' # Column will be added automatically
'Qty'
'Full P'
'Part P'
'Part P on G'
)
$header = Get-Content -Path $inFile -TotalCount 2 | ConvertFrom-Csv
$ref = $header[0].'Ref# / Imp #'
$date = $header[0].'Enter Date'.Split(' ')[0]
$tantmt = $header[0].'Tantmt #'
$originalProperties = $header[0].psobject.Properties | Select-Object -ExpandProperty Name
$data = Import-Csv -Path $inFile
$data = $data | Group-Object -Property $groupProperties | ForEach-Object {
If ($_.Count -eq 1) {
$_.Group
} Else {
$subtotal = $_.Group[0]
ForEach ($prop in ($originalProperties | Where-Object {$totalProperties -contains $_})) {
$subtotal.$prop = ($_.Group | Measure-Object -Property $prop -Sum).Sum
}
If ($originalProperties -contains 'Notes') {
$subtotal.Notes = ($_.Group | Select-Object -ExpandProperty Notes) -join ', '
}
$subtotal
}
}
$totalAll = @{}
ForEach ($prop in ($originalProperties | Where-Object {$totalProperties -contains $_})) {
$totalAll[$prop] = ($data | Measure-Object -Property $prop -Sum).Sum
}
ForEach ($row in $data) {
$codes = @()
If ($row.'Code 1') {$codes += "$($row.'Code 1')=$($row.'Code 1 Qty')"}
If ($row.'Code 2') {$codes += "$($row.'Code 2')=$($row.'Code 2 Qty')"}
If ($row.'Code 3') {$codes += "$($row.'Code 3')=$($row.'Code 3 Qty')"}
If ($codes) {$row.'Code 1' = $codes -join ', '}
}
$properties = @('Line#') + $orderProperties + ($originalProperties | Where-Object {($removeProperties -notcontains $_) -and ($orderProperties -notcontains $_)})
$separator = '' | Select-Object -Property $properties
$total = '' | Select-Object -Property $properties
$total.($properties[0]) = 'Totals:'
ForEach ($prop in ($properties | Where-Object {$totalProperties -contains $_})) {
$total.$prop = $totalAll[$prop]
}
$totalP = 0
$reportTotalProperties | ForEach-Object {$totalP += $totalAll[$_]}
$i = 1
$summary = $data | Select-Object -Property $properties | ForEach-Object {$_.'Line#' = $i++; $_}
$summary = $summary + $separator + $total
$newProperties = $properties
ForEach ($key in $renameProperties.Keys) {
$newProperties = $newProperties -replace "^$($key)$", $renameProperties[$key]
}
$summary = $summary | ConvertTo-Csv -NoTypeInformation | Select-Object -Skip 1 | ConvertFrom-Csv -Header $newProperties
ForEach ($row in $summary) {
ForEach ($prop in $integerProperties) {
$row.$prop = Try {[int]$row.$prop} Catch {$row.$prop}
}
}
$newProperties | ForEach-Object {
$summary[-2].$_ = '=' * $_.Length
}
@"
General Report
text here..
Ref#: $($ref)
Date: $($date)
Tantmt#: $($tantmt)
text here..
$($summary | Format-Table -Property * -AutoSize | Out-String -Width 1024)
$(($reportTotalProperties | ForEach-Object {"Total $($_): $($totalAll[$_])"}) -join "`r`n")
Total Pallets: $($totalP)
text here..
Signature: ________________________
Date: ________________________
"@ | Set-Content -Path $outFile
$htmlArgs = @{
PreContent = "<H3>Ref#: $($ref)</H3><H3>Date: $($date)</H3><H3>Tantmt#: $($tantmt)</H3>"
PostContent = "<H3>$(($reportTotalProperties | ForEach-Object {"Total $($_): $($totalAll[$_])"}) -join '<br />')<br />Total Pallets: $($totalP)</H3>"
}
$body = ($summary | ConvertTo-Html -Head $htmlHead @htmlArgs) -join "`r`n"
# Send-MailMessage @mailArgs -Body $body -Attachments $outFile
Explore More ContentExplore courses, solutions, and other research materials related to this topic.
-
![]()
received a Solution
Modify powershell script to show each line of data from source but add totals for specific columns -
![]()
wrote an Article
Measuring Server's processing rate with a simple powershell command. -
![]()
created a Video
![]()
Exchange 2016 Installation - Part 15 - Creating your Databases with the GUI and PowerShell
-
Explore Cloud Class® ![]()
Certification: CFR CyberSec First Responder - Threat Detection and Response
