Solved

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

Posted on 2014-10-01
5
148 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

Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

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

How to sign a powershell script so you can prevent tampering, and only allow users to run authorised Powershell scripts
This script can help you clean up your user profile database by comparing profiles to Active Directory users in a particular OU, and removing the profiles that don't match.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

914 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