Get DB Data From Folder

Copper Contributor

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 

22 Replies

@gdrahal 

Once again, it is not likely Microsoft will implement this.

In the meantime, look into creating a recursive VBA function to do it. Here's one such procedure:

https://stackoverflow.com/questions/30356373/using-ms-access-to-import-multiple-text-files 

 

@George_Hepworth Will do. And thank you.

Replying as I have a similar but different use case and just read through the thread. I think my solution might be useful to you.

 

My problem: I want an Access database to link to all Excel files in a specified folder. These files contain the same kind of data, and so ideally I would rather they all aggregate into one table as opposed to each being their own.

 

My solution: I created a new Excel sheet and used PowerQuery (within Excel - Go to Data >> Get Data >> From File >> From Folder) to link to all Excel sheets in my specified folder. In your case, you could then specify within PowerQuery that you want the most recently-updated file or whatever else you need. Save this intermediate Excel sheet somewhere, and then link to it from a database as usual.

 

One caveat about this solution - I am not sure how data refresh will work here. In an ideal world, each time the database checks the linked file, the linked file should check its linked folder. That might be the case here, but I'm not sure.

 

Either of you should feel free to let me know if I'm way off here - I am learning and would appreciate being steered straight if I'm off course. Have a great day.