Power Automate Get Data from Excel on SharePoint

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
  • Department
  • Manager
  • HireDate
  • Address
  • Phone Number
  • Date Of Birth
  • Social Insurance Number

Then select the data in excel and change the data into table format.

power automate get data from excel
power automate get data from excel

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 NameColumn Type
TitleSingle Line Of Text
Department Choice
Hire DateDate and Time
ManagerPerson
AddressSingle Line Of Text
Phone NumberNumber
Date Of BirthDate and Time
SINSingle Line Of text
Employee Information List
power automate get data from excel sheet
power automate get data from excel sheet

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.

Microsoft flow get data from excel
Microsoft flow get data from excel

Now provide the flow name, and then select the trigger ” Manually trigger a flow”. Then click on Create.

Microsoft Flow import excel data into SharePoint list
Microsoft Flow imports excel data into SharePoint list

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”.

Power Automate update SharePoint list from Excel
Power Automate update SharePoint list from Excel

Now provide the Location, Document Library, File, and Table values of the Excel sheet

microsoft flow update sharepoint list from excel file
Microsoft flow update sharepoint list from excel file

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.

power automate get data from excel on SharePoint
power automate get data from excel on SharePoint

In a similar way add another initialize variable action for var Date2.

power automate get data from excel table
power automate get data from excel table

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”.

power automate get data from excel one drive
power automate get data from excel one drive

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.

power automate get data from excel one drive
power automate get data from excel one drive

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.

Import data from Excel to SharePoint list
Import data from Excel to SharePoint list

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
addDays('1899-12-30',int(variables('varDate1')),'yyyy-MM-dd')
  • For Date of Birth
addDays('1899-12-30',int(variables('varDate2')),'yyyy-MM-dd')
power automate get data from excel sheet
power automate get data from excel sheet

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.

import data from excel to sharepoint list power automate
import data from excel to sharepoint list power automate

Once your Flow ran successfully, you can check the imported data in the SharePoint list.

How do I import Excel data to SharePoint list with power automate MS flow?
How do I import Excel data to SharePoint list with Power automate MS flow?

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.

Update Excel from SharePoint list using flow
Power automate 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.

Update Excel from SharePoint list using flow
Update Excel from SharePoint list using flow

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.

microsoft flow update sharepoint list item from excel
microsoft flow update sharepoint list item from excel

Now set the Location, Document Library, File and table values in the “List rows present in a table”.

Add & Update Excel Table Row from SharePoint List
Add & Update Excel Table Row from SharePoint List

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.

Update existing SharePoint list from Excel using Microsoft flow
Update existing SharePoint list from Excel using Microsoft flow

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”.

power automate update sharepoint list from excel table
power automate update SharePoint list from excel 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.

power automate update sharepoint list from excell llist
power automate update sharepoint list from excell llist

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.

power automate flow update sharepoint list from excel
power automate flow update sharepoint list from excel

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.

Add & Update Excel Table Row from SharePoint List
Add & Update Excel Table Row from 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:

length(outputs('Get_items')?['body/value'])
microsoft flow update sharepoint list item from excel
microsoft flow update sharepoint list item from excel

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:

addDays('1899-12-30',int(variables('varDate1')),'yyyy-MM-dd')

Then in the Date of Birth add the below expression:

addDays('1899-12-30',int(variables('varDate2')),'yyyy-MM-dd')
How do I automatically update a SharePoint list in Excel?
How do I automatically update a SharePoint list in Excel?

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:

addDays('1899-12-30',int(variables('varDate1')),'yyyy-MM-dd')

And in the Date of birth provide the below expression:

addDays('1899-12-30',int(variables('varDate2')),'yyyy-MM-dd')
Power Automate Excel date to SharePoint list
Power Automate Excel date to SharePoint list

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.

Flow excel date to sharepoint
Flow excel date to sharepoint

Now the employee data present in the excel sheet is imported to SharePoint list.

power automate get data from excel
power automate get data from excel

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.

microsoft flow update sharepoint list item from excel
microsoft flow update sharepoint list item from excel

Now you can check the SharePoint list item is get updated by the Power automate.

power automate get data from excel sheet
power automate get data from excel sheet

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.

Add or update Excel data to SharePoint list using Power Automate
Add or update Excel data to SharePoint list using Power Automate

And also you can check the SharePoint list the new item is created.

Add or update Excel data to SharePoint list using Power Automate
Add or update Excel data to SharePoint list using Power Automate

You may like the following Power Automate tutorials:

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.
>