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

asked on

How can I modify this Powershell script to add the location?

Current script:

$InputFile = "C:\Data\Original.txt"
$OutputFile = "C:\Data\New.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})|(IEA\*)"}
	}
Process{
	$Data | % {
	#Check the end of data set
		If ($_ -match "IEA\*"){
			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`r`n" | 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 = "PO Number: $($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]), Price: `$$($Matches[4])"
		}
	}
 }
}

If(Test-Path $InputFile){
 ParseText $OutputFile $InputFile
}

Open in new window



Sample Source Data:

  ISA*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~
  IEA*1*000025011~
  ISA*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~
  IEA*1*000014052~

Open in new window



I would like to add a Location to the output.

Currently, the output displays similar to this:

PONumber: 1234561234
 Item 1: UPC: 12345678912345, Desc: information here, Qty: 248 Price: $10.00
 Item 2: UPC: 23456789123452, Desc: information here, Qty: 329 Price: $1.00
 Item 3: UPC: 98765432198765, Desc: information here, Qty: 20 Price $2.00
 Total: $Calculated total

Open in new window


I would like the output to look like this, notice the Ship To information.

 PONumber: 1234561234
 Ship To:  Street, Location
 Item 1: UPC: 12345678912345, Desc: information here, Qty: 248 Price: $10.00
 Item 2: UPC: 23456789123452, Desc: information here, Qty: 329 Price: $1.00
 Item 3: UPC: 98765432198765, Desc: information here, Qty: 20 Price $2.00
 Total: $Calculated total

Open in new window


NB - The Ship To information comes from the 2nd instance of the N3 and the N4

For example,
In the first set of data between ISA and IEA, the 2nd instance of N3 and N4 looks like this:
  N3*Street~
  N4*Location*BB*ABC DEF*CA~

Open in new window


The second line in the output data needs to pickup the Street and the Location after the first asterisk.
Avatar of E=mc2
E=mc2
Flag of Canada image

ASKER

Any assistance would be appreciated.
ASKER CERTIFIED SOLUTION
Avatar of Robert Schutt
Robert Schutt
Flag of Netherlands 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

Works perfectly, thank you.
Avatar of E=mc2

ASKER

Excellent.
Avatar of E=mc2

ASKER

I posted another question which essentially takes the same code but instead of using the 2nd instance of the N3 and N4, it needs to use the first instance of it.