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.

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
Department	Choice
Hire Date	Date and Time
Manager	Person
Address	Single Line Of Text
Phone Number	Number
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

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

• For Date of Birth

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

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:

length(outputs('Get_items')?['body/value'])

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')

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')

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.