CODEWITHSUNDEEP
vbscriptscripting
Steven Chandra
Steven Chandra

Reputation: 39

Delete File based on DateCreated or filename which consist of date

I have to delete data based on its filename. This is what the files look like:

Nostro_BO_FCC_130317.csv [130317 is a created date]
Nostro_BO_FCC_120317.csv
Nostro_BO_FCC_110317.csv
Nostro_BO_FCC_100317.csv
Nostro_BO_FCC_090317.csv

and this is where the data located: D:\BDI\CTS\Data\Nostro\BO FCC\

I have developed VBScript to delete the file but it does not work at all. All I want is to delete the file which below 2 days since current date (13/03/2017).

This is my VBScript:

Dim infolder
Dim ad, intcount, i, str, Postdate, Uploaddate, fileExists, ExpireDate
Dim sql_query, rs, rsU
Dim ObjFSO, objFile, OFile, OfPath, osf, MM, DD 

Set ad = CreateObject("ADODB.Connection")
ad.Provider = "sqloledb"

If Len(Month(varDate)) = 1 then
    MM = "0" & Month(varDate)
Else
    MM = Month(varDate)
End If

If Len(Day(varDate)) = 1 then
    DD = "0" & Day(varDate)
Else
    DD = Day(varDate)
End If

PostDate = Year(varDate) & MM & DD
Uploaddate = DD & MM & Right(Year(varDate), 2)
ExpireDate = CDate(DD) < Date - 1 & MM & Right(Year(varDate), 2)

ad.CursorLocation = 3
ad.Open propstr

Set osf = CreateObject("Scripting.FileSystemObject")
OfPath = "D:\BDI\CTS\Data\Nostro\BO FCC\"

'this below my logic steven
Set infolder = osf.GetFolder(OfPath)
Set OFile = Nothing

fileExists = True
fullfilename = "Nostro_BO_FCC_"& Uploaddate &".csv"

'create file if not exits and delete if exits then create again
If Not osf.FileExists(OFPath & fullfilename) Then
    Set OFile = osf.CreateTextFile(OFPath & fullfilename, True)
    Set OFile = Nothing
End If

For Each file In infolder.Files
    If DateDiff("d", file.DateCreated, Date) < Date -2 Then
    '    oFSO.DeleteFile(oFile) 
    'If osf.FileExists(OfPath & "Nostro_BO_FCC_" & ExpireDate & ".csv") Then
        'osf.DeleteFile OfPath & "Nostro_BO_FCC_" & ExpireDate & ".csv"
        file.Delete(True)
    End If
Next

Upvotes: 0

Views: 420

Answers (1)

Ansgar Wiechers
Ansgar Wiechers

Reputation: 200293

CDate(DD) < Date -1 & MM & Right(Year(varDate),2) isn't going to do what you apparently expect it to do. I already told you that when answering your previous question where you used a similar construct.

If you want to compare date strings with a < or > operator, the strings must be in a format where string order and date order are identical. That is not the case for your DDMMYY format. Because of that you basically have two options:

  • Since you have a small number of valid dates you could build reference filenames:

    Function dd(s) : dd = Right("00" & s, 2) : End Function

d1 = Date
d2 = d1 - 1
d3 = d1 - 2

fn1 = "Nostro_BO_FCC_"& dd(Day(d1)) & dd(Month(d1)) && Right(Year(d1), 2) &".csv"
fn2 = "Nostro_BO_FCC_"& dd(Day(d2)) & dd(Month(d2)) && Right(Year(d2), 2) &".csv"
fn3 = "Nostro_BO_FCC_"& dd(Day(d3)) & dd(Month(d3)) && Right(Year(d3), 2) &".csv"

    and delete all files whose name isn't among them:

    For Each f In infolder.Files
  If f.Name <> fn1 And f.Name <> fn2 And f.Name <> fn3 Then
    f.Delete
  End If
Next

  • A more generic approach is to parse the date from each filename:

    a  = Split(osf.GetBaseName(f), "_")
ds = a(UBound(a))
d  = DateSerial(Mid(ds, 5, 2), Mid(ds, 3, 2), Mid(ds, 1, 2))

    and delete all files with a date below your reference date:

    refDate = Date - 2

For Each f In infolder.Files
  ...
  If d < refDate Then
    f.Delete
  End If
Next

Edit: If you want to compare the files' creation date against a reference date you can do that like this:

refDate = Date - 2

For Each f In infolder.Files
  If f.DateCreated < refDate Then
    f.Delete
  End If
Next

Upvotes: 1

Related Questions