SOLVED

Automate - Merging Excel Files from Multiple Folders

Copper Contributor

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?

4 Replies

@RAllenA 

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.
best response confirmed by RAllenA (Copper Contributor)
Solution

@RAllenA 

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.

@Sergei Baklan
Thanks again for your advice. That eliminated the need to sync files and query OneDrive. I've set up the sheets to query directly from SmartSheet-- one per sub-channel-- and I will find out if the appending sheet can successfully update when the next batch comes in tomorrow morning.
1 best response

Accepted Solutions
best response confirmed by RAllenA (Copper Contributor)
Solution

@RAllenA 

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.

View solution in original post