We help IT Professionals succeed at work.

Modify Powershell script to report separate lines for a specific condition

Medium Priority
54 Views
1 Endorsement
Last Modified: 2020-03-02
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
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2019
Most Valuable Expert 2018

Commented:
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

Open in new window

CERTIFIED EXPERT
Most Valuable Expert 2019
Most Valuable Expert 2018
Commented:
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

Open in new window

Michael B. SmithManaging Consultant
CERTIFIED EXPERT

Commented:
*snort*

At this point you should be using RPG or COBOL. :-)

Author

Commented:
@oBdA
Excellent work as usual.  Many thanks.