UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Cleanup Old Files Code, Access 2013    
 
   
ry94080
post Jul 18 2019, 02:06 PM
Post#1



Posts: 1,008
Joined: 27-July 05



Hi all,

I usually use a version of this code to clean up old files in folders:
CODE
Sub CleanupFolder()


Set oFSO = CreateObject("Scripting.FileSystemObject")



sFolder = "I:\lvteci\lvtecr\Archived Files\"

For Each ofile In oFSO.GetFolder(sFolder).Files
    sfilefullpath = ofile
    sFile = Mid(ofile, InStrRev(ofile, "\") + 1)
    
    If InStr(1, sFile, "_reformatted.csv") > 1 Then
        If ShowFileInfo(sfilefullpath) < Now() - 30 Then
            KillFile sFolder & sFile
        End If
    End If
Next
End Sub

Function ShowFileInfo(filespec)
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set f = oFSO.GetFile(filespec)
sShowFileInfo = f.DateCreated
ShowFileInfo = sShowFileInfo
End Function


Sub KillFile(sFile)
Set oFSO = CreateObject("Scripting.FileSystemObject")
oFSO.DeleteFile sFile, sTo
End Sub


However I now have a folder with 10's of thousands of files in there. This code loops through every single file in the folder to check it's file specifications and it takes forever. Is there a way to "query" the folder for these items to specify which "old" files to delete?
Go to the top of the page
 
theDBguy
post Jul 18 2019, 02:08 PM
Post#2


Access Wiki and Forums Moderator
Posts: 76,000
Joined: 19-June 07
From: SunnySandyEggo


Hi. Not that I am aware of. Maybe the process would be a little bit faster if you simply read all data info into a table and then use a query to decide which files to clean up and then loop through it. Just a thought...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
jleach
post Jul 18 2019, 02:16 PM
Post#3


UtterAccess Editor
Posts: 10,037
Joined: 7-December 09
From: St Augustine, FL


You can try Windows Management Instrumentation, I'd expect it to be quite a bit faster:

https://docs.microsoft.com/en-us/windows/wi...les-and-folders

https://www.pixelchef.net/how-retrieve-file...-and-powershell

--------------------
Go to the top of the page
 
PhilS
post Jul 18 2019, 02:28 PM
Post#4



Posts: 594
Joined: 26-May 15
From: The middle of Germany


QUOTE
However I now have a folder with 10's of thousands of files in there. This code loops through every single file in the folder to check it's file specifications and it takes forever. Is there a way to "query" the folder for these items to specify which "old" files to delete?

No, but your code is extremely inefficient. CreateObject is a very expensive (=slow) operation, and two of the three times you use it (for all the files...) are completely superfluous.

CODE
Sub CleanupFolder()

Set oFSO = CreateObject("Scripting.FileSystemObject")

sFolder = "I:\lvteci\lvtecr\Archived Files\"

For Each ofile In oFSO.GetFolder(sFolder).Files
    If oFile.DateCreated < Now() - 30 Then
        If oFile.Name LIKE "*_reformatted.csv" Then
            oFile.Delete
        End If
    End If
Next
End Sub

Please report back how much faster this is!

BTW: You should really, really, use Option Explicit in your modules and declare the variables!
This post has been edited by PhilS: Jul 18 2019, 02:29 PM

--------------------
Go to the top of the page
 
kfield7
post Jul 18 2019, 02:52 PM
Post#5



Posts: 976
Joined: 12-November 03
From: Iowa Lot


Seems to me that a batch (a.k.a. Shell) process would be most efficient.

See this discussion.
Go to the top of the page
 
ADezii
post Jul 18 2019, 06:12 PM
Post#6



Posts: 2,535
Joined: 4-February 07
From: USA, Florida, Delray Beach


I'm with theDBGuy on this one. It may be advantageous to:
  1. Copy all Files to a Table with [FName] and [FDate] Fields.
  2. Index the [FDate] Field.
  3. Create a Temporary Query that lists all the Files to be Deleted.
  4. Process this Query and Delete the Files listed within.
  5. A lotta work, not really sure if it will be faster or not, but worth a try.
  6. Actually had a brainstorm (doesn't happen that often). The following DOS Command will display all Files in the C:\Sounds Folder and redirect the OUTPUT to a File named Files.txt in the C:\TEMP Folder. The Text File can now be Linked or Imported into the CurrentDB as a Table then acted on accordingly. The Files will be listed in chronological order, earliest first.
  7. DOS Commands, preferably in a Batch File:
    CODE
    CD\Sounds
    Dir /od > C:\TEMP\Files.txt
  8. Sample Files.txt:
    CODE
    02/05/1992  04:03 AM            16,084 BONSAI.WAV
    04/11/1992  11:59 AM           111,204 HUNTIN.WAV
    04/12/1992  12:44 AM             9,556 SCHWING.WAV
    07/12/1992  05:27 PM            18,904 AHHHHH.WAV
    07/12/1992  05:27 PM            12,976 GAMEOVER.WAV
    07/12/1992  05:27 PM            32,332 MANOVER.WAV
    09/04/1992  03:56 PM            74,286 6FARTS.WAV
    09/21/1992  02:00 AM            24,454 LOCKDOOR.WAV
    12/11/1992  01:30 PM            12,044 GO2HELL.WAV
    12/11/1992  01:31 PM            35,564 DAY&WORK.WAV
    12/11/1992  01:38 PM            23,054 BORING.WAV
    12/11/1992  01:39 PM             5,486 DOOH.WAV
    01/28/1993  10:40 AM            29,526 TAPS2.WAV
    03/25/1993  12:00 PM            41,070 DRUM.WAV
    03/25/1993  12:00 PM             4,136 NEATSTUF.TXT
    03/25/1993  12:00 PM             7,832 BOING.WAV
    04/15/1993  03:42 PM             8,292 VICCANOL.WAV
    04/15/1993  03:45 PM             6,406 VICTANKL.WAV
    04/15/1993  03:49 PM            16,142 VICSRN.WAV
    04/25/1993  06:34 AM            77,592 JERRY1.WAV
    05/24/1993  12:36 PM            16,792 PHONE.WAV
    05/31/1993  06:56 PM            15,226 VICSIREN.WAV
    06/23/1993  11:25 AM           412,108 VULMIND.WAV
    07/16/1993  06:12 PM            18,886 CHEER.WAV
    09/23/1993  05:24 PM            55,334 HORSES.WAV
    09/24/1993  02:11 PM            41,168 VICSLOW.WAV
    12/25/1993  11:37 AM            26,914 TIMEWRP.MID
    12/28/1993  08:08 PM            89,122 FUNNYGUY.WAV
    04/22/1994  12:00 AM            11,696 CATWAIL.WAV
    05/17/1994  12:00 PM             3,988 POP.WAV
    05/17/1994  12:00 PM            12,120 TWANG.WAV
  9. The File can also be processed without Importing/Linking.

This post has been edited by ADezii: Jul 18 2019, 06:45 PM
Go to the top of the page
 
gemmathehusky
post Jul 19 2019, 07:24 AM
Post#7


UtterAccess VIP
Posts: 4,725
Joined: 5-June 07
From: UK


you can use dir() to iterate the files in a folder
access has an intrinsic filedate function (I forget it off hand - filedatetime, or something like that)
so you can do this without any external libraries.

See below - I can't see how it can run quicker than this. If you add a DoEvents to the code loop, it will release control of the database to other actions, and just effectively run in the background, while you do other stuff, which improves the user experience. You could add a form to show the progress and count of deleted files easily enough, as the code/pseudo code indicates.

I use progress forms like this to show the progress of long tasks.


note - the code below isn't aligning correctly for some reason

CODE
filename = dir()
docmd.openform "progressform"
set frm = application.forms("progressform")   'it's something like that, without looking up the precise syntax

while filename<>""
      show filename on progressfrom
      if file matches spec for deletion then
           show deletion on progressform
        frm!displayfield = "Deleting " & filename
          
        kill filename
        (or name filename as archivedfilename to move to another folder, but kill is quicker)
            (write deletion to a logfile if it matters, or store deleted filename in a table)
      end
      filename = dir()

      DoEvents
wend

close the progressform

--------------------
Dave (Male)

(Gemma was my dog)
Go to the top of the page
 
kfield7
post Jul 19 2019, 07:37 AM
Post#8



Posts: 976
Joined: 12-November 03
From: Iowa Lot


I believe this is all accomplished in one command line:

ForFiles /p "I:\lvteci\lvtecr\Archived Files" /M "*_reformatted.csv" /S /D -30 /C "cmd /c del @file"

deletes all files older than 30 days, ending in "_reformatted.csv", from the folder "I:\lvteci\lvtecr\Archived Files" and any subfolders.
Go to the top of the page
 
jleach
post Jul 19 2019, 08:53 AM
Post#9


UtterAccess Editor
Posts: 10,037
Joined: 7-December 09
From: St Augustine, FL


Windows bash loops. I'd rather write perl! pullhair.gif

thumbup.gif

--------------------
Go to the top of the page
 
DanielPineault
post Jul 19 2019, 08:59 AM
Post#10


UtterAccess VIP
Posts: 6,765
Joined: 30-June 11



You can use Dir to filter the return files to reduce to a minimum the files to process.

CODE
Function FF_ListFilesInDir(sPath As String, Optional sFilter As String = "*.*") As Variant
    Dim aFiles()              As String
    Dim sFile                 As String
    Dim i                     As Long

    On Error GoTo Error_Handler

    If Right(sPath, 1) <> "\" Then sPath = sPath & "\"
    sFile = Dir(sPath & sFilter)
    Do While sFile <> vbNullString
        If sFile <> "." And sFile <> ".." Then
            ReDim Preserve aFiles(i)
            aFiles(i) = sFile
            i = i + 1
        End If
        sFile = Dir     'Loop through the next file that was found
    Loop
    FF_ListFilesInDir = aFiles

Error_Handler_Exit:
    On Error Resume Next
    Exit Function

Error_Handler:
    MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: FF_ListFilesInDir" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Function


CODE
FF_ListFilesInDir("I:\lvteci\lvtecr\Archived Files\", "*_reformatted.csv")





I also agree, you don't want to keep creating FSO objects over and over and over, Create a global FSO variable once and keep using it and close it once you are all done processing, use a self-healing FSO variable.

--------------------
Daniel Pineault (2010-2019 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    18th August 2019 - 10:06 AM