Link to home
Start Free TrialLog in
Avatar of E=mc2
E=mc2Flag for Canada

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*00000020*0*R*>~
GS*PO*4161234567*4164567894*20100917*0018*0199*X*001111PQRS~
ST*150*12345678~
BEG*00*SA*8888888887**20140917~
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*12345678912345~
CTP**UCP*12~
PID*F****ITEM 1 DESCRIPTION~
PO4*10~
PO1**50*CA*20**UK*23456789123456~
PID*F****ITEM 2 DESCRIPTION~
PO1**30*CA*14.03**UK*34567891234567~
CTP**UCP*14.03~
PID*F****ITEM 3 DESCRIPTION~
PO4*15~
PO1**10*CA*20.50**UK*45678912345678~
CTP**UCP*20.50~
PID*F****DESCRIPTION FOR ITEM 4~
PO1**100*CA*18**UK*78945612345678~
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*000000021*0*R*>~
GS*PO*4167913211*4162475478*20100917*0019*0200*X*001111PQRS~
ST*150*2345678~
BEG*00*SA*8888888891**20130917~
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*10060383002975~
CTP**UCP*40.10~
PID*F****DESCRIPTION~
PO1**20*CA*10**UK*21234567897450~
PO1**30*CA*11**UK*45678912345678~
PO1**50*CA*22.05**UK*1234567895458~
CTP**UCP*22.38~
PO1**80*CA*20**UK*4567894578454~
SE*40*50501231~
GE*1*1234~
EFG*1*000014052~


Sample script:

$InputFile = "C:\Programs\originalfile.txt"
$OutputFile = "C:\Programs\newdatafile.txt"

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.
Avatar of SubSun
SubSun
Flag of India image

PO1**50*CA*20**UK*23456789123456~

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~
Avatar of E=mc2

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.
What about Qty is that 50 in above example?
Avatar of E=mc2

ASKER

Yes, in the above example, 50 is the quantity and the 20 is actually $20 which is the price.
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?

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~

Open in new window

Avatar of E=mc2

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
Avatar of SubSun
SubSun
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of E=mc2

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.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of E=mc2

ASKER

Works Excellent!
Avatar of E=mc2

ASKER

Excellent work1