Handling Date Time In Excel To SharePoint List

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

Introduction

 
Business requirement
  1. When Email arrives with Attachment name “Excel.xlsx” to Outlook it should add attachments to SharePoint Library.
  2. Reading the data from Excel and insert/update to SharePoint list based on Primary Key column
  3. Handling Date Time from Excel to SharePoint list

Adding Email Attachment to SharePoint Library

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



  2. It will navigate to the below page and click on continue.



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



  5. 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 the Excel file from SharePoint library we need to add “List 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 the below steps to read all rows
  1. Create on dummy loopTrack,stopUntil variable of int type. And assign loopTrack=0 and stopUntil=10.
  2. Add one do while loop and configure as per the below screen shot.
  3. As per the below screenshot configure “List Rows Present in a Table” and skipCount should depend on loopTrack variable



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



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



  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 SharePoint list

 
By default date will be converted to text while reading the data from action “List Rows Present in a Table”
 
 
So before inserting it in to 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 per the below screen.
 
if(empty(items('loop_items_from_excel')['LOCKED_DATE']),null,concat(variables('LockedDateComponent'),'T',
variables('LockedHourComponent'),':',variables('LockedMinComponent'),':00Z'))