Handling Date-Time in Excel to SharePoint List through MS Flow

In this blog, we will learn about handling Date-Time in Excel to a SharePoint List through MS Flow

Introduction

 
In this blog, we will try to solve the given business requirement problem.
 

Business Requirement

  1. When an email arrives with the attachment name “Excel.xlsx”, it should add attachments to the SharePoint Library.
  2. Take data from Excel and insert/update to a SharePoint list based on the Primary Key column
  3. Handling Date-Time from Excel to a SharePoint list
  4. Adding an email attachment to SharePoint Library
 
Please follow the below steps:
 
Step 1
 
Click on create and search for an email attachment. Select the highlighted flow from the below list.
 
image1 
 
Step 2
 
It will navigate to the below page. Click on continue.
 
image2 
 
Step 3
 
It will automatically render some flow actions, such as on a new email with an attachment, create a file 

Step 4
 
Click on a new email with the attachment action in inbox(I didn’t apply any filters in Outlook)
 
image3 
 
Step 5
 
As per the requirement, configure Create file activity
 
image4 
 
We have successfully completed the flow to add an attachment (Excel file) to the SharePoint library.
 

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

 
To read an Excel file from the SharePoint library, we need to add “List Rows Present in a Table” action. 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,
 
Step 1
 
Create on dummy loopTrack, stopUntil variable of int type. And assign loopTrack=0 and stopUntil=10.
 
Step 2
 
Add one do-while loop and configure as shown in the below screenshot.
 
Step 3
 
As shown in the following screenshot, configure “List Rows Present in a Table”. SkipCount should depend on the loopTrack variable
 
image5
 
Step 4
 
Once we added the above “List Rows Present in a Table” it will automatically have an output variable as “value”. Then, we need to iterate this for retrieving all rows.

Step 5
 
Add a loop activity and follow the below steps:
 
image6 
 
Step 6
 
For checking insert/update, add Get Items activity and configure as below. In my case, CASE_NUM is the primary key so I am filtering the data with that.
 
 image7
 
Step 7
 
Add condition action to check insert/update, if the total length of Get_Items activity is zero, then it will be inserted. If not it will be updated. Use length(body('Get_items')?['value']) to check count.
 
image8 
 

Handling Date Time from Excel to a SharePoint list

 
Step 1
 
By default, the date will be converted to text while reading the data from action “List Rows Present in a Table”
 
image9
 
Before inserting it into SharePoint list we should:
 
image10 
 
image11 
 
Value : sub(int(split(string(items('loop_items_from_excel')['LOCKED_DATE']),'.')[0]),2)
 
image12 
 
Value : addDays(formatDateTime('1900-01-01T00:00:00'),int(variables('dummyForCompleteDateOperations')),'yyyy-MM-dd')
 
image13 
 
Value : split(string(items('loop_items_from_excel')['LOCKED_DATE']),'.')[1]
 
image14 
 
Value: split(string(mul(float(variables('dummyForCompleteDateOperations')),24)),'.')[0]
 
image15 
 
image16 
 
image17 
 
Value : split(string(items('loop_items_from_excel')['LOCKED_DATE']),'.')[1]
 
image18 
 
Value: split(string(mul(float(variables('dummyForCompleteDateOperations')),24)),'.')[1]
 
image19 
 
Value: split(string(mul(float(variables('LockedMinComponent')),60)),'.')[0]
 
image20 
 
image21 
 
In add/update item, we need to use as shown below:
 
if(empty(items('loop_items_from_excel')['LOCKED_DATE']),null,concat(variables('LockedDateComponent'),'T',variables('LockedHourComponent'),':',variables('LockedMinComponent'),':00Z'))
 
image22