Solved

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

Posted on 2014-09-17
11
158 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
  • 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
Is Your AD Toolbox Looking More Like a Toybox?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

 

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

Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

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

This script checks a path to see if a folder exists. If the folder does exist you will get output "The folder has previously been created. No action taken" If not it will create the folder. Then adds one user modify permission to the folder. It …
The following article is intended as a guide to using PowerShell as a more versatile and reliable form of application detection in SCCM.
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

816 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now