E=mc2
asked on
How can I get this Powershell script to output various calculations?
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~
Sample script:
$InputFile = "C:\Programs\originalfile. txt"
$OutputFile = "C:\Programs\newdatafile.t xt"
Function ParseText ($OutputFile,$InputFile){
Begin{
$poline,$totline,$total = $NULL,$NULL,$NULL
Set-Content $OutputFile $null
}
Process{
Get-Content -Path $InputFile | % {
If ($_ -match "AIS\*1"){
If ($poline -ne $null -and $total -ne $null){
$Totline = "Total: `$$Total"
write-host "This is Total `$$total"
"`n$poline `r`n$totline" | out-file $OutputFile -Encoding UTF8 -Append
$poline,$totline,$total = $NULL,$NULL,$NULL
}
}
If($_ -match "(SA\*)(\d{0,10})") {
$poline = "PONumber: $($Matches[2])"
}Elseif ($_ -match "PO1"){
$col=$_.split("*") ; $total += [double]$col[2] * [double]$col[4]
}
}
}
}
if (Test-Path $InputFile){
ParseText $OutputFile $InputFile
}
Desired Ouput in a new file:
PO Number: 8888888887 (this is the number next to the SA)
Item 1: UPC: (enter UPC code from the PO1 line), Desc: (insert Description from the PID line), Qty: (from the PO1 line)
Item 2: UPC: (enter UPC code from the PO1 line), Desc: (insert Description from the PID line), Qty: (from the PO1 line)
Item 3: UPC: (enter UPC code from the PO1 line), Desc: (insert Description from the PID line), Qty: (from the PO1 line)
etc
...add more items if there are more
Total: (the total calculation for each PO1 data it sees for this PO1 ie
PO Number: 8888888891 (this is the number next to the SA)
Item 1: UPC: (enter UPC code from the PO1 line), Desc: (insert Description from the PID line), Qty: (from the PO1 line)
Item 2: UPC: (enter UPC code from the PO1 line), Desc: (insert Description from the PID line), Qty: (from the PO1 line)
Item 3: UPC: (enter UPC code from the PO1 line), Desc: (insert Description from the PID line), Qty: (from the PO1 line)
etc
...add more items if there are more
Total: (the total $ calculation for each PO1 data it sees for this PO, ie qty 10 x $12.00, + 50 x 20 and so forth...)
etc..add more if applicable..
What modifications need to be made to the script in order for this to occur?
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~
Sample script:
$InputFile = "C:\Programs\originalfile.
$OutputFile = "C:\Programs\newdatafile.t
Function ParseText ($OutputFile,$InputFile){
Begin{
$poline,$totline,$total = $NULL,$NULL,$NULL
Set-Content $OutputFile $null
}
Process{
Get-Content -Path $InputFile | % {
If ($_ -match "AIS\*1"){
If ($poline -ne $null -and $total -ne $null){
$Totline = "Total: `$$Total"
write-host "This is Total `$$total"
"`n$poline `r`n$totline" | out-file $OutputFile -Encoding UTF8 -Append
$poline,$totline,$total = $NULL,$NULL,$NULL
}
}
If($_ -match "(SA\*)(\d{0,10})") {
$poline = "PONumber: $($Matches[2])"
}Elseif ($_ -match "PO1"){
$col=$_.split("*") ; $total += [double]$col[2] * [double]$col[4]
}
}
}
}
if (Test-Path $InputFile){
ParseText $OutputFile $InputFile
}
Desired Ouput in a new file:
PO Number: 8888888887 (this is the number next to the SA)
Item 1: UPC: (enter UPC code from the PO1 line), Desc: (insert Description from the PID line), Qty: (from the PO1 line)
Item 2: UPC: (enter UPC code from the PO1 line), Desc: (insert Description from the PID line), Qty: (from the PO1 line)
Item 3: UPC: (enter UPC code from the PO1 line), Desc: (insert Description from the PID line), Qty: (from the PO1 line)
etc
...add more items if there are more
Total: (the total calculation for each PO1 data it sees for this PO1 ie
PO Number: 8888888891 (this is the number next to the SA)
Item 1: UPC: (enter UPC code from the PO1 line), Desc: (insert Description from the PID line), Qty: (from the PO1 line)
Item 2: UPC: (enter UPC code from the PO1 line), Desc: (insert Description from the PID line), Qty: (from the PO1 line)
Item 3: UPC: (enter UPC code from the PO1 line), Desc: (insert Description from the PID line), Qty: (from the PO1 line)
etc
...add more items if there are more
Total: (the total $ calculation for each PO1 data it sees for this PO, ie qty 10 x $12.00, + 50 x 20 and so forth...)
etc..add more if applicable..
What modifications need to be made to the script in order for this to occur?
Thanks.
ASKER
The UPC number is the one that appears after the UK... 23456789123456 (in the case above).
Yes, all data ends with EFG, that's the end of the data for each PO.
Yes, all data ends with EFG, that's the end of the data for each PO.
What about Qty is that 50 in above example?
ASKER
Yes, in the above example, 50 is the quantity and the 20 is actually $20 which is the price.
Thanks.
Thanks.
I also have some confusion about PID description. As you can see in given sample, PID line comes after the PO1 line, also after some PO1 line there is no PID line follows.
In the given example, I would consider description 'ITEM 1 DESCRIPTION' is for '12345678912345' &
'ITEM 2 DESCRIPTION' is for 23456789123456
If there is no PID line follows the description will be empty.. is that correct?
In the given example, I would consider description 'ITEM 1 DESCRIPTION' is for '12345678912345' &
'ITEM 2 DESCRIPTION' is for 23456789123456
If there is no PID line follows the description will be empty.. is that correct?
PO1**10*CA*12**UK*12345678912345~
PID*F****ITEM 1 DESCRIPTION~
PO1**50*CA*20**UK*23456789123456~
PID*F****ITEM 2 DESCRIPTION~
PO1**30*CA*14.03**UK*34567891234567~
PID*F****ITEM 3 DESCRIPTION~
PO1**10*CA*20.50**UK*45678912345678~
PID*F****DESCRIPTION FOR ITEM 4~
PO1**100*CA*18**UK*78945612345678~
PID*F****THIS IS ITEM 5~
PO1**2*CA*40.10**UK*10060383002975~
PID*F****DESCRIPTION~
PO1**20*CA*10**UK*21234567897450~
PO1**30*CA*11**UK*45678912345678~
PO1**50*CA*22.05**UK*1234567895458~
PO1**80*CA*20**UK*4567894578454~
ASKER
Yes, sorry I should have mentioned. Where there is no PID after the PO1, please leave the description blank. The rest should be filled out however.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This works excellent! Thank you. Would it be much work for one last request, to leave a blank line after each Total line?
Thanks again, very good work.
Thanks again, very good work.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Works Excellent!
ASKER
Excellent work1
How do you identify the UPC code and the Qty from above line?
Also does all data set's end with following data pattern?
EFG*1*000025011~