Forum Discussion
Get DB Data From Folder
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....
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
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.
- 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.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.