Forum Discussion
HarrisonBeck
Mar 31, 2021Copper Contributor
Get DB Data From Folder
Hello,
Is there a way to do this? Power BI and excel can both get data from a folder, not just a file. I'd like to be able to get the data from the most recent file added to a folder when the DB is refreshed. Do I need to write this in SQL?
Thank you,
Harris
- George_HepworthSilver Contributor
I slept on this and decided a demo would be a good way to convey a better understanding of the tasks needed to accomplish what I think you want to do. It's not simple, but it is highly doable.
The attached zip file contains an accdb which has two basic functions:
- Loop through all of the files in a designated folder (and in any subfolders under that designated folder) and list them in a table in the database by file name, path, date created and last modified date. The list is displayed in a form which you can then use to:
- Select any one of the files to link as text files (if they are .csv format files) or as Excel files (if they are .xlsx or .xls format files).
It will ONLY select those three file extensions, which is what I assumed you wanted from this discussion.
When the new files are linked as tables, the tables are named for the original file name as it was found in the folder and with the date of that file formatted as "YYYYMMDDHHMMSS" so you can track of previously linked files.
PLEASE NOTE: Prior to using this you must specify the path to the folder where you want it to work, i.e. the folder on your network where the new .csv files and any .xls or .xlsx files are stored.
Otherwise it will fail.
e.g. "G:\YourReportFolder" where G is a mapped drive on your network.
If you have trouble in this part, keep adjusting the folder so that it correctly identifies the right location.
- HarrisonBeckCopper Contributor
George_Hepworth Thank you for putting all of this together. I hope all of this wasn't for me and you had it already made. This id a lot of code.
I tried running it but got this error:
Do you know off-hand what I can change in order to use this? Also, do you thnk this is a code that I can integrate into my in-use Accdb? I'd like to have a few tables within one DB update when I hit refresh.
This concept is above my level of understanding. If this is too much to explain, don't worry about this. I appreciate your help thus-far.
- George_HepworthSilver Contributor
Hm. This works on my own 64 bit Access, so I'm not sure where the problem lies, unless I uploaded the wrong version.
Here's the relevant code that error normally would apply to. It's already been updated in my 64 bit environment, though:#If VBA7 Then
Private Declare PtrSafe Sub sapiSleep Lib "kernel32" _
Alias "Sleep" _
(ByVal dwMilliseconds As Long)
#Else
Private Declare Sub sapiSleep Lib "kernel32" _
Alias "Sleep" _
(ByVal dwMilliseconds As Long)
#End IfThat is called in
Public Sub Sleep(ByVal lngMilliSec As Long)
If lngMilliSec > 0 Then
Call sapiSleep(lngMilliSec)
End If
End SubI don't think it's used in this demo, so maybe you can comment it out both the API Declaration and the Sub that uses it, and see if that works.
No problem on the work involved. This is consolidated from two or three other working databases I've produced over the years, albeit combined here. I now use it primarily to search for image files to import into a website, but it originated with a window manufacturer who had daily csv file dumps somewhat similar to your requirement. (Hence my insistence that it would work.)
I intend to offer it as a download on my website as well, once I figure out what the issue is. As noted, I am not getting an error. I am using Access 2019 Professional 64 bit, so it should behave the same as Access 365 64 bit.
- George_HepworthSilver Contributor
What do you mean "...get data from a folder..."?
In Windows, data is in files, files are in folders.
If you can explain more clearly what it is that you have in that folder, we could attempt some suggestions.
That said, Access is very powerful in importing data from a large variety of data sources, so I would imagine what you want is highly doable, we just need a clearer picture of that data....
- HarrisonBeckCopper Contributor
Hello George_Hepworth,
Thanks for getting back to me. What I am trying to do, is connect to the folder and use Power Query Editor in order to load into Access the most recent file regularly dropped into that folder. Both PowerBI and Excel have the functionality in the picture below.
From what I was googling, you can use SQL to do this, but I'd like to avoid that if possible. I'm trying to show my co-workers they can do this instead of copying and pasting data. Currently, I have a linked table set-up (Access to Excel), and I am pulling data from a folder in Excel.
Let me know if you need additional info.
Thank you,
Harris
- George_HepworthSilver Contributor
What FILES are in that folder to which you are referring. You are importing FILES, not FOLDERS. I imagine they are .csv or .xls or .xlsx FILES, are they not?
Or to put it more accurately, that dialog points you to a FOLDER containing FILES, and once you identify the FOLDER, the next dialog offers you a choice of the FILES in that folder.
And that is exactly the same thing Access does, albeit the dialog is slightly different. It looks like you are already doing it, in fact.
"I have a linked table set-up (Access to Excel), and I am pulling data from a folder in Excel." To be more accurate, that data is coming from an .xls or .xlsx FILES in that folder.