Link to home
Start Free TrialLog in
Avatar of Luis Diaz
Luis DiazFlag for Colombia

asked on

VB Script: append multiples csv files into a single file

Hello experts,

The VB Script reported bellow allows me to append multiple files into a single file:

I would like to simplify  and take into account the following requirement:
1-Instead of move csv files into Archive folder, I would like to move to YYYYMMDD_HHMMSS Archive sub folder
2-Add in last available columns of appended csv name and creation date of each file involved by the append process
3-Generate appended file into a Result folder and log file into Log folder instead of InputFolder
4-Generate appended file with YYYYMMDD_HHMMSS date stamp instead of YYYYMMDD to avoid error message when execution is performed the same day

If you have questions, please contact me.

Thank you for your help.

'Create the file system object for creating folders:
Set objFSO = CreateObject("Scripting.FileSystemObject")


' Text file I/O constants
Const ForReading = 1
Const ForWriting = 2
Const ForAppending = 8
Const TristateTrue = -1
Const TristateFalse = 0
Const TristateUseDefault = -2

' Define folders and files to work with

strScriptName = Replace(WScript.ScriptName, ".vbs", "", 1, -1, vbTextCompare)
strScriptDir = Replace(WScript.ScriptFullName, WScript.ScriptName, "")
cInputFolder = strScriptDir & "\Input"
cArchiveFolder = strScriptDir & "\Archive"
cMergedFile = strScriptDir  & "\Input\[[STAMP]]_Reported_Name.csv"
cHeaderFile = strScriptDir & "Header.csv"
cLogFile = strScriptDir  & "\Input\[[STAMP]]_[[SCRIPTNAME]].log"
cHeaderLines =1
cDelim = ";"



' Get date in YYYYMMDD format
strStamp = Year(Now) & Right("0" & Month(Now), 2) & Right("0" & Day(Now), 2)

' Resolve full paths to all files involved (substitute any metadata tags)
strInputFolder = objFSO.GetAbsolutePathname(cInputFolder)
strArchiveFolder = objFSO.GetAbsolutePathname(cArchiveFolder)
strMergedFile = objFSO.GetAbsolutePathname(Replace(cMergedFile, "[[STAMP]]", strStamp, 1, -1, vbTextCompare))
strHeaderFile = objFSO.GetAbsolutePathname(cHeaderFile)
strLogFile = objFSO.GetAbsolutePathname(Replace(cLogFile, "[[STAMP]]", strStamp, 1, -1, vbTextCompare))
strLogFile = objFSO.GetAbsolutePathname(Replace(strLogFile, "[[SCRIPTNAME]]", strScriptName, 1, -1, vbTextCompare))

' Create input folder if needed, exit if can't be created
If objFSO.FolderExists(strInputFolder) Then
    LogIt strLogFile, "Using existing input folder: """ & strInputFolder & """", True, True
Else
    MakeDir strInputFolder
    If Not objFSO.FolderExists(strInputFolder) Then
        LogIt "", "Could not create input folder: """ & strInputFolder & """", False, True
        Wscript.Quit
    End If
    LogIt strLogFile, "Created input folder: """ & strInputFolder & """", True, True
End If

' Create output folder if needed, exit if can't be created
If objFSO.FolderExists(strArchiveFolder) Then
    LogIt strLogFile, "Using existing archive folder: """ & strArchiveFolder & """", True, True
Else
    MakeDir strArchiveFolder
    If Not objFSO.FolderExists(strArchiveFolder) Then
        LogIt "", "Could not create archive folder: """ & strArchiveFolder & """", False, True
        Wscript.Quit
    End If
    LogIt strLogFile, "Created archive folder: """ & strArchiveFolder & """", True, True
End If

' Exit if header file not found
If Not objFSO.FileExists(strHeaderFile) Then
    LogIt strLogFile, "Could not locate header file: """ & strHeaderFile & """", True, True
    Wscript.Quit
End If

' Copy header file to archive file
objFSO.CopyFile strHeaderFile, strMergedFile, True
LogIt strLogFile, "Using header file: """ & strHeaderFile & """", True, True

' Open output archive file to adde data after header line(s)
Set objMergedFile = objFSO.OpenTextFile(strMergedFile, ForAppending, True)

' Process all files in input folder
Set objFolder = objFSO.GetFolder(strInputFolder)
For Each objFile In objFolder.Files

    ' Make sure it's a CSV file
    If LCase(objFSO.GetExtensionName(objFile.Path)) = "csv" Then

        ' Make sure it's not the new merged file we are building
        If LCase(objFile.Path) <> LCase(strMergedFile) Then

            'LogIt strLogFile, "Merging file: """ & objFile.Path & """", True, True

            ' Open it for reading
            With objFSO.OpenTextFile(objFile, ForReading, False, TriStateUseDefault)

                ' Read each line
                intLine = 0
                Do Until .AtEndOfStream
                    strData = .ReadLine

                    ' Skip any desired "header" lines
                    intLine = intLine + 1
                    If intLine > cHeaderLines Then

                        ' Skip any "empty" lines
                        If Replace(strData,cDelim, "") <> "" Then

                            ' Write this line to merged output file
                            objMergedFile.WriteLine strData

                        End If

                    End If

                Loop

                ' Done with this file, close it
                .Close

            End With

            ' Move this file to archive location
            'LogIt strLogFile, "Archiving file: """ & objFile.Path & """", True, True
            objFile.Move strArchiveFolder & "\"

        End If

    End If

Next
Wscript.Echo "Done"
'Done
objMergedFile.Close
Wscript.Quit()


' Small function to write a line to log file and/or the console
Sub LogIt (strFile, strText, blnFile, blnConsole)
    ' Write to log file if requested
    If blnFile Then
        With objFSO.OpenTextFile(strFile, ForAppending, True)
            .WriteLine FormatDateTime(Now(), vbShortDate) & " " & FormatDateTime(Now(), vbShortTime) & " - " & strText
            .Close()
        End With
    End If

    ' Write to console if requested
    If blnConsole Then
        Wscript.Echo FormatDateTime(Now(), vbShortDate) & " " & FormatDateTime(Now(), vbShortTime) & " - " & strText
    End If
End Sub

Sub MakeDir(ByVal strPath)
    ' Small recursive function to create a folder (and all parent folders in path if needed)
    If Not objFSO.FolderExists(strPath) Then
        ' Make sure parent of this folder exists first
        MakeDir objFSO.GetParentFolderName(strPath)
        ' Now create this folder
        objFSO.CreateFolder strPath
    End If
End Sub

Open in new window

Avatar of Bill Prew
Bill Prew

2-Add in last available columns of appended csv name and creation date of each file involved by the append process
I don't understand this part.


»bp
I think this would take care of all but #2, which isn't clear to me...

'Create the file system object for creating folders:
Set objFSO = CreateObject("Scripting.FileSystemObject")


' Text file I/O constants
Const ForReading = 1
Const ForWriting = 2
Const ForAppending = 8
Const TristateTrue = -1
Const TristateFalse = 0
Const TristateUseDefault = -2

' Define folders and files to work with

strScriptName = Replace(WScript.ScriptName, ".vbs", "", 1, -1, vbTextCompare)
strScriptDir = Replace(WScript.ScriptFullName, WScript.ScriptName, "")
cInputFolder = strScriptDir & "\Input"
cArchiveFolder = strScriptDir & "\Archive\[[STAMP]]"
cLogFolder = strScriptDir & "\Log"
cMergedFile = strScriptDir  & "\Input\[[STAMP]]_Reported_Name.csv"
cHeaderFile = strScriptDir & "Header.csv"
cLogFile = strLogDir  & "\[[STAMP]]_[[SCRIPTNAME]].log"
cHeaderLines =1
cDelim = ";"



' Get date in YYYYMMDD format
strStamp = TimeStamp(Now())

' Resolve full paths to all files involved (substitute any metadata tags)
strInputFolder = objFSO.GetAbsolutePathname(cInputFolder)
strArchiveFolder = objFSO.GetAbsolutePathname(cArchiveFolder)
strMergedFile = objFSO.GetAbsolutePathname(Replace(cMergedFile, "[[STAMP]]", strStamp, 1, -1, vbTextCompare))
strHeaderFile = objFSO.GetAbsolutePathname(cHeaderFile)
strLogFile = objFSO.GetAbsolutePathname(Replace(cLogFile, "[[STAMP]]", strStamp, 1, -1, vbTextCompare))
strLogFile = objFSO.GetAbsolutePathname(Replace(strLogFile, "[[SCRIPTNAME]]", strScriptName, 1, -1, vbTextCompare))

' Create input folder if needed, exit if can't be created
If objFSO.FolderExists(strInputFolder) Then
    LogIt strLogFile, "Using existing input folder: """ & strInputFolder & """", True, True
Else
    MakeDir strInputFolder
    If Not objFSO.FolderExists(strInputFolder) Then
        LogIt "", "Could not create input folder: """ & strInputFolder & """", False, True
        Wscript.Quit
    End If
    LogIt strLogFile, "Created input folder: """ & strInputFolder & """", True, True
End If

' Create output folder if needed, exit if can't be created
If objFSO.FolderExists(strArchiveFolder) Then
    LogIt strLogFile, "Using existing archive folder: """ & strArchiveFolder & """", True, True
Else
    MakeDir strArchiveFolder
    If Not objFSO.FolderExists(strArchiveFolder) Then
        LogIt "", "Could not create archive folder: """ & strArchiveFolder & """", False, True
        Wscript.Quit
    End If
    LogIt strLogFile, "Created archive folder: """ & strArchiveFolder & """", True, True
End If

' Create log folder if needed, exit if can't be created
If Not objFSO.FolderExists(strLogFolder) Then
    MakeDir strLogFolder
    If Not objFSO.FolderExists(strLogFolder) Then
        LogIt "", "Could not create log folder: """ & strLogFolder & """", False, True
        Wscript.Quit
    End If
    LogIt strLogFile, "Created log folder: """ & strLogFolder & """", True, True
End If

' Exit if header file not found
If Not objFSO.FileExists(strHeaderFile) Then
    LogIt strLogFile, "Could not locate header file: """ & strHeaderFile & """", True, True
    Wscript.Quit
End If

' Copy header file to archive file
objFSO.CopyFile strHeaderFile, strMergedFile, True
LogIt strLogFile, "Using header file: """ & strHeaderFile & """", True, True

' Open output archive file to adde data after header line(s)
Set objMergedFile = objFSO.OpenTextFile(strMergedFile, ForAppending, True)

' Process all files in input folder
Set objFolder = objFSO.GetFolder(strInputFolder)
For Each objFile In objFolder.Files

    ' Make sure it's a CSV file
    If LCase(objFSO.GetExtensionName(objFile.Path)) = "csv" Then

        ' Make sure it's not the new merged file we are building
        If LCase(objFile.Path) <> LCase(strMergedFile) Then

            'LogIt strLogFile, "Merging file: """ & objFile.Path & """", True, True

            ' Open it for reading
            With objFSO.OpenTextFile(objFile, ForReading, False, TriStateUseDefault)

                ' Read each line
                intLine = 0
                Do Until .AtEndOfStream
                    strData = .ReadLine

                    ' Skip any desired "header" lines
                    intLine = intLine + 1
                    If intLine > cHeaderLines Then

                        ' Skip any "empty" lines
                        If Replace(strData,cDelim, "") <> "" Then

                            ' Write this line to merged output file
                            objMergedFile.WriteLine strData

                        End If

                    End If

                Loop

                ' Done with this file, close it
                .Close

            End With

            ' Move this file to archive location
            'LogIt strLogFile, "Archiving file: """ & objFile.Path & """", True, True
            objFile.Move strArchiveFolder & "\"

        End If

    End If

Next
Wscript.Echo "Done"
'Done
objMergedFile.Close
Wscript.Quit()


' Small function to write a line to log file and/or the console
Sub LogIt (strFile, strText, blnFile, blnConsole)
    ' Write to log file if requested
    If blnFile Then
        With objFSO.OpenTextFile(strFile, ForAppending, True)
            .WriteLine FormatDateTime(Now(), vbShortDate) & " " & FormatDateTime(Now(), vbShortTime) & " - " & strText
            .Close()
        End With
    End If

    ' Write to console if requested
    If blnConsole Then
        Wscript.Echo FormatDateTime(Now(), vbShortDate) & " " & FormatDateTime(Now(), vbShortTime) & " - " & strText
    End If
End Sub

Sub MakeDir(ByVal strPath)
    ' Small recursive function to create a folder (and all parent folders in path if needed)
    If Not objFSO.FolderExists(strPath) Then
        ' Make sure parent of this folder exists first
        MakeDir objFSO.GetParentFolderName(strPath)
        ' Now create this folder
        objFSO.CreateFolder strPath
    End If
End Sub

Function TimeStamp(ByVal dtmDateTime)
    ' YYYYMMDD_hhmmss
    TimeStamp = Year(dtmDateTime) & Right("0" & Month(dtmDateTime), 2) & Right("0" & Day(dtmDateTime), 2) & "_" & Right("0" & Hour(dtmDateTime), 2) & Right("0" & Minute(dtmDateTime), 2) & Right("0" & Second(dtmDateTime), 2)
End Function

Open in new window


»bp
Avatar of Luis Diaz

ASKER

Thank you Bill,
Concerning #2:
I want to add in the cMergedFile file the file name and creation date.
For example in Input folder I have
Test.csv;2019/10/20
Test2.csv;2019/10/20
I also want those information in cMergedFile additionally to the content of files located in input folder.
First of all: Use Option Explicit.

1) Not sure, whether having seconds or even time in the folder name makes sense. This will result in a lot of folders.
2) Just add that information to your output string.
3) & 4)  Just set it in SetupOrQuit.

E.g.

Option Explicit

Dim ARCHIVE_PATH, INPUT_PATH, HEADER_FILE_NAME, LOG_FILE_NAME, MERGED_FILE_NAME, CSV_COLUMN_DELIMITER, CSV_HEADER_LINES
Dim FileSystemObject, FilesToProcess, MergedFile

Set FileSystemObject = CreateObject("Scripting.FileSystemObject")
SetupOrQuit
If EnumerateFilesToProcess Then
  InitializeProcessingOrQuit
  ProcessFiles
Else
  LogMessage "No files found.", True
End If

Set FileSystemObject = Nothing
LogMessage "Done.", True
WScript.Quit()

' Main methods (in order of importance).

Sub ProcessFile(AFileName)

  Dim CsvLineCount
  Dim CsvLineData
  Dim File

  Set File = FileSystemObject.GetFile(AFileName)
  With FileSystemObject.OpenTextFile(File, ForReading, False, TriStateUseDefault)
    CsvLineCount = 0
    Do Until .AtEndOfStream
      CsvLineData = .ReadLine
      CsvLineCount = CsvLineCount + 1
      If CsvLineCount > CSV_HEADER_LINES Then
        If Replace(CsvLineData, CSV_COLUMN_DELIMITER, "") <> "" Then
          CsvLineData = CsvLineData + _
            CSV_COLUMN_DELIMITER + AFileName + _
            CSV_COLUMN_DELIMITER + File.DateCreated
          MergedFile.WriteLine CsvLineData
        End If
      End If
    Loop

    .Close
  End With

  File.Move ARCHIVE_PATH & "\"
  Set File = Nothing

End Sub

Sub InitializeProcessingOrQuit()
  
  Const ForAppending = 8

  CreateFolderOrQuit ARCHIVE_PATH, "archive"

  If Not FileSystemObject.FileExists(HEADER_FILE_NAME) Then
    LogMessage "Could not locate header file: """ & HEADER_FILE_NAME & """", True
    WScript.Quit
  End If

  FileSystemObject.CopyFile HEADER_FILE_NAME, MERGED_FILE_NAME, True
  LogMessage "Using header file: """ & HEADER_FILE_NAME & """", True

  Set MergedFile = FileSystemObject.OpenTextFile(MERGED_FILE_NAME, ForAppending, True)

End Sub

Sub ProcessFiles()

  Dim File

  For Each File In FilesToProcess
    LogMessage "Processing " & File & "...", False
    ProcessFile File
  Next

End Sub

Function EnumerateFilesToProcess()

  Dim CanProcessFile, Folder, File

  Set FilesToProcess = CreateObject("Scripting.Dictionary")
  Set Folder = FileSystemObject.GetFolder(INPUT_PATH)
  For Each File In Folder.Files
    CanProcessFile = _
      (LCase(FileSystemObject.GetExtensionName(File.Path)) = "csv") _
      And (LCase(File.Path) <> LCase(MERGED_FILE_NAME))
    If CanProcessFile Then
      FilesToProcess.Add File.Path, False
    End If
  Next

  Set File = Nothing
  Set Folder = Nothing

  EnumerateFilesToProcess = (FilesToProcess.Count > 0)

End Function

Sub SetupOrQuit()

  Dim ScriptName, ScriptPath, Stamp

  ARCHIVE_PATH = "[[SCRIPT_PATH]]\Archive\[[STAMP]]\"
  INPUT_PATH = "[[SCRIPT_PATH]]\Result\"
  MERGED_FILE_NAME = "[[SCRIPT_PATH]]\Result\[[STAMP]]_Reported_Name.csv"
  HEADER_FILE_NAME = "[[SCRIPT_PATH]]\Header.csv"
  LOG_FILE_NAME = "[[SCRIPT_PATH]]\Result\[[STAMP]]_[[SCRIPTNAME]].log"

  CSV_COLUMN_DELIMITER = ";"
  CSV_HEADER_LINES =1

  ScriptName = Replace(WScript.ScriptName, ".vbs", "", 1, -1, vbTextCompare)
  LOG_FILE_NAME = Replace(LOG_FILE_NAME, "[[SCRIPTNAME]]", ScriptName)

  ScriptPath = Replace(WScript.ScriptFullName, WScript.ScriptName, "")
  ARCHIVE_PATH = Replace(ARCHIVE_PATH, "[[SCRIPT_PATH]]", ScriptPath)
  INPUT_PATH = Replace(INPUT_PATH, "[[SCRIPT_PATH]]", ScriptPath)
  MERGED_FILE_NAME = Replace(MERGED_FILE_NAME, "[[SCRIPT_PATH]]", ScriptPath)
  HEADER_FILE_NAME = Replace(HEADER_FILE_NAME, "[[SCRIPT_PATH]]", ScriptPath)
  LOG_FILE_NAME = Replace(LOG_FILE_NAME, "[[SCRIPT_PATH]]", ScriptPath)

  Stamp = CurrentTimeStamp(True) ' YYYYMMDD_HHNNSS
  ARCHIVE_PATH = Replace(ARCHIVE_PATH, "[[STAMP]]", Stamp)
  MERGED_FILE_NAME = Replace(MERGED_FILE_NAME, "[[STAMP]]", Stamp)

  Stamp = CurrentTimeStamp(True) ' YYYYMMDD
  LOG_FILE_NAME = Replace(LOG_FILE_NAME, "[[STAMP]]", Stamp)

  ARCHIVE_PATH = FileSystemObject.GetAbsolutePathname(ARCHIVE_PATH)
  INPUT_PATH = FileSystemObject.GetAbsolutePathname(INPUT_PATH)
  MERGED_FILE_NAME = FileSystemObject.GetAbsolutePathname(MERGED_FILE_NAME)
  HEADER_FILE_NAME = FileSystemObject.GetAbsolutePathname(HEADER_FILE_NAME)
  LOG_FILE_NAME = FileSystemObject.GetAbsolutePathname(LOG_FILE_NAME)

  LogMessage "ARCHIVE_PATH:     " & ARCHIVE_PATH, False
  LogMessage "INPUT_PATH:       " & INPUT_PATH, False
  LogMessage "MERGED_FILE_NAME: " & MERGED_FILE_NAME, False
  LogMessage "HEADER_FILE_NAME: " & HEADER_FILE_NAME, False
  LogMessage "LOG_FILE_NAME:    " & LOG_FILE_NAME, False

  If Not FileSystemObject.FolderExists(INPUT_PATH) Then
    LogMessage "INPUT_PATH does not exist.", True
    WScript.Quit
  End If

End Sub

' Helper methods.

Sub CreateFolderOrQuit(APath, APathType)

  If FileSystemObject.FolderExists(APath) Then
    LogMessage "Using existing " & APathType & " folder: """ & APath & """", True
  Else
    MakeDirRecursive APath
    If Not FileSystemObject.FolderExists(APath) Then
      LogMessage "", "Could not create " & APathType & " folder: """ & APath & """", True
      Wscript.Quit
    End If

    LogMessage "Created " & APathType & " folder: """ & APath & """", True
  End If

End Sub

Function CurrentTimeStamp(ADateOnly)

  CurrentTimeStamp = Year(Now) & Right("0" & Month(Now), 2) & Right("0" & Day(Now), 2)
  If Not ADateOnly Then
    CurrentTimeStamp = CurrentTimeStamp & "_" & Right("0" & Hour(Now), 2) & Right("0" & Minute(Now), 2) & Right("0" & Second(Now), 2)
  End If

End Function

Sub LogMessage(strText, blnFile)

  Const ForAppending = 8

  WScript.Echo strText
  If blnFile Then
    With FileSystemObject.OpenTextFile(LOG_FILE_NAME, ForAppending, True)
      .WriteLine FormatDateTime(Now(), vbShortDate) & " " & FormatDateTime(Now(), vbShortTime) & " - " & strText
      .Close()
    End With
  End If

End Sub

Sub MakeDirRecursive(APath)

  If Not FileSystemObject.FolderExists(APath) Then
    MakeDirRecursive FileSystemObject.GetParentFolderName(APath)
    FileSystemObject.CreateFolder APath
  End If

End Sub

Open in new window

@Bill: I tested your proposal but I
The following error messageUser generated imageWhich is not in the previous script.
Thank you in advance for your help.
@ste5an:
I tested your proposal but I got the following:
User generated imageThank you for your help.
Did you change the code before line 6? Cause the FileSystemObject is the first line of code, which needs to be executed.

Otherwise you can safe-guard it:

Option Explicit

Dim ARCHIVE_PATH, INPUT_PATH, HEADER_FILE_NAME, LOG_FILE_NAME, MERGED_FILE_NAME, CSV_COLUMN_DELIMITER, CSV_HEADER_LINES
Dim FileSystemObject, FilesToProcess, MergedFile

EnsureFileSystemObject
SetupOrQuit
If EnumerateFilesToProcess Then
  InitializeProcessingOrQuit
  ProcessFiles
Else
  LogMessage "No files found.", True
End If

LogMessage "Done.", True
WScript.Quit()

' Main methods (in order of importance).

Sub ProcessFile(AFileName)

  Dim CsvLineCount
  Dim CsvLineData
  Dim File

  EnsureFileSystemObject
  Set File = FileSystemObject.GetFile(AFileName)
'  With FileSystemObject.OpenTextFile(File, ForReading, False, TriStateUseDefault)
'    CsvLineCount = 0
'    Do Until .AtEndOfStream
'      CsvLineData = .ReadLine
'      CsvLineCount = CsvLineCount + 1
'      If CsvLineCount > CSV_HEADER_LINES Then
'        If Replace(CsvLineData, CSV_COLUMN_DELIMITER, "") <> "" Then
'          CsvLineData = CsvLineData + _
'            CSV_COLUMN_DELIMITER + AFileName + _
'            CSV_COLUMN_DELIMITER + File.DateCreated
'          MergedFile.WriteLine CsvLineData
'        End If
'      End If
'    Loop
'
'    .Close
'  End With

  File.Move ARCHIVE_PATH & "\"
  Set File = Nothing

End Sub

Sub InitializeProcessingOrQuit()

  Const ForAppending = 8

  CreateFolderOrQuit ARCHIVE_PATH, "archive"

  EnsureFileSystemObject
  If Not FileSystemObject.FileExists(HEADER_FILE_NAME) Then
    LogMessage "Could not locate header file: """ & HEADER_FILE_NAME & """", True
    WScript.Quit
  End If

  FileSystemObject.CopyFile HEADER_FILE_NAME, MERGED_FILE_NAME, True
  LogMessage "Using header file: """ & HEADER_FILE_NAME & """", True

  Set MergedFile = FileSystemObject.OpenTextFile(MERGED_FILE_NAME, ForAppending, True)

End Sub

Sub ProcessFiles()

  Dim File

  For Each File In FilesToProcess
    LogMessage "Processing " & File & "...", False
    ProcessFile File
  Next

End Sub

Function EnumerateFilesToProcess()

  Dim CanProcessFile, Folder, File

  Set FilesToProcess = CreateObject("Scripting.Dictionary")
  EnsureFileSystemObject
  Set Folder = FileSystemObject.GetFolder(INPUT_PATH)
  For Each File In Folder.Files
    CanProcessFile = _
      (LCase(FileSystemObject.GetExtensionName(File.Path)) = "csv") _
      And (LCase(File.Path) <> LCase(MERGED_FILE_NAME))
    If CanProcessFile Then
      FilesToProcess.Add File.Path, False
    End If
  Next

  Set File = Nothing
  Set Folder = Nothing

  EnumerateFilesToProcess = (FilesToProcess.Count > 0)

End Function

Sub SetupOrQuit()

  Dim ScriptName, ScriptPath, Stamp

  ARCHIVE_PATH = "[[SCRIPT_PATH]]\Archive\[[STAMP]]\"
  INPUT_PATH = "[[SCRIPT_PATH]]\Result\"
  MERGED_FILE_NAME = "[[SCRIPT_PATH]]\Result\[[STAMP]]_Reported_Name.csv"
  HEADER_FILE_NAME = "[[SCRIPT_PATH]]\Header.csv"
  LOG_FILE_NAME = "[[SCRIPT_PATH]]\Result\[[STAMP]]_[[SCRIPTNAME]].log"

  CSV_COLUMN_DELIMITER = ";"
  CSV_HEADER_LINES =1

  ScriptName = Replace(WScript.ScriptName, ".vbs", "", 1, -1, vbTextCompare)
  LOG_FILE_NAME = Replace(LOG_FILE_NAME, "[[SCRIPTNAME]]", ScriptName)

  ScriptPath = Replace(WScript.ScriptFullName, WScript.ScriptName, "")
  ARCHIVE_PATH = Replace(ARCHIVE_PATH, "[[SCRIPT_PATH]]", ScriptPath)
  INPUT_PATH = Replace(INPUT_PATH, "[[SCRIPT_PATH]]", ScriptPath)
  MERGED_FILE_NAME = Replace(MERGED_FILE_NAME, "[[SCRIPT_PATH]]", ScriptPath)
  HEADER_FILE_NAME = Replace(HEADER_FILE_NAME, "[[SCRIPT_PATH]]", ScriptPath)
  LOG_FILE_NAME = Replace(LOG_FILE_NAME, "[[SCRIPT_PATH]]", ScriptPath)

  Stamp = CurrentTimeStamp(True) ' YYYYMMDD_HHNNSS
  ARCHIVE_PATH = Replace(ARCHIVE_PATH, "[[STAMP]]", Stamp)
  MERGED_FILE_NAME = Replace(MERGED_FILE_NAME, "[[STAMP]]", Stamp)

  Stamp = CurrentTimeStamp(True) ' YYYYMMDD
  LOG_FILE_NAME = Replace(LOG_FILE_NAME, "[[STAMP]]", Stamp)

  EnsureFileSystemObject
  ARCHIVE_PATH = FileSystemObject.GetAbsolutePathname(ARCHIVE_PATH)
  INPUT_PATH = FileSystemObject.GetAbsolutePathname(INPUT_PATH)
  MERGED_FILE_NAME = FileSystemObject.GetAbsolutePathname(MERGED_FILE_NAME)
  HEADER_FILE_NAME = FileSystemObject.GetAbsolutePathname(HEADER_FILE_NAME)
  LOG_FILE_NAME = FileSystemObject.GetAbsolutePathname(LOG_FILE_NAME)

  LogMessage "ARCHIVE_PATH:     " & ARCHIVE_PATH, False
  LogMessage "INPUT_PATH:       " & INPUT_PATH, False
  LogMessage "MERGED_FILE_NAME: " & MERGED_FILE_NAME, False
  LogMessage "HEADER_FILE_NAME: " & HEADER_FILE_NAME, False
  LogMessage "LOG_FILE_NAME:    " & LOG_FILE_NAME, False

  If Not FileSystemObject.FolderExists(INPUT_PATH) Then
    LogMessage "INPUT_PATH does not exist.", True
    WScript.Quit
  End If

End Sub

' Helper methods.

Sub CreateFolderOrQuit(APath, APathType)

  EnsureFileSystemObject
  If FileSystemObject.FolderExists(APath) Then
    LogMessage "Using existing " & APathType & " folder: """ & APath & """", True
  Else
    MakeDirRecursive APath
    If Not FileSystemObject.FolderExists(APath) Then
      LogMessage "", "Could not create " & APathType & " folder: """ & APath & """", True
      Wscript.Quit
    End If

    LogMessage "Created " & APathType & " folder: """ & APath & """", True
  End If

End Sub

Function CurrentTimeStamp(ADateOnly)

  CurrentTimeStamp = Year(Now) & Right("0" & Month(Now), 2) & Right("0" & Day(Now), 2)
  If Not ADateOnly Then
    CurrentTimeStamp = CurrentTimeStamp & "_" & Right("0" & Hour(Now), 2) & Right("0" & Minute(Now), 2) & Right("0" & Second(Now), 2)
  End If

End Function

Sub EnsureFileSystemObject()

  On Error Resume Next

  If FileSystemObject Is Nothing Then
    Set FileSystemObject = CreateObject("Scripting.FileSystemObject")
  End If

End Sub

Sub LogMessage(strText, blnFile)

  Const ForAppending = 8

  WScript.Echo strText
  If blnFile Then
    EnsureFileSystemObject
    With FileSystemObject.OpenTextFile(LOG_FILE_NAME, ForAppending, True)
      .WriteLine FormatDateTime(Now(), vbShortDate) & " " & FormatDateTime(Now(), vbShortTime) & " - " & strText
      .Close()
    End With
  End If

End Sub

Sub MakeDirRecursive(APath)

  EnsureFileSystemObject
  If Not FileSystemObject.FolderExists(APath) Then
    MakeDirRecursive FileSystemObject.GetParentFolderName(APath)
    FileSystemObject.CreateFolder APath
  End If

End Sub

Open in new window

ste5an:

I tested last version but I got the following message:
"No File found" and all my csv files are in Input folder.

Thank you for your help.
Sorry, I still don't understand this part:

Concerning #2:
I want to add in the cMergedFile file the file name and creation date.
For example in Input folder I have
Test.csv;2019/10/20
Test2.csv;2019/10/20
I also want those information in cMergedFile additionally to the content of files located in input folder.


»bp
Corrected a couple of bugs in the proir version I posted, try this.

'Create the file system object for creating folders:
Set objFSO = CreateObject("Scripting.FileSystemObject")


' Text file I/O constants
Const ForReading = 1
Const ForWriting = 2
Const ForAppending = 8
Const TristateTrue = -1
Const TristateFalse = 0
Const TristateUseDefault = -2

' Define folders and files to work with

strScriptName = Replace(WScript.ScriptName, ".vbs", "", 1, -1, vbTextCompare)
strScriptDir = Replace(WScript.ScriptFullName, WScript.ScriptName, "")
cInputFolder = strScriptDir & "Input"
cArchiveFolder = strScriptDir & "Archive\[[STAMP]]"
cLogFolder = strScriptDir & "Log"
cMergedFile = cArchiveFolder  & "\[[STAMP]]_Reported_Name.csv"
cHeaderFile = strScriptDir & "Header.csv"
cLogFile = cLogFolder  & "\[[STAMP]]_[[SCRIPTNAME]].log"
cHeaderLines =1
cDelim = ";"



' Get date in YYYYMMDD format
strStamp = TimeStamp(Now())

' Resolve full paths to all files involved (substitute any metadata tags)
strInputFolder = objFSO.GetAbsolutePathname(cInputFolder)
strArchiveFolder = objFSO.GetAbsolutePathname(Replace(cArchiveFolder, "[[STAMP]]", strStamp, 1, -1, vbTextCompare))
strMergedFile = objFSO.GetAbsolutePathname(Replace(cMergedFile, "[[STAMP]]", strStamp, 1, -1, vbTextCompare))
strHeaderFile = objFSO.GetAbsolutePathname(cHeaderFile)
strLogFile = objFSO.GetAbsolutePathname(Replace(cLogFile, "[[STAMP]]", strStamp, 1, -1, vbTextCompare))
strLogFile = objFSO.GetAbsolutePathname(Replace(strLogFile, "[[SCRIPTNAME]]", strScriptName, 1, -1, vbTextCompare))

' Create log folder if needed, exit if can't be created
If Not objFSO.FolderExists(cLogFolder) Then
    MakeDir cLogFolder
    If Not objFSO.FolderExists(cLogFolder) Then
        LogIt "", "Could not create log folder: """ & cLogFolder & """", False, True
        Wscript.Quit
    End If
    LogIt strLogFile, "Created log folder: """ & cLogFolder & """", True, True
End If

' Create input folder if needed, exit if can't be created
If objFSO.FolderExists(strInputFolder) Then
    LogIt strLogFile, "Using existing input folder: """ & strInputFolder & """", True, True
Else
    MakeDir strInputFolder
    If Not objFSO.FolderExists(strInputFolder) Then
        LogIt "", "Could not create input folder: """ & strInputFolder & """", False, True
        Wscript.Quit
    End If
    LogIt strLogFile, "Created input folder: """ & strInputFolder & """", True, True
End If

' Create output folder if needed, exit if can't be created
If objFSO.FolderExists(strArchiveFolder) Then
    LogIt strLogFile, "Using existing archive folder: """ & strArchiveFolder & """", True, True
Else
    MakeDir strArchiveFolder
    If Not objFSO.FolderExists(strArchiveFolder) Then
        LogIt "", "Could not create archive folder: """ & strArchiveFolder & """", False, True
        Wscript.Quit
    End If
    LogIt strLogFile, "Created archive folder: """ & strArchiveFolder & """", True, True
End If

' Exit if header file not found
If Not objFSO.FileExists(strHeaderFile) Then
    LogIt strLogFile, "Could not locate header file: """ & strHeaderFile & """", True, True
    Wscript.Quit
End If

' Copy header file to archive file
objFSO.CopyFile strHeaderFile, strMergedFile, True
LogIt strLogFile, "Using header file: """ & strHeaderFile & """", True, True

' Open output archive file to adde data after header line(s)
Set objMergedFile = objFSO.OpenTextFile(strMergedFile, ForAppending, True)

' Process all files in input folder
Set objFolder = objFSO.GetFolder(strInputFolder)
For Each objFile In objFolder.Files

    ' Make sure it's a CSV file
    If LCase(objFSO.GetExtensionName(objFile.Path)) = "csv" Then

        ' Make sure it's not the new merged file we are building
        If LCase(objFile.Path) <> LCase(strMergedFile) Then

            'LogIt strLogFile, "Merging file: """ & objFile.Path & """", True, True

            ' Open it for reading
            With objFSO.OpenTextFile(objFile, ForReading, False, TriStateUseDefault)

                ' Read each line
                intLine = 0
                Do Until .AtEndOfStream
                    strData = .ReadLine

                    ' Skip any desired "header" lines
                    intLine = intLine + 1
                    If intLine > cHeaderLines Then

                        ' Skip any "empty" lines
                        If Replace(strData,cDelim, "") <> "" Then

                            ' Write this line to merged output file
                            objMergedFile.WriteLine strData

                        End If

                    End If

                Loop

                ' Done with this file, close it
                .Close

            End With

            ' Move this file to archive location
            'LogIt strLogFile, "Archiving file: """ & objFile.Path & """", True, True
            objFile.Move strArchiveFolder & "\"

        End If

    End If

Next
Wscript.Echo "Done"
'Done
objMergedFile.Close
Wscript.Quit()


' Small function to write a line to log file and/or the console
Sub LogIt (strFile, strText, blnFile, blnConsole)
    ' Write to log file if requested
    If blnFile Then
        With objFSO.OpenTextFile(strFile, ForAppending, True)
            .WriteLine FormatDateTime(Now(), vbShortDate) & " " & FormatDateTime(Now(), vbShortTime) & " - " & strText
            .Close()
        End With
    End If

    ' Write to console if requested
    If blnConsole Then
        Wscript.Echo FormatDateTime(Now(), vbShortDate) & " " & FormatDateTime(Now(), vbShortTime) & " - " & strText
    End If
End Sub

Sub MakeDir(ByVal strPath)
    ' Small recursive function to create a folder (and all parent folders in path if needed)
    If Not objFSO.FolderExists(strPath) Then
        ' Make sure parent of this folder exists first
        MakeDir objFSO.GetParentFolderName(strPath)
        ' Now create this folder
        objFSO.CreateFolder strPath
    End If
End Sub

Function TimeStamp(ByVal dtmDateTime)
    ' YYYYMMDD_hhmmss
    TimeStamp = Year(dtmDateTime) & Right("0" & Month(dtmDateTime), 2) & Right("0" & Day(dtmDateTime), 2) & "_" & Right("0" & Hour(dtmDateTime), 2) & Right("0" & Minute(dtmDateTime), 2) & Right("0" & Second(dtmDateTime), 2)
End Function

Open in new window


»bp
HHCIB, that's why I prefer clean code.. just do the necessary adjustments:

Option Explicit

Dim ARCHIVE_PATH, INPUT_PATH, HEADER_FILE_NAME, LOG_FILE_NAME, MERGED_FILE_NAME, RESULT_PATH, CSV_COLUMN_DELIMITER, CSV_HEADER_LINES

' [..]

Sub SetupOrQuit()

  Dim ScriptName, ScriptPath, Stamp

  ARCHIVE_PATH = "[[SCRIPT_PATH]]\Archive\[[STAMP]]\"
  INPUT_PATH = "[[SCRIPT_PATH]]\Input\"
  RESULT_PATH = "[[SCRIPT_PATH]]\Result\"
  MERGED_FILE_NAME = "[[SCRIPT_PATH]]\Result\[[STAMP]]_Reported_Name.csv"
  HEADER_FILE_NAME = "[[SCRIPT_PATH]]\Header.csv"
  LOG_FILE_NAME = "[[SCRIPT_PATH]]\Result\[[STAMP]]_[[SCRIPTNAME]].log"

  CSV_COLUMN_DELIMITER = ";"
  CSV_HEADER_LINES =1

  ScriptName = Replace(WScript.ScriptName, ".vbs", "", 1, -1, vbTextCompare)
  LOG_FILE_NAME = Replace(LOG_FILE_NAME, "[[SCRIPTNAME]]", ScriptName)

  ScriptPath = Replace(WScript.ScriptFullName, WScript.ScriptName, "")
  ARCHIVE_PATH = Replace(ARCHIVE_PATH, "[[SCRIPT_PATH]]", ScriptPath)
  INPUT_PATH = Replace(INPUT_PATH, "[[SCRIPT_PATH]]", ScriptPath)
  RESULT_PATH = Replace(RESULT_PATH, "[[SCRIPT_PATH]]", ScriptPath)
  MERGED_FILE_NAME = Replace(MERGED_FILE_NAME, "[[SCRIPT_PATH]]", ScriptPath)
  HEADER_FILE_NAME = Replace(HEADER_FILE_NAME, "[[SCRIPT_PATH]]", ScriptPath)
  LOG_FILE_NAME = Replace(LOG_FILE_NAME, "[[SCRIPT_PATH]]", ScriptPath)

  Stamp = CurrentTimeStamp(True) ' YYYYMMDD_HHNNSS
  ARCHIVE_PATH = Replace(ARCHIVE_PATH, "[[STAMP]]", Stamp)
  MERGED_FILE_NAME = Replace(MERGED_FILE_NAME, "[[STAMP]]", Stamp)

  Stamp = CurrentTimeStamp(True) ' YYYYMMDD
  LOG_FILE_NAME = Replace(LOG_FILE_NAME, "[[STAMP]]", Stamp)

  EnsureFileSystemObject
  ARCHIVE_PATH = FileSystemObject.GetAbsolutePathname(ARCHIVE_PATH)
  INPUT_PATH = FileSystemObject.GetAbsolutePathname(INPUT_PATH)
  RESULT_PATH = FileSystemObject.GetAbsolutePathname(RESULT_PATH)
  MERGED_FILE_NAME = FileSystemObject.GetAbsolutePathname(MERGED_FILE_NAME)
  HEADER_FILE_NAME = FileSystemObject.GetAbsolutePathname(HEADER_FILE_NAME)
  LOG_FILE_NAME = FileSystemObject.GetAbsolutePathname(LOG_FILE_NAME)

  LogMessage "ARCHIVE_PATH:     " & ARCHIVE_PATH, False
  LogMessage "INPUT_PATH:       " & INPUT_PATH, False
  LogMessage "RESULT_PATH:      " & RESULT_PATH, False
  LogMessage "MERGED_FILE_NAME: " & MERGED_FILE_NAME, False
  LogMessage "HEADER_FILE_NAME: " & HEADER_FILE_NAME, False
  LogMessage "LOG_FILE_NAME:    " & LOG_FILE_NAME, False

  If Not FileSystemObject.FolderExists(INPUT_PATH) Then
    LogMessage "INPUT_PATH does not exist.", True
    WScript.Quit
  End If

End Sub

' [..]

Open in new window

Thank you for those updates.
I will test them and keep you informed.
@Bill:
#2 File name and Modified date should be part of the append process.
As you posted at the following solution: https://www.experts-exchange.com/questions/29154094/Powershell-Windows-batch-append-multiple-log-files.html?anchorAnswerId=42917496#a42917496
I would like to take as a reference filename and date modified in the cMergedFile.
Let me know if it is clearer.
ASKER CERTIFIED SOLUTION
Avatar of Bill Prew
Bill Prew

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Bill: I tested and last version covers all the requirements!
Thank you again for your great support!
Welcome, glad that got it.


»bp