Forum Discussion
Automate - Merging Excel Files from Multiple Folders
I'm looking for a way to merge Excel sheets from different folders. Situation: Excel files are added to a SharePoint each day via email delivery to a sub-channel. SharePoint automatically puts them into a folder based on the delivery month... so in the future, there will be new folders created automatically.
I've tried to use Power Query to pull all the files from the parent folder-- it's utterly stumped, and won't read the files themselves; it meticulously pulls data from the email (.eml) files, and only pulls the file properties from the Excel (.xls) files.
How do I get it to read the .xls files for data when they're in different folders-- and more folders will be added over time?
To query the single file located in SharePoint connector is
Data->Get Data->From Other Sources->From Web
Enter file URL taken from File->Info->Copy path if to open the file, the only to remove ?web=1 at the end.
Alternatively file URL could be taken in SharePoint, at the bottom of Details pane for the file
To query the folder use Data->Get Data->From SharePoint Folder connector, enter root URL on the site with library, on next step filter on desired folder.
Not sure what exactly do you mean under merging of files. If all of them have the same structure, you may access them from parent folder using File->From SharePoint Folder connector. Filter the list as needed, remove unnecessary columns and combine files from Content column.
- Thank you for your reply-- I'm using Power Query to append sheets from multiple SharePoint folders to one file. The process has been a mess, and I don't think it's necessarily a fault in Power Query, but in SharePoint. Excel seems to be unable to access files in a SharePoint folder directly, but it can access them via OneDrive when they are synced.
Basically, Excel doesn't recognize the direct file path to the SharePoint folders.
My solution, if it helps anyone, has been to sync the SharePoint folder to One Drive, set up the Power Query to get data from the One Drive folders, and then grant access to the One Drive folders for any user who will need the final results. It's not pretty, and complicates manageability.To query the single file located in SharePoint connector is
Data->Get Data->From Other Sources->From Web
Enter file URL taken from File->Info->Copy path if to open the file, the only to remove ?web=1 at the end.
Alternatively file URL could be taken in SharePoint, at the bottom of Details pane for the file
To query the folder use Data->Get Data->From SharePoint Folder connector, enter root URL on the site with library, on next step filter on desired folder.
