E=mc2
asked on
How can I change this Powershell script so that the price is added to the detail line?
Sample data:
AIS*00* *00* *AA*PPPPPP *11*4165555555 *999999*0001*P*00123*00000 020*0*R*>~
GS*PO*4161234567*416456789 4*20100917 *0018*0199 *X*001111P QRS~
ST*150*12345678~
BEG*00*SA*8888888887**2014 0917~
FOB*PB~
DTM*119*20131002~
DTM*004*20131008~
N9*AH*4802028592~
MSG*Message.~
MSG*Message~
MSG*Message~
N1*SU*Company*8*123456789~
N1*BT*Name*8*1234567891234 ~
N3*1 Company~
N4*LOC*BB*ABC DEF*CA~
N1*ST*D021 Loc*9*1234567891236~
N3*Street~
N4*Location*BB*ABC DEF*CA~
PO1**10*CA*12**UK*12345678 912345~
CTP**UCP*12~
PID*F****ITEM 1 DESCRIPTION~
PO4*10~
PO1**50*CA*20**UK*23456789 123456~
PID*F****ITEM 2 DESCRIPTION~
PO1**30*CA*14.03**UK*34567 891234567~
CTP**UCP*14.03~
PID*F****ITEM 3 DESCRIPTION~
PO4*15~
PO1**10*CA*20.50**UK*45678 912345678~
CTP**UCP*20.50~
PID*F****DESCRIPTION FOR ITEM 4~
PO1**100*CA*18**UK*7894561 2345678~
PID*F****THIS IS ITEM 5~
CTT*5~
SE*45*12345648~
GE*2*1234~
EFG*1*000025011~
AIS*00* *00* *AA*PPPPPP *11*4165555555 *999999*0002*P*00123*00000 0021*0*R*> ~
GS*PO*4167913211*416247547 8*20100917 *0019*0200 *X*001111P QRS~
ST*150*2345678~
BEG*00*SA*8888888891**2013 0917~
DTM*119*20131001~
DTM*004*20131002~
N9*AH*1234034034~
N1*ST*D072 LOC*9*2471916380072~
N3*2700 NAME~
N4*CITY*QQ*ABC DEF*CA~
PO1**2*CA*40.10**UK*100603 83002975~
CTP**UCP*40.10~
PID*F****DESCRIPTION~
PO1**20*CA*10**UK*21234567 897450~
PO1**30*CA*11**UK*45678912 345678~
PO1**50*CA*22.05**UK*12345 67895458~
CTP**UCP*22.38~
PO1**80*CA*20**UK*45678945 78454~
SE*40*50501231~
GE*1*1234~
EFG*1*000014052~
Script to modify:
$InputFile = "C:\Programs\originalfile. txt"
$OutputFile = "C:\Programs\newdatafile.t xt"
Function ParseText ($OutputFile,$InputFile){
Begin{
$line,$poline,$totline,$to tal,$i = $null,$null,$null,$null,0
Set-Content $OutputFile $null
$Data = Get-Content -Path $InputFile | ?{$_ -match "(SA\*)|(PID\*)|(PO1\*{2}) |(EFG\*)"}
}
Process{
$Data | % {
#Check the end of data set
If ($_ -match "EFG\*"){
If($poline -ne $null -and $total -ne $null){
$Totline = "Total: `$$Total"
Write-host "This is Total `$$total"
#Write the data collection to output file
"$poline$line`r`n$totline` r`n" | out-file $OutputFile -Encoding UTF8 -Append
#reset the variables
$line,$poline,$totline,$to tal,$i = $null,$null,$null,$null,0
}
}
#Collect the PO Number
If($_ -match "(SA\*)(\d{0,10})"){
$poline = "PONumber: $($Matches[2])"
}
#Collect PO1 details and following PID Discription
If($_ -match "(PO1\*{2})(\d{1,})\*(\w{1 ,})\*(\d{1 ,}(\.\d{1, })?)\*{2}( \w{1,})\*( \d{1,})"){
$i++
$total += [double]$Matches[2] * [double]$Matches[4]
$Desc = ($Data[([Array]::IndexOf($ Data,$_)+1 )] -split "\*\*\*\*|~")[1]
[String[]]$line += "`r`nItem $i`: UPC: $($Matches[7]), Desc: $Desc, Qty: $($Matches[2])"
}
}
}
}
If(Test-Path $InputFile){
ParseText $OutputFile $InputFile
}
Desired output should include the Line Item line with the price added as follows:
PONumber: 1234561234
Item 1: UPC: 12345678912345, Desc: information here, Qty: 248 Price: $10.00
Item 2: UPC: 23456789123452, Desc: information here, Qty: 329 Price: $1.00
Item 3: UPC: 98765432198765, Desc: information here, Qty: 20 Price $2.00
Total: $Calculated total
NB - If a price does not exist then leave the Price blank with no $ value.
Thanks,
AIS*00* *00* *AA*PPPPPP *11*4165555555 *999999*0001*P*00123*00000
GS*PO*4161234567*416456789
ST*150*12345678~
BEG*00*SA*8888888887**2014
FOB*PB~
DTM*119*20131002~
DTM*004*20131008~
N9*AH*4802028592~
MSG*Message.~
MSG*Message~
MSG*Message~
N1*SU*Company*8*123456789~
N1*BT*Name*8*1234567891234
N3*1 Company~
N4*LOC*BB*ABC DEF*CA~
N1*ST*D021 Loc*9*1234567891236~
N3*Street~
N4*Location*BB*ABC DEF*CA~
PO1**10*CA*12**UK*12345678
CTP**UCP*12~
PID*F****ITEM 1 DESCRIPTION~
PO4*10~
PO1**50*CA*20**UK*23456789
PID*F****ITEM 2 DESCRIPTION~
PO1**30*CA*14.03**UK*34567
CTP**UCP*14.03~
PID*F****ITEM 3 DESCRIPTION~
PO4*15~
PO1**10*CA*20.50**UK*45678
CTP**UCP*20.50~
PID*F****DESCRIPTION FOR ITEM 4~
PO1**100*CA*18**UK*7894561
PID*F****THIS IS ITEM 5~
CTT*5~
SE*45*12345648~
GE*2*1234~
EFG*1*000025011~
AIS*00* *00* *AA*PPPPPP *11*4165555555 *999999*0002*P*00123*00000
GS*PO*4167913211*416247547
ST*150*2345678~
BEG*00*SA*8888888891**2013
DTM*119*20131001~
DTM*004*20131002~
N9*AH*1234034034~
N1*ST*D072 LOC*9*2471916380072~
N3*2700 NAME~
N4*CITY*QQ*ABC DEF*CA~
PO1**2*CA*40.10**UK*100603
CTP**UCP*40.10~
PID*F****DESCRIPTION~
PO1**20*CA*10**UK*21234567
PO1**30*CA*11**UK*45678912
PO1**50*CA*22.05**UK*12345
CTP**UCP*22.38~
PO1**80*CA*20**UK*45678945
SE*40*50501231~
GE*1*1234~
EFG*1*000014052~
Script to modify:
$InputFile = "C:\Programs\originalfile.
$OutputFile = "C:\Programs\newdatafile.t
Function ParseText ($OutputFile,$InputFile){
Begin{
$line,$poline,$totline,$to
Set-Content $OutputFile $null
$Data = Get-Content -Path $InputFile | ?{$_ -match "(SA\*)|(PID\*)|(PO1\*{2})
}
Process{
$Data | % {
#Check the end of data set
If ($_ -match "EFG\*"){
If($poline -ne $null -and $total -ne $null){
$Totline = "Total: `$$Total"
Write-host "This is Total `$$total"
#Write the data collection to output file
"$poline$line`r`n$totline`
#reset the variables
$line,$poline,$totline,$to
}
}
#Collect the PO Number
If($_ -match "(SA\*)(\d{0,10})"){
$poline = "PONumber: $($Matches[2])"
}
#Collect PO1 details and following PID Discription
If($_ -match "(PO1\*{2})(\d{1,})\*(\w{1
$i++
$total += [double]$Matches[2] * [double]$Matches[4]
$Desc = ($Data[([Array]::IndexOf($
[String[]]$line += "`r`nItem $i`: UPC: $($Matches[7]), Desc: $Desc, Qty: $($Matches[2])"
}
}
}
}
If(Test-Path $InputFile){
ParseText $OutputFile $InputFile
}
Desired output should include the Line Item line with the price added as follows:
PONumber: 1234561234
Item 1: UPC: 12345678912345, Desc: information here, Qty: 248 Price: $10.00
Item 2: UPC: 23456789123452, Desc: information here, Qty: 329 Price: $1.00
Item 3: UPC: 98765432198765, Desc: information here, Qty: 20 Price $2.00
Total: $Calculated total
NB - If a price does not exist then leave the Price blank with no $ value.
Thanks,
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER