E=mc2
asked on
How can I modify the Powershell script to produce desired output?
Input Data:
NB: The above data can have several data sets starting with ISA and ending with IEA.
In the above script, lines to focus on are:
BEG*00*SA*099999**20140613 ~ (099999 is the PO)
...
PO1*1*10*CA*5.05**VN*55555 *AA*888888 88*IP*0111 11*UK*1234 5678945612 ~
PID*F****DESC 1~
(10 is the Qty, 5.05 is the price, UPC is 12345678945612, and Description is DESC 1)
Script:
Desired type of output, for as many PO's that it finds between each set of ISA and IEA in the input file.
What modification needs to be made to the script?
Thank you.
ISA*00* *00* *AA*ABCDEF *99*9999999999 *123456*9999*P*12345*1234567891*9*Q*>~
GS*PO*ABCDEF*9999999999*20140613*9999*99*Y*999999ABCD~
ST*850*0001~
BEG*00*SA*099999**20140613~
ITD*20*8*1**99**99~
DTM*001*20140704~
N9*AH*099999~
MSG*TERMS:~
MSG*TERMS~
N1*AB*COMPANY*5*5555555555555~
N1*TT*COMPANY*5*5555555555555~
N3*ADDRESS~
N4*LOC*ZZ*ABC DEF~
PER*AA*AAA*AA*(123) 456-12345~
N1*ST*NAME*8*1234567891234~
N3*NO AND STREET*LOCATION~
N4*LOCATION*ZZ*ABC DEF~
PO1*1*10*CA*5.05**VN*55555*AA*88888888*IP*011111*UK*12345678945612~
PID*F****DESC 1~
PO4*11~
PO1*2*5*CA*20.50**VN*44444*BB*77777777*IP*022222*UK*45678912345678~
PID*F****DESC 2~
PO4*11~
CTT*2*450~
AMT*AB*12345.1~
SE*00*0001~
GE*1*99~
IEA*1*999999999~
ISA*00* *00* *AA*ABCDEF *99*9999999999 *123456*9999*P*12345*1234567891*9*Q*>~
GS*PO*ABCDEF*9999999999*20140613*9999*99*Y*999999ABCD~
ST*850*0001~
BEG*00*SA*099998**20140613~
ITD*20*8*1**99**99~
DTM*001*20140704~
N9*AH*099999~
MSG*TERMS:~
MSG*TERMS~
N1*AB*COMPANY*5*5555555555555~
N1*TT*COMPANY*5*5555555555555~
N3*ADDRESS~
N4*LOC*ZZ*ABC DEF~
PER*AA*AAA*AA*(123) 456-12345~
N1*ST*NAME*8*1234567891234~
N3*NO AND STREET*LOCATION~
N4*LOCATION*ZZ*ABC DEF~
PO1*1*20*CA*15.05**VN*55555*AA*88888888*IP*011111*UK*12345678145612~
PID*F****DESC 1~
PO4*11~
PO1*2*50*CA*2.50**VN*44444*BB*77777777*IP*022222*UK*15678912345678~
PID*F****DESC 2~
PO4*11~
CTT*2*450~
AMT*AB*12345.1~
SE*00*0001~
GE*1*99~
IEA*1*999999999~
NB: The above data can have several data sets starting with ISA and ending with IEA.
In the above script, lines to focus on are:
BEG*00*SA*099999**20140613
...
PO1*1*10*CA*5.05**VN*55555
PID*F****DESC 1~
(10 is the Qty, 5.05 is the price, UPC is 12345678945612, and Description is DESC 1)
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 "(BEG\*)|(PO1\*)|(PID\*)|(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 "BEG\*" -and $_ -match "\*(\w{2}\*)(\d{3,10})"){
$poline = "PO Number: $($Matches[2])"
}
#Collect details
If($_ -match "(PO1\*{1,})(\d{1,})\*(\w{1,})\*(\d{1,}(\.\d{1,})?)\*{1,}(\w{1,}|\d{1,})\*{1,}(\d{1,})"){
$i++
$total += [double]$Matches[2] * [double]$Matches[4]
$Desc = ($Data[([Array]::IndexOf($Data,$_)+1)] -split "\*{1,}|~")[2]
[String[]]$line += "`r`nItem $i`: UPC: $($Matches[7]), Desc: $Desc, Qty: $($Matches[2]), Price: `$$($Matches[4])"
}
}
}
}
If(Test-Path $InputFile){
ParseText $OutputFile $InputFile
}
Desired type of output, for as many PO's that it finds between each set of ISA and IEA in the input file.
PONumber: 099999
Item 1: UPC: 12345678945612, Desc: Desc 1 Qty: 10 Price $5.05
Item 2: UPC: 45678912345678, Desc: Desc 2, Qty: 5 Price $20.50
Total: $ (Caculated Total goes here)
PONumber: 099998
Item 1: UPC: 12345678145612, Desc: Desc 1, Qty: 20 Price $15.05
Item 2: UPC: 15678912345678, Desc: Desc 2, Qty: 50 Price $2.50
Total: $ (Calculated total goes here)
and so forth for every PO and line that it finds in the original file What modification needs to be made to the script?
Thank you.
I wandered down a different solution path, but ran out of time. I'll post what I had written when I moved on to billable work (and life, in general)
1. If you create a multi-line string variable (see example below), you will be able to do your parsing with regular expressions.
2. The first pattern gets the PO sets, capturing both the zip code and the text between the "BEG" and the "IEA" lines.
BEG\*[^*]*\*[^*]*\*([^*]*) \*(.|\n)*? IEA
$matches[0] is the between text
$matches[1] is the zip code
$matches[2] is text that does not contain any useful data for your purposes
3. For each of the $matches sets (above), the between text can be parsed with the following pattern, capturing the number of items, unit cost, UPC, and description.
PO1\*.*?\*([^*]*)\*.*?\*([ ^*]*)\*.*\ *([^~]*)~\ s*PID\*.*\ *([^~]*)~
1. If you create a multi-line string variable (see example below), you will be able to do your parsing with regular expressions.
$data = get-content Q_28529073.txt | out-string
2. The first pattern gets the PO sets, capturing both the zip code and the text between the "BEG" and the "IEA" lines.
BEG\*[^*]*\*[^*]*\*([^*]*)
$data | select-string 'BEG[*][^*]*[*][^*]*[*]([^*]*)[*](.|\n)*?IEA' -allmatches
Each match set produces a $matches collection with three items.$matches[0] is the between text
$matches[1] is the zip code
$matches[2] is text that does not contain any useful data for your purposes
3. For each of the $matches sets (above), the between text can be parsed with the following pattern, capturing the number of items, unit cost, UPC, and description.
PO1\*.*?\*([^*]*)\*.*?\*([
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER