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 ...
Forum Discussion
No, there is not and you'd find it unwieldy if there were.
Basic rule: Excel is not like Access. When using Excel, one has certain tools and functions available. When using Access, one has other tools and functions available.
While it would theoretically be possible to implement bulk imports from multiple worksheets into Access, the reality of how Access tables work would make that difficult.
You always have the option of automating Excel, though, using VBA to import multiple external data sources, such as a CSV or worksheet.
Basic rule: Excel is not like Access. When using Excel, one has certain tools and functions available. When using Access, one has other tools and functions available.
While it would theoretically be possible to implement bulk imports from multiple worksheets into Access, the reality of how Access tables work would make that difficult.
You always have the option of automating Excel, though, using VBA to import multiple external data sources, such as a CSV or worksheet.
It would be unweidly for MS Access developers but they could add "From Folder" to the dropdown and let it append data the same way so we end up with a 150 MM row table without a thousand import operations. Excel has a row limit and Access apparently doesn't.
"... add "From Folder" to the dropdown ..."
I don't think that's in the cards for Access, for a number of technical and practical reasons. As I previously noted, VBA is a very powerful programming environment. You could create a procedure to append the records from one or more identically structured csv or xlsx files if you can be certain that they are actually consistent. I have had to do that for clients in the past, as a matter of fact. The key was a guarantee by the client that each csv would have the same columns, with the same names, in the same order, and with every record having the same datatypes in those fields. NO variations permitted.Access requires structured data for tables: the same fields in the same order with the same datatypes in each field (no mix of text and dates, for example). You simply can't guarantee that consistency in some sort of bulk import of all items in a folder. Not unless you control the files going into the folder in the first place.
However, adding records from files with different columns or columns in different orders, which is 100% possible in Excel, without the intervention of some logic that ensures compatibility, is a bridge too far for a generic process that just asks for one or more files.
It's a nice idea, but not worth investing development time and resources in pursuing.
- On further consideration, I guess it could be possible to import multiple csv or single sheet workbooks into multiple tables in Access. And that would leave you only the task of validating the data in those tables and appending them to your master table. I'm not very optimistic it's going to happen, though.
George_Hepworth No files are perfect but our files are identically formatted. Each 2+gb monthly folder has 67 county text files and there are a decade worth of monthly folders-all with the same headings, data type, etc. . In a perfect world, I could import a folder of folders of text files all in one fell swoop.