how to add fields in script

Can some one please help.

Regards,

Ammar

First, the VBS file you posted does not produce the CSV file that you attached, so I'm confused by that.  Different fields are present in each, they don't exactly match.  So I am reluctant to modify that VBS sample when it doesn't seem to be the one producing the existing CSV file?

Is the ONLY change you want the addition of two fields to the end of each line in the CSV, taken from the line below for each item?  And you just want to the two fields bolded below?

ORDERED  2  ALLOCATED  2  PICKED  2  DESPATCHED  2  INVOICED  2  BACK ORDER  0

~bp

Hi Bill,

yes that the two fields i want.

i have tried to modified the scipt and got somewhere close.

Option Explicit

' ==============================================================================
' C O N S T A N T S   &   V A R I A B L E S
' ==============================================================================

' Define needed constants
Const ForReading = 1
Const ForWriting = 2
Const TriStateUseDefault = -2
Dim vbCrFf
vbCrFf = Chr(13) & Chr(12)

' Define global variables
Dim objFSO
Dim strInFile
Dim strOutFile

' ==============================================================================
' I N I T I A L I Z A T I O N
' ==============================================================================

' Create filesystem object
Set objFSO = CreateObject("Scripting.FileSystemObject")

' Get command line parms
GetParms()

' ==============================================================================
' M A I N   L O G I C
' ==============================================================================

' Process the input file
ProcessFile strInFile, strOutFile

' ==============================================================================
' W R A P U P
' ==============================================================================

' Done, cleanup and exit
Wscript.Quit

' ==============================================================================
' S U B R O U T I N E S   &   F U N C T I O N S
' ==============================================================================

Sub GetParms()

   ' Get parms from command line
   If (WScript.Arguments.Count < 2) Then
       WScript.Echo "Usage: " & Wscript.ScriptName & " <input-report-file> <output-csv-file>"
       WScript.Quit
   Else
       strInFile = objFSO.GetAbsolutePathname(WScript.Arguments(0))
       strOutFile = objFSO.GetAbsolutePathname(WScript.Arguments(1))
   End If

   ' Make sure input file exists
   If Not objFSO.FileExists(strInFile) Then
      Wscript.Echo "*ERROR* Input file does not exist. (" & strInFile & ")"
      Wscript.Quit
   End If

End Sub

Sub ProcessFile(strInFile, strOutFile)

   ' Define local varaibles
   Dim objInFile
   Dim objOutFile
   Dim strData
   Dim strLine
   Dim strOutput
   Dim arrLines
   Dim strDelim
   Dim strProduct
   Dim strOrderQty
   Dim strOrderNumber
   Dim strAccNo
   Dim strOrderDate
   Dim strDespDate
   Dim strDeliveryPoint
   Dim strRef
   Dim strSellPrice
   Dim strSalesAmount
   Dim strCostAmount
   Dim strInvoice
   Dim strBackorder

   ' Open input file, read into array for processing
   Set objInFile = objFSO.OpenTextFile(strInFile, ForReading, False, TriStateUseDefault)
   strData = objInFile.ReadAll
   objInFile.Close
   Set objInFile = Nothing

   ' Determine what is used in the file for line delimiters
   If InStr(strData, vbCrLf) > 0 Then
      strDelim = vbCrLf
   ElseIf InStr(strData, vbLf) > 0 Then
      strDelim = vbLf
   ElseIf InStr(strData, vbCr) > 0 Then
      strDelim = vbCr
   End If
   strData = Replace(strData, vbCrFf, strDelim)
   strData = Replace(strData, vbLf & vbLf, vbLf)

   ' Split file data into lines based on delimiter
   arrLines = Split(strData, strDelim)

   ' Open output file, output header line
   Set objOutFile = objFSO.OpenTextFile(strOutFile, ForWriting, True)
   objOutFile.WriteLine Quote("OrderNumber") & "," & Quote("AccNo") & "," & Quote("OrderDate") & "," & Quote("Product") & "," & Quote("OrderQty") & "," & Quote("DespDate") & "," & Quote("DeliveryPoint") & "," & Quote("Ref")  & "," & Quote("SellPrice") & "," & Quote("SalesAmount") & "," & Quote("CostAmount") & "," & Quote("Invoiced")  & "," & Quote("Backorder")
   strOutput = ""

   ' Process each line of the file
   For Each strLine in arrLines

      ' Ignore all blank lines
      If Trim(strLine) <> "" Then

         ' If this is the order info line, save off needed order fields
         If Mid(strLine, 3, 1) = "/" And Mid(strLine, 16, 1) = "/" And Mid(strLine, 69, 1) = "/" And Mid(strLine, 72, 1) = "/" And Mid(strLine, 80, 1) = "/" And Mid(strLine, 83, 1) = "/" Then
            strOrderNumber = Mid(strLine, 1, 11)
            strAccNo = Trim(Mid(strLine, 13,12))
            strDeliveryPoint = Trim(Mid(strLine, 59,4))
            strOrderDate = Trim(Mid(strLine, 67, 8))
            strDespDate = Trim(Mid(strLine,78,8))
         End If

         'get REF
         If Mid(strLine, 59,3) = "Ref" Then
            strRef = Trim(Mid(strLine,59,20))
            'strRef = strRef & "/" & Right("0000"&strDeliveryPoint,4)
         End If

         'get Invoice and Backorder
         If Mid(strLine,1,7) = "ORDERED" Then
             strInvoice = Trim(Mid(strLine, 100,6))
        End If

       'get Backorder
         If Mid(strLine,1,7) = "ORDERED" Then
             strBackorder = Trim(Mid(strLine, 124,3))
        End If

         ' If this is the item detail line, get needed fields, output CSV record
         If Mid(strLine, 68, 1) = "." And Mid(strLine, 5, 1) = " " And IsNumeric(Trim(Mid(strLine, 1, 4))) = True Then
            strProduct = Trim(Mid(strLine, 6, 11))
            strOrderQty = Trim(Mid(strLine, 59, 1)) & Trim(Mid(strLine, 51, 8))
            objOutFile.WriteLine strOrderNumber & "," & strAccNo & "," & strOrderDate & "," & Quote(strProduct) & "," & strOrderQty & "," & strDespDate & "," & strDeliveryPoint & "," & strRef & "," & strSellPrice & "," & strSalesAmount & ","  & strInvoice & "," & strBackorder
         End If

      End If

   Next

   objOutFile.Close
   Set objOutFile = Nothing

End Sub

' Add surrounding double quotes to a string
Function Quote(s)
   Quote = Chr(34) & s & Chr(34)
End Function

Select allOpen in new window

attach is what is generated.
171819OCTInv.csv
171819OCTInvLisaWF.txt

What do you need differently than what you already have in the 171819OCTInv.csv file?

hi aiki,

the Invoice qty is not coming in there respective columns and also the back order.
I adjusted manually the file which was attach but please see this attach file and this is how its translated.
Ammar2.csv

Yes, that will be the case since the new line you are getting those two fields from comes after the detail item line that we used as the trigger to write the CSV line out to.  I can help adjust for that, will post up a proposed modification to your last code.

~bp

thanks Bill,

I am learning so thanks for your and Aiki help.

Regards,

Ammar

@Bill
While you're in the code, you might want to look at this line.  It doesn't smell right.  However, Ammar hasn't posted context on which prior questions are relevant.

strOrderQty = Trim(Mid(strLine, 59, 1)) & Trim(Mid(strLine, 51, 8))

Select allOpen in new window

Thanks Bill,

I am learning still alot to do but getting there.

i have run the script it runs fine but no file created.

What a expert and skill really inspired me to learn how to write i am still learning but Great help he is

Thank you so much

If you approached this with regular expressions, there might be less code.
Example:

    Dim strInputData
    Dim vNames_Order, vNames_Detail
    Dim oDic, vItem
    
    Dim oFS, oTS
    Const ForReading = 1, ForWriting = 2, ForAppending = 8
    Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
    
    Set oFS = CreateObject("scripting.filesystemobject")
    Set oTS = oFS.OpenTextFile("C:\users\mark\downloads\171819OCTInvLisaWF.txt", ForReading, True, TristateFalse)
    strInputData = oTS.readall
    oTS.Close
    
    Dim oRE_Order
    Dim oMatches_Order
    Dim oM_Order
    Dim oRE_Detail
    Dim oMatches_Detail
    Dim oM_Detail
    Dim oSM_Order
    Dim oSM_Detail
    Dim lngSM_Order
    Dim lngSM_Detail
    
    Set oRE_Order = CreateObject("vbscript.regexp")
    oRE_Order.Global = True
    oRE_Order.Pattern = "\n *(\S*/\S+)\s+(\S+) +\s.{33}(.{4})\s*(\d\d/\d\d/\d\d)\s*(\d\d/\d\d/\d\d)(?:(?:.|\n)*?)\n.{57} Ref +([^\r]+)((?:.|\n)*?)(?=$|\n *\S*/\S+\s+\S+.*\n.*\n.{57} Ref )"
    Set oRE_Detail = CreateObject("vbscript.regexp")
    oRE_Detail.Global = True
    oRE_Detail.Pattern = "\n *\d+ (\S{4,}) .{37}(.{9})\s+(\d+\.\d\d) .{8}\s+(\d+\.\d\d)\s+(\d+\.\d\d).*\n.*\n.*\n.+?INVOICED\s+(\d+)\s+BACK ORDER\s+(\d+)"

    Set oDic = CreateObject("scripting.dictionary")

    vNames_Order = Split("OrderNumber,AccNo,DeliveryPoint,OrderDate,DespDate,Ref", ",")
    vNames_Detail = Split("Product,OrderQty,SellPrice,SalesAmount,CostAmount,Invoiced,Backorder", ",")
    
    If oRE_Order.test(strInputData) Then
        Set oTS = oFS.OpenTextFile("C:\users\mark\downloads\171819OCTInvLisaWF.csv", ForWriting, True, TristateFalse)
        oTS.writeline "OrderNumber,AccNo,OrderDate,Product,OrderQty,DespDate,DeliveryPoint,Ref,SellPrice,SalesAmount,CostAmount,Invoiced,Backorder"
        
        Set oMatches_Order = oRE_Order.Execute(strInputData)
        For Each oM_Order In oMatches_Order
            For lngSM_Order = 0 To oM_Order.submatches.Count - 2
                oDic(vNames_Order(lngSM_Order)) = oM_Order.submatches(lngSM_Order)
            Next
                
            Set oMatches_Detail = oRE_Detail.Execute(oM_Order.submatches(oM_Order.submatches.Count - 1))
            For Each oM_Detail In oMatches_Detail
                For lngSM_Detail = 0 To oM_Detail.submatches.Count - 1
                    oDic(vNames_Detail(lngSM_Detail)) = oM_Detail.submatches(lngSM_Detail)
                Next
                
                oTS.writeline Join(Array(Trim(oDic("OrderNumber")), Trim(oDic("AccNo")), Trim(oDic("OrderDate")), _
                            Trim(oDic("Product")), Trim(oDic("OrderQty")), Trim(oDic("DespDate")), Trim(oDic("DeliveryPoint")), _
                            Trim(oDic("Ref")), Trim(oDic("SellPrice")), Trim(oDic("SalesAmount")), Trim(oDic("CostAmount")), _
                            Trim(oDic("Invoiced")), Trim(oDic("Backorder"))), ",")
                
            Next
        Next
        oTS.Close
    End If

Select allOpen in new window

I think the RegEx approach might need some fine tuning, it seem to find considerable less output lines than the non RegEx approach.

~bp

@Bill

I noticed the output line difference.  I suspect that not all orders have a "Ref" line.

@Aiki

you are write not all the orders have Ref line or Reason code

I've encountered both an empty string (no data) following the "Ref" as well as an absence of a "Ref" line.  Also, I think I've even encountered an order with no products.

@Bill & Ammar

The strangeness I perceived about the strOrderQty assignment statement was the handling of negative values.  I usually let some intrinsic VB function do that.
Example:

?CLng("1-")
-1
?ccur("1.23-")
-1.23 

Select allOpen in new window