Forum Discussion
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 ...
Thanks for all the info. It looks like I'll have to have the Excel file make the connection to the folder, then used the linked table function in access to get the day. I'm going to perform what's described in your last paragraph.
Thanks for the info!
At the risk of flogging a dead horse, Excel is NOT connecting to the folder either. It is connecting to a file (.csv file) within that folder. The data connection dialog specifying "Folder" requires that you browse to the folder and then pick one file from within it.
What you are doing is formatting that data in a table in Excel once you select it.
And that brings up another angle, which I should have thought of earlier. Sorry.
You CAN link Access directly to any csv file as well. So because the csv files are in the "folder", you could link Access directly to them and bypass Excel that way. I didn't think of that because it sounded like you are doing some formatting and maybe consolidating with the Excel table. But if it's just the same raw data, and all you need to do is import, or link to it, that's a possibility, too. If the .csv files change, of course, you'd have to relink to the newest set as needed.
This is where we keep talking past one another, I'm afraid.
"... specifically the most recent .csv file dumped into a folder."
You are NOT getting the data from the folder itself; you are getting the data from that .csv file which is inside that folder.
The good news is that Access certainly does have that ability. It would be the same thing as linking to the Excel file. You can link Access to a very disparate variety of data sources,
You can automate the process of navigating to that specific folder, locating the .csv file with the most recent file date, and importing or linking to that file. It then becomes available to Access as a linked table. I may have gotten the wrong impression about the need to put it in an Excel table. If the raw data is the same, then Excel is not useful as an intermediary step. If the data is transformed in Excel, which is the impression I got mistakenly, then it would be needed.
I don't have sample code at hand, but I have done similar tasks many times, Access supports VBA functions, such as Dir() which can locate files in folders. Here is an older download which does what you need to do, for example.
- Hello,
I think we're saying the same thing but I'm not being clear enough. I'm intending to extract data from a folder, specifically the most recent .csv file dumped into a folder. Excel and Power BI allows you to use Power Query to connect to a folder, select the most recent file, and pull the data from that file into a table. It sounds like Access doesn't have this functionality.
Regarding your last paragraph, I don't want to connect directly to a .csv file because this is not an option "If the .csv files change, of course, you'd have to relink to the newest set as needed". For my needs, pulling the most recent file in a folder needs to be automatic.
Thanks for your help. I think I'm going to keep utilizing the linked table functionality for now.