How to Combine Multiple Excel Files with Power Automate

[Music] hello everyone and welcome back to another video so today's video is actually probably one of the most requested videos i've ever received and it's back on power automate and it's demonstrating how we can use the tool to combine multiple files from in the same folder so the scenario we'll look at today is we've got some old sales data again mentioned sales data this is all complete random data so there's no sense of data being obviously viewed here at all just in case that comes to mind so we can see if we go into snapshots we've got three files and do ignore the historical dating on them and each file contains some sales data for that actual date so if i was going to this one for the 31st of jan you'll see we've got five columns of data and what 13 rows or 12 rows if you exclude the headers and what i want to do is for each of these files in here we want to combine them into one master file so we've only got three files here but regardless of if you've got three files or 100 files this process will work based on the file's content so whatever's in that folder it is all going to get combined into this process so what we'll do also one thing to mention i've also got a templates file here called headers so just some context before we jump into that all this simply is is what it says a template file for headers and it's just an easy way for us to pick up this file and to use the headers in here rather than having to paste them in our step so template files are great uh sort of a trick to save time when it comes to using headers but just for context as when we look at that later on you know what that is so in power automate we'll jump straight into it so the first thing we need to do is obviously identify the files within the folder that we wish to combine so simply what we're going to do is go into our folder option down the left hand side here and we're going to go down to get files and folder and we'll pull that across into our main pane here so we just now need to navigate to that folder so for me it's going to be if i can remember where it is it's going to be in here and it's going to be in snapshots so we're just selecting this the folder as a whole and as you might have seen in previous videos there's many options we can apply to this we can even do some filtering to the files if it's applicable but for us today we don't need that so we'll just simply click on to save and then next before we get into looking at each of these files we're then going to go into launch our template file so we'll go to excel and launch excel drag that into the middle there and we don't want to open a blank document because we want to use our template file we just looked at which contains our headers so again just to remember where i have to source this from so i think it's into these folders at templates and then headers and we'll go okay that looks good yes we'll leave it as visible it's not a problem and this is where obviously i don't know if i touched on this before if you scroll down you're actually able to rename your variable if more useful so excel instance let's put this excel instance output just so as we look at this as we build we will know that output is going to be our final combined file so let's save that so i believe we now have the two key elements we need to start uh going through and getting this data so the first thing well not the first thing because obviously it's now the third thing but we want to now loop uh through each of these files in this folder or the files that we've got in this very first step here so what we need to do there is simply search for each i'm very lazy so i always like to use the search box there and once it's worked out what it's doing sometimes take a bit of time yeah we can see within loops we've got this option of for each so this is logic that we want to apply to each of the files that are in step number one within this folder so the value that we need to iterate here is simply going to be current item so if we just go into our variables on the side here sorry we can select list of files select that and you can see it's going to store it as current item so basically what it's going to iterate through is the list of files we can see at the top here this being the variable that's been received from going into the folder so if we do save on here we've now got our loop set up and ready to go so what we'll now need to do and again i'm sorry for jumping over jumping around but hopefully this will start to make sense as we step through this is we're going to go back and do an excel process and what we need to do is we need to understand which is the next available row within our template folder so if you can imagine what's happened here is we've launched our excel file containing our headers so we know there's data in row number one but we now need uh power automate to identify that the next free row or next available row is going to be row number two and that's there where we want to now paste our data so we need to go into the advanced section here of the excel options and we right the very bottom you can see there's get first three row on column so we're going to click and drag but this time make sure we drag it into our for each loop so excel instance so which excel files are going to be looking at well we've only got one open at the moment and we can see it's excel instance output at the top here and if we to do this drop down you can see there is only the one option had we got multiple excel files open you would see obviously a lot longer list there so for us we're only interested in column a all columns hopefully are going to be the same but ultimately column a serves as the good test for us to know which has got the next available free row okay so now that we've identified which is the next free row we can start loop or actually opening each of the files within our for each loop so what we'll do once again we'll launch excel make sure it brings into the uh for each and again we don't want a blank document what we actually want is to open the following document and where it says document path here all we need to do is in the variable here select current item so current item being the current item we're on within our loop of all those files so select current item deselect uh make instance visible no i'm not interested to make it invisible but you can if you wish and we should go save so you can see that's ready for us so we've now opened that first file the next thing we need to do now is obviously read the data within that file so if we go into again our list of excel options we should have an option here from read from excel worksheet yep so you can see just down here read from excel worksheet we'll drag that into there excel instance so now we're going to have multiple options available so we've got our output file which is one here but we need this excel instance so this is the current one that we're working with obviously having loot through the files so what do we want to retrieve the value of a single cell no we want all available values from within the worksheet obviously you could define a specific range of cells here but what we're going to do is keep up the most basic option variable and just take all available values from the worksheet we're also quite confident that all the fact we we are confident that all the files are going to have that exact same template because there's another process that uh is building those files in the first place but again we'll stick with a simple option what we've got here so we're now reading all of that content then once we've read this data obviously we now need to paste it into our output file so in order to do that we will go down and find right to excel uh there you go just slightly big there you go right to excel so this is where we're now obviously going to be using the information we gathered up here the get first free row on the column so the excel instance we want is the excel instance output the value to write is going to be our excel data so where's it gone data table so this second one here so this is the excel data that we've got when we read from this excel worksheet so we can go select on there right mode on that specific cell so we want to go into yet on specific cell and that specific cell for us is going to be column a and the row is going to be if we go into here once again the first free row on the column so if you select okay so we can see what's happening here is we're now looking at our output file we want to paste into that file our excel data what was just captured from this the last file opened and we want to paste it onto a spec specified cell and that specified cell was is within column a and it's going to be the first three the first free row in the column so what we've captured here and then probably what you can start to see is the reason for this get first free row being at the start of the uh each loop is for every time it goes into a new file and obviously as this file the mark the output files gets bigger obviously that free row is going to become lower and lower in the list as more data is added to it so if we click save we've got that element done so now all we need to do is tie it off so the first thing we're going to do is close obviously the current file so excel instance so we want to do excel instance here because that's what we want to close uh we're not bothered in saving the workbook and then lastly what we want to do this will now obviously loop through and do every single file within our specified folder but once it's all done the last thing to do will be obviously to close our template file we've got here so what we're simply going to do is we'll go into close excel and we'll bring this outside of the for loop because we only want it to close once all the iterations are done so excel instance is going to be the output before closing yes we do want to save so go save document as default extension so we'll leave the extension as it is but for us the file path is simply going to be navigable we'll navigate to where it's going to be so documents excel i've got create a new folder called merge and i'm simply going to call this combined but again you could obviously call it something a bit more creative and then we'll select okay and finally save so this is the extent uh the extensive list the whole ten steps we need to obviously build this combined file so what should we shall do as always is let's minimize this and we'll bring up our file explorer for our not our template one but we'll go into our uh destination uh file so let's go into here there we go we've got it ready so what we should hopefully see when we click run on here is it'll go through and loop through here i think it was three files and ultimately we will see a combined file being created in this merge file here so route verb delay let's hit play i say play it's actually called run but just to be really specific cool so you can see it's opening the file and then you'll see it start to continuously loop through all of these steps here and obviously we can see it looping around on the screen and shouldn't take too long because not many files and yes we can see it went down to that last step and finished and we've now got a new file created over here called combined so if we just double click onto that and just see how the content is looking yeah we can see that we've now now got all of our data is in that file however we can see that our column headers are being kept in here multiple times and that's because i've made a mistake and not changed one piece of information so what we'll do is we'll go and delete that combined file as if it never existed so what we need to do now is just go back into our flow and specifically step number six so read from excel worksheet so we'll just double click that and within the advanced section you can see we've got first line of range contains column names so we'll just tick that just so it knows that we're not interested in that information and click save and once that's saved yet we're all good to go so one more time we'll just click run and hopefully this time it will now work as intended see you guys head it's got the header file now open it's going to continuously loop through and open all those other files and you can see the variables obviously updating as we go through great so we've now got to the end just waiting for for it to finish yeah and then we can see we've got our combined file so let's now open this one and there we go we can see the data is looking a lot better and how we want it so if we just expand these columns we can now see all of our data has been captured we've got the third for november 31st to 12th and the 31st of january so we know all three datasets have been captured and of course our column headers are only appearing once uh rather than multiple times like we saw earlier so one last thing i just noticed as well is our flows seem to keep looping at the end there so all i'm going to add in there as well is if i search into stop and again this is probably good uh good to know for good practice uh once obviously it finds the options yeah we've got flow controls so i'm just literally going to go down and bring this stop flow at the bottom here inflow successfully just so it knows that that's obviously the last step is number 10 and then it can exit the flow just so if there's any confusion it's not going to continue running so there you go i hope you enjoyed that video and it gave you an answer to your question if you're one of the many people who contacted me asking for such a video if it did please don't forget to give the video a like because it's not only greatly appreciated by me but it does help that all imported youtube algorithm if you haven't yet subscribed to the channel please can i ask you to hit that subscribe button and also hit that bell notification button that way you'll be notified as more of our videos come out in the future so thank you very much again for watching and we'll see you in the next video [Music] [Applause]

Channel: Essential Excel

Views: 37,868

Keywords: how to combine multiple excel files with power automate, how to combine multiple excel files, combine excel files, power automate, power automate desktop, power automate tutorial, tutorial, combine files, power automate excel, append excel files

Id: A9A8q5BBO_I

Length: 14min 16sec (856 seconds)

Published: Fri Sep 16 2022