E=mc2
asked on
How can I modify this Powershell script to add the location?
Current script:
Sample Source Data:
I would like to add a Location to the output.
Currently, the output displays similar to this:
I would like the output to look like this, notice the Ship To information.
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:
The second line in the output data needs to pickup the Street and the Location after the first asterisk.
$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
}
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~
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
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
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~
The second line in the output data needs to pickup the Street and the Location after the first asterisk.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Works perfectly, thank you.
ASKER
Excellent.
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.
ASKER