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

Attach the sample input file and the current output file

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~
~

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

$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
 }

Select allOpen 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

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.

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]
~

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.

upload a new input file

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~
~

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