Handling Date Time In Excel To SharePoint List

Introduction

Business requirement

1. When an Email arrives with the Attachment name “Excel.xlsx” to Outlook it should add attachments to SharePoint Library.
2. Reading the data from Excel and inserting/updating to SharePoint list based on the 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 the 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 an attachment, create the file, and condition for creating file was successful or not.
4. Click On the new email with the attachment action and Inbox (I didn’t apply any filters in Outlook).
   
5. As per the requirement configure Create file activity
   

We have completed the flow to add attachments (Excel files) to the SharePoint library.

Read Excel data, insert/update in SharePoint using Primary Key

Reading the data from Excel and inserting/updating to SharePoint list based on the Primary Key column

To read the Excel file from the SharePoint library we need to add the “List Rows Present in a Table” action, and this cannot 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, stop until variable of int type. And assign loopTrack=0 and stopUntil=10.
2. Add one do-while loop and configure it as per the below screenshot.
3. As per the below screenshot configure “List Rows Present in a Table” and skip-count should depend on the loop track variable
   
4. Once we add the above “List Rows Present in a Table” it will automatically have the 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 the primary key so I am filtering the data with that.
   
7. Add condition action to check insert/update, if the total length of the 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 the action “List Rows Present in a Table”
  
• So before inserting it into the 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 items we need to use the below screen.
• if(empty(items('loop_items_from_excel')['LOCKED_DATE']),null,concat(variables('LockedDateComponent'),'T',
• variables('LockedHourComponent'),':',variables('LockedMinComponent'),':00Z'))