Solved

How can I get this Powershell script to output various calculations?

Posted on 2014-09-17
11
160 Views
Last Modified: 2014-09-18
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.
0
Comment
Question by:100questions
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
11 Comments
 
LVL 40

Expert Comment

by:Subsun
ID: 40328451
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~
0
 

Author Comment

by:100questions
ID: 40328483
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.
0
 
LVL 40

Expert Comment

by:Subsun
ID: 40328553
What about Qty is that 50 in above example?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:100questions
ID: 40328746
Yes, in the above example, 50 is the quantity and the 20 is actually $20 which is the price.
Thanks.
0
 
LVL 40

Expert Comment

by:Subsun
ID: 40328776
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

0
 

Author Comment

by:100questions
ID: 40328855
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.
0
 
LVL 40

Assisted Solution

by:Subsun
Subsun earned 500 total points
ID: 40329009
Try this and see if you get expected results..
$InputFile = "C:\Programs\originalfile.txt"
$OutputFile = "C:\Programs\newdatafile.txt"

Function ParseText ($OutputFile,$InputFile){
Begin{
		$line,$poline,$totline,$total,$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" | out-file $OutputFile -Encoding UTF8 -Append
				#reset the variables
				$line,$poline,$totline,$total,$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
}

Open in new window

0
 

Author Comment

by:100questions
ID: 40330213
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.
0
 
LVL 40

Accepted Solution

by:
Subsun earned 500 total points
ID: 40330262
Change line 18 to

"$poline$line`r`n$totline`r`n" | out-file $OutputFile -Encoding UTF8 -Append

Open in new window

0
 

Author Comment

by:100questions
ID: 40330297
Works Excellent!
0
 

Author Closing Comment

by:100questions
ID: 40330298
Excellent work1
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This script can help you clean up your user profile database by comparing profiles to Active Directory users in a particular OU, and removing the profiles that don't match.
Previously, on our Nano Server Deployment series, we've created a new nano server image and deployed it on a physical server in part 2. Now we will go through configuration.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question