Option Explicit
' Define files and folders to work with (NO TRAILING BACKSLASH)
Const cBaseDir = "b:\ee\ee29122035\files\[[DATE]]"
Const cControlFile = "b:\ee\ee29122035\list.csv"
Const cDelim = ","
' Text file I/O constants
Const ForReading = 1
Const ForWriting = 2
Const ForAppending = 8
' Declare global variables
Dim objFSO, strBaseDir, strControlFile, strDate
Dim arrControl, strControl, arrFields, i, arrTemp
Dim strOldName, strNewName, strOldPath, strNewPath
' Create file system object
Set objFSO = WScript.CreateObject("Scripting.FileSystemObject")
' Get date in YYYYMMDD format
strDate = Year(Now) & Right("0" & Month(Now), 2) & Right("0" & Day(Now), 2)
' Resolve file paths
strBaseDir = objFSO.GetAbsolutePathname(Replace(cBaseDir, "[[DATE]]", strDate, 1, -1, vbTextCompare))
strControlFile = objFSO.GetAbsolutePathname(cControlFile)
' Make sure base folder exists
If Not objFSO.FolderExists(strBaseDir) Then
Wscript.Echo Now & " ERROR: Base folder does not exist - " & strBaseDir
Wscript.Quit
End If
' Make sure control file exists
If Not objFSO.FileExists(strControlFile) Then
Wscript.Echo Now & " ERROR: List file does not exist - " & strControlFile
Wscript.Quit
End If
' Load csv file into array
With objFSO.OpenTextFile(strControlFile, ForReading)
arrControl = Split(.ReadAll, vbCrLf)
.Close
End With
' Loop over each control record and process as needed (skip header row)
For i = 1 To UBound(arrControl)
' Split line at delimiter, gather fields
arrFields = CSVParse(arrControl(i))
If UBound(arrFields) = 2 Then
' Get old file name based on control file info
strOldName = arrFields(2) & ".pdf"
' Build new name from control file pieces
arrTemp = Split(arrFields(1), "|")
strNewName = arrFields(2) & "_" & Replace(Trim(arrTemp(0)), " ", "_") & "_" & Replace(Trim(arrTemp(1)), " ", "_")
arrTemp = Split(arrFields(0), " ")
strNewName = strNewName & "_" & arrTemp(0) & "_" & arrTemp(1) & "_" & arrTemp(2) & ".pdf"
strNewName = CleanFilename(strNewName)
' Build full paths for old and new file names
strOldPath = strBaseDir & "\" & strOldName
strNewPath = strBaseDir & "\" & strNewName
' If old file exists, and new name is not used, rename. Otherwise display warning.
If objFSO.FileExists(strOldPath) Then
If Not objFSO.FileExists(strNewPath) Then
objFSO.MoveFile strOldPath, strNewPath
Else
Wscript.Echo Now & " WARNING: New file already exists - " & strNewName
End If
Else
Wscript.Echo Now & " WARNING: Old file does not exist - " & strOldName
End If
End If
Next
' Function to parse comma delimited line and return array of field values.
Function CSVParse(ByVal strLine)
Dim arrFields
Dim blnIgnore
Dim intFieldCount
Dim intCursor
Dim intStart
Dim strChar
Dim strValue
Const QUOTE = """"
Const QUOTE2 = """"""
' Check for empty string and return empty array.
If (Len(Trim(strLine)) = 0) then
CSVParse = Array()
Exit Function
End If
' Initialize.
blnIgnore = False
intFieldCount = 0
intStart = 1
arrFields = Array()
' Add "," to delimit the last field.
strLine = strLine & ","
' Walk the string.
For intCursor = 1 To Len(strLine)
' Get a character.
strChar = Mid(strLine, intCursor, 1)
Select Case strChar
Case QUOTE
' Toggle the ignore flag.
blnIgnore = Not blnIgnore
Case ","
If Not blnIgnore Then
' Add element to the array.
ReDim Preserve arrFields(intFieldCount)
' Makes sure the "field" has a non-zero length.
If (intCursor - intStart > 0) Then
' Extract the field value.
strValue = Mid(strLine, intStart, _
intCursor - intStart)
' If it's a quoted string, use Mid to
' remove outer quotes and replace inner
' doubled quotes with single.
If (Left(strValue, 1) = QUOTE) Then
arrFields(intFieldCount) = _
Replace(Mid(strValue, 2, _
Len(strValue) - 2), QUOTE2, QUOTE)
Else
arrFields(intFieldCount) = strValue
End If
Else
' An empty field is an empty array element.
arrFields(intFieldCount) = Empty
End If
' increment for next field.
intFieldCount = intFieldCount + 1
intStart = intCursor + 1
End If
End Select
Next
' Return the array.
CSVParse = arrFields
End Function
Function CleanFilename(strName)
Dim arrIllegal, strIllegal, i
arrIllegal = Array("<",">",":","""","/","\","|","?","*")
CleanFilename = Trim(strName)
For Each strIllegal In arrIllegal
CleanFilename = Replace(CleanFilename, strIllegal, "_")
Next
For i = 0 To 31
CleanFilename = Replace(CleanFilename, Chr(i), "_")
Next
End Function
Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.
”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.
Our community of experts have been thoroughly vetted for their expertise and industry experience.
The Expert of the Year award recognizes an expert who helped improve Experts Exchange in the past year through high levels of contributions and participation on site. This award is given to the expert who has achieved the highest levels of participation, while maintaining quality contributions and professionalism.
The Distinguished Expert awards are presented to the top veteran and rookie experts to earn the most points in the top 50 topics.