Luis Diaz
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.
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
I think this would take care of all but #2, which isn't clear to me...
»bp
'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
»bp
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.
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.
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
ASKER
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:
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
ASKER
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.
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:
»bp
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.
»bp
'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
»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
' [..]
ASKER
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Bill: I tested and last version covers all the requirements!
Thank you again for your great support!
Thank you again for your great support!
Welcome, glad that got it.
»bp
»bp
»bp