In this Power Automate tutorial, we will discuss how to get data from excel on SharePoint in Power automate. This is a complete example of Power Automate Get Data from Excel on SharePoint. So we will discuss the below points:
- Microsoft Flow import excel data into SharePoint list.
- Add or update Excel data to SharePoint list using Power Automate.
Microsoft Flow import excel data into SharePoint list
Here we will see how to import excel data into an existing SharePoint list using Power automate.
So here I have an Excel sheet i.e. Employee Information and then uploaded this Excel sheet in SharePoint Online Document Library. Also, you can upload an excel sheet to One Drive Business.
As we have an existing SharePoint list based on the Employee Information excel sheet.
So here we will create a Microsoft flow that will import data from Excel to the existing SharePoint list.
Read Power Automate remove characters from a string
Import excel data into SharePoint list using Power automate
Step 1: Create an Excel sheet
Create an Excel sheet for the Employee information, and make sure the excel file has a .xlsx extension.
Then create the below columns in the excel sheet of the Employee information.
- Full name
- Phone Number
- Date Of Birth
- Social Insurance Number
Then select the data in excel and change the data into table format.
Once the Excel spreadsheet is done, upload the excel file into SharePoint Document Library or OneDrive Business.
Step 2: Create a SharePoint list
Now in this step create a Sharepoint list that will be similar to the excel sheet column name and data type.
|Column Name||Column Type|
|Title||Single Line Of Text|
|Hire Date||Date and Time|
|Address||Single Line Of Text|
|Date Of Birth||Date and Time|
|SIN||Single Line Of text|
If you have an existing SharePoint list, check the list the column name and column type are similar to the Excel sheet.
Once the SharePoint list and excel sheet are ready, we will move towards creating a flow in the Power Automate.
Step 3: Create a Flow in the Power Automate
Now we will create a flow that will import excel data into the SharePoint list.
To create a Flow, log in to Power Automate and then click on Create -> choose Instant cloud Flow.
Now provide the flow name, and then select the trigger ” Manually trigger a flow”. Then click on Create.
Step 4: List the rows present in the Excel sheet
To list all the rows present in the excel table, we will create an action by clicking on the Next step. Then choose ‘List rows present in a table‘ -“Excel Online”.
Now provide the Location, Document Library, File, and Table values of the Excel sheet
Now in this step, we targeted the date data present in the excel sheet because excel dates don’t carry over the way we expect. So, we will use the variable to store the dates, and later we will apply an expression to do the conversion.
We will need two variables for importing i.e. varDate1 and varDate2, as our excel table contains two date columns.
So to initialize a variable, click on the next step then choose the ‘Initialize variable‘ action. Then provide the variable name and choose the type as String.
In a similar way add another initialize variable action for var Date2.
Step 6: Apply to each output value of excel
In this step, we will add Apply to each action to apply each output value of the “List rows present in a table”.
Step 7: Set the variable for the Date column
In this step, we will use the ‘set variable‘ action to set the variable for each of your date columns and set the values of each dates column.
Step 8: Compose the excel number column
In this step, we will use the ‘compose‘ action to take the input of the number column from the “List rows present in the table”, so later we will use the output in mapping.
Step 9: Create an item in a SharePoint list
Now we will add a ‘Create item‘ action to map the data from the Excel sheet to the SharePoint list.
So click on the next step, and then select the ‘Create item’ action. And then provide the site address and List name.
Then set the column to align with your excel data and for the Phonenumber column add the output from the compose.
But for the date column, we will add the below expression
- For hire date
- For Date of Birth
Note:As Power automate doesn’t support location column(SharePoint), therefore if the column information is necessary change the column type to Single Line of Text. So it will show in the ‘Create item’ action and you can map the data of excel table.
Once the Flow is ready, click on the save and run the flow.
Step 10: Run the Flow
Now to run the Flow click on the Save then Test the flow manually. So the flow will trigger manually.
As the Employee information table contains two information so, when the flow will trigger it will import the two pieces of information from the Excel table to the SharePoint list.
Once your Flow ran successfully, you can check the imported data in the SharePoint list.
Read Power Automate list rows present in a table filter query
Add or update Excel data to SharePoint list using Power Automate
Here we will see how to add and update the Excel data to the SharePoint list using Power Automate.
Here also we will use the same Employee Information excel sheet and SharePoint list. We will use the title column as a unique column.
So, we will update the item to the SharePoint list from the excel table, when the item is already present in the SharePoint list and also if the title in excel is equal to the SharePoint item title.
Otherwise, create an item in the SharePoint list if there is no item present and if the title of each item is not equal to the excel title field.
Add/update Excel data to SharePoint List using Microsoft Flow
Step 1: Create a Flow using Power Automate
To create a Flow, Log in to Power automate, then click on create -> Scheduled Cloud Flow.
Now provide the Flow name, then select the start date and time. Next choose repeat every 1 and day. So, this flow will run every day. Then click on create.
Step 2: List rows present in a table
In this step we will fetch all the data from the Excel table, so click on the Next step then select List rows present in the table.
Now set the Location, Document Library, File and table values in the “List rows present in a table”.
Step 3: Initialize the variable Date
So, in this step, we targeted the date data present in the excel sheet because excel dates don’t carry over the way we expect, so we will initialize variable per date column. So in Employee information list contain two date list, therefore we will initialize to variable i.e. varDate1 and varDate2.
Later we will use the variable to store the date from excel and then apply the expression to do the conversion.
So click on the next step, select the initialize variable, then set the name varDate1 and type String. Similarly, choose the initialize variable action and then set the name to varDate2 and type String.
Step 4: Apply to each Excel value
In this step, we will add Apply to each action which will apply to each output value from the “List rows present in table” action – “Excel Online”.
So, click on the next step then select ‘Apply to each’ action and then from the dynamic content select the ‘value’ – “List rows present in a table”.
Step 5: Set the Date variable
In this step we will set the date variable, so click on the next step then select the ‘Set variable‘ action.
Now set the variable for varDate1, from the value select Hire date from the dynamic content.
Similarly set the variable for the varDate2 and select the value Date of Birth from dynamic content.
Step 6: Compose excel number
In this step, we will use the compose action to set the Phone number because when we will map the excel number field to the SharePoint number column, Power automate doesn’t allow it.
So, click on the next step inside Apply to each, then select ‘ Compose‘ action. Set the inputs as a Phone numbers from the dynamic content.
Step 7: Get items from the SharePoint list
In this step we will Get items from the SharePoint list, so for this click on the Next step, then select the ‘Get items‘ action.
Now Provide the site address and List name of the SharePoint. Next, we will filter the items if the title column of SharePoint is equal to the Title column of Excel then only it will fetch the items from the SharePoint list.
Step 8: Condition to check the SharePoint list items
Here we will check that the Sharepoint list contains items or not, if the SharePoint list contains items then any changes are done in the excel sheet, that will be updated to the SharePoint list else the new items will be created in the list.
So for this we will check the length of the item in the SharePoint list, write the below expression is not equal to 0:
Step 8: Update item in SharePoint List
To update each item we will use apply to each loop, so click on the next step( in the If yes part of condition), then select ‘Apply to each loop’ action and set the value- “Get items- SharePoint”.
Next select the ‘Update Item’ action inside the ‘Apply to each SharePoint item’, and provide the Site address, List name, Id, and map the SharePoint Field with Excel field from the dynamic content. In the Phone number field map the output- Compose from the dynamic content
In Hire date add the below expression:
Then in the Date of Birth add the below expression:
Step 9: Create an item in a SharePoint list
If the condition does not match we will create an item in if no part of the condition.
So to create an item, click on the Add an action ( if no part) and select ‘Create item‘- SharePoint.
Now provide the site address, list name, and then map the SharePoint field with the Excel field. In the Phone number column map the output – Compose from the dynamic content.
In the Hire date column provide the below expression:
And in the Date of birth provide the below expression:
Once the Flow is ready, click on the Save and run the flow manually.
Step 10: Run the Flow
Case 1: No item in SharePoint list – Create Item
Now run the flow manually, as our SharePoint list does not contain any item, so power automate will create items.
Now the employee data present in the excel sheet is imported to SharePoint list.
Case 2: Update item in a SharePoint list
If we made changes in the excel sheet, for example, I change the department of Haripriya to HR, so now run the flow manually to see the changes. As our condition is true i.e. length of the item is not equal to zero, so the item gets updated.
Now you can check the SharePoint list item is get updated by the Power automate.
Case 3: SharePoint list contains items- Create item
Now if you want to change the title in the excel sheet, then the condition of ‘get items filter query” is not satisfied.
For example, I change the title Haripriya to Haripriya Dhall, so in this case, Sharepoint title column is not equal to Excel title column, power automate will create a new item in the SharePoint list.
And also you can check the SharePoint list the new item is created.
You may like the following Power Automate tutorials:
- Convert SharePoint document to PDF using Power Automate
- Microsoft Flow or PowerAutomate Example: Send a customized email when a new file is added
- Power Automate Forms to Excel
- Power Automate create an HTML table
- Power Automate Switch case
- Power Automate send an email with an attachment from SharePoint
In this Power Automate tutorial, we learned how to get data from excel using Power Automate. And also we discuss the below points:
- Import excel data into SharePoint list using power automate.
- Add/update Excel data in SharePoint list using Power automate.
Bhawana Rathore is a Microsoft MVP (3 times in Office Apps & Services) and a passionate SharePoint Consultant, having around 10 years of IT experience in the industry, as well as in .Net technologies. She likes to share her technical expertise in EnjoySharePoint.com and SPGuides.com
I am having an issue with names that have an apostrophe in them “Trent’s” failing the flow. However, the flow actually does succeed in adding and updating rows, it just fails the status of the flow. Any ideas?
This has been extremely helpful! I am having an issue with the date column. I need to be able to account for some of the data may not have a date in the field. Any ideas of how I can account for the empty fields in the date column?
Hi, I have tried to use the steps above, but I am still getting an error, due to the expected format not being correct.
“The ‘inputs.parameters’ of workflow operation ‘Create_item_2’ of type ‘OpenApiConnection’ is not valid. Error details: Input parameter ‘item/DatePaymentRecd’ is required to be of type ‘String/date-time’. The runtime value ‘”addDays(‘1899-12-30’,int(variables(‘44286′)),’yyyy-MM-dd’)”‘ to be converted doesn’t have the expected format ‘String/date-time’.
Here is how it still looks in the raw inputs: addDays(‘1899-12-30’,int(variables(‘44286′)),’yyyy-MM-dd’)
once again another guide that fails to include an absolutely necessary explanation because i followed this step-by-step and i am not getting this to work. so annoying. i almost had faith.
I am doing this very same flow, however when I submit a new line in my excel, all the previous dates change to the same date from the last submission..
I have excel with 12000 records Can PowerAutomate will process it one by one? or is there any way to process it as bulk?