Power Apps import Excel to SharePoint list or any data source with Power Automate

in today's show we're going to use powerapps to import excel to a sharepoint list or other data sources so the idea here is that we're going to upload a file in our powerapp pass it over to power automate flow it's going to parse it out for its tables and then dynamically push that to our data source should be fun should be fast but first here's our intro hi my name is shane young with powerapps911 those guys and today we'll learn how to use powerapps to upload an excel file straight into a sharepoint list so we've done uploading files in general before right but now we want to do is let a user attach a share an excel file into our powerapp and then once they've attached it we're going to take it we're going to pull out the columns the tables and we're going to use that to populate a sharepoint or other data source doesn't really matter at that point and it's all with the magic of power automate so we can combine a couple concepts of videos we've done before to build a pretty cool little business solution should be fun so let's switch over to my desktop and take a look okay so what we've got here is just a simple powerapp where we're gonna upload an excel file so we're gonna let's hit the import this is using our technique for uploading files in general we're gonna browse here i want this one called a different name we're going to say open and so then what this is doing is this is passing this over to our dear friend power automate flow power automate flow is interrogating the excel file for the tables and then once it finds the tables it's in saying hey for every record in that table create a sharepoint item bingo bango it then returns we do a refresh the data source and you can see that the data was successfully loaded in here now from the excel perspective it's nothing too fancy all the excel file has done is it's got a table that is important the data has to be in a table and so you can see my table's name sales history right here and so when you do this right the data that comes in has to be in a table and you're going to want the table to be in a pre-defined format so we can't be dynamic we can't let them upload just any table and we'll figure out what the columns are and figure out where they go we're going to kind of need a repeatable pattern from our users that are doing this but as long as they're giving us the same table of data we can parse it and do what we need to it so how do we do that well let's look over here first at the flow so we'll say edit over interflow our trigger is first going to be just a regular powerapps trigger nothing fancy there very straightforward we're then going to do a create a file and so this is our standard way of uploading a file from powerapps to a sharepoint document library or onedrive wherever you want to put the file um and so if you've never done that before you'll need to know how that works so check out the video up above i will show you kind of in passing but we're not gonna get in the details of this whole base64 to binary and the json and all that but so what this does is this creates the file in sharepoint once we've created the file in sharepoint because we have to create the file before we can use the file then we're going to use the excel um on our excel for business action if you wherever those are called and we're going to get the tables from the file that we just created now this isn't going to work if you're building it exactly as we go but we're going to we'll build this by hand in a second so you can see how we would actually go through this this is how the working one works so we get the tables of the file we just uploaded and then we're going to go down here and say hey i need you to list the rows present from that file and from the the first table so i'm counting on the excel file only having one table if you had more than one table you'd have to add a bunch of tricky logic we're not going to cover that in the video i'm assuming they're uploading one excel file with one table with a predetermined set of columns because that's about the only way you're going to do this successfully now when you list rows and presents in a table make sure you expand show advanced options if you're using dates at all by default they're going to come out as serial dates which is like the number of ticks since like december 31st 1999 or 18 i don't know a long long time ago 18.99 i think and you don't want that most likely so what you probably want is to switch this to iso 8601 format doing this will save yourself a lot of trouble there so make sure you've you know expanded this and make this change so then now we've got all the rows present then we're going to loop through the list of tables and we're going to make sure that none of the fields are blank flow gets really grumpy if any of the fields are blank so i just did this simple little and you know name is not equal to nothing amount not equal to nothing date not equal nothing email etc so and i did this because in my original data set i actually had a blank row and i kept erroring i was like so i did that there's probably a more elegant way to do it but that worked for me then we're going to use the simple create an item action here and so here we're going to create that in that sales history sharepoint list right the where sales history at right here this level list and so this one as you can see you know just has those samples this is uh the title column renamed this is a currency column a date column and another text column no big deal there and then once we've built all that right we're just going to fill in the blanks boom now then the down here underneath this then what i want to do is i want to purge the file because i didn't want to keep the excel file we're just importing and deleting it but if you've ever tried to use the sharepoint delete file on something you've just operated against you probably know that you end up running into locks and so what i found was that there was someone um this blog post right here cleverworkarounds i'll put a link to this down in the description but so this one it shows you how to use the api to delete and the beauty of this is down here we're going to hit the api to delete the file and we're just dynamically getting the item id from the create file step above and we just do this prefer bypass shared lock boom they um it doesn't give you any error messages about the the file is locked or anything and then we're gonna respond to our power app and the reason we're gonna do this is because i want the power app to wait on this to finish because if we go look at our powerapp code where we're doing all this is the on fi add file of our attachment control so we're starting a setting a variable to true that causes a little spinner to load we're stripping down the um the file that is uploaded to get its raw base64 that's these two steps remember that's in that other video i linked to a minute ago and then we're running this flow to just say hey flow upload the file name and the base64 we just did once again that's also in that previous video we reset the attachment control and then we're using a refresh here if you watch my video you know don't ever it says don't ever use refresh this is one of those cases where you have to use refresh the reason for that is because unfortunately right there's no way for powerhouse doesn't know that your the flow is changing the sharepoint list but i need the new sharepoint list files to show up here my app so i have to manually do a refresh this is one of the very few occasions i permit using review or refreshes and then we turn off the spinner variable that is all it takes to do this right so simple oh and i apologize my voice is really little it feels terrible right now okay but so now that we've seen that right this is just a simple uh data table and this is just a little count rows and it's going a delegation row because count rows is not delegable but i just put that there so that way you guys can see that you know if i click this again and we upload that same file it'll work again and the key the reason it works again is because i delete the file when i'm done with it so i'm not having any conflicts there between this the same file being done only that when it's done it refreshes and now we've got 10 items where we just had five because there's five rows in there we're loading them five at a time cool cool okay so with all that said i want to talk a little bit more i guess before we do this i also remind you guys if you go out to training.powerapps911.com and sign up for the youtube library you can download this working app and this working flow right so if all this is too crazy you don't want to have to go right we're about to talk through then please just go download it but i want to talk about this because it is a little trickier these two steps are a little weird because they're dynamic so let's go over here let's just create another flow we're not gonna have to we're gonna have to make it that complicated we can make it a much more simple flow but we will say um create let's do an instant flow powerapps create and so when we do the create file right it is create file sharepoint there you go and so then we just you know this is easy peasy go find the site you want to upload it to what folder do you want to upload it to notice that my share my documents folder is called shared documents in some places documents and others it's real weird but here it's called share documents then what file name do i want to give it and so we just said ask in powerapps and then what file content do we want to give it and so we're going to say create file file name nope we're going to say see more ask in powerapps again but then we have to delete this out and then we have to use the whole base64 to binary parentheses and paste this in right oh but not that one i did the wrong one it's going too fast try again paste in file content say okay and i'll hover there for seconds you can see what it looks like but there you go that's that that's covered in the other video not getting in the mechanics there now what you want to do is you want to be able to find the tables you need to be able to dynamically find the table from the excel file so we're gonna do a new step we're going to search for excel excel online business and then we're going to say get tables because we're going to need the table name and in theory they can name the table differently if you can force them always name the same you don't have to do this step great now for the location we're going to manually go choose that sharepoint site so you have to scroll forever but somewhere in here is that right there and then here for the document library this time it's called documents so weird and for the file okay now we just talked about or you just saw on the other one what we want is the file we just created but if you choose this then you're not going to get any dynamic content right it's not going to understand the file and it's going to make the flow impossible to read so when you're writing the flow the first time you have to hard code the file so you need this file to already be in the sharepoint document library if it's not which it's currently not then i would go back out here to documents i'm going to manually upload the file and so then now the file is uploaded now we'll go back over here we'll browse again hopefully the browser is fast enough to find it there it is okay so we're going to have to put the file in here when i did a version of this before people got really confused by this but put a version in the file in for now we're going to undo it in just a second then you can do new step and then we're going to say excel again and this time it is list rows present in a table make sure it's excel business so same location there's powerapps videos document library same documents again file once again choose the file you have to manually choose it for right now we're going to make that dynamic later and then for table though see this is the beauty of hard coding the file right now it is finding the table because we need it to know what um what that is right we need it to be able to see that because we want to use the dynamic content so then the last piece here new step we're going to say sharepoint and then we're going to we're going to create item there it is okay so then this is my powerapps videos list name is sales history and just so we're all on the same page so we can see sales history over here we'll just do list settings real quick and you can see right title was renamed a customer name and then i just added order amount which is currency order date which is date and time and customer email which is single line text columns don't matter but i want to show you the columns i was using okay so then look now it sews those here and you can just map so i know from excel the name goes into customer name now when you add the first one it puts in a loop apply to each here and that's because it knows that list row spit out a table and so since it's in a table it knows that you know you want to do that so there's five rows in there you want to do this step five times right but then now you can just go in here order amount um and so if something doesn't show up order amounts not let's see if order date does order date does we can choose date and then customer email does great sometimes what happens is because order amounts a number and like excel and powerapps and share or power automate and sharepoint they're all confused right now it's not going to auto suggest order amount over here now if you type an amount though it absolutely will so just keep that in mind sometimes the columns won't show up it's not because they're not there it's because it doesn't think the right type it doesn't think it's the right type so it's not going to auto-suggest it but there you go this simple flow will then create the items okay now that you've done all of this right you've got all the pieces here then what i want you to do is now we can go back and make it dynamic but but we have to finish this because we needed these columns this dynamic content to show up this dynamic content wouldn't show up if we didn't do it exactly what we just did here so now you go by to get tables you can delete this and you're going to choose id not item id id let's pull that right there list rows present table same thing delete this what do we want we want not from get tables we want from here from create file we want the id again the table turns the gibberish okay so what you need to do now is you're going to say x and you say enter custom value and what you want is you're going to say i want the first table's name from here now if you just put name in here let's just do it so from get tables put name that will put you and apply to each we're not supporting an applied h we don't want multiple tables technically this would work but it feels it feels yucky to me so i'm going to delete this out i'm going to drag him out of there and delete that apply each okay so what we're going to do is we're going to go in here and we're going to say i want to write an expression we're going to do the first function that says give me the first record or the first row from a table which is what get tables was spitting out right so first one of those dynamic content so this is the value that came out of there then you're gonna go all the way the end after the closed parenthesis you're gonna do a question mark you're gonna do square brackets you're gonna do single quotes and then you're gonna do name and capital n all right we'll say control a control c we'll say okay and i'm just gonna paste this right here under add a note so it's easy to see but so that stops you around that apply to each loop that you didn't want anyway that just let us dr drill straight into that name but now that we've done that and that's you know this advanced option make sure that you set this to iso 8601 i almost forgot glad i didn't there you go and then now down here under create item these all still work so but if we tried to add ones this is still composing here if we do a compose and we were to say hey in the compose i want to see something from the the get tables and so look from get tables it doesn't know all of the or sorry let's row not get tables from the list rows where'd you go westeros so right here from west rose it doesn't know the names of your columns and the reason is because we now made the table name dynamic and the file name dynamic so it doesn't know what's inside of them so that's why we had to put the file there manually before we could do this but there you go we'll delete this composer we didn't really need it it was just a demo that would let you do all the things that we want to do um if you really wanted then remember we also i showed uh let's just go here open link in the tab and let's edit this one we just did a minute ago so we've built all the hard part and then we we use that delete uh delete file from the clever workaround and so i'm just gonna leave this here it's to send an http request but i'm not gonna get in the mechanics um their blog post does a great job of explaining it and i just use their blog post so you should too if you wanna add this but that's what the steps will look like okay so there you go that would get you everything you hoped and dreamed for that gets us the same exact flow we did the respond to powerapps or flow once again because we wanted to be able to wait until it got finished just make sure if you're going to do that if you have a really large file and it's going to take more than 90 seconds you could have errors there as long as you have small files it would not be a problem whatsoever and that does it right what do you think you like that you know it's a common or more common scenario than i think it is so if you have any questions any comments any ideas how i could do this better different or other video ideas leave them below i always read all the comments and i respond to these days there's a lot i get so i try to respond as means i can but yeah i think with that i'm gonna say thanks and have a great day hey me again before you go click on the subscribe button right join the list of hundred thousand plus people that subscribed already or if you need any help right check us out at powerapps91 we do big projects little projects we do training we do everything and we can help you or if you want to see more videos you probably do then just click on the playlist above cool thanks and have a great day

Channel: Shane Young

Views: 47,506

Keywords: Shane Young, powerapps911, PowerApps, Power Apps, powerapps import excel to sharepoint list, powerapps import data from excel, power apps import excel, powerapps excel, power apps upload, excel online power automate, excel business flow, power automate get tables, power automate list rows, power automate excel, power automate sharepoint, power automate import excel to sharepoint list, power automate import data from excel to sharepoint list

Id: MhFNwjaR10A

Length: 18min 49sec (1129 seconds)

Published: Wed Aug 31 2022