How to Send Excel Table Data to a SharePoint List Using Power Automate | 2022 Tutorial

hey everyone Michael here with another video in this video I'm going to show you how to export data from an Excel table into a SharePoint list if you like Power automate powerapps SharePoint and teams videos feel free to subscribe because I'll be putting out tutorials in the future about these software all right so first things first is we need a SharePoint list to match our Excel data and the Excel file is going to need to be stored in a SharePoint document Library so for my file I just have um some sample data from the about my employees that non-exist so I have six rows of data five column names so to export this data we need every single column to be matched to the SharePoint list let's go ahead I created a employee data list already which has no data no columns so I will actually go ahead and rename these first name in the com headers don't need a match but I assume they're going to be pretty similar to which column headers are in the Excel document we got first name last name job title and the next one was start date so I'm making this a date field just have some variety besides single line attacks and the last one's gonna be a number with the salary so this one's going to have a dollar sign all right so it looks like our list is ready to go our Excel document is ready to go you just want to make sure the the data in your Excel table is in the table all my data's in the table one table and you'll need that so the list knows where to grab I mean the power out I mean knows where to grab the rows from and also just a side note make sure your connector I mean your files are xlsx or xlxb format or else you won't be able to use the Excel connectors so mine is actually an xlsx all right so we got both of these done let's go into Power automate and we will create the flow it's going to be an instant Cloud flow it's not going to be a schedule flow because if you try doing this with the schedule flow unless you have it to where it doesn't create new rows If the previous data already exist maybe I'll make a video on that but yeah you can just have end up with duplicate rows if you do that so we're doing an instant Cloud flow and I'll name Excel data to SharePoint create all right so the first step in this flow is actually going to be listro's president table for my location and this is why you want your Excel file to be in a SharePoint back in the library because it's going to ask you for a location on a document Library so the file employees and table it's going to look in this file and grab all the table names mine's table one and so we actually need to get the output from this this uh this action so we can parse Json in the next section I'll just run a quick test on this it's going to ask me to connect my Excel connector which is fine all right so I ran successfully I'm going to click to download I'm going to control a control C list to copy it and there's a bunch of data that I don't need so I'm actually going to work with the schema to make it a schema that I actually want to work with the parse Json and I'm grabbing this content from the value so it's a list of items lists rows present table in the scheme I'm going to generate from sample you're just pasting this data and click on done it actually has a bunch of information which I'll drag in my notepad plus plus all right as you can see this is the data that I actually want it's this this array right here I really don't want anything else besides this so it's going to be the value I'm looking for all this other data I don't really need so I'm just going to remove it and actually you can get an error if you don't like format this correctly so I will remove this value just so the curly brace with my type my type is an array type then a little list the items and this will be the column headers so you got the file a first name last name job title start date salary and I recommend you do listen notepad plus plus because it will actually line up the parentheses of how many you need as you can see the red matches the red up here and you can do this and the schema it will tell you if you make a mistake so if I remove this curly bracket it will show in a red air right here and so I need I know I need the curly bracket back so I'll add it back and it looks like my schema is good the type should be first on it'll list your items so new step I want to go ahead and do apply to each because I want to work with each item in this array from the parse Json and each item is going to be a separate row we'll go ahead and apply to each and I want to take the body from my parse Json because that'll contain all the items and then I want to go to create item with the SharePoint connector my list is in marketing employee data and then for my parse Json if you map it correctly it will show the previews in the dynamic content so first name last name job title start date and since this is a number field the salary it's going to show me previews of what it thinks I'm looking for but you can actually type in the dynamic content and search for salary all right so we'll go ahead and click on Save I believe it's your run I'm not really expecting any issues the Json looks correct and the source everything matches so if I try to run this on a recently used trigger it's actually going to fail because I added so I actually gives you this air the shared SharePoint online connector I couldn't find any value connection because on the previous run we did not have this SharePoint create action item and it didn't have this connection in it so it doesn't know where to get a connection for the create item that's why we have to rerun it manually so it knows that okay we have a new connection and it'll actually ask me for the connection over here so SharePoint we'll go ahead and press continue run flow done as you can see if we look at these ones but as you can see I know what that issue is so the issue is actually B item since we this this column is looking for a date it actually imported the runtime value 4486 as the start date and to correct this we have to go back in this Rose president table show Advanced options and the date time format so I tried using the serial number we don't want that we want the iso 8601 so it actually passes a date instead of a five was a five number character five character string so we'll go ahead and press automag we will rerun it and hopefully it works this time all right we'll just check the parse Json really quick so as you can see instead of that five digit shrink string it gave us the actual date now and it ran successfully so as you can see it ran six items so we should go into our SharePoint list and as you can see it wrote Every all six items successfully from this Excel table so the flow works the data copied over and everything looks good so that is how you send data from an Excel table into a SharePoint list I hope this video helps you out and if you have any questions or comments regarding the Json or like any errors you occur just leave them in the comments because this can get a little bit tricky nope any video recommendations feel free to comment if you like the video like it subscribe let me know how I did and I will catch you in the next one

Channel: Michael Alex

Views: 35,406

Keywords: Microsoft, Microsoft Forms, Forms, Microsoft SharePoint, SharePoint, Teams, Power Automate, Power Platform, Microsoft Flow, Flows, Automation, Tutorial, Guide, 2022, Microsoft Outlook, Outlook, Email, power automate, excel data to sharepoint list, sharepoint list, excel table, data, send data

Id: CgfFQKG2_Jc

Length: 9min 3sec (543 seconds)

Published: Wed Nov 23 2022