Solved

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

Posted on 2014-09-17
11
157 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
 

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
Are your AD admin tools letting you down?

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: 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
powershell and sql server - alerting 7 77
Delete email from user mailbox 23 76
Powershell console output Parsing 5 31
change the windows script file to BAT 10 30
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 …
Microsoft Windows Server Update Service (WSUS) is free for everyone, but it lacks of some desirable features like send an e-mail to the administrator with the status of all computers on the WSUS server. This article is based on my PowerShell script …
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 is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…

920 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

17 Experts available now in Live!

Get 1:1 Help Now