Batch Merge Byline with airSlate SignNow

Batch merge byline

hello and welcome to this video on how to import and union multiple Excel files so it's a very common task you want to do in all tricks and I just want to run through three scenarios of how you can achieve that each a little bit more complicated than the next okay so the treat scenarios are you've got multiple sheets in the same workbook so multiple tabs in the same workbook and they're all identical so maybe it's downloaded from a system and every month or every week it's exactly the same format or what's known as schema and you just want to be able to Union all of these together the second example is similar so you've got multiple sheets in a workbook but the format might be slightly different from tab to tab okay so how does all how do you do that in all tricks and for that we'll need what's called a batch macro and then finally we'll look at importing from multiple different Excel workbooks but where we have different schemas as well so where the format isn't identical okay so let's jump into ultrix and have a look at this for real okay so I'm gonna open a bit a blank workbook and we'll go true scenario one so I'm gonna import data and I'm gonna connect to my file that I've set up so I have this ultrix multi-sheet same schema okay so it's a single file it has two sheets sheet 1 and sheet - but they're identical in terms of structure and I want to be able to Union these together alright and just to say this example to work whether you 2 or 10 or 20 sheets within the same workbook so rather than import them one by one and try and manually uhn them I'm gonna import the list of feet names and you can see here when I run this one and she - I can then go up and grab the dynamic input tool and add that to my workflow I'm going to give it a template to look up so I'm gonna connect again to that file same schema file and I'm just gonna pick one of those sheets ok so it just gives it a template to work off and the main setting here is I need to tell us I wanted to read in a list of the data sources and data sources in this case are the sheet names okay I'll add a browse tool run and you can see here it's red in two records were red sheet names the dynamic input tree were red from sheet 1 3 records were red from XI 2 and I now have 6 records in my helpers ok from the first table on the second table ok it's a really simple example import the sheet names and use the dynamic input tool so let me just add that to a container and we can close it down to keep this nice and simple ok so we'll call this one and let's go to the second scenario so snack is the second scenario is very similar we've got multiple sheets in the one workbook but this time they're different schemas so the the structure of the file of each tab is not exactly the same okay so we can't use the first method so we have to use something different so let's start again let's go input data and this time I'll connect to my recent files and should be this one so this particular example is three sheets I'm gonna do the same thing I'm going to import the sheet names so it imports store 1 store to store 3 the problem here is if I was to add the dynamic input to this and just repeat the same process here just connect to an example one read in the names I click run I get an error to say 4 records have been read from store one but store 2 and store true 3 have different schemas and therefore don't import so my final date assess only contains the four records from sheet that's obviously not what we want and this method doesn't work because the files are different structures so it's not as straightforward and the files are slightly different structures so we need to do this in another way ok so the first step I need to do is I need to create a proper a path here okay so proper file path so I'm gonna export the full path here on option 5 and then we're gonna go in and out of formula in a second so so you can see here I get the file name or the full path but instead of listing the actual sheet here at the end it says list of sheet names so what I want to do is I want to swap in store 1 here where it says list of sheet names and that's what this formula tool is gonna do so I'm gonna create a new formula called file name so it's gonna overwrite this particular aspect and I'm gonna do replace replace within the file name field so it's gonna look what in file name and the target I'm trying to find and they're out and let me type this out let's grab the exact text here so the target here is list of sheet names and the replacement is going to be the actual sheet so I wanted to replace where it says list of sheet names with store 1 check that that's worked perfect so we've got our store 1 store to store 3 as almost 3 separate files now that fixes I suppose kind of step 1 for us but I want to add in the batch macro now that will allow us Union files together that have different schemas and this is probably the most important bit ok so I'm going to create a new workflow here to create my batch macro I'm gonna impose that file again we're just gonna choose one of these as a template ok so I'm gonna pick store 1 here as my template file but again it doesn't really matter which which if the EZ pick is it's going to Union them all together eventually and now to turn this into a batch macro I'm gonna go to my interface tool apalis and I'm gonna add a control parameter so let me just do that last step again so control parameters anti drag from the question mark to the lightning bolt here and I get this action to here and importantly I need to click on what I want to update so when this macro runs what should change and I actually want the the name of the file to change okay so that's what I'm looking to update here is the name of the file so it's going to run through each of those file names that we've created that's gonna run the macro for each one of these now a couple of steps here I wanna add on that a macro outputs this is really the connecting like the Adaro here that you see so we're able to use it in our workflow and then the last step here is I need to go to the interface designer now if you can't see that it'll be view interface designer to bring this up and this is an important step the most important step probably but is easily missed so on the properties here you can see the output mode is all iterations of the same output schema so that means all of the files that I run through this have an identical format or structure to them and that actually is in the case as we know they're different so I want to also config by name which means all tricks are going to look for columns that have the same name and it's going to align those one on top of the other you could also do it by position so if you know the column a b c and d should line up with a b c and d from the next one regardless of what they're called and auto-config by position is another way of doing this okay so once i've set - we can then save this as a macro so i'm gonna go save house put it in here as our Excel macro demo that's what I'll call us I'm the cool thing about macros is they can be reused so let's jump back here I'm gonna right click on the canvas insert macro and put in my demo macro so now what I have to tell it is choose the fields to pass in so that's really the file name and we've created that called file name here and that's what I want to pass in to the macro and finally we'll just out of browse tool so we can see our data from this and you can see now that ultra six it has ignores the fact that these of different schemas has just lined up each of the column headings as it's seen so that comes from sheet 1 sheet 2 and sheet 3 I click on my white space and you can see here three records were read so that was the list of sheet names and eventually I've ended up with eleven records they've come from my different iterations of the macro okay so you can re-watch that and see how that's configured but very very useful to have that unless I said you can reuse these macros quite a lot it's not something you've to setup each and every time so again very very useful so again let me just add that to the container we can turn that off and we'll do the very final example of this which is where we have different Excel workbooks entirely and they might have different formats and we want to bring those together so the change here is is within the input instead of using an input data which points to a single file I want to use a directory tool which allows me point to a folder a directory of files okay so I have a couple of files here that I want to import so different schema one different schema two I'm not there there what I'm looking for so two separate files slightly different name here okay so let me just grab the file name for one of these properly okay so what the directory tool is it looks in a particular folder and I look for any file that meets this specification so this specification here is a wild-card so essentially this will try and merge all of the files in my miscellaneous folder which is not really what I want I wanted to look for files called ultrix Excel different schema not just one but maybe one and two so I can change that number one to an asterisk and that means it's like a wild card so to look for any file that starts like this and then as any number basically before the xlsx okay so let me just run out and show you so you can see down below here it's import these two files or it's found these two files within that directory and it's very similar to our previous example where we created this full path formula it's already brought this in when we use the directory tool which is very useful so now what we could do is just add that macro that we've already made because we already have the file path the full path here so you can change it to full path and again add our browse tool and now it's read in my two different files it's put them into the batch macro regardless of them having slightly different schemas or structures and we have an output so this has come from far one and this has come from file - okay so three different scenarios we've run through identical scheme is in the same workbook different scheme is in the same workbook different schemas across multiple workbooks thank you very much [Music] you