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 ...
HarrisonBeck
Apr 01, 2021Copper Contributor
Hello,
Our IT team dumps .CSV files into a folder (or multiple different folders) regularly. I'd like to connect to the folder so that I can pull the most recent .CSV file dumped into the folder. This is exactly what I'm trying to do, but in Access: "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."
The linked tables I am pulling in is data set as a table within a .xlsx file. This works, but I'd like to connect directly into the source folder instead of using Excel as a bridge via a linked table.
Our IT team dumps .CSV files into a folder (or multiple different folders) regularly. I'd like to connect to the folder so that I can pull the most recent .CSV file dumped into the folder. This is exactly what I'm trying to do, but in Access: "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."
The linked tables I am pulling in is data set as a table within a .xlsx file. This works, but I'd like to connect directly into the source folder instead of using Excel as a bridge via a linked table.
George_Hepworth
Apr 01, 2021Silver Contributor
Access doesn't work that way (nor does Excel really, as I previously noted). Once you browse to the folder, you still have to pick one file, .csv or .xlsx or whatever.
Also, you have revealed another critical detail not present earlier:
"The linked tables I am pulling in is data set as a table within a .xlsx file."
You MUST use the .xslx file to get to anything within that file, including that table. So, the whole request seems a bit unrealistic in that sense. In other words, that data set does not exist outside, or independently, or separately, from its container, which is the .xlsx file. Hence, the data has to be sourced via the .xlsx file.
I suppose you could save the data set in a different file format, but that presents the same circumstance. It would not be possible to have a persisted data set independently of the file containing it. Interestingly enough, disconnected data sets can be instantiated as recordsets in memory from within a relational database engine, but that's no help here.
You have to have a persisted dataset, laid out as a table, and that table must be in another container, i.e. a file, or in this case, the .xlsx file.
Is it not possible to create a permanently linked table from Access to the table in Excel? We do that frequently. The data in the table can be updated in Excel and that new data is reflected in the Access table.
- HarrisonBeckApr 01, 2021Copper Contributor
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!
- George_HepworthApr 01, 2021Silver Contributor
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.
- HarrisonBeckApr 02, 2021Copper ContributorHello,
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.