How can I modify this vbscript to show the PO number?

Input file containing this data.  File named original.txt, found at c:\scripts\original.txt

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

Current VBScript:  
'EE28712743

' Text file I/O constants
Const ForReading = 1
Const ForWriting = 2
Const ForAppending = 8

' Data structure for accumulations
Class ItemInfo
  Public ItemUPC
  Public ItemQty
  Public ItemPrice
  Public ItemCost
End Class

' Define filenames
Const cInFile = "C:\scripts\original.txt"
Const cOutFile = "C:\scripts\new\new.txt"

' Set up objects needed
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oDict = CreateObject("Scripting.Dictionary")

' Read input file gathering item info
Set oInFile = oFSO.OpenTextFile(cInFile, ForReading)

Do Until oInFile.AtEndOfStream
   sLine = Trim(oInFile.Readline)
   If sLine <> "" Then
      aField = Split(sLine, "*")
      If aField(0) = "PO1" Then
         sUPC = Replace(aField(11), "~", "")
         dQty = CDbl(aField(2))
         dPrice = CDbl(aField(4))
         dCost = dQty * dPrice
         sKey = sUPC & "|" & FormatNumber(dPrice, 2)
         ' Add this mapping to the dictionary (new, or extend existing entry)
         If oDict.Exists(sKey) Then
            oDict.Item(sKey).ItemQty   = oDict.Item(sKey).ItemQty + dQty
            oDict.Item(sKey).ItemCost  = oDict.Item(sKey).ItemCost + dCost
         Else
            Set oItemInfo = New ItemInfo
            oItemInfo.ItemUPC = sUPC
            oItemInfo.ItemQty = dQty
            oItemInfo.ItemPrice = dPrice
            oItemInfo.ItemCost = dCost
            oDict.Add sKey, oItemInfo
         End If
      End If
   End If
Loop

oInFile.Close
 
' Open output file
Set oOutFile = oFSO.OpenTextFile(cOutFile, ForWriting, True)

dTotal = 0
For Each sKey In oDict
   dTotal = dTotal + oDict.Item(sKey).ItemCost
   sData = "UPC: " & oDict.Item(sKey).ItemUPC & ",  Total Qty: " & FormatNumber(oDict.Item(sKey).ItemQty) & "  Price per UPC  $" & FormatNumber(oDict.Item(sKey).ItemPrice) & " Total $ Value: $" & FormatNumber(oDict.Item(sKey).ItemCost)
   oOutFile.WriteLine sData
Next
sData = "Total PO Value: $" & FormatNumber(dTotal, 2)
oOutFile.WriteLine sData

' Done
oOutFile.Close

Open in new window


I would like to modify the output so that when it reads the BEG segment and if finds a similar PO, with the exception of the last 4 digits, then it should output similar to what is shown below, keeping in mind the current structure of the input data.

NB - If the PO number differs, then it should list it separately, with the corresponding UPCs for that particular PO, however if the PO number starts with the same digits, except for the last 4, then it should look similar to the output below.


PO:  1234-2222222
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

Can the script be modified to make allowance for this?

Thanks very much.
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.

Bill PrewCommented:
So, would the new version only need to look at the BEG lines?  Is there a PO number on some other lines that needs to be worked with too?

Not crystal clear on the processing yet...

~bp
0
Bill PrewCommented:
Give this s try, not completely sure I understand the data possibilities, but worth a shot.  If it is useful I'll tidy it up a bit and add more comments, but I didn't want to go crazy if I had misunderstood.

'EE28712743

' Text file I/O constants
Const ForReading = 1
Const ForWriting = 2
Const ForAppending = 8

' Data structure for accumulations
Class ItemInfo
  Public sUPC
  Public dQty
  Public dPrice
  Public dCost
End Class

' Define filenames
Const cInFile = "C:\scripts\original.txt"
Const cOutFile = "C:\scripts\new\new.txt"

' Set up objects needed
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oPOs = CreateObject("Scripting.Dictionary")

' Read input file gathering item info
Set oInFile = oFSO.OpenTextFile(cInFile, ForReading)

Do Until oInFile.AtEndOfStream
   sLine = Trim(oInFile.Readline)
   If sLine <> "" Then
      aField = Split(sLine, "*")
      Select Case aField(0)
      Case "BEG"
         sPO = Left(aField(3), Len(aField(3))-5)
         If oPOs.Exists(sPO) Then
            Set oItems = oPOs.Item(sPO)
         Else
            Set oItems = CreateObject("Scripting.Dictionary")
            oPOs.Add sPO, oItems
         End If
      Case "PO1"
         sUPC = Replace(aField(11), "~", "")
         dQty = CDbl(aField(2))
         dPrice = CDbl(aField(4))
         dCost = dQty * dPrice
         sKey = sUPC & "|" & FormatNumber(dPrice, 2)
         ' Add this mapping to the dictionary (new, or extend existing entry)
         If oItems.Exists(sKey) Then
            oItems.Item(sKey).dQty   = oItems.Item(sKey).dQty + dQty
            oItems.Item(sKey).dCost  = oItems.Item(sKey).dCost + dCost
         Else
            Set oItemInfo = New ItemInfo
            oItemInfo.sUPC = sUPC
            oItemInfo.dQty = dQty
            oItemInfo.dPrice = dPrice
            oItemInfo.dCost = dCost
            oItems.Add sKey, oItemInfo
         End If
      End Select
   End If
Loop

oInFile.Close
 
' Open output file
Set oOutFile = oFSO.OpenTextFile(cOutFile, ForWriting, True)

dTotal = 0
For Each sPO In oPOs
   dPOTotal = 0
   sData = "PO:  " & sPO
   oOutFile.WriteLine sData
   Set oItems = oPOs.Item(sPO)
   For Each sKey In oItems
      dPOTotal = dPOTotal + oItems.Item(sKey).dCost
      sData = "UPC: " & oItems.Item(sKey).sUPC & ",  Total Qty: " & FormatNumber(oItems.Item(sKey).dQty) & "  Price per UPC  $" & FormatNumber(oItems.Item(sKey).dPrice) & " Total $ Value: $" & FormatNumber(oItems.Item(sKey).dCost)
      oOutFile.WriteLine sData
   Next
   sData = "Total PO Value: $" & FormatNumber(dPOTotal, 2) & vbCrLf
   oOutFile.WriteLine sData
  dTotal = dTotal + dPOTotal
Next
sData = "Total Value: $" & FormatNumber(dTotal, 2)
oOutFile.WriteLine sData

' Done
oOutFile.Close

Open in new window

Input file used for test:
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~
PO1*2*5*CA*1.10*UM*AB*456789456*AB*descddd*UP*999999111111~
PO1*2*5*CA*1.10*UM*AB*456789456*AB*descddd*UP*999999111111~
~

Open in new window

Output file produced:
PO:  1234-2222222
UPC: 123456123456,  Total Qty: 2.00  Price per UPC  $0.75 Total $ Value: $1.50
UPC: 999999111111,  Total Qty: 5.00  Price per UPC  $1.00 Total $ Value: $5.00
Total PO Value: $6.50

PO:  1234-1111111
UPC: 123456123456,  Total Qty: 1.00  Price per UPC  $0.75 Total $ Value: $0.75
UPC: 999999111111,  Total Qty: 5.00  Price per UPC  $1.00 Total $ Value: $5.00
UPC: 999994565444,  Total Qty: 2.00  Price per UPC  $0.50 Total $ Value: $1.00
UPC: 999999111111,  Total Qty: 10.00  Price per UPC  $1.10 Total $ Value: $11.00
Total PO Value: $17.75

Total Value: $24.25

Open in new window

~bp
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
aikimarkCommented:
In your posted example:
BEG*00*SA*1234-2222222-1234**20150101*Q~
BEG*00*SA*1234-2222222-2345**20150101*Q~

Open in new window

You have two BEG lines.

I don't see any pattern that would tie the data in theses two lines to any of the PO# lines.  Please explain
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

100questionsAuthor Commented:
@bp - This works great, thank you.
0
100questionsAuthor Commented:
Works great! Thanks you.
0
Bill PrewCommented:
Great, glad that was helpful.

~bp
0
aikimarkCommented:
@Bill
So, the BEG and PO1 items are only positionally related and not related by any key?
0
Bill PrewCommented:
@aikimark

That is my understanding, this looked like an ANSI EDI X12 850 transaction, and the PO segment there doesn't seem to reference a PO number, only the BEG segment.

http://www.fcx.com/company/pdf/AN_850_PURCHORD_004010_12_Guideline.pdf

~bp
0
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
VB Script

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.