Add & Update Excel Data to SharePoint List using Power Automate | Excel Import using flow

hello everyone Reza here are you looking to add or update data from Excel to SharePoint then this step-by-step tutorial is for you we will begin right from creating the Excel template to adding rows to excel to loading that Excel file to SharePoint to creating a power automate flow to read data from that Excel file and load all that information into our SharePoint list which would include a wide variety of column types text Choice lookup person multi-select columns and more so let's check it out in action foreign [Music] I have a modern SharePoint site in which I have a list called job tracking the list tracks job information for construction work if I was to click on new it will load the form experience where I can add job information into my tracking list for example I can enter the title of the job select an engineer this is a person type column pick the status of the job it's a choice column tools is a multi-select choice column subcontractors multi-select person column job site Choice column due date is a date column start date is a date column that also includes the time aspect I have a URL column and then another text column that tracks the job number and then pick the type of the job which is a lookup column that looks up to another list and I'll click save this will create a record inside my SharePoint list the lookup column is looking up to a list called job types that has the list of all the types of jobs the scenario here is importing data from Excel into our SharePoint list and we want to automate that process in order to do that we need an Excel file that we are going to receive from users that will have information about Job tracking that will be loaded into a SharePoint document library and power automate will read those Excel files get every row of data from it and start adding it into this job tracking list first step is to create an Excel file that is based on a specific template now one option is directly go to excel create a table in Excel start creating our columns in there which are in line with our SharePoint list columns and then that template is what we can provide to users who can add information in there now a quick way of creating that template experience is by directly heading over to export and clicking on export to excel this will download a file for us if we were to open this file it will launch Excel and here it will try and add a data connection now this is a connected Excel file based on the SharePoint list I will go ahead and click on enable and you may be prompted to sign in to your Office 365 account it will give me the schema of my SharePoint list here are all the columns coming in from my SharePoint list and it also shows the data because it is connected to my SharePoint list since we are creating the template head over to table design and go ahead and click unlink so that we disconnect this from our SharePoint list I'll select ok the table here has a specific name associated with it I'll go ahead and rename this to job table here I want you to focus on a few specific columns person type columns it has exported it with the name of my user now if we were to import this data using power automate name is not enough we need something unique related to a user and the answer to that is their email address so here what I would do is I will change this to the email address of that specific user I've made that change next I have a multi-select choice column called tools now this is where you need to Define how you want to delineate your choice values when the user is plugging the information in now it's already delineated with semicolon and hash now you could keep that or you can make it more simple and I'll just keep it semicolon separated so when I am being provided the choice values they need to be semicolon separated subcons is a multi-select person type column so once again I would need the email addresses so I've plugged in the first email address I'll put a semicolon as my separator and I can plug in additional email addresses and then at the end here I have two columns called item type and path these are like the internal Columns of SharePoint so I don't need them as part of my template so I will select them right click and delete them since it's a template I only want the columns I don't want the rows in there so I can go ahead I can delete the rows that completes my template definition I will go to file go to save I'll give it a name job template and save it on my desktop since I want the users to leverage this template file in my SharePoint site and my documents Library I can go ahead and upload that template file users can come in download this template file start adding the data in there and then submit it to the job drop off Library that's where all the Excel files will be held so let's create that drop-off Library create a document Library I'll call it the job drop off Library this is where users will upload their Excel files based on that template in a document Library if you go to new it has this option you have to create files directly on the Fly one thing we can do right here is ADD our own templates so if I click add template I can upload that job template document that I had right to this Library so that when users go to new they have the template available right here you only want users to work with job templates in this Library we can go to edit new menu and uncheck all the other options and just leave job template as one of the options so now if I come into this Library I can go to new click job template and this is where I can start adding my data that I would like to import now as part of the template the key here is to make sure that the users provide information relative to our SharePoint list let's take one example job status is a choice type column and the only three options that need to be provided are new completed and in progress however in the Excel file job status is a text field so I can put in a status that does not even exist in my system so to ensure that you're getting clean data from users you can add data validations right inside Excel as an example I can go to data data validation here I can pick allow list and I can provide a list of values which are new completed and in progress and click ok the user will get a list picker option here where they can pick the desired values and these are in line with our SharePoint list I have leveraged my template and plugged in information and that file is what I will upload in my job drop off Library in power automate I will create a scheduled Cloud flow I've given my flow a name and this flow is scheduled to run every day at 8 am I'll click create I'll add a new step we need the Excel files that are placed in this job drop off library in SharePoint so I will use the get files properties only SharePoint action I'll pick my SharePoint site I will pick my document Library this will give me all the files in that Library Next Step Loop through every file that it has captured from this document Library from Dynamic content I'll pick the list of items now I need to read the data within those Excel files and for that we will add an action and from the Excel online business connector I will pick list rows present in a table the location you can either pick your SharePoint site or head over to enter custom value and plug in the URL of your SharePoint site the document Library will be the job drop off Library and the file that we are pointing to from Dynamic content I will leverage full path since we are dynamically pointing to a file in our document Library the table drop down will not load any results however in our library the files that are coming in are based on a template the template has a table which we had called job table for table I'll go to enter custom value and paste job table I will add another apply to each Loop this time to Loop through all the rows of my Excel file I will rename this to apply to each row the loop will be on the dynamic content value which has all the list of items or the rows in our Excel file and in this is where we would like to add an action for SharePoint to create item the URL of my SharePoint site the name of the list where we want to create the item it's job tracking and this will now load up all the columns related to my SharePoint list next step is to start mapping the columns that we have in our Excel file to our SharePoint list columns title and SharePoint is a text column in order to get the current rows title column if we look at Dynamic content it is not providing Dynamic content for the Excel connector information and the reason is because we are dynamically loading data from the Excel file so what we need to do here is we need to play with expressions to do that I will head over to the expression tab to get the current running item in my apply to each row Loop I will use the function item and in this I need the column title the syntax we use is square brackets and within the square brackets under single quotes you will plug in the name of that column and that name has to match the name of the column in the Excel file you can always put a question mark before this question mark meaning if for some reason the Excel file did not contain the title column it will plug in the value empty otherwise the flow will break and throw an error I'll click OK this should put in the value of that title column for that row next column is job ID so same steps as the title column we select job ID go to expression item off in single quotes I need the exact name of this column so I'll copy it and paste it in here and click ok the column is engineer it's a single select person type column the create item action gives engineer claims it's expecting the claims token to be plugged in right here now if I open the drop down it won't give me any data but I can head over to enter custom value and once again plug in an expression here I need the email address of the engineer the Excel file has that in a column called engineer I'll click ok job status single select Choice column which has three Choice values in my Excel file the column is called job status if you open the drop down it will provide you the standard three values but our data is coming from the Excel file so we need to write that expression I'll head over to enter custom value go to expression and type in item off my column name job status job site is also a choice column here's job site enter custom value plug in the column name from our Excel file due date is date only start date and end date is date and time key thing to understand is when the data is returned from list rows present in a table action it Returns the dates in numeric format and previously we had to do a lot of calculations to get the accurate date value the advantage now is if we head over to show Advanced options for the list rows action we have something known as date time format here we can pick ISO 8601 it will return the date and time in that specific format and it will be in UTC coordinated universal type for all my date columns I can leverage the same technique item of due date start date and end date notes is a multi-line text column simply write the same expression for notes and info is a URL type column I will leverage the column coming in from Excel which is info now let's look at some challenging column types multi-select Choice column which is called tools value in my Excel file the column for tools the multiple choices are provided as follows and they are semicolon separated to plug in those values it's very important to understand the format that this create item expects so if I was to open this drop down here and let's say I randomly pick one choice value let's say hammer and I click on add new items let's pick another one I pick gloves if you were to switch over to input entire array you will observe the format in which this specific column expects its data it needs to be an array that has an object with a key value and then it has the text related to the choice option and SharePoint so we need to create this array data based upon this text information that's coming in from our Excel file column tools to do this I need to take a step before create item action I'll add add an action I will Leverage a data operation function called select the from property expects an array now I need to convert this into an array and to do that I need to split it based on my delineation character in my case I defined that as semicolon so for the select action I will write an expression split item off tools tools is the name of my column in my Excel file this I want to split with semicolon this will return an array of data the create item action expects a key called value and then it needs the actual text value so in the map property I will Define value that's my key now select it's an array so when you're mapping I can again use that item property in my expression and this time it will map to the current running item in this select action which is nothing but the value the values that it has obtained from my split function this action I will rename to select tools and now since I want the create item action to be performed after select tools I will select this drag it and place it right below this in create item where I have my tools I will remove all of this and you need to make sure that you have switched over to input entire array right here is where I can pick the output of Select tools on similar lines for multi-select person type columns if you look at my Excel file I have my data once again semicolon separated this since I want to look at the format I'll go to enter custom value and enter some sample data I'll enter a add another item go to enter custom value add B and let's switch over to the input entire array and this is the format that this specific column expects it requires an array this time with the key claims which would have those email addresses I will add an action before create item I will pick my select action the from property would be the expression split on my column called subcons split by semicolon this will give me the array of email addresses and I need to create the key needs to be called claims and the value will be the current running item I'll rename this to select subcons and the create item action for subcons I'll pick the output of Select subcons lookup column called job type here it expects the ID of the job type if I was to manually open the drop down it will list out all those lookup column values from SharePoint however we need the data that's coming in from our Excel file and in the Excel file job type is coming back as text I don't have the IDS of the job types in SharePoint in my job types column I'm looking up to the text however every SharePoint list has a column called ID which maintains the internal unique ID this is the ID that I need in my Excel file I can't expect my users to enter or know the IDS they would enter the text sort of map to those IDs directly in flow right after get files I will add an action called get items get items from my SharePoint site and the list now that I will point to is job types I will rename this to get job types inside my apply to each row before I am creating the item I will add an action filter data operation filter array I want to filter my array of job types Dynamic content the value property coming in from the get job types action where the title column of this array to get that I'll head over to expression item off title is equal to here I need the job type column value for my Excel row that's running in the loop however filter array itself is running on a collection so if I use item there will be a conflict so at this moment I need to use the items of apply to each row to get that if I search for current item apply to each row has this current item property so I'll select this it will plug in this formula here do a Ctrl a control C to copy this I will close this now head over to expression and paste what I copied I'll remove the add symbol at the beginning items of apply to each row that's the formula to give me the current item context of the running row here I will put a question mark and plug in the name of my column which is job type in my Excel file and click ok for create item I will go to enter custom value and write the following expression body of the name of my action is called filter array so filter spaces are replaced by underscores when you're using the body attribute array question mark I'll pick zero to give me the first row index and then from this I need the ID so as you can see there's a lot of work involved for me to get that lookup column ID value from my SharePoint list I will go ahead and click on save to save my flow my flow has been saved the flow Checker does give me a warning that says you're trying to read data from the job types list but you are not applying any queries so you might have to take into account some performance considerations my lookup column has a limited set of data so I'm not too worried about that this flow is scheduled to run every day at 8 am for now I will manually run it so we can look at the output of the slope I'll click on run and click on run the flow the moment I do that the flow has triggered and the flow has completed if I go to my job tracking list I can see three new records that got created all this information has flown in from my Excel file directly into my SharePoint list sample build 2 has this URL information in this info column I'll set this to blank and let's take sample build three I will take my multi-select choice column and make it empty I'll close my Excel file let's go ahead and run the flow again now my flow run has completed but the status is failed if I was to select my flow run and look at where this has failed if I go to apply to each row and go to the next failed item the create item action has failed and the error message here is the info column is required to be of type string URI the runtime value which is empty is not in the expected format and if we look at our SharePoint list only two rows got added it did not add sample build 2. and the reason is because I made this column empty so there are going to be scenarios like this that you will have to handle empty values in the info column I will remove the value go back to expression I need to check if the incoming value is equal to blank so I'll use the equals function my incoming row value for column info is this equal to empty string if it is then I want to put the value null else I will put the value of that info column so I had to add this null check to my existing process I'll click OK and save my flow I have gone ahead and cleared up my job tracking list let's run the flow again the flow this time has completed successfully and if we look at our job tracking list I have all the three records come in and the info column for record number two has come in as empty let's focus on the start date column for sample build 2 it is the first of March at 10 pm if I look at my Excel file and look at the start date that I entered for sample build 2. here is the 2nd of March at 6 am my date and time has changed SharePoint showcases dates and times based upon the regional settings of the site if I go to settings go to site information and view all site settings under site Administration we have Regional settings my site is set in Pacific time Pacific time is UTC minus eight so it goes eight hours behind if we observe the start date and time here it is 10 pm on the 1st of March here it is 6 am on the 2nd of March if I convert this which is UTC and go back 8 hours it is showcasing the correct date and time because this is showcasing in Pacific time zone however maybe my users are entering the data in Pacific time and I wanted this exact same date and time to be showcased by SharePoint now when flow is reading this column value it's reading it as UTC so what we need to do is we need to have a column wherein the user can enter their desired date and time but then it converts it into UTC right and Excel itself where I have start date I can copy the cell and I can paste it right next to this I will call start date and UTC and here I will write the following Excel formula equal to the cell number that I'm pointing to which is J2 in my case Plus I will use the time function to add 8 hours to this so this now converts it into the UTC experience and this I can simply select and just drag down so it applies it to all of the other ones now in my flow that I was plugging in the start date I will change my expression to use start date UDC I'll save my flow let's run the flow the flow has succeeded my three items are in the start date that I entered for sample Bill 2 was the 2nd of March at 6 am now if we look at that value in SharePoint it matches now the beauty of this approach is that you can add multiple files in here I've added a second file in here as well and this file too has a lot of data as you can see if I was to go ahead and click on run my flow it will now Loop through all the files in my drop-off library and it will start loading all that data into my job tracking list common question would be how much data can I load can I keep uploading as many files and as much data as I want are there any limits the answer is yes there are limits if we look at the list rows present in a table action if I click on the help icon here and in the panel on the right go to learn more this will take us to the documentation for the Excel connector action list rows present in a table and the first thing I want you to do here is go to General known issues and limitations the maximum file size has to be 25 Max you cannot exceed that when flow works with this action the Excel file may be locked for an update or delete operation for up to 6 minutes since the last use of the connector currently in my drop-off Library all I have is files being dropped in but I would like to add a status column to know what's the status is it currently in progress is it processing it is it complete has it failed for some reason to do that I would have to update the property of the file if I try and do that without waiting for six minutes in my flow it will throw an error that the file is locked so I have to keep that in mind as well another key aspect is list rows present and a table by default returns 256 rows and after that if you need more rows you have to turn on pagination so if you look at this action here go to settings we have pagination that you would need to turn on there is a reason why it only loads 256. that's because that connector or that action is defined for good performance up to 256. the moment you start going beyond that it has to start paginating so in cases where your Excel file is large it will start performing slow in fact if it goes past 5000 kind of almost goes to a standstill so key is not to keep loading too much data in fact if you are able to keep your data within 256 per file like in my case here I can add as many files I want into the drop-off Library the flow will go pick it up one by one and start importing the data just as an example my last flow run was a minute and it imported 103 rows we can speed this up further by going to settings and turning on concurrency and going to Max concurrency so it will create 50 different threads wherein it would try and add rows on the Fly for a drop off Library currently we are not maintaining any status every time the flow runs it's going to pick up all the data and move but that's not what we want here I want to create a status column so I'll pick choice I'll name the status and I'll provide three choices new completed and in progress and by default I'll keep the value new I've gone ahead and cleared the data in this Library and I will upload a couple of files in here the status of both of those files is new now in our flow it locks that Excel file for six minutes I'll add an action first below this update file properties pick my SharePoint site pick my document Library and the ID will come from the ID property of my get files action since that's what I'm looping through here so that'll give me the context of the current running file and for this I will change the status here to in progress list rows present in a table I will move it below this so first I will update the property of the file to in progress and then I will start working on it now once it completes a file I would like to set the status of that to complete it and to do that I will copy this action and after the loop for adding the rows to SharePoint I will paste this action again and this time I will change the status to completed now before this I need to ensure that I wait for at least six minutes so I will insert a step there is a delay action and flow so I'll pick that and here I will delay it for six minutes the advantage of doing this is the user can see the status of the file live plus because the status for those running files would have changed the next time the flow runs we would need to ensure that the flow only picks up those files where the status is new and to do that in the get files action under Advanced options I'll enter a filter query status is equal to new so all of the logic below will only be performed if the status of the file is new and we can test this out I'll run the flow the flow will Loop through all the files in my job drop off library that have the status new once it picks a file and it is working on a file it will change the status of that file to in progress and we can see that in action right here so if I head over to the job tracking list it will now start populating this list with all the data from that file and once all of that data is loaded it will have to wait for up to 6 minutes and then it will move to the next file and so and so forth and once my flow completed we can see the status of both the jobs completed and all the data has come in to my job tracking list I will upload an Excel file job data 3 status is new the status of the file is in progress and the data will start loading into my job tracking list now this Excel file job data 3 had three rows in there and all those three rows have been added to my SharePoint list now let's say job ID is that unique identity of my system and if there is another Excel file which has rows that match the job ID I do not want to create a record instead I want to update that existing record so here I have another Excel file wherein job J1 and j3 the status completed and for job J2 I have gone ahead and updated the notes and I have a new role coming which has the job ID j4 so three job IDs that are already in my system so I would like to update them with the data that's plugged in this Excel file and j4 which is a new record needs to go in as a new row in my SharePoint list I've uploaded that Excel file in my drop-off Library and the change that we need to make in flow is as follows the point at which I was creating an item right before this I need to check if that job ID is already present in my SharePoint list and to do that I would have to query SharePoint so I'll add an action get items from my SharePoint list my construction site my job tracking list sure Advanced options I need to enter my filter query here every column in SharePoint has something known as the internal name the easiest way to get to that is go to settings and list settings here's job ID so I'll select this and in the URL right at the end where it says field here is that internal name for my uni column which is job ID so I will copy this go to my flow job ID is equal to it's a text column so in single quotes I need the value of the job ID in my current running item of my Excel file so I'll copy the name of this column and here right in between the single quotes plug in the expression item of job ID I will add an action I'll add a condition to check if the length of here I will switch to Dynamic content and pick the list of items that CAD items returns if the length of those items is equal to zero then I would like to create an item otherwise I would like to update right here I need the ID of the item that I want to update the get items action will return me an array under expressions I will plug in outputs of get underscore items there is a space here that's why underscore question mark there's a property called body question mark a property called value that gets me all the values that it returns now this is an array I need the first value of the array so I'll use 0 and from there I need that ID column from SharePoint which has the internal Name ID all caps after this all of those values here are going to be exactly the same as I did for the create item action and once I have that defined I will save my flow and I've started the Run of my flow status has gone to in progress and as the flow runs we can see how on the Fly the items that have those existing job numbers are being updated J1 job status went to completed j3 also went to completed the notes for J2 were updated to working on it and we also now have j4 that was the role in the Excel file that did not have a matching job ID and SharePoint so it went ahead and created the item you can also add error handling features to that flow for example here's my job drop off Library I have my completed status which is if all of the items in that Excel file are successfully added or updated in SharePoint I have a success log which puts in all those job numbers in cases where they did not pass for example years job data 3 there were failures if I go to the details for this and look at the failure log I can see that the only failure was job number j3 and that's the error that flow caught which is the info column which is My URL type column is empty it is expecting a value adding validations means adding more complexity into your flows here I have multiple Scopes one is for creating an item if it fails I get the error value from it and plug it in an error log if it succeeds I add that job ID to my success log variable in my flow experience that's for create item I do the same thing for update item once all of my items are processed I then go and initiate a delay and then I check to see if there are any errors that were captured if there is no error then put the status as complete put the success log for all the items that were processed if there was even one error then go ahead and put the success items and the field items so the users have a log of the outcome of every record in that Excel file if you enjoyed this video then do like comment and subscribe to my YouTube channel and thank you so much for watching

Channel: Reza Dorrani

Views: 417,843

Keywords: Power Automate, Excel, SharePoint, import excel table to sharepoint list, power automate excel to sharepoint list, flow, power automate excel, update sharepoint list with excel file, reza dorrani, import excel to sharepoint list using flow, excel, add excel rows to sharepoint list, how to, update sharepoint list from excel using flow, microsoft flow excel, update list from excel to sharepoint, import excel to sharepoint list office 365, power automate update sharepoint list from excel

Id: uEZI_b1Gs-k

Length: 41min 50sec (2510 seconds)

Published: Mon Mar 07 2022