best way to scan directories

[LucasH]

Hi,

Im wondering what the recommended method is to scan through a directory structure is?

Currently Im using the Dir command and then looping through the results but that is taking a long time. Granted there are a lot of files.

But Im wondering if I could use some of windows/dos functionality to copy all files that meet a certain date restriction and copy them somewhere for processing and if that would be faster.

Any thoughts?

 

[fumei]

You do not give enough information.

" copy them somewhere for processing " Any actual copying of actual files somewhere will definitely add overhead processing (and time).

"Im using the Dir command and then looping through the results "

Looping through and...doing what???

If what you are looking for is a way to determine what files in a folder "meet a certain date restriction", then using FSO (FileSystemObject) would possibly be a better route.

However, if you can, please elaborate on what exactly you are trying to do.

Gerry

 

[LucasH]

Thanks for your message...

To elaborate

Currently, I use dir to provide a list of files in a directory and then add the files that meet a certain date condition to an access table. I use it recursively to go into subfolders as well.

once the files are in a table, i loop through each one and import them into linked sql tables. Once the code gets to the import process, I am satisfied with the results, but building the list of files to import is taking a long time. Granted, there are 350k files to go through which I know is a ton and unfortunately I can do nothing about that, but I was just wondering if there was a better way to at least get a list of files faster.

 

[fumei]

And you are using Dir in Access?

"Currently, I use dir to provide a list of files in a directory and then add the files that meet a certain date condition to an access table. "

How are you using Dir to get this information? Dir does not get date information by itself.

I would suggest using FSO to get the file names. It does have a DateCreated value. And it is fast. You can use to build an array perhaps of the files that meet your criteria.

IMPORTANT!!!!

The dates returned by FSo may seem wrong (or at least weird). For example:

Sub MyDates()
Dim fso As Scripting.FileSystemObject
Dim fld As Scripting.Folder
Dim fil As Scripting.File

Dim strNames As String
Set fso = CreateObject("Scripting.FileSystemObject")
Set fld = fso.GetFolder("c:\zzz\Test\test2")
For Each fil In fld.Files
      strNames = strNames & fil.Name & vbTab & _
         "Created: " & fil.DateCreated & vbTab & _
         "Modified: " & fil.DateLastModified & vbCrLf
Next
MsgBox strNames
End Sub

Returns:

B.doc Created: 2010-01-11 1:22:38 PM Modified: 2007-11-29 9:51:48 AM
C.doc Created: 2010-01-11 1:22:38 PM Modified: 2007-11-29 9:52:00 AM
CollectHyperlinks.doc Created: 2010-01-11 1:22:38 PM Modified: 2007-06-22 9:59:53 AM
CollectHyperlinks_A.doc Created: 2010-01-11 1:22:38 PM Modified: 2007-10-23 8:34:47 AM
D.doc Created: 2010-01-11 1:22:38 PM Modified: 2007-11-29 9:52:14 AM
E.doc Created: 2010-01-11 1:22:38 PM Modified: 2007-11-29 9:52:24 AM

Notice that the ModifiedDate is BEFORE the DateCreated.

ModifiedDate is part of the file information itself (IN the file); DateCreated comes from the OS file system information.

Thus, if you have restored a file, the file is newly created - as far as the file system is concerned - but it retains its modified date information from the file itself.

In any case, generally speaking, if your logic requires information about the file, FSO is better.


Gerry

 

[LucasH]

Yes dir in vba...


This looks promising I will check it out...thanks!

 

[fumei]

Just remember that to use FSO you must have the Microsoft Scripting Runtime library available as a reference.

Gerry

 

[LucasH]

Yes, thank you, I got that.

Im going to check into this, but do you know a quick way to include subfolders under c:\zzz\Test\test2

 

[LucasH]

This is what I have so far to include subfolders

Function MyDates()
Dim fso As Scripting.FileSystemObject
Dim fld As Scripting.Folder
Dim fil As Scripting.File
Dim fsub As Scripting.Folder
Dim fld2 As Scripting.Folder

Dim fsubs
Dim masterfolder As String
Dim fsubfil



WarningsOff
sql ("Delete from tblFilenamestest")

masterfolder = "R:\Client\TBird\PosiImportProcessed\"
Dim strNames As String
Set fso = CreateObject("Scripting.FileSystemObject")

Set fld = fso.GetFolder(masterfolder)
Set fsubs = fld.SubFolders
For Each fsub In fsubs
    Set fld2 = fso.GetFolder(masterfolder & fsub.Name)
     For Each fil In fld2.Files
      sql ("INSERT INTO tblFilenamestest (Path) VALUES('" & masterfolder & fsub.Name & "\" & fil.Name & "');")
      
     Next
Next
WarningsOff
DoCmd.OpenTable "tblFilenamestest"
End Function

Is there a quicker way to add the records or do I have to insert into my filenames table for each record...ie can insert all files at once like an array or something?

 

[LucasH]

one more question...


in my for each statement

For Each fil In fld2.Files

is there syntax that i can say something like this?

For Each fil In fld2.Files where fil.Datecreate > #4/1/2010#

or something along those lines?

 

[PHV]

For Each fil In fld2.Files
   If fil.DateCreated > #2010-04-01# Then
      sql ("INSERT INTO tblFilenamestest (Path) VALUES('" & fil.Path & "');")
   End If
Next

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[LucasH]

Yeah that's what I have now, but I was hoping to avoid having to go through each file independently as I imagine that's goig to eat up performance.

Ideally, I'd like to produce an array or collection of all files with a create date > 4/1/10 for example and dump the entire thing into a table, instead of one at a time.

 

[fumei]

avoid having to go through each file independently "

How can you, since you are trying to GET information about each file?

Gerry

 

[Gruuuu]

There IS an Application.FileSearch, it includes subfolders and has search properties (PropertyTests object). I haven't ever done anything with PropertyTests, but if you fiddle with it long enough, you may get what you want. It's supposed to mimic the window file search utility (in terms of finding files, not displaying them in a window. That's your job!)

 

[LucasH]

Thanks Gruuu I will look into it. Just trying to speed this slow process up. Not sure if it was possible but thought maybe. I will update this thread with my results

 

[fumei]

FileSearch is very fast and works extremely well. Just note that Microsoft in its penultimate wisdom removed FileSearch from all 2007 applications. You do not state what version you are working with.

If all you are needing is some date attribute, FSO is sufficient. I am not sure FileSearch would be faster for this.

Gerry

 

[LucasH]

Im using 2003 for this implementation thank god. Whenever I connect to a client and they have 2007, I cringe. Makes things so much harder. Anyway, I will test out the FileSearch, we'll see if it's any better.