how to add field in my script

Hi All,

I have a script which is running perfectly fine, i need 2 things done which i am unable to do.

1. Add a field in my output which is "Ref"
2. Account number should be : AccNo+Deliverypoint but i need to add a check which will validate delivery point number.

Delivery point format is XX/XXXXXX/XXXX so if a accno is HP/023465 and delivery point is 1 then the field should say HP/023465/0001

if Delivery point is empty then it will be HP/023465/0000

so you can see that the last 4 characters are /0000 if empty otherwise it will pick what every numbers are there and add 0 to make up 4 characters, if there are already 4 characters like 9094 then no need to add 0 becasue your delivery point will become HP/023465/9094.

I have uploaded the file to test and also a screen shoot explaining what is needed.

if you see the script i am already picking up delivery point number as a separate column.

Please help.

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

   ' 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")
   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, 5,1) = "Ref" Then
            strRef = Trim(Mid(strLine,59,20))
         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
         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

Regards,

Ammar
Camberley.txt
delivery-point-and-ref.PNG