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.
  
  
  
  
• It will navigate to below page and click on continue.
  
  
  
  
• 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)
  
  
  
  
• As per the requirement configure Create file activity
  
  
  

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