Extract Excel data from multiple folders & files (No Tables)

[MStark171]

Hi,

I'm unsure how to do this but I have probably a couple hundred or so files to go through for data and I'm unsure how to easily extract the data from them onto one sheet. Luckily, each sheet only has one tab of data so my hope was to create a process that could take each sheet and put onto a new single sheet with each excel file it's pulling being a tab on the new sheet. I've put a basic format below which I hope will help explain what I'm trying to do. Additionally, If I can only extract sheets from one folder that's fine too. Just need to speed up the process by any means.

Procedure steps

1. get list of all excel files within folder (preferably within each parent folder i.e. all 2020. Still ok if it can only pull from a single folder i.e 2020 - January)
2. Within each folder is maybe 15 excel files. Take each file and copy data inside each and put onto a single excel output sheet
   1. Picture below; so, if there's 5 excel files named (a,b,c,d,e) take all data from each sheet and convert to tab on output excel sheet or add data to one sheet tab only if that's the only way or easier. Ex: new file (Output 1) with tabs (a,b,c,d,e) correlating with data from said sheets it extracted from

Let me know if you need more information. Thanks

[trice602 · Accepted solution]

Hi @MStark171 ,

You can do that without the use of Power Automate or you can use inside Power Automate.  If this was my project, I would do outside of Power Automate unless there was a process that required it.

So essentially open a blank workbook and Get Data from folder.  Here you can combine all the files from a folder.  You can also do this from other folders, etc, etc, etc. and then you can easily combine all excel sheets into a single table of data to use in automation projects.

Now if you need to do this in automation, again ask yourself why you need it, you can get data with dataflows and do this in the cloud.  Then when you combine sheets you can save the output in a dataverse table and use in automation.

Again, unless required, my preference is to combine manually with Power Query since you are working with files from the past; there's an assumption they are not changing.

Another option to consider is write quick automation that copies all sheets into a temp folder and then simply process that folder you get data > folder > combine ....

You have options and you should be able to accomplish this with Power Query directly from Excel or Dataflows in Dataverse.

[Henderson87]

@MStark171 There are really 3 options for this.

1. For power automate cloud you need the data in the worksheet to be in an excel table not just in the sheet. It looks like yours is not in a table. Then you can loop through and add data using variables and get file content using path. This isn't a perfect solution and needs a lot of set up work for it to be done.
2. You can do what you want to do in power automate desktop much easier. Power automate desktop is more complicated to learn, but ultimately that gives you more control and you can have it pull information from specific cells and add worksheets and things of that nature.  
3. You can use a macro to accomplish this where you can record the steps you are looking to do and make a few tweaks in visual basic and have it complete for all the files.

Regardless of the method you choose to do it exactly as you described you will need a good understanding in power automate desktop or visual basic. Both of these can handle the no tables and changes in the worksheets.

[trice602]

For clarification:  Power Query inside or outside of Power Automate does not require your data to already be in tables; that's the really benefit here, mass file processing of range data and your data will come back as a single collection of data.  It could be a csv, excel data, or actual table inside excel or in dataverse but regardless it is available downstream in automation.

[MStark171]

I did not know that was an option. I just figured power automate since I've used it in the past to automate other things and figured it was the easiest way. I'll try it out and see if it works. Thanks

[trice602]

Hi @MStark171 ,

You will love it!  Here's a sample solution that works amazing!

In the middle of the night, I receive 4 separate inline html tables of data by email.  With Power Automate I turn that data into four separate csv files and save in a folder OneDrive or SharePoint.  At the end of this automation, I then use the Power Automate action "Refresh a dataflow".

That dataflow is essentially Power Query (in the cloud) and I take the 4 csv files in that folder, combine them, do other things and save the data in dataverse table.  Dataflows lives in Power Apps but is the cloud version of Power Query.

Then I have a separate flow that says, "When a dataflow refresh is complete"... do these actions.

Once you use this, a lot of automation options will open up for you!

[MStark171]

So I ran into a problem where it turns out besides each sheet being named differently. They also named each tab in said sheets differently as well so I haven't been able to import everything besides a single sheet. Additionally, each file seems to have on average 70k rows of data and I believe the limit is 100k? Any ideas how to work around this and pull with a filter as well?

[trice602]

Hi @MStark171 ,

If you are using Power Query, the tab name is not important because you can get the first sheet everytime.  The key to Power Query (cloud or non-cloud) is that the columns are named the same and in a consistent format.

There isn't a limit with Power Query on the # of rows.  

[MStark171]

Strangely I was getting an error earlier and it would only pull one file but I found a reference online and managed to get it to work although I still hit a row limit on imported data. Thanks for your help