how to read a csv file and change the delivery date where needed

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

' Define global variables
Dim objFSO
Dim strCheckFolder

' Specify path to folder of files to process
strCheckFolder = "B:\EE\EE28959745\files"

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

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

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

' Process the base folder
ProcessFolder strCheckFolder

' ==============================================================================
' 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 ProcessFolder(strCheckFolder)

   ' Define local variables
   Dim objCheckFolder
   Dim objCheckFile

   ' Make sure the folder exists
   If Not objFSO.FolderExists(strCheckFolder) Then
      Wscript.Echo "*ERROR* Folder [" & strCheckFolder & "] does not exist, quitting."
      Wscript.Quit
   End If

   ' Access the folder
   Set objCheckFolder = objFSO.GetFolder(strCheckFolder)

   ' Look at all files in this folder, process each one
   For Each objCheckFile In objCheckFolder.Files
      ProcessFile objCheckFile
   Next

End Sub

Sub ProcessFile(objCheckFile)

   ' Define local varaibles
   Dim objFile
   Dim strData
   Dim arrLines
   Dim arrFields
   Dim blnFileChanged
   Dim i

   ' Read the file contents into an array for processing
   Set objFile = objFSO.OpenTextFile(objCheckFile.Path, ForReading, False, TriStateUseDefault)
   strData = objFile.ReadAll
   arrLines = Split(strData, vbCrLf)
   objFile.Close
   Set objFile = Nothing

   ' Assume no changes needed to this file
   blnFileChanged = False

   ' Look at each line of the file and check the date field
   For i = 0 To UBound(arrLines)

      ' Split the fields of this line apart into an array (comma delim)
      arrFields = Split(arrLines(i), ",")

      ' Make sure we found at least 7 columns (array indexes are 0 based...)
      If UBound(arrFields) > 5 Then
         ' Check if 7th column is in the future, if not set it to tomorrows date
         If DateDiff("d", Now, StringToDate(Trim(arrFields(6)))) < 1 Then
            arrFields(6) = DateToString(DateAdd("d", 1, Now))
            arrLines(i) = Join(arrFields, ",")
            blnFileChanged = True
         End If
      End If

   Next

   ' If we changed any data in the file, then rewrite it now
   If blnFileChanged Then
      strData = Join(arrLines, vbCrLf)
      Set objFile = objFSO.OpenTextFile(objCheckFile.Path, ForWriting, True)
      objFile.Write(strData)
      objFile.Close
      Set objFile = Nothing
   End If

End Sub

Function StringToDate(strDate)
   ' Convert a string inb format YYYYMMDD to a date type value
   StringToDate = CDate(Mid(strDate, 5, 2) & "/" & Mid(strDate, 7, 2) & "/" & Mid(strDate, 1, 4))
End Function

Function DateToString(datDate)
   ' Convert a date type value to a text string in format YYYYMMDD
   DateToString = Year(datDate) & LPad(Month(datDate), 2, "0") & LPad(Day(datDate), 2, "0")
End Function

Function LPad( strText, intLen, chrPad )
   ' Left pad a string to any length with a specified character
   LPad = Right( String( intLen, chrPad ) & strText, intLen )
End Function

Select allOpen in new window