Solved

Modification of the Powershell script

Posted on 2014-09-26
2
152 Views
Last Modified: 2014-09-29
This is a continuation of the recent requests for Powershell script changes

The lines which are different in the input data are as follows:

G20*N*20140902*98X123456~
(The 98X123456 is the PO in this case)

G86*100*CA*10.05*123456789456~
G87*DESC 1~
G87*15/9OZ~


The script I am using is similar to this:

$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 "(G20\*)|(G87\*)|(G86\*)|(AEI\*)"}
       }
 Process{
       $Data | % {
       #Check the end of data set
             If ($_ -match "AEI\*"){
                   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 "G20\*" -and $_ -match "(\w{2}\*)(\d{3,10})"){
                   $poline = "PONumber: $($Matches[2])"
             }
             #Collect  G87 details and following G87 Description
             If($_ -match "(G86\*{1,})(\d{1,})\*(\w{1,})\*(\d{1,}(\.\d{1,})?)\*{1,}(\w{1,}|\d{1,})"){
                   $i++
                   $total += [double]$Matches[2] * [double]$Matches[4]
                   $Desc = ($Data[([Array]::IndexOf($Data,$_)+1)] -split "\*{1,}|~")[1]
                   [String[]]$line += "`r`nItem $i`: UPC: $($Matches[6]), Desc: $Desc, Qty: $($Matches[2]) Price  `$$($Matches[4])"
             }
       }
  }
 }

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


The type of output I am looking for should be something similar to this:

PONumber: 999999
   Item 1: UPC: 12345678912345, Desc: DESC 1 , 15/9OZ, Qty: 100 Price: $10.05
   Item 2: UPC: 23456789123452, Desc: DESC 2 , 16/9OZ, Qty: 329 Price: $1.00
   Item 3: UPC: 98765432198765, Desc: DESC 3, 1/9OZ, Qty: 20 Price $2.00
   Total: $Calculated total

  PONumber: 999985
   Item 1: UPC: 12345678912345, Desc: information here, Qty: 250 Price: $10.00
   Item 2: UPC: 23456789123452, Desc: information here, Qty: 200 Price: $1.00
   Item 3: UPC: 98765432198765, Desc: information here, Qty: 100 Price $2.00
   Total: $Calculated total

Currently when I run the script it produces a blank file.

What modification do I need to make in order to allow this to work properly?
0
Comment
Question by:100questions
2 Comments
 

Author Comment

by:100questions
ID: 40346789
@subsun - Do you have any suggestions for this?  Thanks,
0
 
LVL 40

Accepted Solution

by:
footech earned 500 total points
ID: 40347037
Try changing these two lines as follows:
If($_ -match "G20\*" -and $_ -match "(?:\w{1,2}\*)(?:[0-9]{8}\*)([0-9a-z]{3,10})"){
$poline = "PONumber: $($Matches[1])"

Open in new window


Here's a suggestion.  Go through all of your data and work out the variations that can occur.  Variations can include:
-placement of a desired portion within a line
-what characters are surrounding the portion
-how the portion itself can vary, like
   -what kind of characters it can include (e.g. numbers only, letters and numbers, etc.)
   -the length of the portion
All these things are needed to come up with a regex pattern that will match your data under various circumstances.  Otherwise you're stuck with modifying the regex pattern again and again each time something slightly different occurs.  What I posted above works for the sample you gave, but there's no guarantee that it'll work for another sample if it differs much.
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Set OWA language and time zone in Exchange for individuals, all users or per database.
Create and license users in Office 365 in bulk based on a CSV file. A step-by-step guide with PowerShell script examples.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

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