Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

How can I modify the Powershell script to produce desired output?

Posted on 2014-10-01
5
150 Views
Last Modified: 2014-10-01
Input Data:
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~

Open in new window


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*88888888*IP*011111*UK*12345678945612~  
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
 }

Open in new window


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)

Open in new window

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.
0
Comment
Question by:100questions
5 Comments
 

Author Comment

by:100questions
ID: 40354826
The ouput is a text file, should it be in a snippet?
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40355069
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.
$data = get-content Q_28529073.txt | out-string

Open in new window


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
$data | select-string 'BEG[*][^*]*[*][^*]*[*]([^*]*)[*](.|\n)*?IEA' -allmatches

Open in new window

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\*.*?\*([^*]*)\*.*?\*([^*]*)\*.*\*([^~]*)~\s*PID\*.*\*([^~]*)~
0
 
LVL 40

Accepted Solution

by:
Subsun earned 500 total points
ID: 40355088
Try this modified code..
$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\*(\d{1,})\*\w{1,}\*(\d{1,}(\.\d{1,})?).*\*(\d{1,})~"){
     $count = $Matches[1]
		 $Price = $Matches[2]
		 $UPC = $Matches[4]
		 $total += [double]$count * [double]$Matches[2]
  }
  If($_ -match "PID\*"){
      $i++
      $Desc = $_ -Replace "PID\*\w\*{1,}(\d+\*{1,})?|<NL>|~"
      $line += "`r`nItem $i`: UPC: $UPC, Desc: $Desc, Qty: $count Price  `$$Price"
     }
   }
  }
 }

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

Open in new window

0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

This is a PowerShell web interface I use to manage some task as a network administrator. Clicking an action button on the left frame will display a form in the middle frame to input some data in textboxes, process this data in PowerShell and display…
Create and license users in Office 365 in bulk based on a CSV file. A step-by-step guide with PowerShell script examples.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

790 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