Count Files in a Directory by Created Date

IshmaelDS · Sep 16, 2009

I have been trying to get this to work but can't figure out my next step. I'm new to vbscript.
Basically I'm writing a log to a directory multiple times a day. I need to Count how many times that log file is created each day, and if it succeded or not. The Log file name has a tag depending on certain results. Here is my code. I'm having problems figuring out how to get the list of files by date, and keep the count going. I would like it to count all the files with a created date of X and then write that count, then go on to a created date of X+1.

On Error Resume Next
Dim fso, folder, files, NewsFile, LogFolder, LogDir, DateTest, createdate
Dim FailCount, DuplicateCount, TNcount, NoResults, VCcount
Const ForReading = 1
Const ForWriting = 2
Const ForAppending = 8
Set fso = CreateObject("Scripting.FileSystemObject")
LogFolder = "\\serverpath\Results\"
LogDir = "\\serverpath\Results\"
Set folder = fso.GetFolder(LogFolder)
Set files = folder.Files
VCcount = 0
TNcount = 0
FailCount = 0
DuplicateCount = 0
NoResults = 0
For Each folderIdx In files
' Set DateTest=FSO.GetFile(folderidx.name)
createdate = folderidx.DateCreated
DateTest = (right("00" & month(createdate),2) & "-" & right("00" & day(createdate),2))
Set NewFile = fso.CreateTextFile("c:\Macro\LogReport.txt", True)
' msgbox DateTest
If instr(folderidx.name, "Import Log-TN") > 0 Then
VCcount = VCcount + 1
Elseif Ifinstr(folderidx.name, "Import Log-VC") > 0 Then
TNcount = TNcount + 1
ElseIf Ifinstr(folderidx.name, "Import Log-Fa") > 0 Then
FailCount = FailCount + 1
ElseIf Ifinstr(folderidx.name, "Import Log-Or") > 0 Then
DuplicateCount = DuplicateCount + 1
ElseIf Ifinstr(folderidx.name, "Import Log-Day-") 0 Then
NoResults = NoResults + 1
End if

NewFile.WriteLine(DateTest)
NewFile.WriteLine(FailCount)
NewFile.WriteLine(DuplicateCount)
NewFile.WriteLine(TNcount)
NewFile.WriteLine(VCcount)
NewFile.Close

Geates · Sep 17, 2009

if your log files overwrite each other eventually, then you will want to use .DateLastModified instead of .DateCreated. The reason (from what I understand) is because the create date will never change for a file that has the same namespace (filename). It's viewed as a modification. Try:

Code:

modifydate = folderidx.DateLastModified

If this doesn't help, can you post the file structure of the data you are trying to filter.

-Geates

PS. There was a recent post about createdate

http://www.tek-tips.com/viewthread.cfm?qid=1568289&page=2

IshmaelDS · Sep 17, 2009

I'm not having trouble with the date. The log's wont overwrite each other. What I'm having trouble doing is I need to count the files by day by class.
This is an example of the output I want.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
09/16
FailCount=5
DuplicateCount=3
TNCount=55
VCCount=60

09/15
FailCount=3
DuplicateCount=6
TNCount=65
VCCount=50

Ect.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

The file list i'm looking at is like so

Import Log-TN000146124-Results.txt
Import Log-TN000146123-Results.txt
Import Log-TN000146122-Results.txt
Import Log-VC000131155-Results.txt
Import Log-VC000131154-Results.txt
Import Log-VC000131153-Results.txt
Import Log-ORDER ALREADY EXISTS (VC000130968-Results.txt
Import Log-FailedDay-16-9-Time-9-53-32-Results.txt
Import Log-Day-6-7-Time-10-26-15-Results-NotAvailable.txt

So for this list the log would write out
09/17
FailCount=1
DuplicateCount=1
TNCount=3
VCCount=3

Geates · Sep 17, 2009

I see one major problem with your code. It is recreating LogResults.txt for every single file! Move

Code:

Set NewFile = fso.CreateTextFile("c:\Macro\LogReport.txt", True)

outside of the for loop.

-Geates

IshmaelDS · Sep 17, 2009

I got it. Thanks for the tips.

Geates · Sep 17, 2009

Ohhh, I see the problem. You need to keep multiple counts for multiple instances of import logs by date..

Code:

set objShell = CreateObject("WScript.Shell")
objShell.Run ("%comspec% /c dir *.txt /od > temp.txt")

will dump a list of .txt filesto temp.txt in lowercase and sorted by date (oldest first). Then you can iterate temp.txt and count.

Here's the code

Code:

CONST FOR_READING  = 1

strLogFolder = "c:\temp\logs"
strLogFile   = strLogFolder & "\logs.txt"
strLogReport = strLogFolder & "\LogReport.txt"

set objFSO   = CreateObject("Scripting.FileSystemObject")
set objShell = CreateObject("WScript.Shell")

objShell.Run "%comspec% /c dir " & strLogFolder & "\*.txt /od > " & strLogFile

wscript.sleep 2000 'give time to left the previous command finish

set objStream = objFSO.OpenTextFile(strLogFile, FOR_READING)
set objOutputFile = objFSO.CreateTextFile(strLogReport, True)

do while not objStream.AtEndOfStream
	strLine = objStream.ReadLine
	boolWrite = false
	if (inStr(strLine, ".txt")) then
		strDate = left(strLine, inStr(strLine, " "))
		if (strDate <> strDateBuffer) then
			if (strDateBuffer <> "") then boolWrite = true
			strDateBuffer = strDate
			intTNCount = 0
			intVCCount = 0
			intFailCount = 0
			intDupCount = 0
		end if

		strFileName = trim(right(strLine, len(strLine) - inStrRev(strLine, "Import") + 1))
		select case(mid(ucase(strFileName), 12, 2))
			case "TN" : intTNCount = intTNCount + 1
			case "VC" : intVCCount = intVCCount + 1
			case "FA" : intFailCount = intFailCount + 1
			case "OR" : intDupCount = intDupCount + 1
		end select
	end if

	if (boolWrite) then
		objOutputFile.WriteLine(strDate)
		objOutputFile.WriteLine("TNCount = " & intTNCount)
		objOutputFile.WriteLine("VCCount = " & intVCCount)
		objOutputFile.WriteLine("FailCount = " & intFailCount)
		objOutputFile.WriteLine("DuplicateCount = " & intDupCount)
	end if

loop

-Geates

IshmaelDS · Sep 18, 2009

The way i went about it was slightly different. I started nesting.

I set a Do Loop around my For Next. So my DO Loop checks the date and sees if it matches a the current system date, if it does it then runs the for next to get the data from the log files. Then I subtract one from the current system date and run the do loop again, and repeat always subtracting 1+x from the current system date untill it reaches the date I set in the script to end the do loop. (I also changed it so now I read the file and grab some data from it) I was haveing a few problems with the file name checks so I found a function(can't remember where) that works for me. Here's the code.

Code:

On Error Resume Next
  Dim FailCount, DuplicateCount, TNcount, NoResults, VCcount, FileCount
  Dim fso, folder, file, NewsFile, LogFolder, LogDir, DateTest, CreateDate, datelist, Count, AppendFile
  Dim ResultsFile, ImportFile, ImportLine, ItemCountTN, Items, ImportFullFile, ReadThroughFile, ItemCountVC, ItemCount
  Const ForReading = 1
  Const ForWriting = 2
  Const ForAppending = 8
  Set FSO = CreateObject("Scripting.FileSystemObject")
  LogFolder = "\\server\path\"
  LogDir = "\\server\path\"
  Set folder = fso.GetFolder(LogFolder)
  Set folderidx = folder.Files
'Create Log file and setup columns"
  Set NewFile = fso.CreateTextFile("c:\Macro\LogReport.csv", True)
  NewFile.WriteLine("Order Import Log")
  NewFile.WriteLine("Date,TNCount,VCCount,FailCount,DuplicateCount,NoResultsCount,FileCount,ItemCount")
  NewFile.Close
  Count = 0
'Set Log File for open for appending
  Set AppendFile = FSO.OpenTextFile("c:\Macro\LogReport.csv", ForAppending, True)
  Do
'Set Date to check for
	  DateTest = Date()-Count
	  DateList = right("00" & day(DateTest),2) & "-" & right("00" & month(DateTest),2) & "-" & year(DateTest)
	  FailCount = 0
	  DuplicateCount = 0
	  NoResults = 0
	  FileCount = 0
	  TNCount = 0
	  VCCount = 0
	  ItemCount = 0
	  For Each file In folderidx
'Check date of files
		CreateDate = file.DateCreated
		CreateDate = right("00" & day(CreateDate),2) & "-" & right("00" & month(CreateDate),2) & "-" & year(CreateDate)
		IF DateList = CreateDate Then
		  FileCount = Filecount + 1
			If RegExpTest(file, "Import Log-Day-") Then
				NoResults = NoResults + 1
			ElseIf RegExpTest(file, "Import Log-TN") Then
					'Start Count of items section
					'Open the import results file to find the import file
					Set ResultsFile = fso.OpenTextFile(file, ForReading)
					ReadThroughFile = 0
					Do Until ReadThroughFile=4
					ImportFile = ResultsFile.ReadLine
					ImportFile = Trim(ImportFile)
					'Msgbox ImportFile
					If RegExpTest(ImportFile, "-A.txt") or RegExpTest(ImportFile, "-1.txt") or RegExpTest(ImportFile, "_dPO") or RegExpTest(ImportFile, "-a.txt") or RegExpTest(ImportFile, "_ddPO.txt") or RegExpTest(ImportFile, "_pTerm.txt") or RegExpTest(ImportFile, "_CreditTerm.txt") or RegExpTest(ImportFile, "_crTerm.txt") or RegExpTest(ImportFile, "_LocationError.txt") or RegExpTest(ImportFile, "_LCOATION.txt") or RegExpTest(ImportFile, "-PO-Incorrect.txt") or RegExpTest(ImportFile, "_VISA.txt") or RegExpTest(ImportFile, "-not-imported.txt") or RegExpTest(ImportFile, "_cpu.txt") or RegExpTest(ImportFile, "_emptyPaymentMethod.txt") then
						ReadThroughFile=4
						ImportFile = Right(Left(ImportFile,121),16)
						ImportFile = Trim(ImportFile)
						ResultsFile.Close
						'msgbox importfile
						'Open the Import file and read the contents to find out the items ordered and sum them.
						Set ImportFullFile = FSO.OpenTextFile("\\server\path\"&ImportFile, ForReading)
						ImportFile.Close
						Do Until ImportFullFile.AtEndOfStream
						ImportLine = ImportFullFile.ReadLine
						If RegExpTest(ImportLine, "ITM") Then
							If Mid(ImportLine,18,1) = "^" Then
								Items = Mid(ImportLine,17,1)
								ItemCount = ItemCount + Items
							ElseIf Mid(ImportLine,19,1) = "^" Then
								Items = Mid(ImportLine,17,2)
								ItemCount = ItemCount + Items
							ElseIf Mid(ImportLine,20,1) = "^" Then
								Items = Mid(ImportLine,17,3)
								ItemCount = ItemCount + Items
							End If
						Else   
						End If
						Loop
						ImportFullFile.Close
					ElseIf RegExpTest(ImportFile,".txt") Then 
						ReadThroughFile=4
						ImportFile = Right(Left(ImportFile,119),14)
						ImportFile = Trim(ImportFile)
						ResultsFile.Close
						'msgbox importfile
						'Open the Import file and read the contents to find out the items ordered and sum them.
						Set ImportFullFile = FSO.OpenTextFile("\\server\path\"&ImportFile, ForReading)
						ImportFile.Close
						Do Until ImportFullFile.AtEndOfStream
						ImportLine = ImportFullFile.ReadLine
						If RegExpTest(ImportLine, "ITM") Then
							If Mid(ImportLine,18,1) = "^" Then
								Items = Mid(ImportLine,17,1)
								ItemCount = ItemCount + Items
							ElseIf Mid(ImportLine,19,1) = "^" Then
								Items = Mid(ImportLine,17,2)
								ItemCount = ItemCount + Items
							ElseIf Mid(ImportLine,20,1) = "^" Then
								Items = Mid(ImportLine,17,3)
								ItemCount = ItemCount + Items
							End If
						Else   
						End If
						Loop
						ImportFullFile.Close
					Else
					ReadThroughFile=ReadThroughFile+1
					End If
					Loop
					'msgbox "done file"
				TNcount = TNcount + 1
			ElseIf RegExpTest(file, "Import Log-FailedDay") Then
				FailCount = FailCount + 1
			ElseIf RegExpTest(file, "Import Log-VC") Then
					'Start Count of items section
					'Open the import results file to find the import file
					Set ResultsFile = fso.OpenTextFile(file, ForReading)
					ReadThroughFile = 0
					Do Until ReadThroughFile=4
					ImportFile = ResultsFile.ReadLine
					ImportFile = Trim(ImportFile)
					'Msgbox ImportFile 
					If RegExpTest(ImportFile, "-A.txt") or RegExpTest(ImportFile, "-1.txt") or RegExpTest(ImportFile, "_dPO") or RegExpTest(ImportFile, "-a.txt") or RegExpTest(ImportFile, "_ddPO.txt") or RegExpTest(ImportFile, "_pTerm.txt") or RegExpTest(ImportFile, "_CreditTerm.txt") or RegExpTest(ImportFile, "_crTerm.txt") or RegExpTest(ImportFile, "_LocationError.txt") or RegExpTest(ImportFile, "_LCOATION.txt") or RegExpTest(ImportFile, "-PO-Incorrect.txt") or RegExpTest(ImportFile, "_VISA.txt") or RegExpTest(ImportFile, "-not-imported.txt") or RegExpTest(ImportFile, "_cpu.txt") or RegExpTest(ImportFile, "_emptyPaymentMethod.txt") then
						ReadThroughFile=4
						ImportFile = Right(Left(ImportFile,121),16)
						ImportFile = Trim(ImportFile)
						ResultsFile.Close
						'msgbox importfile
						'Open the Import file and read the contents to find out the items ordered and sum them.
						Set ImportFullFile = FSO.OpenTextFile("\\server\path\"&ImportFile, ForReading)
						ImportFile.Close
						Do Until ImportFullFile.AtEndOfStream
						ImportLine = ImportFullFile.ReadLine
						If RegExpTest(ImportLine, "ITM") Then
							If Mid(ImportLine,18,1) = "^" Then
								Items = Mid(ImportLine,17,1)
								ItemCount = ItemCount + Items
							ElseIf Mid(ImportLine,19,1) = "^" Then
								Items = Mid(ImportLine,17,2)
								ItemCount = ItemCount + Items
							ElseIf Mid(ImportLine,20,1) = "^" Then
								Items = Mid(ImportLine,17,3)
								ItemCount = ItemCount + Items
							End If
						Else   
						End If
						Loop
						ImportFullFile.Close
					ElseIf RegExpTest(ImportFile,".txt") Then 
						ReadThroughFile=4
						ImportFile = Right(Left(ImportFile,119),14)
						ImportFile = Trim(ImportFile)
						ResultsFile.Close
						'msgbox importfile
						'Open the Import file and read the contents to find out the items ordered and sum them.
						Set ImportFullFile = FSO.OpenTextFile("\\server\path\"&ImportFile, ForReading)
						ImportFile.Close
						Do Until ImportFullFile.AtEndOfStream
						ImportLine = ImportFullFile.ReadLine
						If RegExpTest(ImportLine, "ITM") Then
							If Mid(ImportLine,18,1) = "^" Then
								Items = Mid(ImportLine,17,1)
								ItemCount = ItemCount + Items
							ElseIf Mid(ImportLine,19,1) = "^" Then
								Items = Mid(ImportLine,17,2)
								ItemCount = ItemCount + Items
							ElseIf Mid(ImportLine,20,1) = "^" Then
								Items = Mid(ImportLine,17,3)
								ItemCount = ItemCount + Items
							End If
						Else   
						End If
						Loop
						ImportFullFile.Close
					Else
					ReadThroughFile=ReadThroughFile+1
					End If
					Loop
				VCcount = VCcount + 1
			ElseIf RegExpTest(file, "Import Log-ORDER ALREADY EXISTS (") Then
				DuplicateCount = DuplicateCount + 1
			End If
		Else
		End If
	  Next
'Write Log data.
	If FileCount = 0 Then
	Else
		AppendFile.WriteLine(DateTest&","&TNcount&","&VCcount&","&FailCount&","&DuplicateCount&","&NoResults&","&FileCount&","&ItemCount)	  
	End If
	Count = Count + 1
	'Msgbox DateList
  Loop Until DateList="01-08-2009"
  AppendFile.Close

Function RegExpTest(strInput, strPattern)  
	Dim objRegExp : Set objRegExp = New RegExp  
	objRegExp.IgnoreCase = True  
	objRegExp.Global = False  
	objRegExp.Pattern = strPattern  
	RegExpTest = objRegExp.Test(strInput)  
	objRegExp.Pattern = ""  
end function

Geates · Sep 18, 2009

... Whatever works.

I notice this code block showing up alot

Code:

If RegExpTest(ImportLine, "ITM")Then
   If Mid(ImportLine,18,1) = "^" Then
      Items = Mid(ImportLine,17,1)
      ItemCount = ItemCount + Items
   ElseIf Mid(ImportLine,19,1) = "^" Then
      Items = Mid(ImportLine,17,2)
      ItemCount = ItemCount + Items
   ElseIf Mid(ImportLine,20,1) = "^" Then
      Items = Mid(ImportLine,17,3)
      ItemCount = ItemCount + Items
   End If
Else
End If

it can be replaced with

Code:

If RegExpTest(ImportLine, "ITM")Then
   intCharPos = inStr(18, ImportLine, "^")
   ItemCount = ItemCount + mid(ImportLine, intCharPos - 1, 1)
end if

IshmaelDS · Sep 22, 2009

Thanks I'll try that out.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Count Files in a Directory by Created Date

IshmaelDS

IS-IT--Management

Geates

Programmer

IshmaelDS

IS-IT--Management

Geates

Programmer

IshmaelDS

IS-IT--Management

Geates

Programmer

IshmaelDS

IS-IT--Management

Geates

Programmer

IshmaelDS

IS-IT--Management

Similar threads

Part and Inventory Search

Sponsor