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.

Step 2

It will navigate to the below page. Click on continue.

 

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)

Step 5

As per the requirement, configure Create file activity

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

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:

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.

 

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.

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”

Before inserting it into SharePoint list we should:

Value :

sub(int(split(string(items('loop_items_from_excel')['LOCKED_DATE']),'.')[0]),2)

Value :

addDays(formatDateTime('1900-01-01T00:00:00'),int(variables('dummyForCompleteDateOperations')),'yyyy-MM-dd')

Value :

split(string(items('loop_items_from_excel')['LOCKED_DATE']),'.')[1]

Value:

split(string(mul(float(variables('dummyForCompleteDateOperations')),24)),'.')[0]

Value :

split(string(items('loop_items_from_excel')['LOCKED_DATE']),'.')[1]

Value:

split(string(mul(float(variables('dummyForCompleteDateOperations')),24)),'.')[1]

Value: 

split(string(mul(float(variables('LockedMinComponent')),60)),'.')[0]

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