Import Excel data to SharePoint list with Power Apps #powerapps #sharepoint #excel #powerautomate

hey everyone welcome back to my channel so in today's video we will learn how we can import data from Excel to the power apps so this is my power app and I would like to import data to this power app and of course behind the scene I have SharePoint list that is connected to this particular power app but directly from here I would like to import data in bulk so I can click on this import data this going to show me a control where I can attach the file so this Excel file has multiple record I can drag and drop here and I can click import what now going to happen so power app is going to extract all the information from this Excel and import it directly to my backend SharePoint list and give me a successful message here so now I can see my data directly imported from Power app and it will be reflected to my app so we're going to create this process very simple and very useful so stay tuned [Music] welcome back and if you have not subscribed yet please go ahead and click subscribe button okay so let's start so you can start or you can create this import process to any of your existing app or you can create this into your new app that you are building currently what I'm going to do I'm going to pick my existing app okay so I have my app app and this power app is connected to a backend SharePoint list that is collecting different request for travel okay so first thing that we're going to do we're going to create a button right here for the bulk import okay so I'm going to add an icon here you can select any I icon that you want change the color okay and a text message that can say import data okay and I'm going to change the color so we can read the text okay so basic part is done now we have a button that telling us that okay if I click on this one the import data process can start okay now the second thing that we need we need the control where user can attach the file right so as you know there is no attachment control directly available in power app that can help us to upload a document and send it to the backend list so what we're going to do we're going to use a trick that is very useful so you just need to follow me along so add a blank screen on your app okay and here what we're going to do we're going to insert a form and this form should connect to your SharePoint list okay so if you don't have connection in your power app to any of the existing SharePoint list what you can do you can create a new connection here so I'm going to go to the data search for SharePoint and select any of the adjusting site and connect to any list that you prefer to connect to we're not going to use it for anything else so data source doesn't really matter you can connect to any list in your in your uh environment okay once you do that from the data source we're going to select the data source and the form the power app actually generates the form okay now here what we are most interested right now is this attachment control okay select the attachment okay go to the ADV once and we're going to unlock it okay and after you unlock select or click on this Central box that says attachment copy it contrl C go to your main screen your main app screen that you have and paste it okay and we can also resize it so that's all so now we got the attachment control that we going to work on okay now what you can do you can actually go to your screen that you just add it and remove it it's no more needed okay so this is how you can get the attachment control that you can use to build your import functionality now you may get some error that we can fix so you may get error like this so this is saying border color if you want to keep the color you can keep it or just delete it there is no item we're going to keep it as default and also the display name okay so you can just discard those error because we not really going to use any of those okay then what we're going to do the next thing go to the items okay and we're going to set as blank okay and then if you select the properties on the right hand side it's saying Max attachment six we're going to keep it to one because we want to import one file at a time okay okay so this part is done now what next we're going to do we're going to again go to the plus icon here and I'm going to add a rectangle icon here just for the background of it okay so under shapes we going to right click reorder send it to backward okay so we have our attachment control we have the background now we also need two button one to start the import process another one to actually cancel if I don't want to start the import process so we're going to insert two button over here okay so I have two buttons here this one is for import okay so so basic part is ready now one thing that we going to do on click off this button we want to show this and on cancel we want to hide this these controls right so go to this button that we have just created and on select we are going to set a variable show import to just reverse it so if it is true we're going to make it false and if it is false we're going to make it true okay and then we're going to select this rectangle the attachment control so all these four controls and select all these four controls and go to the visible property and we're going to set it to that variable that we just created okay and the moment you're going to do it they're not going to be visible so I'm going to play the app and click on this import and now you can see them and if I click again you can they blue disappear right but we also want to do the same thing on cancel so if I click cancel I also want to make them disappear right so cancel it what we are doing here you're going to repeat the same functionality on on select of this button okay so now if I do it it's getting hidden right so now we have the basic setup ready where I can see the form I can click on attach file or I can drag and drop file I can cancel if I don't want to so now let's focus on actual import functionality okay so what we want to do when we click import we want to get the content or the file that is attached here get that file and because power apps cannot directly going to update the item in the in the SharePoint list that is the back end list for this app we're going to send this file with other information to a power automate flow that flow will actually take the file get the content out of the Excel and update the backend list with all the row that we have in Excel okay okay so on import click what we want to do we want to call a power automate flow so I'm going to select the button go to the power automate icon and add a flow and create a new flow okay I'm going to start create from blank okay and you will you will go to the flow what we need to do we need to create an input so that we can ask the power app to send us the file and we have a file icon here so I'm going to select that file content please select a file or image okay one thing you also need to do make sure that this field is required okay and how you know this is required when you click on these three dots you should see make the file optional if you see this that means this is required okay okay so I just added one more temporary action here okay and as you can see here the flow is like this I'm going to edit this flow again and give a good name okay so name change I'm going to close this I'm going to add one more step that if everything that we're going to do inside this flow completed successfully then I'm going to just send a response back to my app the data is imported successfully so search for response okay and once you type response you can see power apps select that respond to a power app or flow select that add an output we are sending a text message response okay save it now I'm going to go to the button on Select Property and we're going to write a logic here so I'm going to set a variable okay this is just collecting whatever response we going to send from our flow okay then we going to call our flow so our flow is import Excel once you select the name it automatically going to say run okay and as you can see here it's saying the file we need to send a file a file content how we can do that so how you can send the file that you are going to have in attachment control you just need to write like this so start with one curly bracket content whites colon first and this is where you going to give the name of the control or the attachment control that we have so if I go here our attachment control name is data card value 10or 1 so remember you pick the right one okay dot attach attachments dot value okay so this going to give me the value of the content the second thing is the name of the file so name column same thing first data card do attachments do name okay and then last thing is response because this flow is going to give us the response that we have created close the bracket we format it for you so this is how it look like we are setting a variable that is a variable response then we are calling workflow this is the structure that we need to pass to send the file okay content byte is going to whatever attachment we have the value of it we are doing it first because the attachment control that we have can have more than one attachment even if we are saying maximum allow attachment is one but still behind the scene it can have a multi multiple value so you need to use the first to make it work okay same thing goes here attachments. name this going to give us the name of the file now I'm going to run the app now and we're going to drag one file and click import just to see the flow and how it is working so I logged into my power automate flow and we should see that power automate here and you can see one successful run because we just click it now let's go there and see how we are getting the data so look at this the name of the file this is the file that we imported and the content bite so we are getting everything that we need the only thing we need to do we need to get this file get the data out update the SharePoint list now we are going to work between the trigger and the response okay so click on plus icon here add action the first thing that we need to do so because we are getting these file content we cannot just start getting the data from these files we need to save that file temporarily somewhere so that we can read it okay so I'm going to use create file SharePoint create file so what we are doing we are temporarily creating the file the Excel file with that we just uploaded in a shint document Library we will create it use it get the data out of it and then delete it afterward okay so you can pick any of the document library that you want to use for temporary location okay and this is my document library that I'm going to use file content you already have so if you select you can see how the file content is coming in saying trigger body file content whites you can copy the same thing go to the file name go to the expression remove the Curly from the front and the end and instead of using content byes we're going to use the name because that's what we are passing right this is the name that we are passing click on okay so the file will be created now what we need to do once the file is created now we need to extract or connect or gas that file extract the information from that Excel and then start creating the item for each row in Excel okay so next action that we're going to use is list row present in Excel and always and remember one criteria that I think you should already know because we are using Excel power automate can only read data from Excel if it is in a table format in an Excel okay so the data that we're going to use should be inside an table so like as you can see here this is my template that I'm importing and this data is inside an Excel okay in listro present in Excel we're going to select the location location is my SharePoint site okay the document library is the one that we just used to create the file the file itself you can select once you click on the file you can select the file that we just created an ID so this is is going to be the unique ID of the file that we just created okay and the table from where the data is coming in so if you open your template that you will be using to import the data and if you go to that Excel and if you go to the table design you need to see what is the table name here so whichever name you give to your table in your template that you will be using to import that's the name that you should be using there so in my case it's table one so enter custom value okay so this will get all the list from the table the next action that we're going to do we're going to go through each row that we just got and start creating item into our SharePoint list so we are going to add a control that is for each so apply to each the value that we are getting from listro present in table okay and then inside this I'm going to use create item of SharePoint okay and site address list name that's the name of the list that your power apps is connected to so I have travel request that's my list name and once you're going to select the title it's not going to automatically give you the name because we are using the Excel so what we need to do we need to just put a logic here to extract the title and other column information and how you will do that so title expression items then it's going to ask you the loop name our Loop name is apply undor 2core each so wherever you have space you need to apply underscore so if in case you re if you rename this use that name or if you have other loop before and before this one it may be underscore each underscore one underscore 2 depending on the name you should use the same name and wherever you see a space you need to use underscore okay question mark okay and bracket single code and the name of the column in your Excel okay so whatever the name that you have in your in your Excel that's what we're going to use here okay so my column name is strip title and then I can use the same approach to pass information for other column like for example reason for travel I can select that go to expression and change the column name from here okay so I added bunch of columns that I have in my list and of course these columns will be different in in your case and you can use accordingly one thing to remember if you have dates in your Excel go to the list of present in the table show Advance option and in the date format select the right format okay so that date will be correctly imported into the showerpoint Okay click save and once we go through the each of the item in Excel before sending the response back we need to make sure that we delete this file because if we don't delete it number one thing this file is going to stay there and ALS if you try to rerun the flow again with the same file name it might not work so if you use the basic SharePoint delete action you may get a error saying the file is on hold or is locked right because the flow is using the file to read through everything so you might not be able to delete it with within 10 minute I believe that's the time but if you want to just complete the process within the same flow and don't want to put a wait you can use the SharePoint rest API to delete the file permanently without this going to recycle in as well okay so search for SharePoint okay and send an HTTP request select the site address the method is going to be the post okay and in the URI is going to beore API web list Cat by title the name of the library that you are using to Temporary save that file my library name is the data items and then you need to pass the ID and how you will get the ID is from create file so create file item id this is what you need to use okay this will be the URI then you also need to pass the header okay so you need to pass this header these different header values to make it work I'm going to copy this entire thing into the comment section so you can directly copy come here click on this button and paste it and this is going to be applicable irrespective of what is your library name and anything so you can easily copy it okay so now we have actually everything that we need what we're going to do we're going to upload the file and see if this will works or not okay so I'll go back to my app click on import I still have my file because I have not deleted it click import port and I can see my flow started and it's working as you can see and it's completed successfully so I have almost 19 item that I have just created into my SharePoint list from power apps and as you can see here the message also bent back to my power app but we have not actually displaying it so we will do that as if we take a look on the backend SharePoint list you can see all of these item are just got created and then we are going to add one text message here so we can see the display message as well okay so a label okay and this label is going to be the variable that we have just created the variable response okay and we can also change the color I'm going to save it publish this one okay so I just published the app and load it again and as you can see I have 1247 item we're going to try to import okay I'm going to drag the file click import let's wait for flow to run in the behind the scene and we will see if the data is actually changed okay so import as successful I'm going to cancel it refresh my app and as you can see here the item has changed from 1247 to 1266 perfect yes our import functionality is working as expected for this video this was all I hope this will help you thank you for watching and thank you for subscribing thank you

Channel: Deepak Shrivastava

Views: 1,779

Keywords: power automate, power platform, import excel table to sharepoint list, power apps tutorial, power automate excel to sharepoint list, import excel to sharepoint list using flow, powerapps, power apps, powerapps import excel to sharepoint list, powerapps import data from excel, power apps import excel, power apps upload, excel online power automate, power automate get tables, power automate list rows, power automate excel, power automate sharepoint

Id: hrQOvE68Wvo

Length: 20min 52sec (1252 seconds)

Published: Wed May 15 2024