April 7, 2005 at 9:20 am
I want to delete files in a folder older than 14 days. I have this code shown below. Does this look correct? Also, can I run this code in Visual Basic to test? Thanks.
Function Main()
Dim objFSO, objFolder, objFile, colFiles, DaysBack
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.getfolder("C:\Temp")
Set colFiles = objFolder.Files
Set DaysBack = 14
If colFiles.Count > 0 Then
For Each objFile In colFiles
If DateValue(objfile.dateCreated) < DateValue(Now - DaysBack) Then
objFSO.DeleteFile objFile
End If
Next
End If
End Function
April 7, 2005 at 9:25 am
That looks great.
You can copy dummy files to a test directory with different create dates. Then copy this code into a .VBS file (notepad, save as txt and rename .vbs). Just run the file and see the results... Make sure you the the right info for the directory b4 the test.
April 7, 2005 at 9:41 am
Thanks Remi. Found a link right here on this site too for anyone else.
http://qa.sqlservercentral.com/columnists/hji/usingvbscripttoautomatetasks.asp
Pat
April 7, 2005 at 10:52 am
Below is how we do it. A control file tells the VBS script which folders to examine
Dim filesys, metabackfolder, fil, filecoll, filist, ext, VFile, VerStr, intDaysOld
intDaysOld = 30
Set filesys = CreateObject("Scripting.FileSystemObject")
Set VFile = filesys.OpenTextFile("C:\Applications\XXXX\Scripts\Control.ini", 1, False)
While VFile.AtEndOfStream <> True
VerStr = Trim(VFile.readline)
if (VerStr <> "[Path]" And VerStr <> "[Backup]" And VerStr <> "") then
Set metabackfolder = filesys.GetFolder(VerStr)
Set filecoll = metabackfolder.Files
For Each fil in filecoll
if InStr(1, fil.name, ".txt.", 1) or InStr(1, fil.name, ".log", 1) then
If datediff("d", fil.DateLastModified, Date()) > cInt(intDaysOld) then
'WScript.Echo("Will delete " & fil.name)
fil.delete
end if
End If
Next
end if
Wend
VFile.Close
Good Hunting!
AJ Ahrens
webmaster@kritter.net
April 7, 2005 at 8:30 pm
and yet another way. get the values from whatever and just call the sub. Don't forget to add the success constant to the main function. I've always been partial to datediff.
'Strict
Option Explicit
Function Main()
DeleteOlderFiles "c:\temp", 1
End Function
Sub DeleteOlderFiles(strFolder, intDays)
Dim objFile
With CreateObject("Scripting.FileSystemObject")
For each objFile in .GetFolder(strFolder).Files
If datediff("d",objFile.DateCreated, Now) > intDays _
Then .DeleteFile objFile
Next
End With
End Sub
April 7, 2005 at 11:36 pm
I'd definately use the datediff function to determine if the date changes. It's the most reliable way to check for differences in dates, a function that is specifically written for the task. I use it in my clean up script.
Julian Kuiters
juliankuiters.id.au
April 8, 2005 at 8:00 am
Not sure if this would matter in your environment, but I usually use the files DateLastModified property and not the DateCreated property.
Some methods of File copies will retain the Create Date on the destination device and only update the Modified Date. I would much rather delete less than more if the data is critical.
As an added note, DateDiff can return actual days of difference between files; where as a comparison of a ( Now - 14 ) will retain the time portion of the date returned by the Now function.
-Mike Gercevich
April 8, 2005 at 11:12 am
In today SQLServerCentra.com news
http://qa.sqlservercentral.com/columnists/hji/usingvbscripttoautomatetasks.asp
April 11, 2005 at 12:44 am
ok people can any one know how to do this in c#...
thankx
amal
Amal Fernando
94-0717-318631 http://spaces.msn.com/members/amalatsliit/
April 11, 2005 at 12:44 am
ok people can any one know how to do this in c#...
thankx
amal
Amal Fernando
94-0717-318631 http://spaces.msn.com/members/amalatsliit/
April 11, 2005 at 8:41 am
Thanks for all the great responses. I love the SQL community! Here's how my script looks now. It's basically checking for files older than a month that begin with "qury" and end with the two extensions listed below. One other thing I need to do is to write a log file listing the files that were deleted and their delete date. Does anyone have any tips on that? Thanks again.
Pat
Function Main()
Dim oFSO
Dim sDirectoryPath
Dim oFolder
Dim oFileCollection
Dim oFile
'Set Variables
Set oFSO = CreateObject("Scripting.FileSystemObject")
sDirectoryPath = "C:\TEMP"
set oFolder = oFSO.GetFolder(sDirectoryPath)
set oFileCollection = oFolder.Files
'Walk through each file in this folder collection.
'If it is older than 30 days, then delete it.
For each oFile in oFileCollection
If DateDiff("d", oFile.DateLastModified, Now) > 30 AND UCASE(LEFT(oFile, 4)) = "qury" AND _
(UCASE(Right(oFile, 9)) = ".sas7bvew" OR UCASE(Right(oFile, 9)) = ".sas7bdat") Then
oFile.Delete(True)
End If
Next
'Clean up
Set oFSO = Nothing
Set oFolder = Nothing
Set oFileCollection = Nothing
Set oFile = Nothing
Main = DTSTaskExecResult_Success
April 11, 2005 at 8:53 am
Function Main()
Dim oFSO
Dim sDirectoryPath
Dim oFolder
Dim oFileCollection
Dim oFile
Dim oLog
Dim iDaysOld
'Set Variables
iDaysOld = 30
Set oFSO = CreateObject("Scripting.FileSystemObject")
sDirectoryPath = "C:\TEMP"
set oFolder = oFSO.GetFolder(sDirectoryPath)
set oFileCollection = oFolder.Files
Set oLog = oFSO.OpenTextFile("LogFilePath.log", ForAppending, True /*create if not exists*/)
'Walk through each file in this folder collection.
'If it is older than 30 days, then delete it.
For each oFile in oFileCollection
If oFile.DateLastModified < (Date() - iDaysOld) AND UCASE(LEFT(oFile, 4)) = "qury" AND _
(UCASE(Right(oFile, 9)) = ".sas7bvew" OR UCASE(Right(oFile, 9)) = ".sas7bdat") Then
oFile.Delete(True)
oLog.WriteLine "File deleted on ..."
End If
Next
'Clean up
oLog.Close
Set oLog = Nothing
Set oFSO = Nothing
Set oFolder = Nothing
Set oFileCollection = Nothing
Set oFile = Nothing
Main = DTSTaskExecResult_Success
April 12, 2005 at 3:30 pm
Thanks Remi. I'm new to writing vbscript. Does the way I'm checking for the existence of the log file look correct (it seems to work), or should it be placed somewhere else? Thanks.
-Pat
Dim oFSO
Dim sDirectoryPath
Dim oFolder
Dim oFileCollection
Dim oFile
Dim oLog
Const ForReading = 1
Const ForWriting = 2
Const ForAppending = 8
'Set Variables
Set oFSO = CreateObject("Scripting.FileSystemObject")
sDirectoryPath = "C:\TEMP"
Set oFolder = oFSO.GetFolder(sDirectoryPath)
Set oFileCollection = oFolder.Files
Set oLog = oFSO.OpenTextFile("C:\Temp\LogFile.txt", ForAppending, True)
'Walk through each file in this folder collection.
'If it is older than 30 days, then delete it.
For Each oFile In oFileCollection
If DateDiff("d", oFile.DateLastModified, Now) > 30 And Left(oFile.Name, 4) = "qury" And (UCase(Right(oFile, 9)) = ".SAS7BDAT" Or UCase(Right(oFile, 9)) = ".SAS7BVEW") Then
If oFSO.FileExists("C:\Temp\LogFile.txt") Then
oLog.WriteLine oFile.Name & " deleted on " & FormatDateTime(Date, vbShortDate)
Else
oFSO.CreateTextFile ("C:\Temp\LogFile.txt")
oLog.WriteLine oFile.Name & " deleted on " & FormatDateTime(Date, vbShortDate)
End If
oFile.Delete (True)
End If
Next
'Clean up
oLog.Close
Set oLog = Nothing
Set oFSO = Nothing
Set oFolder = Nothing
Set oFileCollection = Nothing
Set oFile = Nothing
April 13, 2005 at 6:47 am
"LogFilePath.log", ForAppending, True /*create if not exists*/
the 3rd parameter set to true means the the file will be created if it doesn't exists. So you don't need to do the check in the while. Anyways the OpenTextFile method would fail if the file didn't exists and the 3rd parameter would be set to false.
April 13, 2005 at 6:55 am
Thanks for the help Remi !
-Pat
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply