How can I get a summary using a .bat or vbscript to read a .txt file?

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

Output file, which contains data below.  Output file path is new.txt, at c:\scripts\new\...


 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

Using a .bat or vbscript, how can the output be obtained?

Thank you.
100questionsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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 PrewIT / Software Engineering ConsultantCommented:
Here is a VBS that should do what you asked for, check the path constants near the top to make sure they are where the files should be and give it a test...

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

' Data structure for accumulations
Class ItemInfo
  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
         ' Add this mapping to the dictionary (new, or extend existing entry)
         If oDict.Exists(sUPC) Then
            oDict.Item(sUPC).ItemQty   = oDict.Item(sUPC).ItemQty + dQty
            oDict.Item(sUPC).ItemCost  = oDict.Item(sUPC).ItemCost + dCost
         Else
            Set oItemInfo = New ItemInfo
            oItemInfo.ItemQty = dQty
            oItemInfo.ItemPrice = dPrice
            oItemInfo.ItemCost = dCost
            oDict.Add sUPC, oItemInfo
         End If
      End If
   End If
Loop

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

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

' Done
oOutFile.Close

Open in new window

~bp
100questionsAuthor Commented:
Thanks Bill, this works great.
Last modifications if possible, unless you want me to start another question.

If the price changes by mistake for the same UPC, it looks like currently there is no check on that.
If the price changes, which it should not, then is it possible for it to list the same UPC on another line with the corresponding price?

I have some further modifications for which I will open a new question as well, however for this change I was wondering if it's a small change, if not I will add it to my next question.
Bill PrewIT / Software Engineering ConsultantCommented:
So, is what you are saying that rather than sum by just UPC number, you want to sum by UPC+PRICE?  Meaning if you had three lines of a UPC at one price, and 3 lines at a different price, you would want to see two lines in the output with the sum of their respective 3 lines each?

~bp
Learn 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.

Bill PrewIT / Software Engineering ConsultantCommented:
If my prior comment is accurate, this should handle that:

'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

~bp

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
100questionsAuthor Commented:
Yes, this is exactly it.
I have some further modifications, and I'll open a new ticket.
Thanks very much!
100questionsAuthor Commented:
Excellent.
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
Windows Batch

From novice to tech pro — start learning today.