SharePoint Files to AWS S3 via Power Automate

what up y'all what up what up i am blue in today's video we're going to be talking about how to create a power a microsoft power automate job that's going to connect to the sharepoint site download the files and drop it to an s3 bucket but the way we're going to do it is because there's not a connector and power automate that is able to connect directly to an s3 bucket so what we're going to end up doing is we're going to be dropping the files into aws transfer service what we're going to be using aws transfer service which is an sftp site and the back end is an s3 bucket so it's an sftp site but because the way aws family transfer family service works the back end is an s3 bucket so as files get dropped to the sftp site they drop into an s3 bucket so that's how we're going to do it i'm going to walk through the steps what we need to do also if you are interested and want to see how to set up aws transfer service check out my other video aws transfer family that video will walk you through step by step exactly what you need to do from a permissions i mean from a policies set up from a row setup from a ssh key it'll walk you through all those steps hey guys before we get started if you can give me a like give me a follow man i appreciate it man i really do let's get started guys so what we're going to end up doing again we're going to be connecting to sharepoint and we're going to be taking the files from a specific folder sharepoint and we're going to drop those files to sftp right so just so you understand the flow we're moving files from sharepoint to sftp but keep in mind though behind the scenes of the sftp what's really happening it's dropping into an s3 bucket right and that's key because one of this is an action that i have got a lot of requests from from people is how do we move files over from sharepoint to an s3 bucket so i have a video that shows you how to do that using python okay but if you want to use a non-programmatic way to do it then this is gonna be the way to do it what i'm about to show you so let's go ahead get started as you can see i am the sharepoint site that i have it's called the development and then i have i enter my documents i have a folder called data 2022 right so documents data 2022 then i have three files in here right it's a custom list of customers for january february and march right so their csv files they're in this folder all right so we know what we're pulling so now let's kind of go through let me am i connected let me see um where's my aws hold on i think i'm connected to the wrong let me open it up in the other browser yep here it goes so let's go to um the s3 bucket just kind of show you we're going to be dropping it to this bucket which is called sftp i am lu let me move it over so i have two folders in here so again if you watch the previous video that i made which is um what is um aws family transfer family we created pretty much an uh bucket that's used for that's used at the next sftp site and then i created two different folders one for each customer i call one of my customers apple the other customer google which means when they connect to the smtp they only have access to what they see inside their folder so right now there's zero files in there then of course there's a few files in here but that's all they see that's all they have access to okay so that's kind of what what i'm going to end up doing is i'm going to be dropping the files from sharepoint over to this the apple uh folder right so this is where it's going to be dropping into our s3 bucket all right so now that we kind of reviewed what we're trying to do let's go ahead and do it now let's get this done so go to power automate by me that's what we're going to be using again this is a microsoft service um if you have a a micros office 365 account you have this by default so it's already included um and you're good to go there are there there are some premium features that potentially you got to pay for but everything that we're doing here none of it's premium so it's all included you shouldn't have to pay for anything extra right you can start using it right out the box so what i'm going to do is i'm not going to use any of these templates i'm going to create a new one from scratch so let's go ahead and do that let's create um it would be let's do schedule cloudflow i'm gonna skip for now we're gonna schedule it but i'm gonna skip it for now so what i'm gonna do is let me find the schedule uh connector so these are all connectors right whether you're trying to connect to youtube trying to connect to outlook whatever it is you may be able to find it in here but what we're looking for we're going to be dealing with um three connectors really we're dealing with a schedule connector which is how often we want it to run we're going to be dealing with a a sharepoint connector and then we're going to be doing dealing with the sftp connector so those are three connectors so let's kind of go to the process but the first one is schedule so i'm going to click on schedule click on it again and then i'm going to start configuring my sk my let me make this a bit bigger kind of small so i'm gonna start making it i'm gonna start scheduling my scheduler so in this case i'm gonna have it run once a week um wanted to run once i wanted to be i'm i'm um central time so i'm gonna have it run under central time start so see how this is the format that it wanted in so that's the format that we're going to put it in um so let's say we want this to start on 20 22 06 0 2 t for time i want this to run um 11 p.m so it's gonna be 2300 2300 um on these days i wanted to run only uh let's say on friday at these hours well my bad okay so let me kind of step back so this start time that we have here um it's really when you want this to start in general uh i could put that i put it earlier today i could say um start yes yesterday uh june 1st right we're june 2nd but june 1st which is fine which means any going forward if it meets a criteria which it has to be friday it happens to be at a specific hour so in my case i want it to be 2300 is the hour that i wanted to start minutes it's gonna be zero again you can specify the minutes in this case you want it to run multiple times like more than once let's say you wanted to run at 11 p.m because i'm central then you want to run at 11 30. you would do something like that but in our case we're only going to have a run once which is at 11. as you can tell it tells us down here run at 2300 right if i were to put 30 uh let's see see now you it shows two times yeah 2323.30 which is 11 11 30. i only want it to run once 11. uh cool so now that we're done with the setup i'm going to hit next step the next step is going to be sharepoint so let's look at our sharepoint connectors now under the sharepoint list of connectors you have like many different type of sharepoint connectors right add attachments you could check check in file check out file copy so on what i'm going to be doing is i'm going to be looking for the get files connector properties only this is the one that i'm using um okay there goes boom so i don't know if you notice how it was kind of trying to it was trying to um whenever you click on the sharepoint connector uh keep in mind i'm logged in under my account i am loom right so if i go to my sharepoint site which again it's under my i am new account um it automatically logs you in so and then of course it will show you what you have access to so in my case i'm going to go ahead and i'm going to pick the site i have access to my the level of ment site that i that i created under my i am lu sharepoint site deliverment next i'm going to select the library name which is going to be documents again if i come over here it's under documents that's what i'm selecting over here and then i'm going to specify which folder i want to go into so again if i go to sharepoint it's going to be under the data 2022 folder so now let's click on the little folder icon and i'm going to go under share documents data 2022 so that's what i'm going to click on now if you know it you can just type it in like this right after a while you'll get familiar with it so it becomes easier but if not just search for it that's fine and also and then this if you want to like you know get sub folders and so on in this case we're not i mean he fought it a true to get it but that's fine i don't have any subfolders or nothing so it's good the way it is um show advance nope i don't need to modify none of this stuff so we're good i mean you could do like top count order by filter so on and so on i'm we're not going to those details there may be a need in your case but in our case we're not we're not there's no need for us to do that so now that i'm we're done with this i'm going to click on next all right so the next thing that we're going to have to do and keep in mind the way this works is our sharepoint connector is saying get files files as many files it could be one it could be ten it could be a hundred right it just depends now in order to handle when you're dealing with mini files you need to have um it's gonna there's gonna be an iteration process and you have to apply some kind of action to each item so in this case we're gonna be uploading each item to our sftp site now in order to do that we're going to use the apply um to each which is [Music] uh there we go the pilot each there goes as under controller um so i'm going to select i need to select something ultimately this is what's being imported in from here which is under get files is going to be my uh value so list of items so keep in mind get files is giving them back a list of many files because it's it's a list of many files what i'm doing here is i'm using the values because i want to get back one of those values like one of the files one at a time and they're going to do an action to do do some kind of action for each each value so that's what i'm doing here and then i'm going to add another action um the next action is going to be git file oops this is going to be sharepoint right so under sharepoint i'm going to use the get file [Music] where is it at oh what is it called get file context did i pass it already i think it's up here let me type that in get file there it goes get file oh no i just signed right there it goes get file context so this is what we're going to be using so let me go ahead and click it um again we're going to specify our site so the deliverment site and then the file identifier so in this case if i click on it if we go to the right we'll see all the properties again this is related to the get files properties which is up here so there's a list of different properties we could get the one that we're looking for is what is it identifier let me type it in identifier value that can be used in file related actions by selecting a file so this is we're going to be using let's see that could stay as as is that's fine okay so now that we we have this well pretty much what we're doing here we're saying hey we're getting the context of the file now right so we're connecting to sharepoint we're connecting to the right site we're connected to the right folder now we're in once we do our iteration where we're kind of going through iterating the list of files and starting with one file at a time now we're grabbing the context of each file so that's kind of what we're doing here the next thing is now we're going to take that context and we're going to want to upload it to an sftp site which again it's really going to be dropping into an s3 bucket behind the scenes so let me type in sftp don't use this guy over here this sftp this is uh depreciated already it's no longer uh i don't think you can't even use it to be honest with you if i click on it yep there you go so you cannot you don't see nothing because it's not being utilized anymore um so you click on the one that says sftp ssh and we're gonna do this guy create file sa sftp ssh all right so this is where we're gonna have to enter in a few things and let me go let me go back to [Music] um where's my aws at i thought i had it open over here there it goes so let me go to aws transfer family so this is our our server that's running right now our sftp server again if you want to know how to set this up as you could tell i created users here and you ever need to add more users you will add it in this section but if if you need help by setting up go watch the video i have a video that walks you step by step on how to set this up so that'll help you out so our endpoint is our um or um url they're going to be using to connect to you could add a custom host name i haven't set it up if you do be something like sftp dot you know my my app.com right or something like i don't know whatever your domain is but that's normally what it would be something similar nevertheless i'm gonna type this or paste it under the whole server name um the connection name is just for reference i'm going to call this uh aws transfer sftp right so that's what i want to call it so my name if i go back to it i have two now we said that we're gonna drop stuff into [Music] we're gonna move files into the apple folder right which is it's empty so this is our name i'm going to copy the name password stays blank because we're using ssh there is no password in this case uh ssh private key okay so again if you need help with that look at the video that i have that relates to aws transfer i will show you how to generate ssh keys in this case it needs it needs to be keep in mind whenever whenever it's asking for ssh private keys i don't think you mentioned it here um it doesn't it needs to be open ssh format it cannot be the putty form it has to be open ssh it has to be man so just keep that in mind so i already have some keys here this is the apple the public and private i'm going to open this up let's open this up sublime text and i'm going to copy everything exactly the way it is just copy as is and i'm going to paste that in here under the private key i did not add a a um a uh paraphrase on to my uh private key but if you did generate a paraphrase then of course type it in here uh port number will be 22 [Music] disable ssh um host key validation uh we could disable this it's fine for what we're trying to do it should be fine uh i don't have nothing here blank root folder pass so this could stay blank um i think we could leave it on blank we'll find out but i think so so this will be kind of an example let me see if i go back to the s3 bucket so if i come back to the s3 bucket as you could tell when we connect to this folder there are no subfolders in here but let's just let's assume that you did right let's assume that we had let's create a new folder and we're going to call this inbound and maybe and it's inbound because we that's where we want the client to put all the inbound files and then we could potentially have an outbound if we're trying to um you know give the client files back so i'm gonna create this cool all right so we said we created one called inbound we're gonna put this in here inbound gonna be our root folder pass or our root folder pass is really this but i have a subfolder so i wanted to drop it into the inbound okay now that we have this connected i'm going to hit create and if it's able to connect successfully we'll create if not we'll get an error message up here oh there it goes uh let's see let's click on the folder should show me the inbound folder uh no items it did not show me the inbound folder folder pass required uh let's see what do we do oh my bad so the folder pass is going to be the four slash um the file name is going to be file name with extension you want with extension because again if you're dealing with csv excel you wanted to add that extension as well and then to finish it off it's going to be file context show advanced that's fine get all metadata so now i'm gonna hit save let's see it's saving boom it's good okay so as you could tell we have three um we gotta i guess we got more four steps but nevertheless uh one one connector for the the schedule that's step one step two of getting the file properties again in this case getting all the files that belong to that folder or subfolders as well and then in this piece this is where since we're iterating over the list of files which is this is really a list we're applying an action to each file right in this case we're getting the context from each file and then we're this step here does a few things it connects to the sftp site then and then it's ultimately dropping the um the file generating a file name but but the file name that it's generating is really the file name that comes in the file you can manually type something in here or you could add some logic where you like concatenate right some kind of expression to like append to the file name we're not going to do that but you could do stuff like that if you want i'm just taking the file exactly the way it is dropping it in then of course you have your file contacts here that's it we saved it everything seems to work so i'm going to go back your flow's ready to go turn on it turns out it turns on and will run with its trigger got it so let's see um we're going to run this manually so i'm going to run it by hand again we have it on schedule so by default it will run on schedule but i want to run it you know manually to make sure that it works so let's run it and we'll see if we have any errors so let's run flow done we'll see down here where it would tell us the duration right you could refresh seven seconds running it may take about a minute again doesn't take long it just depends on how many files you have and the size and all that stuff um so far no errors that's good it's running so far it's running 29 seconds doing good 34 seconds come on it's doing good still running there goes so that's good 38 seconds succeeded let's go and confirm so now if i take a look at what we have in an s3 bucket i'm gonna hit refresh boom so see how it dropped everything in this in the main folder and i think i think i know why and we could fix that in a minute but it didn't work though it did drop the file that you can see it it moved it did its job it moved these files that we have here um so data 2022 these files and it dropped it over here now i didn't delete these files but all means but if that's something that you want to do you could add another step after the fact to ultimately drop those files again i'm not going to do this in this exercise but you could add another another connector that will literally delete the files if if you don't want them to exist here but never the way but i'm just trying to show you the concept of how we moved files from sharepoint to s3 bucket without using code and doing this through microsoft power automate and the way we were able to accomplish this is through setting up an aws transfer service which is an sftp site and there's a connector of course if we go back to powerautomate where as you can see we have we're using two connectors here sharepoint and sftp yes there is a connector for sftp um and again aws transfer service is really an s3 bucket behind the scenes it's literally doing the same thing right dropping it to um s3 bucket which is what i what we wanted to do here um but let's make a change i wanted to drop it to the inbound folder not the main folder so in order to do that we're going to go to get files under folder pass uh let's see if it shows because it's not really nope it's not showing which but let's try it out we're gonna do forward slash inbound right so that's what we're gonna do forward slash inbound i don't want to [Music] let's hit save let's make sure that it saves did it save yep there goes so now i'm going to run it again so let's run okay so again it should take about 40 seconds pretty much now what i'm expecting here is for files to there it goes it's already dropping boom yep that worked um so under initially whenever i put inbound under the root folder that's really wasn't the place to put it at it's after you save so that could stay blank and after you save it then you put it to your uh folder work section and then of course we could see files are starting to drop in here boom all three are here again that's exactly what we wanted to do so that's awesome then of course this should be done yep it's done 31 seconds again guys not hard at all pretty straightforward to set up you do have to set up aws transfer service first which is not that challenging to be honest with you um it wasn't that straightforward initially but it honestly again just follow the steps in my video it'll make it way easier you could literally do exactly what i did step by step to get it set up um this wasn't too bad this is a non-programmatic way again i have another i have a video that shows you how to move file from sharepoint to an s3 bucket using python but if you want to do it a non-programmatic way this is the way to do it exactly what i just did right now this is your option to do it i am going to have another continuous video that's part of this process and ultimately it's going to be a our complete pipeline which we're going to be ultimately what what it's going to end up doing is again taking file from sharepoint uploading to sftp which happens to be an s3 bucket behind the scenes then the next step is we're going to create aws glue jobs to take those files or csv files and load them to reshift that's going to be the next the next um video that i'm going to make that's going to show steps on what to do there hopefully this helps guys again give me a like give me a follow man i appreciate all the support and everything and talk to you later

Channel: I am Lu

Views: 14,825

Keywords:

Id: VW6gqVsvOeQ

Length: 30min 40sec (1840 seconds)

Published: Fri Jun 03 2022