How can I modify this script to product a summary based on items

I would like to modify the script below so that the output is similar to the following.

$InputFile = "C:\folder\original.txt"
$OutputFile = "C:\folder\modified.txt"


Function ParseText ($OutputFile,$InputFile){
Begin{
 $line,$totline,$total,$i = $null,$null,$null,$null,0
 Set-Content $OutputFile $null
 $Data = Get-Content -Path $InputFile | ?{$_ -match "(BEG\*)|(PO1\*)|(PID\*)|(SE\*)|(IEA\*)|(ISA\*)|N1"}
 }
 Process{
  $Data | % {
  #Check the end of data set
  If ($_ -match "N1"){
    $strArray = $_.split("*")
    $currString = $strArray[2] -replace '\s+', ' '
    $splitString = $currString.split(" ")
    $stringCnt = $splitString.count - 1
    $i = 0
    for ($i = 0; $i -le $stringCnt - 1; $i++)
    {
        $companyName += $splitString[$i] + " "
    }
  
  }
  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 "ISA\*" -and $_ -match "\*(\w{2}\**)(\d{3,10})"){
     $poline = ""
  }
  #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`nUPC: $UPC,  Qty: $count Price  `$$Price"
     }
   }
  }
 }

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

Open in new window



Desired type of Output:

UPC: 123456123123,  Total Qty: 200,  Price per UPC  $1.00, Total $ Value: $ 200.00
UPC: 123456123546,  Total Qty: 300,  Price per UPC  $3.00, Total $ Value: $ 900.00
UPC: 122313213213,  Total Qty: 100,  Price per UPC  $0.50, Total $ Value: $  50.00
Total PO Value: $1150.00
100questionsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raheman M. AbdulSenior Infrastructure Support Analyst & Systems DeveloperCommented:
Attach the sample input file and the current output file
0
100questionsAuthor Commented:
Here is a sample input file:

ISA*00*0000000000*00*0000000000*01*12345612378     *AA*ABCDEFGH       *123456*1234*P*00123*000000012*1*Q*>~
GS*PO*8888888888*ABCDEFGH*20150101*0123*9*X*001234~
ST*850*123456789~
BEG*00*SA*1234-2222222-1234**20150101*Q~
REF*IA*1234567*COMPANY~
REF*DP*012~
FOB*DF*OR*CITY~
CSH*Y~
ITD*01*9*****99~
DTM*037*20150101~
DTM*038*20150101~
TD5*B*92*SOMETHING~
N1*BY*COMPANY*11*0123~
N3*ADDRESS1~
N4*TOWN*CITY*12345*SOMEWHERE~
PO1*1*2*CA*1.01*UM*AB*123456456*AB*desc*UP*123456123456~
CTP*AB*ABC*1.00********9~
PID*F*01***desc2~
PO4*12~
PO1*2*5*CA*1.00*UM*AB*123456444*AB*desc*UP*999999111111~
CTP*AB*ABC*0.50********9~
PID*F*02***desc2b~
PO4*5~
CTT*1*99~
SE*11*123154641~
ST*850*88888888~
BEG*00*SA*1234-1111111-2345**20150101*Q~
REF*IA*1234567*COMPANY~
REF*DP*013~
FOB*DF*OR*CITY~
CSH*Y~
ITD*01*0*****00~
DTM*037*20150101~
DTM*038*20150101~
TD5*X*11*ABCD*M~
N1*BY*COMPANY*11*0123~
N3*ADDRESS1~
N4*TOWN*CITY*12345*SOMEWHERE~
PO1*1*1*CA*1.70*UM*AB*123456654*AB*descabc*UP*123456123456~
CTP*RS*ABC*0.75********1~
PID*F*08***desc~
PO4*12~
PO1*2*5*CA*5.04*UM*AB*456789456*AB*descddd*UP*999999111111~
CTP*AB*ABC*1.01********1~
PID*F*08***desc~
PO4*12~
PO1*3*2*CA*0.50*UM*AB*456123456*AB*descbcd*UP*999994565444~
CTP*AB*ABC*1.11********1~
PID*F*11***desc~
PO4*5~
CTT*1*19~
SE*01*123456789~
GE*01*1~
IEA*1*123456789~
~
0
100questionsAuthor Commented:
Here is a sample output file, which needs to be changed:


UPC: 123456123456,  Qty: 2 Price  $1.01
UPC: 999999111111,  Qty: 5 Price  $1.00
UPC: 123456123456,  Qty: 1 Price  $1.70
UPC: 999999111111,  Qty: 5 Price  $5.04
UPC: 999994565444,  Qty: 2 Price  $0.50
Total: $34.92
0
ON-DEMAND: 10 Easy Ways to Lose a Password

Learn about the methods that hackers use to lift real, working credentials from even the most security-savvy employees in this on-demand webinar. We cover the importance of multi-factor authentication and how these solutions can better protect your business!

David Johnson, CD, MVPOwnerCommented:
$InputFile = "C:\folder\original.txt"
$OutputFile = "C:\folder\modified.txt"


Function ParseText ($OutputFile,$InputFile){
Begin{
 $line,$totline,$total,$i = $null,$null,$null,$null,0
 Set-Content $OutputFile $null
 $Data = Get-Content -Path $InputFile | ?{$_ -match "(BEG\*)|(PO1\*)|(PID\*)|(SE\*)|(IEA\*)|(ISA\*)|N1"}
 }
 Process{
  $Data | % {
  #Check the end of data set
  If ($_ -match "N1"){
    $strArray = $_.split("*")
    $currString = $strArray[2] -replace '\s+', ' '
    $splitString = $currString.split(" ")
    $stringCnt = $splitString.count - 1
    $i = 0
    for ($i = 0; $i -le $stringCnt - 1; $i++)
    {
        $companyName += $splitString[$i] + " "
    }
  
  }
  If ($_ -match "IEA\*"){
    If($poline -ne $null -and $total -ne $null){
     $Totline = "Total PO Value: `$$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 "ISA\*" -and $_ -match "\*(\w{2}\**)(\d{3,10})"){
     $poline = ""
  }
  #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>|~"
      $Subtotal =  [double] $price * [int] $count
      $strSubtotal = "{0:C2}" -f $Subtotal
      $line += "`r`nUPC: $UPC,"
      $line +='  Total Qty: '+ $count
      $line +='  Price per UPC  $' + $Price
      $line +=' Total $ Value: ' +  $strSubtotal
     }
   }
  }
 }

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

Open in new window


UPC: 123456123456,  Total Qty: 2  Price per UPC  $1.01 Total $ Value: $2.02
UPC: 999999111111,  Total Qty: 5  Price per UPC  $1.00 Total $ Value: $5.00
UPC: 123456123456,  Total Qty: 1  Price per UPC  $1.70 Total $ Value: $1.70
UPC: 999999111111,  Total Qty: 5  Price per UPC  $5.04 Total $ Value: $25.20
UPC: 999994565444,  Total Qty: 2  Price per UPC  $0.50 Total $ Value: $1.00
Total PO Value: $34.92
0
100questionsAuthor Commented:
Thanks David,

What I am hoping to achieve is an amalgamation of UPC's, such as this:
NB - My mistake. If multiple UPCs are listed, and they are the same, they should have the same price, therefore the output you have should really not occur, unless the each price is different.
So for instance the UPC ending in 456, should all be $1.01, and therefore should only show as a summary.  The UPC ending in 111 should be $1.00.  - -  IF the price is different, even though it is the same UPC then yes, it should output as a separate line, otherwise.

Therefore, the output should really look like this:


UPC: 123456123456,  Total Qty: 3  Price per UPC  $1.01 Total $ Value: $3.03
UPC: 999999111111,  Total Qty: 10  Price per UPC  $1.00 Total $ Value: $10.00
UPC: 999994565444,  Total Qty: 2  Price per UPC  $0.50 Total $ Value: $1.00
Total PO Value: $14.03

Can the script be modified to show summaries instead, as above?

Thank you.
0
David Johnson, CD, MVPOwnerCommented:
are you familiar with the term GIGO (garbage in garbage out)
How is the program to determine the price of an item if it changes throughout the document? Where is the data validated? Lowest price?

Are we both looking at the same original.txt ?

PO1*1*2*CA*1.01*UM*AB*123456456*AB*desc*UP*123456123456~
PO1*2*5*CA*1.00*UM*AB*123456444*AB*desc*UP*999999111111~
PO1*1*1*CA*1.70*UM*AB*123456654*AB*descabc*UP*123456123456~

*456 has 2 prices 1.01 and 1.70
*111  again 2 prices 1.00 and 5.04
Are they the same SKU or is one per item and the other per case?
PO1*2*5*CA*5.04*UM*AB*456789456*AB*descddd*UP*999999111111~
PO1*3*2*CA*0.50*UM*AB*456123456*AB*descbcd*UP*999994565444~

PO1*1*
2 [QTY]
*CA* [Dollar?]
1.01 [Price]
*UM*AB*123456456*AB*desc*UP*
123456123456 [UPC]
~
0
100questionsAuthor Commented:
A mistake was made on the input text showing the prices as different for the same UPCs.
The prices will always be the same for identical UPCs.
A summary of each SKU then, which should only show up once is what is the output file should show.
Thank you.
0
David Johnson, CD, MVPOwnerCommented:
upload a new input file
0
100questionsAuthor Commented:
New input file:

ISA*00*0000000000*00*0000000000*01*12345612378     *AA*ABCDEFGH       *123456*1234*P*00123*000000012*1*Q*>~
GS*PO*8888888888*ABCDEFGH*20150101*0123*9*X*001234~
ST*850*123456789~
BEG*00*SA*1234-2222222-1234**20150101*Q~
REF*IA*1234567*COMPANY~
REF*DP*012~
FOB*DF*OR*CITY~
CSH*Y~
ITD*01*9*****99~
DTM*037*20150101~
DTM*038*20150101~
TD5*B*92*SOMETHING~
N1*BY*COMPANY*11*0123~
N3*ADDRESS1~
N4*TOWN*CITY*12345*SOMEWHERE~
PO1*1*2*CA*0.75*UM*AB*123456456*AB*desc*UP*123456123456~
CTP*AB*ABC*0.10********9~
PID*F*01***desc2~
PO4*12~
PO1*2*5*CA*1.00*UM*AB*123456444*AB*desc*UP*999999111111~
CTP*AB*ABC*0.50********9~
PID*F*02***desc2b~
PO4*5~
CTT*1*99~
SE*11*123154641~
ST*850*88888888~
BEG*00*SA*1234-1111111-2345**20150101*Q~
REF*IA*1234567*COMPANY~
REF*DP*013~
FOB*DF*OR*CITY~
CSH*Y~
ITD*01*0*****00~
DTM*037*20150101~
DTM*038*20150101~
TD5*X*11*ABCD*M~
N1*BY*COMPANY*11*0123~
N3*ADDRESS1~
N4*TOWN*CITY*12345*SOMEWHERE~
PO1*1*1*CA*0.75*UM*AB*123456654*AB*descabc*UP*123456123456~
CTP*XY*ABC*0.10********1~
PID*F*08***desc~
PO4*12~
PO1*2*5*CA*1.00*UM*AB*456789456*AB*descddd*UP*999999111111~
CTP*AB*ABC*0.50********1~
PID*F*08***desc~
PO4*10~
PO1*3*2*CA*0.50*UM*AB*456123456*AB*descbcd*UP*999994565444~
CTP*AB*ABC*1.11********1~
PID*F*11***desc~
PO4*5~
CTT*1*99~
SE*01*123456789~
GE*01*1~
IEA*1*123456789~
~
0
100questionsAuthor Commented:
New desired output file:

UPC: 123456123456,  Total Qty: 3  Price per UPC  $0.75 Total $ Value: $2.25
UPC: 999999111111,  Total Qty: 10  Price per UPC  $1.00 Total $ Value: $10.00
UPC: 999994565444,  Total Qty: 2  Price per UPC  $0.50 Total $ Value: $1.00
Total PO Value: $13.25
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
This has been overhauled completely, to remove anything not related to the task at hand. I've also added numeric formatting to have aligned output.
$InputFile = "C:\folder\original.txt"
$OutputFile = "C:\folder\modified.txt"

Function ParseText ($OutputFile,$InputFile){
  $data = @()
  (Get-Content -Path $InputFile) -match "(BEG\*)|(PO1\*)|(PID\*)|(SE\*)|(IEA\*)|(ISA\*)|N1" |
    % {
      #Collect details
      If ($_ -match "PO1\*{1,}\d\*(\d{1,})\*\w{1,}\*(\d{1,}(\.\d{1,})?).*\*(\d{1,})~") {
        $data += New-Object PsObject -Property @{
          count = $Matches[1]
	  price = $Matches[2]
	  UPC   = $Matches[4]
        }
      }

      # Being here means we are finished - generate output
      If ($_ -match "IEA\*") {
        $total = 0.0
        $data | Group-Object UPC | tee -Variable test | % {
          $count = ($_ | select -Expand Group | measure-object -Property count -Sum).Sum
          $prize = [float] $_.Group[0].price
          $upc   = $_.Name
          'UPC: {0}, Total Qty: {1,4}  Price per UPC ${2,6:N2}  Total Value: ${3,7:N2}' -f $upc, $count, $prize, ($prize*$count)
          $total += $count*$prize
        }
        Write-Output "Total PO Value: `$$total"
      }
    } | Out-File $outputFile
}

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

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Powershell

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.