Creating a table from files in a folder

J

jpl458

Well-known member
Local time
Yesterday, 16:17
Joined
Mar 30, 2012
Messages
1,218
I have a folder that has data files with dates, and I to get that data into an excel spreadsheet so I can create an ACCESS table. I have found a way to get the file names but cant get into excel but not the dates.

Thanks
 
This is part of a response to a similar question--
post#9 in thread at https://www.accessforums.net/showthread.php?t=85725


You can use a Dir command to get all files of a certain extension and put these into a txt file.
You repeat for different file extensions and append to the txt file.

My example to recurse alldirectories/subdirectories from C:\users\JP and down and identify *.jpg files with path


Uses a Command Prompt
Code:
C:\Users\JP> dir *.jpg /s/b >c:\users\jp\documents\alljpgs.txt
Here is a sample from the alljpgs.txt produced.

C:\Users\JP\AppData\Local\Google\Chrome\User Data\Default\Extensions\fheoggkfdfchfphceeifdbepao oicaho\8.1.0.2126_0\images\crypto\banners\crypto-what-banner.jpg
C:\Users\JP\AppData\Local\Google\Chrome\User Data\Default\Extensions\fheoggkfdfchfphceeifdbepao oicaho\8.1.0.2126_0\images\crypto\banners\protecty ourself-banner.jpg
C:\Users\JP\AppData\Local\Google\Chrome\User Data\Default\Extensions\fheoggkfdfchfphceeifdbepao oicaho\8.1.0.2126_0\images\crypto\banners\server-image-banner.jpg

...You can process the txt file with Access.
 
Not sure what you are doing, but if looping the files in a folder using filesystemobject you can use the File Object which has a dateCreated, dateLastAccessed, and dateLastModified property.

Solved - Access VBA - Loop through files in Folders, Subfolders, Sub-Sub Folders....

Hi, Before I write my own nested nested nested loop, surely someone has something out there already.... I need to list all the files in a single 'base' folder, which includes subfolders of the base folder. and subfolders of the subfolder's subfolders. The nesting of the subfolders is assumed...
www.access-programmers.co.uk
 
MajP said:
Not sure what you are doing, but if looping the files in a folder using filesystemobject you can use the File Object which has a dateCreated, dateLastAccessed, and dateLastModified property.

Solved - Access VBA - Loop through files in Folders, Subfolders, Sub-Sub Folders....

Hi, Before I write my own nested nested nested loop, surely someone has something out there already.... I need to list all the files in a single 'base' folder, which includes subfolders of the base folder. and subfolders of the subfolder's subfolders. The nesting of the subfolders is assumed...
www.access-programmers.co.uk
Click to expand...
I have a folder that has various audio files stored in m4a format, (Created bt the Sound Recorder) and I want to create a list of the filenames and dates created that I can convert to .csv file and load into ACCESS. My problem is I do not understand the file system the the files are stored in. The recorder has a popup menu where you can add fields to be viewed. I can get a list of the names, but I also need the date that are associated with each file.
 
It is probably creating custom fields. You should still be able to see them with FSO.
 
@moke123 Thanks for posting an example. Any particular reason you chose to run an append query for each file rather than just opening a recordset and using .AddNew?
 
Using a parameter query is also a very good style.
I myself would have used a recordset (single table access).
 
Using a parameter query is also a very good style.
Click to expand...
So you're OK with running hundreds, perhaps thousands of separate queries (one for each file) rather than opening a recordset and using .AddNew??? Surely, there has to be some overhead involved with forcing Access to create hundreds/thousands of query execution plans and then compile them so they can be executed. Maybe Access is smart enough to create a querydef and reuse it. But, someone with intimate knowledge of how the query process works internally would have to jump in to confirm. Even still, running hundreds/thousands of queries, even if there is no need to keep creating new execution plans and compiling them, just running the separate queries can't be right.

Remember, this is Access NOT SQL Server. Every query string you create in VBA has to get turned into a compiled query to run. Using a querydef rather than embedded SQL would eliminate some of the overhead but that's not what's happening here.
 
Well, I would use a stored query, that would eliminate the issue of repeatedly creating execution plans. But I also wrote that I would also prefer the recordset, that should be recognizable with two sentences.

But also: The time for creating an execution plan for this super simple query is in the nanosecond range, and not in the high range. What should be optimized here? And yes, I've practically tried to measure pure times for creation before execution plans.

Overall, I am also pleased that a parameter query was used at all instead of assembling the SQL statement using VBA. Readers see that far too seldom. It should be mastered and therefore written off.
 
Last edited:
Pat Hartman said:
@moke123 Thanks for posting an example. Any particular reason you chose to run an append query for each file rather than just opening a recordset and using .AddNew?
Click to expand...
It was meant as a quick and dirty example how it could be done.

I used the QryDef because I like them (no delimiting needed) and I had my CodeGen form loaded which writes the querydef for me, so it only takes seconds. The procedure executes pretty quickly on a local folder of 1000 files.

frmcg.png
 
moke123 said:
Why the csv? You can load directly into the access table.
Click to expand...
Moke, it works like a champ. Always like your answers. Now the challenge for me is to figure out why it works. I don't understand the code at all.

Thanks again
 
Overall, I am also pleased that a parameter query was used at all instead of assembling the SQL statement using VBA. Readers see that far too seldom. It should be mastered and therefore written off.
Click to expand...
The vast majority of my queries take parameters. I recommend them almost daily. The difference from what you see in this example is that I use saved querydefs. I do not build SQL using VBA when the SQL is not dynamic. I find it to be a unique point of view. Because the QBE's SQL string editor is pretty crummy, many people turn up their nose and refuse to use it thinking that building SQL strings in code is somehow "superior".
 
I'm not sure what your end goal is but one thing to be careful of is keeping data current if folders and files get added or deleted.

I usually avoid using a table for this type stuff. In my instance, I have a folder for each client and all docs and files, etc. for that client go in that folder. When I open a clients profile form, I run a procedure to list just their files in a list box. I can double click it to open the listed file. The data will always be current if files were added or deleted.

If you want to try the listbox route the relevant code is
Code: 
Public Sub sAddFilesToListBox(TopFold As String, Lbx As ListBox)

    Set fso = New FileSystemObject
    Set fCol = New Collection
 
    fCol.Add TopFold  'add the top folder path to the collection

    sGetFolderCollection TopFold  'call recursive proc to add subfolder paths to collection

    Lbx.rowsource  = ""

    Dim i As Long
    Dim Fldr As Folder, fl As File
    
    For i = 1 To fCol.Count                         'loop through the collection of folder paths

        Set Fldr = fso.GetFolder(fCol(i))

        For Each fl In Fldr.Files       'loop through folder adding file info to table
        
            Lbx.AddItem fl.Name & ";" & fl.Path & ";" & fl.DateCreated & ";" & fl.DateLastModified
 
        Next
      
    Next i

End Sub

It also uses this procedure which is in the example I posted
Code: 
Sub sGetFolderCollection(TopFold As String)

    Dim oTopFolder As Folder
    Dim fol As Folder
    Dim sfol As Folder

    Set oTopFolder = fso.GetFolder(TopFold)

    For Each fol In oTopFolder.SubFolders

        fCol.Add fol.Path

        For Each sfol In fol.SubFolders
            Call sGetFolderCollection(sfol.Path)
        Next

    Next fol

End Sub

You would call it with
Code: 
 sAddFilesToListBox strPath, Me.lstFiles
strPath is the path to your top folder, and replace lstFiles with the name of your value list listbox.
 
moke123 said:
I'm not sure what your end goal is but one thing to be careful of is keeping data current if folders and files get added or deleted.

I usually avoid using a table for this type stuff. In my instance, I have a folder for each client and all docs and files, etc. for that client go in that folder. When I open a clients profile form, I run a procedure to list just their files in a list box. I can double click it to open the listed file. The data will always be current if files were added or deleted.

If you want to try the listbox route the relevant code is
Code: 
Public Sub sAddFilesToListBox(TopFold As String, Lbx As ListBox)

    Set fso = New FileSystemObject
    Set fCol = New Collection

    fCol.Add TopFold  'add the top folder path to the collection

    sGetFolderCollection TopFold  'call recursive proc to add subfolder paths to collection

    Lbx.rowsource  = ""

    Dim i As Long
    Dim Fldr As Folder, fl As File
   
    For i = 1 To fCol.Count                         'loop through the collection of folder paths

        Set Fldr = fso.GetFolder(fCol(i))

        For Each fl In Fldr.Files       'loop through folder adding file info to table
       
            Lbx.AddItem fl.Name & ";" & fl.Path & ";" & fl.DateCreated & ";" & fl.DateLastModified

        Next
     
    Next i

End Sub

It also uses this procedure which is in the example I posted
Code: 
Sub sGetFolderCollection(TopFold As String)

    Dim oTopFolder As Folder
    Dim fol As Folder
    Dim sfol As Folder

    Set oTopFolder = fso.GetFolder(TopFold)

    For Each fol In oTopFolder.SubFolders

        fCol.Add fol.Path

        For Each sfol In fol.SubFolders
            Call sGetFolderCollection(sfol.Path)
        Next

    Next fol

End Sub

You would call it with
Code: 
 sAddFilesToListBox strPath, Me.lstFiles
strPath is the path to your top folder, and replace lstFiles with the name of your value list listbox.
Click to expand...
Moke, I have been using the example you sent, but the code is a bit above my experience level with VBA. But, your example clarified my thinking about what I was trying to do. I use the Voice Recorder to record various calls, conversations,etc. Voice Recorder stores those recordings in Documents\Sound Recordings. I know the path to the files, so I don't need to enter it each time. I need some help with code that would reside behind a button that would get the latest recordings and their properties; those files that are later than the last date in a table in an ACCESS database. There is other information that needs to be added to the table, which can only be done by hand, but if I can get the recording name and the start and modified time I can reduce what has to be entered by hand. The flow would look like this:

Get Voice Recording data later than the newest recording date in the table
Append that data to the data in the table
Fill in the blanks at a later time from notes, or listening to the recording.

I hope this makes sense, and I really appreciate the time and the examples. I keep all you examples in the Moke folder.

Thanks again
 
What properties? "the latest recordings and their properties;"
 
I gave you 2 options, Save to table and list in a list box. Which one are you using?
jpl458 said:
a button that would get the latest recordings and their properties; those files that are later than the last date in a table in an ACCESS
Click to expand...
If listing to a table, do you want to get only the new files, or just the latest? How many files to you anticipate having?
 
moke123 said:
I gave you 2 options, Save to table and list in a list box. Which one are you using?

If listing to a table, do you want to get only the new files, or just the latest? How many files to you anticipate having?
Click to expand...

moke123 said:
I gave you 2 options, Save to table and list in a list box. Which one are you using?

If listing to a table, do you want to get only the new files, or just the latest? How many files to you anticipate h
Click to expand...

moke123 said:
I gave you 2 options, Save to table and list in a list box. Which one are you using?

If listing to a table, do you want to get only the new files, or just the latest? How many files to you anticipate having?
Click to expand...
There is no real volume, maybe 5 or 6 recordings max in a day, but mostly less. I am currently using your example as a utility external to ACCESS. I run your example , then export Table1 from your example to my ACCESS app,(after deleting the previous one and saving the max datetime). I then can create what I need with queries. I create a query that then gets all the files that are newer on the new Table1 than the max date from the previous table1. I want to automate that process, but I am having a difficult time understanding your code. I've been running it over and over with a lot debug.prints to try and understand the part that I need. I will continue to work on it. But, some of the terms in your code I can't find anywhere on the web..

Thanks again
 
You must log in or register to reply here.

Similar threads

E
BAT File Command Help
2
Replies
26
Views
481
Isaac
Isaac
S
Solved Kill a Folder
Replies
2
Views
125
sergio vieira
S
J
Export data in Work to different folders
Replies
2
Views
211
JPR
J
D
Darshan Hiranandani : How to Delete Access Lock Files and Prevent Future Issues?
Replies
2
Views
213
DHookom
DHookom
T
Solved Foxpro IDE
Replies
17
Views
452
arnelgp
arnelgp

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom