How can I modify this vbscript to add specific dates?

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*20150105~
   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*20150105~
   DTM*038*20150108~
   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 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


Desired Output, similar to this, to add the Ship Date and Cancel date, in year, month, date format.
NB - The dates should be the same per each PO, however if they change, then they would need to be reflected separately, by PO and UPC etc..


PO:  1234-2222222
Ship Date: 01/01/2015
Cancel Date: 01/05/2015
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
Ship Date: 01/05/2015
Cancel Date: 01/08/2015
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

Can the script be modified to reflect this?
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:
Will every PO always have the DTM*037 and DTM*038 segments, is that guaranteed?

~bp
Bill PrewIT / Software Engineering ConsultantCommented:
I assumed both dates will always be there, and this seems to do what you described.

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

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

' Data structure for PO info
Class PoInfo
  Public sPO
  Public sShipDate
  Public sCancelDate
  Public oItems
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 orders info
Set oInFile = oFSO.OpenTextFile(cInFile, ForReading)

Do Until oInFile.AtEndOfStream

   sLine = Trim(oInFile.Readline)

   ' Skip any blank lines...
   If sLine <> "" Then
      ' Split input line at delimiter "*"
      aField = Split(sLine, "*")

      ' Determine waht segment type this line is and process if desired
      Select Case aField(0)

      ' Start of a PO
      Case "BEG"
         ' Extract PO number, remove right 5 characters, and save
         sPO = Left(aField(3), Len(aField(3))-5)
         Set oItems = Nothing

      ' PO dates
      Case "DTM"
         ' Save ship and cancel dates
         Select Case aField(1)
         Case "037"
            sShipDate = Replace(aField(2), "~", "")
         Case "038"
            sCancelDate = Replace(aField(2), "~", "")
         End Select

      ' Items on the PO
      Case "PO1"
         ' If this is the first item for this PO, then we need to add an entry to the PO dictionary
         ' for this PO and store the PO related information.  In addition we allocate a dictionary
         ' that will hold the item info for all items on this PO, and save this item to that.
         If oItems Is Nothing Then
            sPoKey = sPO & "|" & sShipDate & "|" & sCancelDate
            If oPOs.Exists(sPoKey) Then
               Set oItems = oPOs.Item(sPoKey)
            Else
               Set oPoInfo = New PoInfo
               oPoInfo.sPO = sPO
               oPoInfo.sShipDate = sShipDate
               oPoInfo.sCancelDate = sCancelDate
               Set oItems = CreateObject("Scripting.Dictionary")
               Set oPoInfo.oItems = oItems
               oPOs.Add sPoKey, oPoInfo
            End If
         End If

         ' Get item info from input line
         sUPC = Replace(aField(11), "~", "")
         dQty = CDbl(aField(2))
         dPrice = CDbl(aField(4))
         dCost = dQty * dPrice
         sKey = sUPC & "|" & FormatNumber(dPrice, 2)
         ' Add this item to the item info dictionary, if item already on this PO, add this qty to it
         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

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

' Loop through all PO's and output each
dTotal = 0
For Each sPoKey In oPOs
   Set oPO = oPOs.Item(sPoKey)
   dPOTotal = 0
   ' PO level info
   oOutFile.WriteLine "PO:  " & oPO.sPO
   oOutFile.WriteLine "Ship Date:  " & FormatDate(oPO.sShipDate)
   oOutFile.WriteLine "Cancel Date:  " & FormatDate(oPO.sCancelDate)
   ' Write out all items on this PO
   Set oItems = oPO.oItems
   For Each sKey In oItems
      Set oItem = oItems.Item(sKey)
      dPOTotal = dPOTotal + oItem.dCost
      oOutFile.WriteLine "UPC: " & oItem.sUPC & ",  Total Qty: " & FormatNumber(oItem.dQty) & "  Price per UPC  $" & FormatNumber(oItem.dPrice) & " Total $ Value: $" & FormatNumber(oItem.dCost)
   Next
   ' Item total for this PO
   oOutFile.WriteLine "Total PO Value: $" & FormatNumber(dPOTotal, 2) & vbCrLf
  dTotal = dTotal + dPOTotal
Next
' Item totals for all POs
oOutFile.WriteLine "Total Value: $" & FormatNumber(dTotal, 2)

' Close output file
oOutFile.Close

Function FormatDate(sDate)
   ' YYYYMMDD -> MM/DD/YYYY
   FormatDate = Mid(sDate, 5, 2) & "/" & Mid(sDate, 7, 2) & "/" & Mid(sDate, 1, 4)
End Function

Open in new window

~bp
100questionsAuthor Commented:
Hi Bill,
Yes all PO's will have the D37 and D38 segments.

Thanks for the script.
I encountered an error though at......
(88, 10) Microsoft VBScript runtime error: Object doesn't support this property or method: 'oItems.Exists'

Hope this helps.
OWASP: Forgery and Phishing

Learn the techniques to avoid forgery and phishing attacks and the types of attacks an application or network may face.

Bill PrewIT / Software Engineering ConsultantCommented:
Can you supply the data file you are using.  I tested the code here with the test data at the top of this post and it did not error, and produced the desired results.

~bp
Bill PrewIT / Software Engineering ConsultantCommented:
Nevermind, I think I see it, you must have "duplicate POs" where I didn't in the test I did.  This should fix that:

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

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

' Data structure for PO info
Class PoInfo
  Public sPO
  Public sShipDate
  Public sCancelDate
  Public oItems
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 orders info
Set oInFile = oFSO.OpenTextFile(cInFile, ForReading)

Do Until oInFile.AtEndOfStream

   sLine = Trim(oInFile.Readline)

   ' Skip any blank lines...
   If sLine <> "" Then
      ' Split input line at delimiter "*"
      aField = Split(sLine, "*")

      ' Determine waht segment type this line is and process if desired
      Select Case aField(0)

      ' Start of a PO
      Case "BEG"
         ' Extract PO number, remove right 5 characters, and save
         sPO = Left(aField(3), Len(aField(3))-5)
         Set oItems = Nothing

      ' PO dates
      Case "DTM"
         ' Save ship and cancel dates
         Select Case aField(1)
         Case "037"
            sShipDate = Replace(aField(2), "~", "")
         Case "038"
            sCancelDate = Replace(aField(2), "~", "")
         End Select

      ' Items on the PO
      Case "PO1"
         ' If this is the first item for this PO, then we need to add an entry to the PO dictionary
         ' for this PO and store the PO related information.  In addition we allocate a dictionary
         ' that will hold the item info for all items on this PO, and save this item to that.
         If oItems Is Nothing Then
            sPoKey = sPO & "|" & sShipDate & "|" & sCancelDate
            If oPOs.Exists(sPoKey) Then
               Set oItems = oPOs.Item(sPoKey).oItems
            Else
               Set oPoInfo = New PoInfo
               oPoInfo.sPO = sPO
               oPoInfo.sShipDate = sShipDate
               oPoInfo.sCancelDate = sCancelDate
               Set oItems = CreateObject("Scripting.Dictionary")
               Set oPoInfo.oItems = oItems
               oPOs.Add sPoKey, oPoInfo
            End If
         End If

         ' Get item info from input line
         sUPC = Replace(aField(11), "~", "")
         dQty = CDbl(aField(2))
         dPrice = CDbl(aField(4))
         dCost = dQty * dPrice
         sKey = sUPC & "|" & FormatNumber(dPrice, 2)
         ' Add this item to the item info dictionary, if item already on this PO, add this qty to it
         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

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

' Loop through all PO's and output each
dTotal = 0
For Each sPoKey In oPOs
   Set oPO = oPOs.Item(sPoKey)
   dPOTotal = 0
   ' PO level info
   oOutFile.WriteLine "PO:  " & oPO.sPO
   oOutFile.WriteLine "Ship Date:  " & FormatDate(oPO.sShipDate)
   oOutFile.WriteLine "Cancel Date:  " & FormatDate(oPO.sCancelDate)
   ' Write out all items on this PO
   Set oItems = oPO.oItems
   For Each sKey In oItems
      Set oItem = oItems.Item(sKey)
      dPOTotal = dPOTotal + oItem.dCost
      oOutFile.WriteLine "UPC: " & oItem.sUPC & ",  Total Qty: " & FormatNumber(oItem.dQty) & "  Price per UPC  $" & FormatNumber(oItem.dPrice) & " Total $ Value: $" & FormatNumber(oItem.dCost)
   Next
   ' Item total for this PO
   oOutFile.WriteLine "Total PO Value: $" & FormatNumber(dPOTotal, 2) & vbCrLf
  dTotal = dTotal + dPOTotal
Next
' Item totals for all POs
oOutFile.WriteLine "Total Value: $" & FormatNumber(dTotal, 2)

' Close output file
oOutFile.Close

Function FormatDate(sDate)
   ' YYYYMMDD -> MM/DD/YYYY
   FormatDate = Mid(sDate, 5, 2) & "/" & Mid(sDate, 7, 2) & "/" & Mid(sDate, 1, 4)
End Function

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:
Works perfectly, excellent.  Thanks.
Bill PrewIT / Software Engineering ConsultantCommented:
Welcome.

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