Solved

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

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

Is Your AD Toolbox Looking More Like a Toybox?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Permission on folders 6 20
WPF Select Items 3 24
need assistance with a VBscript 3 32
export the mailbox data to .pst from office365 mailbox 8 51
In this previous article (https://oddytee.wordpress.com/2016/05/05/provision-new-office-365-user-and-mailbox-from-exchange-hybrid-via-powershell/), we made basic license assignments to users in O365. When I say basic, the method is the simplest way …
A recent project that involved parsing Tableau Desktop and Server log files to extract reusable user queries for use in other systems. I chose to use PowerShell to gather the data, and SharePoint to present it...
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

685 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