EXCEL To SharePoint List Through MS Flow

In this article, you will learn about EXCEL to SharePoint list through MS Flow.

Business requirement

  • When Email arrives with Attachment name “Excel.xlsx” to Outlook it should add attachments to SharePoint Library.
  • Reading the data from Excel and insert/update to SharePoint list based on Primary Key column

Adding Email Attachment to SharePoint Library

 
Please follow below steps,
  • Click on create and search for Email attachment. Select highlighted flow from below list.

    EXCEL To SharePoint List Throw MS Flow

  • It will navigate to below page and click on continue.

    EXCEL To SharePoint List Throw MS Flow

  • It will automatically render some flow actions like On new email with an attachment, create file and condition for create file was a success or not.
  • Click On new email with attachment action and Inbox(I didn’t apply any filters in outlook)

    EXCEL To SharePoint List Throw MS Flow

  • As per the requirement configure Create file activity

    EXCEL To SharePoint List Throw MS Flow
We have successfully completed flow to add attachment (Excel file) to SharePoint library.
 

Reading the data from Excel and insert/update to SharePoint list based on Primary Key column

 
To read excel file from SharePoint library we need to add “Lost Rows Present in a Table” action, and this has the limitation to read the data 256 rows at one time. To resolve this we should loop all pages to read complete table rows.
 
Please follow below steps to read all rows,
  • Create on dummy loopTrack, stopUntil variable of int type. And assign loopTrack=0 and stopUntil=10.
  • Add one do while loop and configure as per the below screenshot.
  • As per the below screenshot configure “Lost Rows Present in a Table” and skipCount should depend on loopTrack variable

    EXCEL To SharePoint List Throw MS Flow

  • Once we added the above “Lost Rows Present in a Table” it will automatically having output variable as “value” and we need to iterate this for retrieving all rows.
  • Add loop activity and follow below steps

    EXCEL To SharePoint List Throw MS Flow

  • For checking insert/update add Get Items activity and configure as below, in my case CASE_NUM is primary key so I am filtering the data with that.

    EXCEL To SharePoint List Throw MS Flow

  • Add condition action to check insert/update, if the total length of Get_Items activity is zero then it will be insert, if not it will be update. Use length(body('Get_items')?['value']) to check count.

    EXCEL To SharePoint List Throw MS Flow

    EXCEL To SharePoint List Throw MS Flow