Need a smarty on this one. Any takers? 1

[nerdalert1]

Hello all. Here is what I want to try to do. I have a folder on my C Drive where I have a bunch of PDF files. I then have a SQL table with records that have the path to these files. I need to clean up the folder and delete any files in the directory that do not have a record in the SQL table. Does anyone know the best way to handle this?

 

[Sheco]

If it is a one-time or infrequent thing then just make a little .VBS text file using ADO and FSO

Then just give the icon a click!

-----------------------
set fso = CreateObejct("Scripting.FileSystemObject")
Set cn = CreateObject("ADODB.Connection")
cn.ConnectionString = "blahblahblah"
Set rs = cn.Execute("SELECT filename FROM table")

If rs.State <> 1 Then
MsgBox "Database Query Failed"
End
End If

Do While Not rs.EOF
If Not fso.FileExists(rs("filename")) Then
fso.DeleteFile rs("filename")
End If

rs.MoveNext
Loop

rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing
Set fso = Nothing

 

[Sheco]

Whoops, I didnt read your request carefully... don't do what I said, I'll re do it real quick!

 

[Sheco]

My first one was no good, try this instead


Set cn = CreateObject("ADODB.Connection")
cn.ConnectionString = "blahblahblah"

Set rs = CreateObject("ADODB.Recordset")
rs.CursorType = 3
rs.Open "SELECT filename FROM table"

If rs.State <> 1 Then
MsgBox "Database Query Failed"
End
End If


set fso = CreateObject("Scripting.FileSystemObject")
set TheFolder = fso.GetFolder("C:\")

for each PDF in TheFolder.Files
rs.Filter = "filename = '" & PDF.Name & "'"

if rs.RecordCount = 0 Then
fso.DeleteFile PDF.Path
end if
next

 

[nerdalert1]

Thanks alot Sheco. I really needed this. I appreciate it. So will this loop through all the files in the folder and if the file path is not located in the table it will purge the file from the folder?

 

[nerdalert1]

Thats absolutly perfect. Thanks again Sheco. Huge help. Thanks

 

[Sheco]

First it fills an ADO recordset from the database.

Second it uses FSO to get a Files collection.

Then, for each file it sets the recordset's filter. The filter property on the recordset is basically the same thing as the WHERE clause in a SQL statement. So you set the filter property to the filename and check the RecordCount. If it is 0 then the filename is not in the recordset. If it is 1 then it is in the recordset and if it is greater than 1 then there is either a bug or the filename appears in the database twice.

If I were you, I'd not actually execute the FileDelete line until I was sure the script was working properly... don't want a bug to delete a bunch of good files.

Maybe to test it I would replace:
if rs.RecordCount = 0 Then
fso.DeleteFile PDF.Path
end if


With this:
if rs.RecordCount = 0 Then
MyAnswer = MsgBox(PDF.Path, 3, "Delete this?")
If MyAnswer = 2 Then
MsgBox "Bye bye"
Exit For
End If

If MyAnswer = 6 Then
fso.DeleteFile PDF.Path
End If
end if


And then just run it that way long enough to make sure it was doing what I wanted. Then I would cancel it and put it back like it was and run it and let it go to town.

 

[Sheco]

First it fills an ADO recordset from the database.

Second it uses FSO to get a Files collection.

Then, for each file it sets the recordset's filter. The filter property on the recordset is basically the same thing as the WHERE clause in a SQL statement. So you set the filter property to the filename and check the RecordCount. If it is 0 then the filename is not in the recordset. If it is 1 then it is in the recordset and if it is greater than 1 then there is either a bug or the filename appears in the database twice.

If I were you, I'd not actually execute the FileDelete line until I was sure the script was working properly... don't want a bug to delete a bunch of good files.

Maybe to test it I would replace:
if rs.RecordCount = 0 Then
fso.DeleteFile PDF.Path
end if


With this:
if rs.RecordCount = 0 Then
MyAnswer = MsgBox(PDF.Path, 3, "Delete this?")
If MyAnswer = 2 Then
MsgBox "Bye bye"
Exit For
End If

If MyAnswer = 6 Then
fso.DeleteFile PDF.Path
End If
end if


And then just run it that way long enough to make sure it was doing what I wanted... you know, to debug and whatnot.

One thing I wasn't clear on is if the database holds only the file name or the full path? You might have to tweak the If/Then depending on what you've got.

 

[Sheco]

Sorry for the double-post. They are the same except the last few sentences. I didn't change the code on you again.