Forum Discussion
Dynamic connection to files
Hello there,
I have a a table called "DATAFILENAMES" this table contains rows with the direcrotory of files. (1 Column).
The files are ACCESS files (yeah I am using access). the challange I have is the the Access files are created automatically, and placed into a folder. and each time there is a new file added I need to create the connection to that files and the update the "MAINDATABASE" to append that table.
I have tried to do this with from folder option but it is way to slow (no idea why). and I get the below error when there are a few more files:
"DataFormat.Error: The number of items in the list is too large. Buffered lists can support up to 2147483647 items and streamed lists can support up to 4503599627370496 items.
Details:
2147483648"
so what I would like to be able to do (and I think it is a function that I need - but I am just guessing), is to be automate the manual task that I am doing. The list of the files paths/directories refreshes, then based on that a "Loop" to append that data.
I hope that makes sense?
Hi MarkCBB,
According to your description, the folder contained multiple access files which are created automatically. And you used Folder data source to connect to this folder and append those files, right?
In your scenario, you can create a query connect to the folder, then open Advanced Editor, define a function in M query, then pass dynamic file path and file name within that folder. For more information, please refer to this article: Using Power Query to Combine Data from Multiple Excel Files into One Table .
Best Regards,
Qiuyun Yu
2 Replies
Hi MarkCBB,
According to your description, the folder contained multiple access files which are created automatically. And you used Folder data source to connect to this folder and append those files, right?
In your scenario, you can create a query connect to the folder, then open Advanced Editor, define a function in M query, then pass dynamic file path and file name within that folder. For more information, please refer to this article: Using Power Query to Combine Data from Multiple Excel Files into One Table .
Best Regards,
Qiuyun YuThank you, this is just what I was looking for.
