Handling Date Time In Excel To SharePoint List


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.
    All flows
  2. It will navigate to the below page and click on continue.
    Save my email
  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).
    Click on new email
  5. As per the requirement configure Create file activity
    Requirement configure

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
    Add loop activity
  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.
    Filtering data
  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.
    Add condition action to check

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”
    Date Field
  • So before inserting it into the SharePoint list, we should
    Loop items from excel
    Calculating number of dates to ad
  • Value: sub(int(split(string(items('loop_items_from_excel')['LOCKED_DATE']),'.')[0]),2)
    Adding number of dates
  • Value: addDays(formatDateTime('1900-01-01T00:00:00'),int(variables('dummyForCompleteDateOperations')),'yyyy-MM-dd')
    Add days
  • Value: split(string(items('loop_items_from_excel')['LOCKED_DATE']),'.')[1]
    Set hour component
  • Value: split(string(mul(float(variables('dummyForCompleteDateOperations')),24)),'.')[0]
    Check hours component
    Condition not satisfy
    Calculating number of min to ad
  • Value: split(string(items('loop_items_from_excel')['LOCKED_DATE']),'.')[1]
    Calculating min component
  • Value: split(string(mul(float(variables('dummyForCompleteDateOperations')),24)),'.')[1]
    Set Variables
  • Value: split(string(mul(float(variables('LockedMinComponent')),60)),'.')[0]
    Check min component
  • 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'))
    Create item